field in subform to run a query

[Guest]

Hi

Anyone that can help me with the below:

I have a order form with a orderline subform. This has a field for user to
enter quantity for items being ordered. On entering this quantity I want it
to look up a value which will have to be in a query, and if it is greater
than the result in the query it should give a msgbox (saying e.g not enough
stock reduce quantity).

How can I do this?

I created a query to show me the total items in stock for stock items.
I have also created a query to show me total items orderd by all customers
for items.

What I want is to combine the two queries to give me remaining stock for the
item being entered, and to look at what quantity was entered to make sure it
is less than or equal to what is in stock.

Tried doing this but got duplicate rows in the table and wrong sums
calculated.

Can anyone help me with this?

Thanks in advance.

 

[Duane Hookom]

I would combine your two queries using a union query and then total this to
get a single query by item and the quantity available. Use this query in
your subform as a combo box to select the item. Make sure you use the
quantity available in the row source of your combo box.

Then in the after update event of your quantity field you can use code like:

If Me.txtQuantity > Me.cboItem.Column(2) Then
MsgBox "You don't have that many to sell"
End If

Columns in a combo box are numbered beginning with 0.

 

[Guest]

Not used union query before but do union queries have to have the same fields
in both tables being used, if so, mine dont... correct me if I am wrong please

thanks

 

[Duane Hookom]

Union queries must have the same number of fields and should match the data
type and contents.