Reaching the end of my rope with Access

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.

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

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

HtH

Pieter

"Shael" wrote in message
...
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.

In ,
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!!

In addition to Pieter Wiejnen's comments, is the combo box
"NameDropDown" bound or unbound; that is, does it have anything in its
ControlSource property? If it's bound to the field "Name" (not a good
name for a field), then you're changing the Name field in the current
record every time you make a choice from the combo box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Shael, you don't need a SearchButton. The after update event of the combobox
will work fine. BTW, Name is a reserved word in Access. It may or may not
cause problems, so I recommend you change it. Below is code that will work.
The tricky part is looking at the proper column of the combobox's rowsource
for the search. The bound column is the column that shows in the combobox.
It may or may not be the column that you search on. For example you may
actually search on the column that contains the ID, but you want the name to
show in the combobox, not the ID. The column is zero based. Also, you do not
want this combobox to be bound to a field in your table or query.
This may read confusing, but when you grasp it, it makes perfect sense.

HTH, UpRider

Private Sub NameDropDown_AfterUpdate()
If Not IsNull(Me.NameDropDown) Then
'Save if necessary
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
With Me.RecordsetClone
.FindFirst "[Name] = """ & Me![NameDropDown].Column(2) & """"
If .NoMatch Then
Me.NameDropDown.SetFocus
MsgBox " Must be valid name or blank! ", _
vbOKOnly + vbExclamation, " N A M E N O T F O U
N D "
Else
'Display the found record in the form.
Me.Bookmark = .Bookmark
End If
End With
End If
NameDropDown = vbNullString
End Sub

"Shael" wrote in message
...
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.

Why can't the combobox be bound to a field in the table or query?

"UpRider" wrote:

Shael, you don't need a SearchButton. The after update event of the combobox
will work fine. BTW, Name is a reserved word in Access. It may or may not
cause problems, so I recommend you change it. Below is code that will work.
The tricky part is looking at the proper column of the combobox's rowsource
for the search. The bound column is the column that shows in the combobox.
It may or may not be the column that you search on. For example you may
actually search on the column that contains the ID, but you want the name to
show in the combobox, not the ID. The column is zero based. Also, you do not
want this combobox to be bound to a field in your table or query.
This may read confusing, but when you grasp it, it makes perfect sense.

HTH, UpRider

Private Sub NameDropDown_AfterUpdate()
If Not IsNull(Me.NameDropDown) Then
'Save if necessary
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
With Me.RecordsetClone
.FindFirst "[Name] = """ & Me![NameDropDown].Column(2) & """"
If .NoMatch Then
Me.NameDropDown.SetFocus
MsgBox " Must be valid name or blank! ", _
vbOKOnly + vbExclamation, " N A M E N O T F O U
N D "
Else
'Display the found record in the form.
Me.Bookmark = .Bookmark
End If
End With
End If
NameDropDown = vbNullString
End Sub

"Shael" wrote in message
...
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.

See Dirks answer

Pieter

"Shael" wrote in message
...
Why can't the combobox be bound to a field in the table or query?

"UpRider" wrote:

Shael, you don't need a SearchButton. The after update event of the
combobox
will work fine. BTW, Name is a reserved word in Access. It may or may not
cause problems, so I recommend you change it. Below is code that will
work.
The tricky part is looking at the proper column of the combobox's
rowsource
for the search. The bound column is the column that shows in the
combobox.
It may or may not be the column that you search on. For example you may
actually search on the column that contains the ID, but you want the name
to
show in the combobox, not the ID. The column is zero based. Also, you do
not
want this combobox to be bound to a field in your table or query.
This may read confusing, but when you grasp it, it makes perfect sense.

HTH, UpRider

Private Sub NameDropDown_AfterUpdate()
If Not IsNull(Me.NameDropDown) Then
'Save if necessary
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
With Me.RecordsetClone
.FindFirst "[Name] = """ & Me![NameDropDown].Column(2) & """"
If .NoMatch Then
Me.NameDropDown.SetFocus
MsgBox " Must be valid name or blank! ", _
vbOKOnly + vbExclamation, " N A M E N O T F
O U
N D "
Else
'Display the found record in the form.
Me.Bookmark = .Bookmark
End If
End With
End If
NameDropDown = vbNullString
End Sub

"Shael" wrote in message
...
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.

I tried Pieter's suggestion and changed the Name field to FullName. The code
behind the After Click event of NameDropDown is:

Private Sub NameDropDown_AfterUpdate()
Dim RsC As DAO.Recordset

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

End Sub

Unfortunately this is not working.

Dirk, in response to your question - when I create the Combo Box on the
form, the wizard starts and I specify the fact that the combo box should get
its values from the FullName column in the same query the form is based. When
I open the form, the correct values are listed in the NameDropDown combo box.
In the Data property sheet of NameDropDown, there is nothing in Control
Source, but the following in Row Source:

SELECT [Query1].[FullName] FROM [Query1] ORDER BY [FullName];

"Dirk Goldgar" wrote:

In ,
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!!

In addition to Pieter Wiejnen's comments, is the combo box
"NameDropDown" bound or unbound; that is, does it have anything in its
ControlSource property? If it's bound to the field "Name" (not a good
name for a field), then you're changing the Name field in the current
record every time you make a choice from the combo box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

The combobox is bound to a field in your table or query. You have a record
displayed in your form. Any record. Say "Smith". Now, you want to look up a
new record. So you dropdown the combobox and select "Jones". Tab or Enter.
Whoops. Bad stuff just happened. You changed Smith to Jones *in your table*.

UpRider

"Shael" wrote in message
...
Why can't the combobox be bound to a field in the table or query?

"UpRider" wrote:

Shael, you don't need a SearchButton. The after update event of the
combobox
will work fine. BTW, Name is a reserved word in Access. It may or may not
cause problems, so I recommend you change it. Below is code that will
work.
The tricky part is looking at the proper column of the combobox's
rowsource
for the search. The bound column is the column that shows in the
combobox.
It may or may not be the column that you search on. For example you may
actually search on the column that contains the ID, but you want the name
to
show in the combobox, not the ID. The column is zero based. Also, you do
not
want this combobox to be bound to a field in your table or query.
This may read confusing, but when you grasp it, it makes perfect sense.

HTH, UpRider

Private Sub NameDropDown_AfterUpdate()
If Not IsNull(Me.NameDropDown) Then
'Save if necessary
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
With Me.RecordsetClone
.FindFirst "[Name] = """ & Me![NameDropDown].Column(2) & """"
If .NoMatch Then
Me.NameDropDown.SetFocus
MsgBox " Must be valid name or blank! ", _
vbOKOnly + vbExclamation, " N A M E N O T F
O U
N D "
Else
'Display the found record in the form.
Me.Bookmark = .Bookmark
End If
End With
End If
NameDropDown = vbNullString
End Sub

"Shael" wrote in message
...
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.

This is not happening. The form is bound to a query. The combo box is bound
to a query. Nothing in the table is being updated.

"UpRider" wrote:

The combobox is bound to a field in your table or query. You have a record
displayed in your form. Any record. Say "Smith". Now, you want to look up a
new record. So you dropdown the combobox and select "Jones". Tab or Enter.
Whoops. Bad stuff just happened. You changed Smith to Jones *in your table*.

UpRider

"Shael" wrote in message
...
Why can't the combobox be bound to a field in the table or query?

"UpRider" wrote:

Shael, you don't need a SearchButton. The after update event of the
combobox
will work fine. BTW, Name is a reserved word in Access. It may or may not
cause problems, so I recommend you change it. Below is code that will
work.
The tricky part is looking at the proper column of the combobox's
rowsource
for the search. The bound column is the column that shows in the
combobox.
It may or may not be the column that you search on. For example you may
actually search on the column that contains the ID, but you want the name
to
show in the combobox, not the ID. The column is zero based. Also, you do
not
want this combobox to be bound to a field in your table or query.
This may read confusing, but when you grasp it, it makes perfect sense.

HTH, UpRider

Private Sub NameDropDown_AfterUpdate()
If Not IsNull(Me.NameDropDown) Then
'Save if necessary
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
With Me.RecordsetClone
.FindFirst "[Name] = """ & Me![NameDropDown].Column(2) & """"
If .NoMatch Then
Me.NameDropDown.SetFocus
MsgBox " Must be valid name or blank! ", _
vbOKOnly + vbExclamation, " N A M E N O T F
O U
N D "
Else
'Display the found record in the form.
Me.Bookmark = .Bookmark
End If
End With
End If
NameDropDown = vbNullString
End Sub

"Shael" wrote in message
...
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.

In ,
Shael wrote:
I tried Pieter's suggestion and changed the Name field to FullName.
The code behind the After Click event of NameDropDown is:

Private Sub NameDropDown_AfterUpdate()
Dim RsC As DAO.Recordset

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

End Sub

Unfortunately this is not working.

Dirk, in response to your question - when I create the Combo Box on
the form, the wizard starts and I specify the fact that the combo box
should get its values from the FullName column in the same query the
form is based. When I open the form, the correct values are listed in
the NameDropDown combo box. In the Data property sheet of
NameDropDown, there is nothing in Control Source, but the following
in Row Source:

SELECT [Query1].[FullName] FROM [Query1] ORDER BY [FullName];

That sounds right. I hope the Column Count property and Bound Column
property of the combo box are both 1.

If it's still not working, the next step is to see what is actually
happening when the code is executed. Set a breakpoint at the top of the
event procedure, then select a name in the combo box. If the code
doesn't stop at your breakpoint, the code is not even being called
(unless you have one of the VB options set to an uncommon value).
Assuming it does stop at the breakpoint, then step through the code line
by line to see what path it takes. By hovering your mouse pointer over
the object and variable names in the code, you can examine their values.
Check the value of NameDropDown in that way, to make sure it has the
value you expect.

Is your form's recordsource also Query1, or based on that query?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)