Reaching the end of my rope with Access

"Pieter Wijnen"
it.isi.llegal.to.send.unsollicited.mail.wijnen.no spam.please@online.
replace.with.norway wrote in
:

First Name is not a wise choice for a Control Name you should
change that

The reason for that, as Pieter knows, is that "Name" is a reserved
word. Access often ignores this and manages to do what you expect,
but sometimes it fails to guess correctly what you mean, and you end
up with things that don't work. Because of that, it's best to avoid
using reserved words as names for objects in Access.

Private Sub NameDropDown_AfterUpdate() ' No need for a button
really
Dim RsC As DAO.Recordset

If VBA.Len(Access.Nz(Me.NameDropDown.Value, VBA.vbNullString))0
Then
Set RsC=Me.RecordsetClone
RsC.FindFirst "[Name] = '" & Me.NameDropDown.Value & "'"
If Not RsC.NoMatch Then
Me.BookMark=RsC.BookMark
End If
End If
Set RsC = Nothing
End Sub

I always say this in response to bookmark navigation posts, but I've
never understood why one would set a recordset value like this
instead of using WITH:

With Me.RecordsetClone
.FindFirst "[Name] = '" & Me.NameDropDown.Value & "'"
If Not .NoMatch Then
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
End If
End With

(I've also added in saving the current record before moving to the
Bookmark, because there are cases in which the data won't get saved)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

=?Utf-8?B?U2hhZWw=?= wrote in
:

I don't get it. I can't imagine that Access should be this
difficult to master.

It isn't difficult, but for some reason *you* are making it
difficult.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

And what we've been looking for the whole time is

Me.RecordSource="SELECT * FROM QUERY1 WHERE FULLNAME ='" &
Me.NAMEDROPDOWN.Value & "'"

Dooh

Pieter


"Rick Brandt" wrote in message
...
Shael wrote:
All I want to do is search for the record in the query where the value
selected in the drop down = FullName in the query and display this
record in the form. In SQL-speak, this is:

SELECT * FROM QUERY1 WHERE FULLNAME = NAMEDROPDOWN

Yes but then NAMEDROPDOWN does NOT belong in the query's output (it is
criteria) and thus that control should NOT be bound to a field in the
query.

By the way what you are doing is "filtering" using the criteria of a
query, not "searching". Searching normally involves having a form open
showing multiple records and having a mechanism that navigates you to the
desired record. If the query only returns the record matching
NAMEDROPDOWN then that is not an actual search (as the term is normally
used) and I think that is what is one thing that has been confusing this
thread.

If you want a ComboBox that will do a "search" then the ComboBox wizard
will build one for you in about 15 seconds. If you want to code an
unbound ComboBox that applies a filter that would also be very simple to
do. Having the form bound to a query that uses the ComboBox as crtiteria
is another way to accomplish this, but in ALL THREE methods the ComboBox
would not be bound. A bound control on a form is used to change data and
that is not what you want this control to do.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi Shael,

In order to help you, we need this info.

1.What is the Form’s property “Allow Filter� is set to?
2.What is the Form’s RecordSource? Please post the query for us to help you.
It may be unnecessary.

In the mean time, put the FilterOn = True on the first line because by
default it is set to False. And you might also want to include another button
to clear the form filtering. Just set it to False.

Private Sub SearchButton_Click()
Me.FilterOn = True
Me.Filter = "[Name] = '" & Me![NameDropDown] & "'"
End Sub


Shael wrote:
Help!!

I have spent days trying to get something very simple to work in Access 2007
to no avail, despite the many suggestions from many of you. I can accomplish
this in minutes using ASP.Net, but I must be missing something in Access.

I have a form that is bound to a query. One of the fields on the form is
called Name. On the same form, I have a combo box called NameDropDown, and a
command button called SearchButton.

When the form is first opened, it displays the fields from the first record
in the query. I would like to be able to select a name from the NameDropDown,
click the SearchButton, and have the form display the respective fields for
the selected name.

The following is the code I have behind the OnClick event of SearchButton:

Private Sub SearchButton_Click()
Me.Filter = "[Name] = '" & Me![NameDropDown] & "'"
Me.FilterOn = True
End Sub

I have tried with [], and without []. I have tried with single quotes and
without single quotes.

Whenever I click the SearchButton, there is no change to the form contents.

Please help me solve this problem or I may be forced to go back to dBase!!

Thanks.

--
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com