How to make ComboBox change record?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

On a form I have an unbound combobox accessing names from
a "NAMES" field from a table. But when I choose a name.
The record doesn't change to match it. It stays at record
one.

I've tried setting the boundcolumn to the Primary Index
field number. But it still won't work.


Any ideas?

Thanks.
 
In the combo box's AfterUpdate eventhandler, place a call
to DomCmd.ApplyFilter e.g.

DoCmd.ApplyFilter , "{yourNameColumn} = " & {yourComboBox},
if {yourNameColumn} is a numeric or
DoCmd.ApplyFilter , "{yourNameColumn} = '" & {yourComboBox}
& "'" if {yourNameColumn} is a text

You will have to replace {yourNameColumn} and
{yourComboBox} including {} to suit your application.

Hope This Helps
Gerald Stanley MCSD
 
From your description, it sounds like your form is bound to the complete set
of records (?perhaps the entire table). Once you've selected a "Name", you
are trying to filter the set down to one (and the response by Gerald S.
should help you do this).

A couple suggestions ... first, change the field named "Name" to something
more meaningful AND not a reserved word in Access.

And another approach would be to base your form on a query which looks to
the form's combo box to provide the selection criterion. When you open the
form, the combo box is empty, so the query returns no rows. When you make a
selection in the combo box, use the combo box's AfterUpdate event to requery
the form (Me.Requery), filling it with the data related to the selected
"Name".
 
Thanks Gerald,

But it's not working.
It tosses out all kinds of errors If I try it.
Here's what it looks like with my actual names:

DoCmd.ApplyFilter , "{NAMES} = '" & {Combo7}& "'" if
{NAMES} is a text

I'm confused about this approach.
What part of this filter associates the combo Box with the
actual record number?
 
I've confused you with my explanatory notes; try

DoCmd.ApplyFilter , "NAMES = '" & Combo7 & "'"

I am not sure by what you mean by 'actual record number'.
The principle is that the form displays information from
its RecordSource which may be a table or a query. The
filter limits the rows to be displayed from that source as
if an extra WHERE clause had been inserted into the SQL.
In the above example, you are saying that you want the rows
displayed to be only where the NAMES column matches the
contents of combo7.

Hope This Helps
Gerald Stanley MCSD
 
Thanks guys,

I put Geralds code in the wrong field by mistake.
Once I placed it in the combo box after update property.
And edited out the "if" part of it. It seems to do the
trick.

Thanks for the help.
 
I'm sorry Gerald,
I guess I didn't explain myself properly.
By record number I'm referring to the number at the bottom
of the form window where the use can go backwards or
forwards.


Here's what I've got:

In My Table named "NAMES".
My primary Index field is named "ID". And uses Autonumber.
The other two columns are "NAMES" & "PASSWORDS"

On my form called "NAMES".
I have a combo box named "Combo7"
I have a text box named "PASSWORDS"
I have an unbound text box named "typepassword"

If the user enters the correct password in
the "typepassword" text box. A docmd opens the application.
If the user types the wrong password. A message box pops
up as says "wrong password try again".

It works great except for one glaring problem.
When I choose a name from the combo box. Even the correct
password doesn't work because the combo box doesn't change
the record (ID) it's married to on the table.
If I manually select the proper record number that matches
the name. Then it works very well.

The code you gave me works. But it filters the form and
thus has a side effect of making the whole password
matching code I wrote useless and the use can launch the
application no matter what they enter.


I fear I'm not explaining myself clearly.
It's simpler than it sounds.
The only thing that I really need is how to get the record
selector at the bottom of the form to update and match
with the "NAME" field when I choose one of them from the
combo box.

In other words.
The name I select from the "NAMES" combobox needs to find
the "ID" field it belongs to. Then change to that record
on the form.

Hope that makes things clearer.
 
Hope I can tag along to this discussion because I am
trying to do a similar thing but haven't a clue how. I
have a combo box in the form and a subform. The subform
is based on a query that is looking at what is selected in
the combo box. When I change the combo box I need the
subform to reflect the change without having to do the
back or forward buttons on the form. How would I
accomplish this? I am a newby at this. Thanks.
 
Typically, a sub-form is based on a primary key value from the main form,
not a combobox.

If, after you update the combobox (i.e., select a new item), you want the
main form requeried, add code to the combobox's AfterUpdate event something
like:

Me.Requery

If it's the subform you want requeried, use the same approach on the subform
control.
 
I was able to do a put the requery in the VB code on the
combo field that was changing and it did cause the subform
to refresh itself with the correct info, but... what
happens is that the main form gets reset back to the
beginning record. In other words I was on the 3rd record
in the main screen viewing detail subrecords for year
2004. I change the combo box to 2003, the main form gets
reset back to record number 1 and the subrecords are now
for year 2003. I need the form to stay at the same main
record. Is there a way to do this?
 
I guess I'm still not clear on how you have your main form/sub form set up.
From your description, it sounds like they are not, as I suggested earlier
in this thread, linked by Parent-Child fields. This would be the more
standard approach, and would assure that you see related sub-form data when
you select a record for the main form.

Perhaps there's some code in the AfterUpdate event of the combo box that
resets the main form?
 
Sorry to tag along to this discussion.
Just wanna thank Gerald for the precious hint and Anonymous for posting the question. My problem is finally solved! (after weeks of battling with Access as a newbie)
Thanks guys
 
Back
Top