Query Help (1 Viewer)

[MrSmith]

Hello there , i have two questions if someone can help,

1. I have a form that displays various fields in a table, one of the fields [PO] contains a ref number.
I want to be able to press a button on the form that will open a message box requesting the [PO] number , and once entered will take you to the form page showing that [PO] and its associated data.

I made a query using the PO field and entered the [Please Enter the PO number] in the criteria
this then allows me to enter a po number however it only then displays the number requested in a data table.

2. I have 3 tables , table one contains all the main data it looks to the other 2 tables for drop down combo fields, table 2 has a list of vendors in it , table 3 has a list of websites.
what i want to do is for the user to choose a vendor on table 1 from the drop down box , and this will then automatically choose the assosciated website of the vendor.
how would i go about doing this?

thankyou so much for your help

 

[Bigmo2u]

Here is a sample I got from this site that works great for me. Just a thought.

 

[x0reset]

Okay, Question 1:

I recommend not using a query. Use the ApplyFilter command instead:

Private Sub YourButton_Click()

Dim stFilter as String
Dim stMsgRslt as String

stMsgRslt = Msgbox ("Your message here"...)

stFilter = "[PO]= '" & stMsgRslt & "'"
DoCmd.ApplyFilter , stFilter

End Sub

Question 2:

Assuming that your main data table can reference multiple vendors, but vendors can only have one website:

Set the "Data Source" property of your main form to the main data table. Then create a subform and modify the "Data Source" property of said subform to use a query with all three tables. Put the subform on the mainform linking the primary key on the main form to the matching field in the subform.

Next, Place your combo box on the main form, not the subform. Finally, use the ApplyFilter technique outlined in question one in the "AfterUpdate" event of your combobox.

Using this method, after the user picks a value from the combobox it will filter the recordset of the main form to show only the selected record, then cascade filter the subform to show only the vendor and website information relevant to the record shown on the main form.

Hope this helps!