Reaching the end of my rope with Access

[David W. Fenton]

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.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]

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.

 

[Pieter Wijnen]

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

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

Dooh

Pieter

 

[AccessVandal via AccessMonster.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

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.