Combo Box "Value List" Default Values Automation 1

[applevacross]

Good morning all, I'm updating some data in my DB and have been requested to make a combo box with default values to choose from.

I've already got two fields in my table; NT USername, and Full Name. I don't want to touch the default data, instead, I want the value from the combo box on the form to update as changes to the records within the form take place.

Here's the situation. I'd like the combo box to have default values for the said field names. However there are over 4000 values for both boxes I'll need to create. I normally edit in nopad the quotes and semicolon then copy and paste into the row source manually. Since this is going to be 4000 names for each, I'd like to see if there is a way to automate so I don't have to do this manually.

Overall, I do not want to overwrite my current table fields data, I need to only create a value list which will update the table as the changes take place. If anybody knows how I edit the data before I add to the value list, I would be greatly appreciative.

Thanks to all in advance.

 

[Remou]

Please answer a few questions.
You have a username fields and a full name field which you wish to leave alone. You wish to add two combo boxes listing in one user names and in the other full names. This data can then be used to change the UserName and FullName fields. Is this correct?
If UserName and FullName are related to one another, do you need two combo boxes, or would two columns in one combo box be better?
Is there any reason why you cannot use a table with UserName and FullName as a Record Source for your combo?

Apologies if I have misunderstood your problem.

 

[applevacross]

Thank you Remou,

Yes, I would like to two seperate combo boxes to update the fields within the current table. I tried doing the second part yesterday by importing my excel spreadsheet, then converting into a new table. I'm just not sure what to do from there. Do I link the tables and then create the combo boxes? If so, wouldn't the linked info from the new table overwrite the data in the current table? I'm not 100% on how the linking works. ALso, this is a split db, so I would have to create the new table on the backend correct?

Thanks I appreciate your help.

 

[Remou]

If you set the Record Source of a combo, but not the Control Source, it is unbound and does not change data in your table. You can make it change data like this:

Private Sub cboFullName_AfterUpdate()
If MsgBox("Do you wish to update Full Name to " _
    & Me.cboFullName & "?", vbYesNo, "Change Name") = vbYes Then
    Me.txtFullName = Me.cboFullName
End If
End Sub

If you are using a table, I think it should be put in the back end. Your Record Source would look something like:
[tt]SELECT [tblNames].[FullName] FROM [tblNames][/tt]

 

[applevacross]

Great, thank you. Shall I link the tables though?

 

[Remou]

Oops. When linking a table using the Access menus, you will find that Access creates a new name (by numbering) each time you link a table with the same name as an existing table. However I am a little confused in that I supposed you had a main table linked to a front end form that you now wish to modify by adding two combos that get their data from another linked lookup table, so overwriting should not be a problem as two different tables are involved. If this is not the case, I have misunderstood you. (Apologies if I am over-simplifying.)

 

[applevacross]

No, no you are correct. I spoke it the way I understand which doesn't mean others will. Sorry about that. You deciphered properly and helped me do what needed to be done.

Thank you much, Two Stars for you!