Protecting data in a table when selecting a record via a combo box 1

[davejackson]

Hi,

I am trying to allow users to select a record via a combo box in a form header and via the next/previous buttons so I can then update related details in a subform on a seperate page. However when I select a record with the combo box the table is being updated with whichever record number is listed in the specific record box. The key field remains the same but the other data for the record changes.

E.g. If I am on this record:

Firstname Surname ID
David Jackson 001 (record number 1)

and try and select record 5 using the combo box
John Smith 005 (record number 5),

record number 1 gets updated to:
John Smith 001.

I would therefore like to either move to the specified record or protect the table from being updated in this part of the form.

Any help would be great.

Thanks

 

[LonnieJohnson]

You're combo box is bound to the field in the table. Meaning that you have a field name from your table in the Conrol Source. If you wish to use this combo box for searching, then you will need to delete the field name from the ControlSource section of the Data Tab on the property window for the combo box.

Now you will need to add another field to your form for the field that you deleted from the Control Source property. You may want to move your combo box away from the rest of the fields, possibly in the header as not to confuse the user into thinking this is for input. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[davejackson]

Thanks Lonnie, that worked brilliantly.

I am also trying to update a seperate table from my selection which is linked using the key field (ID). E.g. Purchases that John Smith has made.
I have the code:

Private Sub Client_AfterUpdate() (this is my combo)
Purchases.Requery
End Sub

This only seems to work if I don't show the key field on my query based on his ID. However I can't then add new records as the key field is missing. It seems like it should work and does with the form on it's own but as part of a subform it doesn't. Any ideas? Help!

 

[LonnieJohnson]

Dave,

Can you put the forms, query and tables (with sample data) in a database and email them to me? Also, kinda sum up your problem in the email. I'll take a look at it. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[davejackson]

Thanks Lonnie, will do.