Textboxes And SQL Queries!

OK, this is my first post and unfortunatly it is for some help...

I have searched Google to my wits end without any luck.

Heres my problem (partially) and I am hoping some kind person can help me.

Suppose I have a form [form1] and a query [query1].

On form1 I have a 2 textboxes. [txtProduct] and [txtStockCount].

Into [txtProduct] I enter the name of a product. In reality I have already a usable listbox in my form, but I am using a textbox for simplicity here!

Now I create an [EventProcedure] using 'On Lost Focus' against [txtProduct].

The event prodedure would partially read:

"SELECT query1.StockTotal, query1.Product FROM query1 WHERE = Product = txtProduct;"

In my actual form I am using a listbox with a listbox.rowsource = (SQL Query)to display the stock count, but I don't think this is the best way... (It works, i'll give it that!).

What I would ideally like to do is take the results from that aformentioned query and display them in txtStockCount.

What I am aiming for is to get the a query to dump its single result into a variable; not just for display purposes (txtStockCount), but to later validate user input when they wish to reduce stock values from the database, IE so they cannot produce negative stock counts.

I have a feeling this might involve Record Sets which I know nothing about at present.

If someone could either provide some working sample code snippet or point me in the right direction I would appreciate it.

I am confident enough to write SQL statements. I am semi confident in using VBA. (I am not a complete newbie).

If I have not been clear enough, I can provide a lot more information using actual details from the database I am trying to develop.

The database is a stock control system which records:

* products in one table
* transactions in another table (goods in and goods out)
* a query to 'sum' up 'goods' in and 'goods out' with an expression to create total stock counts for each item
* a form into which a user can select an item from the product table which would then produce a single count of the stock running another SQL query against the one already created to pick out that single product (phew!)

and finally in the same form add to the transaction table.

Here's hoping!

Damo.