Reaching the end of my rope with Access

[Guest]

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.

 

[Pieter Wijnen]

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

 

[Dirk Goldgar]

In

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.

 

[UpRider]

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

 

[Guest]

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

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

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.

 

[Pieter Wijnen]

See Dirks answer

Pieter

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

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

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.

 

[Guest]

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];

In

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)

 

[UpRider]

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

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

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

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.

 

[Guest]

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.

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

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

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

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.

 

[Dirk Goldgar]

In

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?

 

[Rick Brandt]

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

Because then you aren't just searching. You are editing the current record.
That is not what you want to do right?

 

[Dirk Goldgar]

In

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.

You may be right that the table is not being updated -- it sounds to me
like your combo box is *not* bound to a field, in which case it would
not happen. However, don't think that updating data in a query leaves
the underlying table unchanged. Updating a record in a query *does*
update the corresponding record in the table from which the updated
field was extracted.

 

[UpRider]

Shael, more is involved in the complete picture.
Yes, the form *recordsource* is bound to a query if you say so.
Yes, the combobox *rowsource* is bound to a query if you say so.
But, what is the combobox *datasource* bound to? Should be nothing and
certainly is if nothing is being updated.
With my example code, what is not working?

UpRider

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.

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

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

:

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

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.

 

[Guest]

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

 

[Rick Brandt]

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.

If you change the data in a query you ARE changing it in the table that the
query is based on.

 

[Guest]

I'm getting the same results. I select a value in the drop down and nothing
changes in the form.

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

Shael, more is involved in the complete picture.
Yes, the form *recordsource* is bound to a query if you say so.
Yes, the combobox *rowsource* is bound to a query if you say so.
But, what is the combobox *datasource* bound to? Should be nothing and
certainly is if nothing is being updated.
With my example code, what is not working?

UpRider

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.

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

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

:

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

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.

 

[Guest]

I'm getting the same results. I select a value in the drop down and nothing
changes in the form.

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

Shael, more is involved in the complete picture.
Yes, the form *recordsource* is bound to a query if you say so.
Yes, the combobox *rowsource* is bound to a query if you say so.
But, what is the combobox *datasource* bound to? Should be nothing and
certainly is if nothing is being updated.
With my example code, what is not working?

UpRider

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.

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

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

:

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

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.

 

[Pieter Wijnen]

So the Field is FullName?
Your cboSource should probably be
SELECT FULLNAME FROM QUERY1

Or Better From a Table holding the Names (Employee?), Never use * For
Comboboxes!


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 "FullName = '" & Me.NameDropDown.Value & "'"
If Not RsC.NoMatch Then
Me.BookMark=RsC.BookMark
End If
End If
Set RsC = Nothing
End Sub

HtH

Pieter

"Pieter Wijnen"

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

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.

 

[UpRider]

Sheal, yes. You made that clear with with first post. Now, do you want to
address your database problem or is your ageda to play 'poor me' and trash
Access?
We gave you solutions and asked for feedback. We get whining, not feedback.
Again, what's not working with the solution I gave you?

UpRider

 

[Rick Brandt]

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.