ComboBox Keeps updating Table

[Garito100]

Hi,

I don't know if what I am doing is the best way of doing it but I'm not sure what else to do - either way I'm having combo box problems.

I have a form with a combo box that gives me a list of names from a table. WHen I select the name I want the rest of the form to bring in the rest of the employee details so I use the following script:

Private Sub Combo71_AfterUpdate()
[Form1]![EmployeeName] = [Form1]![Combo71].Column(1)


[Form1]![EmployeeNbr] = [Form1]![Combo71].Column(17)

and so on. It works perfectly but when I close and reopen the form, the employee name is repeated in the drop down and the table has been updated with the details again. I read about un-binding it so removed the row source and changed the name of the combo box so it wasn't the same as something in the table but now it just adds in a blank field to the table everytime I close it.

I'm only using this info to look up the details. They are stored in the table and will remain there - they don't need to be updated - it's just to look up contact details etc. Don't know if this is the best way or if there is another way. Everything is all on the one form - no subforms or anything.

Many thanks for any help! Been tearing my hair out for days with this

[RuralGuy]

Use the ComboBox wizard to create a new cbo and select Option #3.

[Garito100]

I am 99.9% sure that there was no Option #3! I do see it now though - however - this seems to add an embedded macro in the 'after update' bit and that is were I had my other bit of script which updated the other fields and it won't seem to do both. I can't work out what the macro is doing to be able to include it in the rest of the script? Can you help with that at all?

[RuralGuy]

I can certainly assist. Are you still using ac2003 here? The new cbo should be all you need unless I'm not understanding what it is you want.

[Garito100]

I'm sorry - I'm using ac2010. Had an upgrade not so long ago.

The other part of my script
Private Sub Combo71_AfterUpdate()
[Form1]![EmployeeName] = [Form1]![Combo71].Column(1)

I added this in the 'after update' section but when I create the new combo box, I can't see how to put this part onto the end of the macro it creates when I set up the combo box. I'm not sure how to add it in.

[RuralGuy]

You should not need it. If the [EmployeeName] control os bound to the field then it should display properly after you make the selection in the cbo.

[Garito100]

The rest of the fields just stay blank and don't populate with the info from the table.

[RuralGuy]

Have you set the ControlSource of the other controls to the proper fields in the RecordSource of the form?

[RuralGuy]

You can easily keep users from making any changes to the record even though each control is bound to a field in the RecordSource of the form.

[John_G]

It may not be the row source of the combo box that it is the problem - it is the Control Source. If the combo box is bound to a table field, then whenever you change the value in the combo box, you are changing the value in the underlying table. Assuming that you want the form to show the details of the employee selected from the combo box, and the form is bound to the employee table, then first, unbind the combo box (control source is blank). Then in the after update event of the combo box, put something like this:

doCmd.gotocontrol "EmployeeID"
'
' EmployeeID is the name of the form control
'
DoCmd.FindRecord COMBO71


Change the row source of combo71 so that it contains the employeeID as the first column (It can have width 0" to make it invisible), and the Employee Name as the Second column. Set the bound column to 1. This should accomplish what you are trying to do.

John

[RuralGuy]

There may be too many cooks here. As I stated, the ComboBox wizard can locate and move to any record in the RecordSource of your form if the form is set up correctly.

[RuralGuy]

Is this thread resolved?

[Garito100]

GSA DB.accdb
Hi, no it isn't. I'm very lost now I'm sorry!
I've attached my DB so you can see what I've done. It's only got Test Data in it.
The form I am looking at is GSA-Info and Test is the field with the Combo box in it. All the fields will prepopulate when I choose something in the drop down but then it adds a new field to the table.
Thanks to everyone for your help with this!

[RuralGuy]

You have the DataEntry property of the form set True which will *only* show NEW records.

[Garito100]

That's it! It worked. I think I've been messing about with it that much that I forgot what I had changed. Thanks so much.