I Am Going To Pull My Hair Out!

[sparklegrrl]

I have searched the forums, downloaded samples and tried and tried to get a cascading combo box to work to no avail!

Here is the setup:

Workorder Table
WorkOrder ID
Customer ID
ContactID
etc
etc
etc

Customer Table
CustomerID
Customer Name
etc
etc

Contact Table
Contact ID
Fname
LName
Phone
etc
CustomerID

I am building a form based on the Work Orders table. I have a combo box named cboCustomer for the customers and it is working fine.

I want to add a combo box for contacts that shows the contacts listed based on the customer selected and I have an expression in a query.

The cboContact box contains a query exp1:[FName]&" "&[LName] and Customer ID. It works fine....ALL the customers are displayed in the drop down list.

However, if I go into the properties for the box and set the parameters under the CustomerID column to [forms]![WORKORDERSform]![cboCustomers] nothing appears at all!

I am so frusterated I could throw my pc, I've been at this one box for 3 days now!

What are the customer ID and contact id values in the work order table supposed to be set to? Does that make the difference?

 

[dcx693]

However, if I go into the properties for the box and set the parameters under the CustomerID column to [forms]![WORKORDERSform]![cboCustomers] nothing appears at all!

You mean you're setting that as the criteria, right?

How many colums are in your cboCustomers combo box? I'd say you need at least two: one for the customer name, one for the ID number. Which column of the cboCustomers combo box is the "bound" column? You've got to make sure it's storing the values from the 2nd column.

The cboContact combo box probably also needs to consist of two columns: one for the ID and one for the name.

 

[sparklegrrl]

I thought that was how I had it set up! Uugh!

http://users2.ev1.net/~dls4967/newchandler.zip

 

[boblarson]

Your problem, is that your Contacts table uses the customer name as it's CustomerID, but it should be the same as the Customer table which has a number.

So, your contacts combo box is searching for a text name, where your bound column for the customer combo box is the actual customer ID NUMBER.

 

[jeremie_ingram]

What you needed to do was on the work orders form, go to the properties of the combo box Contact Id. When you look at the data source, you needed to change the criteria from
[Forms]![WORKORDERSform]![cboCustomer]

to [Forms]![WORKORDERSform]![cboCustomer].[BoundColumn]

and then the names come right in like you wanted.

 

[boblarson]

Sorry jeremie_ingram, but that is not the case. If you do that, all you get is Contact number 1 - Kerri Higgins. Like I stated before, the Contacts table should be using the primary key (CustomerID) of the Customer Table as it's Foreign key and should not have a column with Customer (the name) as the data within a column named the same as the Customer Table's Primary Key.

 

[dcx693]

A little off topic here, but I did not even know that there was a .BoundColumn property. But how is:

[Forms]![WORKORDERSform]![cboCustomer].[BoundColumn]
different from
[Forms]![WORKORDERSform]![cboCustomer]
?

 

[sparklegrrl]

Okay,

In my CONTACTS table, I have the CustomerID set to number. I have the look up set to ComboBox.

Row Source Type=Table/Query
Row Source..when I click on the three dots & it opens the query window I have 2 columns...CustomerID & Customer (in asending order) from the CUSTOMERS table.

The bound column is 1
Column Count 2
Widths: 0";1"

Is this wrong?

 

[boblarson]

I've never used a lookup within a table, so I can't tell you.

 

[sparklegrrl]

So I don't need that in the table? Only in the combo box??

I have it working right now where the contact list drops down ALL contacts but when I put in [forms]![WORKORDERform]![cboCustomer] in - everything disappears!

God I am so frusterated!

 

[boblarson]

Can you repost the database with your changes, it was so much easier to see what was going on with an actual database. (I'm more visual)

 

[boblarson]

Also dcx693:

There shouldn't be any difference. At least from my understanding.

_____________________________

You wrote:
A little off topic here, but I did not even know that there was a .BoundColumn property. But how is:

[Forms]![WORKORDERSform]![cboCustomer].[BoundColumn]
different from
[Forms]![WORKORDERSform]![cboCustomer]
?

 

[dcx693]

boblarson, that's what I thought. Thanks.

 

[AncientOne]

Sparkle, I thought we thrashed this out two weeks ago? What's gone wrong? Post your db!

 

[sparklegrrl]

We did but I never got it working correctly. Bob did though I believe....I haven't checked my email yet.


THANKS!

 

[Darrell]

Here is my solution if you're curious...

It just took a bit of fiddling with the ContactID combo box

dh