How do I update a form when a combobox selection is selected

[kcmorrissey]

I want to make a form that has a combo box where the user selects a name and the rest of the data on the form (from same table)updates to show the rest of the record.

I have already created the form using the forms wizard and then created the combobox by selecting the tool and then the field from the fields list for the form. I then drag that field on to the form and it creates the combo. But...

When I make a selection from the combo box nothing else happens!!!

Help....

Thx,
Kev.
PS. I am still a novice at this Access stuff, so please forgive me if this is just beginners stuff.


Kev.
The UK's greatest vacant memory space!

 

[KenReay]

Hi

We were all novices at some time!

see faq702-4398 in this forum

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[AArt]

In other words, use the After Update event to trigger your record selection. You can also use On Change (try them both so you can see the difference), or use a command button to trigger it so the user has time to change his selection if he realizes he picked the wrong name.

 

[kcmorrissey]

I have tried this with several variations and seem to come back to the same problem:

My code in the on Change eventProcedure looks like this (cloned from FAQ)
Private Sub ComboSupp_Name_Change()
Me.RecordsetClone.FindFirst "[supp_Name] = '" & Me![ComboSupp_name] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me![ComboSupp_name] = Me![supp_Name]
End Sub

The record on the form still doesn't update and if I change the selection in the combo box more than once access displays and error with the following ' Update or CancelUpdate without AddNew or Edit'.

Am I missing a step to tell access to update the screen or something?

Thx,
Kev.


Kev.
The UK's greatest vacant memory space!

 

[Eupher]

kcmorrisey,
You really want to put the code in the AfterUpdate event of the combo. But the easiest way is to place a new combo, then select "Find a record on my form based on the value I selected in my combo box." and follow the wizard.

Ken S.

 

[kcmorrissey]

Thanks,

It works!!

That was easy... I wish I'd found that when I started this form.

Thx.

Kev.
The UK's greatest vacant memory space!

 

[kcmorrissey]

But It's still not quite right!

It allows me to select from the combobox, but when I close the form it has updated all of my suppliers names with value from the suppliers ID.

The combo box is looking up the suppliers name.
suppliers ID is the key field on the table.

What else have I missed, Can anyone point me at a good example of this sort of thing working?

Thanks for all your time and effort. I feel like a real klutz!

Kev.
The UK's greatest vacant memory space!

 

[PHV]

The search combo must be unbound (no ControlSource).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kcmorrissey]

That seems to have fixed it, but I don't understand the difference between bound and unbound, except that the unbound combo doesn't have a control source.

I'm sure that there must be a good description of these properties somewhere.

Thanks for the help.

Kev.
The UK's greatest vacant memory space!

 

[KenReay]

Hi

"but I don't understand the difference between bound and unbound, except that the unbound combo doesn't have a control source"

that is a VERY important difference, with a bound control data you type intro the control will automatically update the underlying recordset (as you found). The update takes place either when you explicitly execute a save, or when you move off the current record. With an unbound control, anything you type into the control, updates only the form, and such updates are not permenant, ie close the form and the update is lost



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kcmorrissey]

OK, so I get that bit and can see how I can use forms that don't update the database (Thanks to all those who helped). However:

1. This form was created in a wizard that tied the form to a table. Only the combo box is unbound, or did that change the whole form?

2. If the information on the screen needs editing or a new record added or one deleted, will I need to make a different form. If so how do I do that and avoid the earlier problems?



Kev.
The UK's greatest vacant memory space!

 

[PHV]

Only the combo box is unbound

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886