show name in a combo box but store id

[MK1999]

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]

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]

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]

ps, @MK1999

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

Combo Box properties in Microsoft Access (11:25)

 

[MK1999]

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]

hi! thank you i followed this tutorial.. it shows the name but i got this error.. the arabic says cordinator_program.ProgramId

ps, @MK1999

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

Combo Box properties in Microsoft Access (11:25)

!

 

[strive4peace]

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]

yes.. ID from the composite table (cooridnator_program) and name from coordinator table.. then is there any way to do this?

 

[Eugene-LS]

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:

SELECT FieldID, FieldName FROM coordinator_program ORDER BY FieldName;

 

[Eugene-LS]

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]

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 ~

 

[MK1999]

Your record sourse propery of combobox should have a query like that:

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

 

[MK1999]

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

 

[KitaYama]

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
View attachment 100762

Is it Arabic office relation window?

 

[Pat Hartman]

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.

 

[cheekybuddha]

Looks like the BE is MySQL.

 

[Pat Hartman]

Looks like the BE is MySQL.

Why does that matter?

 

[alvesi]

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

 

[cheekybuddha]

Why does that matter?

It was in answer to KitaYama's question. (Sorry, don't remember seeing your reply back at the time )

Is it Arabic office relation window?

@KitaYama - it looks like the ERD is created in MySQL Workbench

 

[strive4peace]

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 )