[MS Access Forms] Want Combo box to populate IDs when text is selected

Presently i have a combo box which i use to populate report IDs to a column field, But to know which ID to populate, i have to refer a reference table which shows which ID correspond to what report name.

I ll like the combo box to have the report names so that if i select on a name in the drop down, the field is auto populated with the appropriate report ID chosen. This will greatly facilitate the process. How do i achieve this? Thanks
peterslove53Asked:
Who is Participating?
 
PatHartmanCommented:
I don't understand your question so I'll try to explain how combo boxes work.  Combo boxes have a RowSource property.  The RowSource can be the column names of a table or query (Field List), a list that is hard-coded in the combo itself (Value List), or a reference to a table or query (Table/Query).  We are probably talking about the Table/Query option in your case.

The RowSource Table/Query typically includes at least two columns.  The unique ID of a record, and some descriptive text.  So a RowSource based on a department table might look like:
DeptID, DeptName
23, Shipping
35, A/R
36, A/P
49, Receiving

The ColumnWidths property is used to specify which columns will be visible.  Usually the "ID" is hidden so in this case the value of ColumnWidths would be:
0";2"
The zero says to hide the first column.

The BoundColumn property specifies which column of the RowSource is the uniqueID which will be saved if the Combo is bound (meaning it has a column name specified in the ControlSource).  In this case, the BoundColumn would be 1.

Assuming the ControlSource of the combo is DeptID, then when you view the combo, the list shows the department names but when you select an entry, the bound column (which is DeptID in the RowSource) will be saved as DeptID in the form's RecordSource.  In this example, DeptID has the same name in both places but that isn't necessary.  Most people use the same name for Foreign keys as their related primary key because it makes matching things up easier and you don't have to remember lots of different subtle, name variations.
0
 
peterslove53Author Commented:
sounds clear,  i have just created the combo box displying in this case, the DeptID, DeptName. when i select on a row, it populates the appropriate ID (good) but i can only search or lookup the ID column. so if i type say 35, my curso go straight to the record; "35, A/R    "   as shown above.

I ll like to be able to search for a particular DeptName instead. The common issue, is that one may know the DeptName but not the DeptID, so it ll be helpful if i can search on DeptName  but get the form populate the DeptID. Is that possible?
0
 
PatHartmanCommented:
You have to hide the first column so that the dropdown list only shows the department name.  Modify the ColumnWidths property to:
0",2"
0 says to hide the first column
2 says to use 2" to display the second column
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
peterslove53Author Commented:
when i apply that setting above, the field is populated with the text value instead of the ID. I actually want to have both the ID and the name show in the combo but for the lookup to be on the name and an ID to populate the field.
0
 
PatHartmanCommented:
You can only search by the visible field.  so if you want to show the ID, you would search by the ID.  If you want to show the text, you would search by the text.  If you want to show both, you will have to create a concatenated field.

Select DeptID, DepartmentName & " -- " & DeptID From YourTable
Order by DepartmentName & " -- " & DeptID;

With the concatenated field, you wll search by the DepartmentName if that is first (as in my example here) or by ID if you put the ID first.

Occasionally, I need to give the user the option to search by either so I create two unbound combos.  One by ID and one by text.
0
 
peterslove53Author Commented:
I also noticed that, using the lookup wizard to create the combo box, i can only create 3 fields, is that a rule in using the wizard? I am sure there maybe other options to search text in combo box but populate a number, maybe a walk around. I see threads saying it could be done but no example of any being done. I know a combo box with multiple columns can be used to populate different fields at thesame time...
0
 
PatHartmanCommented:
It looks like this problem is solved in your other thread.  The problem you are having is being caused because there is a table-level lookup field.  So, even though the combo is actually saving the ID value, all you see when you open the table in data sheet view is the lookup value.  Getting rid of the lookup is the solution.  Then you can actually see what is being stored and will have the option of joining to the lookup table if you specifically need to pull the text value and don't want to use a combo.
0
 
peterslove53Author Commented:
i got rid of the table level lookup. i then created the combo box on the form using the wizard, set up the settings like above and still getting just the name populated in the field instead of the ID. Maybe using VBA code might help
0
 
PatHartmanCommented:
No.  The combo box works.  You are doing something wrong.  Please post the database and identify the form with the combo that is not working.

Also, having multiple threads on the same subject is wasteful of our time.
0
 
peterslove53Author Commented:
i got it.  There was just one missing element. After the combobox is created, and the settings adjusted as above;
ColumnWidths property to:
0",2"

i added this code in the after update event, so even though just the DeptName is displayed, the DeptID is what is populated in the text field


Private Sub Combo32_AfterUpdate()
Me.DeptID = Me.Combo32.Column(0)
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.