Problem with query based on form components

Chunk

Registered User.
Local time
Today, 07:57
Joined
Oct 25, 2004
Messages
64
I have a form with a combo box (sup_numbers_combo), which has a list of supplier numbers in it. (on a master form).

In a sub form, i then set the row source for a combo box (product_numbers_combo) to this:

SELECT [product_number] FROM Supply_link WHERE [Supply_link].[sup_number]=[FORMS]![myForm]![sup_numbers_combo];

Hence, "Sup_number" is a PK in Supplier and an FK in Supply_link.

I want the product_numbers combo to be filled with the values of product_number in Supply_link, where the sup_number is equal to the number selected on the form.

Im pretty sure I have the query correct. If I enter it as a query and then force it a value for: [FORMS]![myForm]![sup_numbers_combo], it works fine.

However, when I set it as the row source for "product_numbers_combo", "product_numbers_combo" is never populated with any values.

Can anyone help?
 
Chunk,

Code:
Me.product_numbers_combo.RowSource = "SELECT [product_number] " & _
                                     "FROM   Supply_link " & _
                                     "WHERE [Supply_link].[sup_number] = " & [FORMS]![myForm]![sup_numbers_combo] & ";"

Wayne
 
WayneRyan said:
Chunk,

Code:
Me.product_numbers_combo.RowSource = "SELECT [product_number] " & _
                                     "FROM   Supply_link " & _
                                     "WHERE [Supply_link].[sup_number] = " & [FORMS]![myForm]![sup_numbers_combo] & ";"

Wayne

OK, got it working. Thanks. Im not sure what event to put it on. click doesnt seem to work, and get_focus only works for focus change on the subform.
 
Last edited:
Chunk,

Since the whole effort revolves around the field [sup_numbers_combo], I'd
use its AfterUpdate event.

Wayne
 
WayneRyan said:
Chunk,

Since the whole effort revolves around the field [sup_numbers_combo], I'd
use its AfterUpdate event.

Wayne

Yep got it working.

Thanks a lot.

Ive got a similar new question:

I have an order details sub form. I want the price of the item on the order details line to be shown.

The price is stored in the Products table. I allready have the product_number in the sub form, so ive made a query that picks out the price from the Product table, where the product number is equal to what the supplier has input on the form.

Im now trying to add an event to the product number combo box in the sub form so that it updates the product price automtically, by looking running the query.

However, im not sure what to put in the event. Ive tried:

[supply_price] = "SELECT Supply_price FROM GetSupplyPriceQuery;"

All i get is the query as a string in the supply price text box.

Can you help?
 
OK, I basically now need to do exactly why Wayne helped me do, but I want to put the result in a text box, not in a combo box.

How can I do this?

I guess I need to change:

Me.product_numbers_combo.RowSource

But I cant find any information on what to change it to.

Thanks in advance.
 

Users who are viewing this thread

Back
Top Bottom