Noob question - Query that restricts to selection of other attribute

[Alexandre Cote]

I dont know how to phrase this in technical terms, so it is difficult for me to check online the solution to this.



My Query is for contacts. When the users will enter a new contact, the two first fields are the Company and the address. There will be hundreds of contacts once this tool is in production, and 1-8 addresses per company. Therefore I need to make it so that when the user selects the proper company, the next field restricts itself to what the user inputted.

How do I set this up?

Currently I only have a selection box, but it is unrestricted, and therefore shows everything in the vendor table.

Thanks for any help

[jzwp11]

You are describing cascading combo boxes. For the cascading combo boxes to work, you will need the correct table structure. You say that a company can have many locations. So a basic structure would look like this

tblCompanies
-pkCompanyID primary key, autonumber
-txtCompanyName

tblCompanyLocations
-pkCoLocationID primary key, autonumber
-fKCompanyID foreign key to tblCompanies
-txtAddress
-txtCity
etc.

Bob Larson's site has an example database that illustrates cascading combo boxes. Roger Carlson's site also has examples

[Alexandre Cote]

Thanks Jwzp11,

My desing corresponds to what you suggested me to do. Thanks for directing me to the right tutorials!!!

[Alexandre Cote]

Ok, I corrected the query so that it restricts to the current form like this:

SELECT tblAddresses.pkAddressID, tblAddresses.fkVendorID, tblAddresses.txtAdressNoAndStreet, tblAddresses.txtCity, tblAddresses.txtProvince_State, tblAddresses.txtCountry, tblAddresses.txtPostal_Zip_Code FROM tblAddresses WHERE (((tblAddresses.fkVendorID) Like [Forms]![frmContacts]![fkVendorID]));


My table structure is:

Vendor Table:
pkVendorID
txtVendorName
txtConsortiumID
txtParentID
…

Contact Table
pkContactID
fkVendorID
txtMailStop
txtLastName
txtFirstName
…

and my form named frmContacts contains everything in the contact table



It does not update.... How come? Is there an additional step I have to take other than changing the query? Thanks.

EDIT: I figured it out. So for the sake of completing my question:

Add VB code to the first combo box... properities -->event --> afterupdate add VB code that executes the query of the second combo box like so:

Private Sub fkVendorID_AfterUpdate()
On Error Resume Next
Me!fkVendorID = "SELECT tblAddresses.pkAddressID, tblAddresses.fkVendorID, tblAddresses.txtAdressNoAndStreet, tblAddresses.txtCity, tblAddresses.txtProvince_State, tblAddresses.txtCountry, tblAddresses.txtPostal_Zip_Code FROM tblAddresses WHERE (((tblAddresses.fkVendorID) Like [Forms]![frmContacts]![fkVendorID]));"

End Sub


And everything works like magic.

[jzwp11]

Regarding your query, it would be better to use = instead of Like in the criteria since you are looking for an exact match. Also, since you are assigning the query to the row source in code, you have to have the vendorID as a variable not as part of the SQL text. You can also use the "me." short hand to refer to the form since you are executing the code from the same form.

 
Me.Combo39.RowSource ="SELECT tblAddresses.pkAddressID, tblAddresses.fkVendorID, tblAddresses.txtAdressNoAndStreet, tblAddresses.txtCity, tblAddresses.txtProvince_State, tblAddresses.txtCountry, tblAddresses.txtPostal_Zip_Code FROM tblAddresses WHERE (((tblAddresses.fkVendorID) ="  & me.combovendor

[Alexandre Cote]

Yeah i was pretty buggy indeed lol that seems to have fixed the problem

[jzwp11]

Great! Let us know if you have any other questions.

[Alexandre Cote]

Thanks a lot for you help, this forum is really helping me a lot! I am an IT intern in a departement in which there is not an appropriate mentor. It is relieving a lot of stress.

[Alexandre Cote]

Finally I changed the code and decided to let the object handle the requiry. I did so by giving this code:

Me.Combo39.Requiry

I also did the changes you said for the "Like"

Everything seems to be perfect now :-) Thanks a lot!

[jzwp11]

You're welcome. Glad you got it worked out.