Combo Box initial value

[ogri]

Hi

I am working on a Access database. One of the forms allows adding or updating a record, with validation done in VBA. A field on here is a combo box to allow the user to select a member of staff as the contact for this record.

When adding a record this works fine, with the drop down box being population from a small bit of SQL, having the record ID (key) and the name. When updating an existing record I need to assign the previously selected record to the combo box, and this I can do by using frm.combobox.value = "Fred Bloggs".

The problem is that if it is an add (or an update where the staff member field has changed) and the user selects the record the ID field is returned. If it is an update where the staff member is not updated then the assigned value is returned.

How can I assign an initial value to the combo box which will be processed consistantly?

All the best

Keith

 

[gaffonso]

Hi Keith,

A quick question, is the ComboBox you are populating bound to a field on the form you are using? I'm a little confused about the problem you are having.

- Gary

 

[Guest_imported]

Hi

The combo box is not bound. It is restricted to the values from a select by using the Row Source (ie, "SELECT UsersTable.ID, UsersTable.StaffName FROM UsersTable;&quot. This presents a selection list. However this is returning the ID field where I need to return just the name field. If I drop the ID field I get nothing in the selection list at all. If I keep the ID then the values cause problems when it comes to updating them when populating the fields.

Maybe I am barking up the wrong tree here, but I am a bit stuck

All the best

Keith

 

[jebry]

Hi Keith!

Maybe I am misunderstanding the problem, but it seems that the problem is with what the combo box is displaying not what it is returning. In the format tab of the properties window for your combo box is a property called column widths. If you set the widths to 0";1"(The second number should be the width of the combo box), the StaffName field should display in the combo box.

hth
Jeff Bridgham

 

[ogri]

Hi

Thanks for that pointer. It will make what I have a tad simpler. However one problem I still have is that if I read the record to display it on the screen I am reading in the fields character value. If I assign this to the Value of the combo box I get nothing, and if I try and assign it to the Column(0) I get an error.

At the moment I am solving this by using a table join on my select to retrieve the ID for the name I have read, and use this to assign to the value of the combo box. This works but isnt really very elegant

All the best

Keith

 

[jebry]

Hi Keith!

I guess I should ask what you intend to use the combo box for? If you want it to display the staff name when a new record comes up, basically your choices are to bind the combo box to the appropriate field in the table or to do what you are now doing progammatically. Binding the combo box will get it to display the staff name automatically when the form goes to a new record, but is subject to possible data corruption if the user is not careful. If you bind the combo box, I would put an validation routine in the beforeupdate event of the combo box asking the user if they intended to change the staff name associated with that record and undo the change if it was accidental.

If your intent is to allow the user to use the combo box to search the table, then you should have a separate box for display and adding.

hth
Jeff Bridgham

 

[ogri]

Hi

Thanks for that. The idea is that there will be a list of possible people (which will vary with time). I am happy that if the name is not found then nothing will be displayed.

I think from what you have said I have taken it as far as it can go. I will keep it as coded

Thanks for all the help

Keith