Combo-Box Options?

[PALman]

I have a number of combo-boxes on a form to give the user the possibility of selecting a number of relevant documents/specs and storing them in the fields e.g. RelevantSpec-01, RelevantSpec-02 and so on.
These combo-boxes are linked to a TABLE but is there a way to add code to the boxes to allow for a different table or list to be displayed depending on customer in customer field?
The code behind the combo-box is at present...

Private Sub Combo108_BeforeUpdate(Cancel As Integer)
End Sub

Or perhaps there is another way to select relevant specs for different customers and placing them in fields.
Any help much appreciated.
PALman

 

[Remou]

Just add a Where statement to the Row Source for the combo. For example:
[/tt]... WHERE CustNo = Forms!frmForm!CustNo[/tt]

 

[PALman]

Thanks Remou for your fast reply.
Not being a programmer and unfamiliar with the Where command could you explain the syntax behind your statement?
Also, I am not sure what [/tt]... and [/tt] mean or do.
Thanks again,
PALman

 

[Remou]

[/tt] and [/tt] mean I did not preview my post . They are Process TGML tags (see below).
If you look at the property sheet for your combo box, you will find Row Source on the Data tab. It will look something like:
[tt]SELECT CustNo, CustName FROM tblTable[/tt]
If you click on the three little dots to the right of this, it will open a query design window. Add a reference to the appropriate control on your form to the criteria line. It should look like this:
=Forms!frmName!txtCustNo
Where frmName is the name of your form and txtCustNo is the name of the control with the Customer Number in it.
This will add a Where statement, as described, to the row source of you combo box. This is more or less the same way that cascading combo boxes are built. There is an FAQ on the subject that you may wish to look at.

 

[PALman]

Thanks again Remou,
I shall try this, however I did try this before.
My difficulty is that I need to use Dlookup to find the CustName in another table (Enquiry Desk) where the common field in the current form and the Enquiry Desk table is JobNo.
So I have been trying code like...
CustName = DLookup("[CustName]", "Enquiry Desk", "[JobNo]='" & Forms![QA-Contract Review]![JobNo] & "'")
I shall keep trying.
Thanks again

 

[Remou]

It should be possible to add tables to the query screen for your combo to get this relationship. Failing that, you can add the DlookUp bit to the criteria for the Row Source.
Can you post the current row source? It will make it easier to explain what (I think) I am getting at.

 

[PALman]

The current Row Source reads...
SELECT [CustomerSpecs-01].[Specification], [CustomerSpecs-01].[Function] FROM [CustomerSpecs-01];
where CustomerSpecs-01 is one of many customer tables which list their own specifications.
Thanks Remou.

 

[Remou]

How does the table CustomerSpecs-01 relate to customers? In other words, is there a field in the table that shows which specification belongs to which customer?

 

[PALman]

No there is not, and that is maybe the problem. Each list or table of specifications are listed in seperate tables for each customer. What I am trying to do is pull down a list of specs depending on which customer.
I am going to have to leave this problem and revisit it at a later date but thanks for all your help.

 

[Remou]

Ok. A different table can be assigned for each customer, but it seems that you may need to look at the design of the database. Have you seen

http://www.r937.com/relational.html?