Combo Box Problem

[burrelly]

I have an issue with some combo boxes that I am unsure how to solve.

I have a table called employees and within that there are two fields both formatted as text. The fields are as follows:

Name
JobTitle

I have a second table called jobtitle with one field formatted as text it is called jobtitles.

What I want is a form that has the name field as a text box and the jobtitle box as a dropdown the default value will = what is in the employee table. The drop down will include all the items in the jobtitle table excluding the default value.

Is this possible? If so how?

Thanks

 

[Access_guy49]

When using a combobox, in the RowSource:

SELECT TableName1.Field1, TableName1.Field2, TableName2.Field1 FROM TableName1 INNER JOIN TableName2 ON TableName1.LinkField = TableName2.Linkfield;

(The above displays 2 fields from the first table and 1 field from a second linked table)
The first field is always the default field (unless changed in the control properties)

Under the Format section of your Dropdown, in the column Width attribute:

0";1";1"
This will hide the first field and display the second and third fields. When the user selects a drop down value they are actually selecting the hidden value to be stored however all they see is the other values.

Hope that helps.

 

[boblarson]

When using a combobox, in the RowSource:

SELECT TableName1.Field1, TableName1.Field2, TableName2.Field1 FROM TableName1 INNER JOIN TableName2 ON TableName1.LinkField = TableName2.Linkfield;

(The above displays 2 fields from the first table and 1 field from a second linked table)
The first field is always the default field (unless changed in the control properties)

Under the Format section of your Dropdown, in the column Width attribute:

0";1";1"
This will hide the first field and display the second and third fields. When the user selects a drop down value they are actually selecting the hidden value to be stored however all they see is the other values.

Hope that helps.

Just a reminder that you also have to set your Number of Columns property to 3 to use this.

 

[wjburke2]

On your update form you need two boxs name and title. on the title set the row source to your jobtitle database. This will give you a screen where the user can select the job titls from a dropdown but the defualt will be what is on the employee record.

 

[Access_guy49]

LOL Sorry about that! I knew i was missing something! Thanks for the save on that Bob!

 

[burrelly]

When using a combobox, in the RowSource:

SELECT TableName1.Field1, TableName1.Field2, TableName2.Field1 FROM TableName1 INNER JOIN TableName2 ON TableName1.LinkField = TableName2.Linkfield;

(The above displays 2 fields from the first table and 1 field from a second linked table)
The first field is always the default field (unless changed in the control properties)

Under the Format section of your Dropdown, in the column Width attribute:

0";1";1"
This will hide the first field and display the second and third fields. When the user selects a drop down value they are actually selecting the hidden value to be stored however all they see is the other values.

Hope that helps.

That is great I am usless when it comes to following code though could you perhaps make a small sample file?

 

[Access_guy49]

take a peek at this. if you have any questions feel free to ask!

 

[burrelly]

That is great but one thing in the jobtitle field in the employee table it puts the id number of the job title what I need there is the actual jobtitle as i pull these details else where in a sub form.

 

[Access_guy49]

This example has had the row source shifted and the field attribute changed to text, now it has the jobtitle as the link. I don't know how everyone else feels about this, but i personally do not like linking data using text values. It leaves alot more room for human error and not to mention your duplicating data that doesn't need to be duplicated. You might want to consider using the number system in example 1 to use with your subform as well. however, if you are deadset on using the jobtitle, then this one should do the trick.

 

[burrelly]

Ok so if I use the number how in the sub form do i show it as a text value?

 

[Access_guy49]

I have created a new example for you to take a look at that uses a subform. The subform displays the information from both tables. I also created a relationship between the two tables, this allows the data in the subform to be associated to the person through the number system that was used in sample1.

The form that contains the subform is called "frm_employee_withsub"

I'm assuming this is sorta what you would want to show?

If you have additional questions or need some clearification don't hesitate!