show name in a combo box but store id (1 Viewer)

MK1999

New member
Local time
Today, 14:11
Joined
Mar 30, 2022
Messages
24
hello, i have a table [coordinator] and table [program] linked together through table [coordinator_program] which contain primary keys of both.. i have a form with combo box that let the user choose the coordinator of program but it shows the id because the record source is coordinator id from [coordinator_program] table to avoid duplicates name.. is there a way to show the names in the combo box but store the id value?
 

strive4peace

AWF VIP
Local time
Today, 06:11
Joined
Apr 3, 2020
Messages
1,003
hi @MK1999

yes. In the design view of the form, choose the combo control. On the Property sheet, on the Data tab, set the RowSource to what you want to store and display. When you click in the property, you'll see ... to the right. Click that and it will take you to a builder that is just like the design view of a query. Get the data how you like. then close and save

on the Format tab of Property Sheet, set:
Column Count
Column Widths
List Width
 

Eugene-LS

Registered User.
Local time
Today, 14:11
Joined
Dec 7, 2018
Messages
481
is there a way to show the names in the combo box but store the id value?
Set combobox properties:
ColumnCount = 2 (or more)
ColumnWidths = 0;8 (first should be = 0)
 

strive4peace

AWF VIP
Local time
Today, 06:11
Joined
Apr 3, 2020
Messages
1,003
ps, @MK1999

here's a short video to show you how to do that:

Combo Box properties in Microsoft Access (11:25)
 

MK1999

New member
Local time
Today, 14:11
Joined
Mar 30, 2022
Messages
24
it show
Set combobox properties:
ColumnCount = 2 (or more)
ColumnWidths = 0;8 (first should be = 0)
s the name but i got in error (oyu must inpur value from id) something like that
 

MK1999

New member
Local time
Today, 14:11
Joined
Mar 30, 2022
Messages
24
hi! thank you i followed this tutorial.. it shows the name but i got this error.. the arabic says cordinator_program.ProgramId
1653377344622.png

ps, @MK1999

here's a short video to show you how to do that:

Combo Box properties in Microsoft Access (11:25)
!
 

strive4peace

AWF VIP
Local time
Today, 06:11
Joined
Apr 3, 2020
Messages
1,003
hi @MK1999 ,

As a general rule, each form should be based on just one table in the RecordSource. Are you trying to enter a record into 2 tables at the same time?
 

MK1999

New member
Local time
Today, 14:11
Joined
Mar 30, 2022
Messages
24
yes.. ID from the composite table (cooridnator_program) and name from coordinator table.. then is there any way to do this?
 

Eugene-LS

Registered User.
Local time
Today, 14:11
Joined
Dec 7, 2018
Messages
481
hi! thank you i followed this tutorial.. it shows the name but i got this error.. the arabic says cordinator_program.ProgramId
Your record sourse propery of combobox should have a query like that:
SQL:
SELECT FieldID, FieldName FROM coordinator_program ORDER BY FieldName;
 

Eugene-LS

Registered User.
Local time
Today, 14:11
Joined
Dec 7, 2018
Messages
481
ID from the composite table (cooridnator_program) and name from coordinator table.. then is there any way to do this?
Can you post a copy of your application with just the form (with subforms) and required tables/queries.
Just a few (fictitious) records to understand.
 

strive4peace

AWF VIP
Local time
Today, 06:11
Joined
Apr 3, 2020
Messages
1,003
yes.. ID from the composite table (cooridnator_program) and name from coordinator table.. then is there any way to do this?
Hi @MK1999
use comboboxes! Then you only need the cross-reference table for the form/subform

interesting that the message box shows Arabic and English ... and only put the tablename in Arabic and not the fieldname also ~
 
Last edited:

MK1999

New member
Local time
Today, 14:11
Joined
Mar 30, 2022
Messages
24
Your record sourse propery of combobox should have a query like that:
SQL:
SELECT FieldID, FieldName FROM coordinator_program ORDER BY FieldName;
But the name only store in coordinator table, and store the ID using coordinator_program table. in order to avoid duplicates name.. here is the ERD
1653463019496.png
 

MK1999

New member
Local time
Today, 14:11
Joined
Mar 30, 2022
Messages
24
Hi @MK1999
use comboboxes! Then you only need the cross-reference table for the form/subform

interesting that the message box shows Arabic and English ... and only put the tablename in Arabic and not the fieldname also ~
what i did is Form bound to Program, subform bound to Program_coordinator with a combobox to select coordinator and control source for the combobox is coordinatorID
row source is coordinaterID, name with 2 columns.. when the user click on the drop down menu is shows both name and id.. but from outside it is only ID which what we dont want.. i hope i explained well.. i am struggling with this from a while
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:11
Joined
Feb 19, 2002
Messages
42,970
If the master/child links are set correctly for the subform, Access will automatically populate the foreign key. You don't need the combo to do that. Start by defining relationships and enforcing Referential Integrity. For existing subforms, you will have to set the master/child links manually. For new ones, Access will do it because you told it how the tables were related.
 

alvesi

New member
Local time
Today, 12:11
Joined
Apr 12, 2023
Messages
13
hi @MK1999

yes. In the design view of the form, choose the combo control. On the Property sheet, on the Data tab, set the RowSource to what you want to store and display. When you click in the property, you'll see ... to the right. Click that and it will take you to a builder that is just like the design view of a query. Get the data how you like. then close and save

on the Format tab of Property Sheet, set:
Column Count
Column Widths
List Width
Hi Strive4peace,

I used your recommendation on a form and it worked very well. Is there a similar parameter configuration for a look up on a table? I am getting the same issue, only can see the “ID number” and I only need to see the “text name”

thank ypu for your reply
Isac
 

strive4peace

AWF VIP
Local time
Today, 06:11
Joined
Apr 3, 2020
Messages
1,003
Hi Strive4peace,

I used your recommendation on a form and it worked very well. Is there a similar parameter configuration for a look up on a table? I am getting the same issue, only can see the “ID number” and I only need to see the “text name”

thank ypu for your reply
Isac
hi Isac,

you're welcome! wonderful, thanks for letting me now.

> similar parameter configuration for a look up on a table?

not exactly sure what you're asking

... to reference a column of a combobox value, use
=me.controlname.column(#)
in ControlSource of another control such as a textbox

... to specify that the first column with the ID number isn't what shows in the combo, in the ColumnWidths, set the first column width to be 0 (zero);

... if you want to display a SAVED value in a table, you can use a domain aggregate function in the ControlSource of a combo ( =dSum(...), = DFirst(...), etc )
 

Users who are viewing this thread

Top Bottom