Combo Box Problem

[Jon Sp]

Hi,

I have what seems to be a very simple problem, but I just cant work out how to fix it.
Within a database I have bodged together with limited know-how and alot of luck I have 2 combo boxes on a particular form. One lists all parts of an engine that could fail and the other lists all the areas that all these parts are fitted. What I want is when the part that has failed is chosen, the area it is fitted in is automatically inputted, so that the user cannot choose the wrong area.
The data is stored thus:
the failed part is stored in a table called Part, which has 2 columns - the part, and the area it is fitted.
The data entered is then stored in a table called Engine Data which, among others, has 2 columns, failed part and area.

When the part is chosen from the combo box how can I get the area to be automatically filled in?

I have looked through many other posts on cascading combos etc and all they do is confuse me.
Please understand that I have no VB experience and limited Macro and Code experience. In fact I only know enough of Access to do what I have already done...please help!!!!

 

[ColinEssex]

Can a part be associated with 2 different areas?

Col

 

[Jon Sp]

No, only the one.

 

[ColinEssex]

So why do you need a second combo box? why not use a DlookUp based on the selection of the first comBo box

Col

 

[Jon Sp]

Yeah I've played around with a DLookUp function, but just cant get it to work. Probably missing something simple.

 

[ColinEssex]

In a textBox control source type this

=DLookUp("[Area]","TableName","[Part] = Form.ComboName")

Obviously [Area] and [Part] are your field names.

In the AfterUpdate of the ComboBox put in a refresh.

Hope this helps

Col

 

[Jon Sp]

Thanks very much.

I'll give it a try - but not today 'cos home is calling.

 

[Jon Sp]

Colin,

I have tried your suggestion but cant get it to work, I just get an error message.
I think we may be talking at cross purposes though because surely if you put the DLookUp function in the control source you cant use the control source to store the selected item in the main table. Could it be used in the default value box, with field name of the storage column in the control source?

Also how do you put a refresh in the afterupdate box? (is it something as simple as typing refresh?)

 

[Rich]

If the data already exists in one table, you don't need to store it again in another, only the PK. If you want to display the area add an unbound text box to the form, set its control source to =Forms!MyForm!MyCombo.Column(1) where column1 is the second column in your combo

 

[Jon Sp]

I think you missunderstand, or I do - the data already exists, but only in a table that is designed to feed a combobox. The operator then chooses a value from the combobox for which part has failed. (From table Parts, which contains 2 columns Parts, and Area relating to where the part was fitted.) I would like the Area to automatically be filled in, in a text, or combo, box, which then feeds a main table (called EngineData) which stores all the info ready for display in various tables and reports.
Therefor the control source must be (as I see it) the Area column in the EngineData table.
Any thoughts?

 

[Oldsoftboss]

See if this helps to explain Rich's reply. It's a cut down version of a combo box sample DB. I can email the whole thing to you if you like. (Too big to post here)
Dave

 

[Jon Sp]

Thats great thanks, and almost exactly what I need, but how would you then take that data selected, and store it in a different table, say to record that the option has been chosen?
i.e. if on the same form you had a text box for someones name, which was manually inputted, and then you select the address of their company as the program does, how would you store all that data in a seperate table, say to populate a report later? As the control source is used up by the code can this be done....Ahh confusion!!!

 

[Oldsoftboss]

As the experts on this forum repeat over and over, you should not store the data more than once. Just pull it together with a query and populate your reports etc from there.

Remember your question

if on the same form you had a text box for someones name, which was manually inputted, and then you select the address of their company as the program does...

remember the data is already there, no need to double up.

Trial and error is sometimes the best way to learn

HTH
Dave