Relationship and combo boxes

[srpatel]

Hi Everyone,

Can some one please help me with cascading combo boxes. The structure of my database is below.

tblAsset_Numbers
Asset_NumberId AutoNumber
Asset_Number PK Text
LineID Number (Went through Look up wizard to get data from tblLine)

tblLine
LineID PK AutoNumber
Line Text


Main Table

tbl4MAssets_B&S

AssetID AutoNumber
Asset_Number Text
LineID Number went through look up wizard


Frm4MAssets_B&S
LineID Combo Box
Asset_Number Combo Box


Now I have a form that is bound to tblT4MAssets. What I am trying to do is cascade my Combo boxes on the form, so I can restrict the values of one combo box based on the value in another.

I want a user to select the LineID, then depending on what LineID is chosen the relevant Asset_Number appears in the Asset_Number Combo box.

I have created the relationships but I am still facing a problem. What I don’t see are the foreign keys being created and I don’t think I have structured the sql queries correctly.

Sql Queries,

Where do I place the queries on the table fields or on the form? Currently on tbl4MAssets_B&S on field LineID and Asset_Number I went through the lookup wizard so that I could view the data on the form but I cant seem to get the cascading to work.

I have tried implementing this on the combo box of Asset_Number in the row source:

SELECT tblAsset_Numbers.Asset_Number, tblAsset_Numbers.Asset_NumberID, tblAsset_Numbers.LineID
FROM tblAsset_Numbers
WHERE (((tblAsset_Numbers.LineID) Like [forms]![frm4MAssets_B&S]![cboLineID]));

I should think the Relationships, fine as I have had no errors.

Could any one please help me out on this. I would appreciate any help I get on this.

Thanks.

 

[KenReay]

Hi

see faq702-4289 in Forms forum

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[srpatel]

Hi Ken

Thanks for your reply.

I have gone through the steps and I have stumbled on another error, I keep getting the following error below, when clicking on any of the combo boxes:

Enter Parameter value:

Forms!frm4mAsset_B&S!cboLineID.

Any Ideas how i can resolve this?

Also on tbl4MAssets_B&S, when I select the drop down for Asset_Number, I cant seem to see anything. But when I go to tblLine, I can see the small plus sign that expands to show which asset number is allocated to what line.

hopefully you will be able to help me further.

Regards
Shreekant

 

[srpatel]

Ok,

I found out the problem with the Enter Parameter Value, but I still seem to have a problem with pulling the information into the second Combo Box.

As mentioned in the previous message, when I go to tables
tblLine, I click on the plus sign and I can see the relevant asset Numbers to the relevant Line, BUT when I got to tblAsset_Numbers and click the plus sign it takes me to tbl4MAssets_B&S.

The other thing I noticed, is that when I go to tbl4MAssets_B&S, no asset numbers appear in the asset_number drop down eventhough there are records in tblAsset_Numbers.