Data Entry Form with Combo Lookup

[MikeZ]

Hi - I hope someone can help me because I am tearing whats left of my hair out over this problem.

I am designing a data entry form based on a table. The data to be entered includes JobNo, CostCode, Activity Code and some times. I want to have a combo box look up another table to show the possible list of JobNo, CostCode and Job Name as verification. Once selected I want those 3 fields to remain visable on the form so that the remaining data can be entered and saved to the table. (Job Name will not be saved)

I can see the 3 columns in the combo box but nothing I try will get it to remain visable on the form after update - at best I get the 1 bound column.

Can someone please point me in the right direction.

MikeZasperated - hair today gone tomorrow

 

[Andrew Fergus]

Hi Mike

Remove TblCostCodes.CCodeID from QryActivity and in it's place add TblActivity.CCodeID. On your form, the control source from the combo box should be TblActivity.CCodeID (but when you select it in your combo box design, you will only be selecting CCodeID - confusing I know, that's why it pays to use a slightly different names for 'foreign keys' in the other tables).

If you still can't get the combo box to work, get it to select the cost code from TblCostCodes instead of QryActivity (like the Northwind example) - although I'm not so sure about the 'Job Name' field. If you still still can't get it to work, build a small query with the 4 fields you want in the combo box and base your combo box on this new query - this will help you get it into the correct sequence. BUT remembering of course to use the correct control source.

BTW thanks for the positive comments - I don't mind giving my time on occasion because I'm keen to see more people use Access. IMO it is not used enough in the workplace but when it is, the learning curve can be quite steep so it can be disheartening for new users. Look for example at the number of question on the Excel forum versus this one - I tend to stick to this forum (and the lounge).

HTH, Andrew.

 

[MikeZ]

Hi Andrew
Success!
I ended up going with option 2, with the combo box based on TblCostCodes and it all seems to work. What a great leaning experience. I have a much better understanding now of relationships and controls as a result of this exercise
Again, thanks very much for all your help. I agree it is a rather daunting learning curve but made the much easier with such generous help.
I anticipate this exercise will develop into a larger project so look for my white flag down the track

ATB
Mike