Select statement syntax?

[ksmith]

Thanks in advance for your help.



I have a form that I am using to enter a lot of info into couple different tables.

I am calling the function below and wanting to retreive the 'value' of field AMOUNT in TABLE; tblShipping.

txtCatalogModelNUmber is an unbound text box on the form that has the Catalog Model Number that the user will enter.

Public Function FindAmount()
Dim strsql As String
Dim strMyString As String

strMystr = Me.txtCatalogModelNumber

strsql = "Select Amount from tblShipping " & _
"Where CatalogModelNumber = " & Chr$(34) & strMystr & Chr$(34)

Debug.Print strsql
End Function

Select Amount from tblShipping Where CatalogModelNumber = "263"
The above statement is what is in the immediate window.

263 is what I entered on the form to start the search.

The questions that I have are;
1. Is this the correct way to do this?
2. How do I 'store' the value that is in the AMOUNT field to a text box on the form?
3.Also I need to be able to subtract another Quantity (on the form) from this AMOUNT and replace AMOUNT with that calculation.

Thanks for the help.

[pbaldy]

1) It's a start. You would then open a recordset on that SQL to actually get the value.

2) As in 1 above, you'd open a recordset on the SQL. You might find DLookup() simpler to use at first.

3) You could execute an update query from code.

Your question implies that you're trying to maintain an inventory quantity on hand or something to that effect. In general, I'd recommend against it. There's a discussion on the topic:

http://allenbrowne.com/AppInventory.html

[ksmith]

Thanks for info I will give it a try. It's nice to know there are MVP's ready to help a struggling Access programmer.

Thanks again.
KSmith

[pbaldy]

No problemo; post back if you get stuck.