I've been trying to help my daughter with her homework and spent the last few hours stuck trying to make an SQL select statement work in her database. She is supposed to enter a product (e.g. AB345) and a quantity at the prompts then the routing is supposed to reply with one of the cases. I've attached the database so you can see what is happening. Running the macro will run the code.

Thanks for your help!

You need single quotes around the variable, or at least from the name of the variable it looks like a string.

"SELECT UnitsInStock FROM Products WHERE ProductID ='" & strId & "'"

And I don't see where you are running the SQL?

Edited by: truittb on Wed May 18 15:33:35 EDT 2005.

Thanks for your reply. I have been trying to get that line to retrieve data from the Products table and it doesn't work. The "Select" line is the SQL statement I was referring to. Do I need to add a statement that initiates SQL code? or opens the Table? I pasted your line into the code with "strSelect =" in front of it and I get a "Type Mismatch" error. The variable "strSelect" is an Integer and the UnitsInStock field is a number so there shouldn't be a type mismatch. How do I make this work?

If your query is returning a single unique value and for the homework assignment you need to use a SQL statement, I would do this.

Create an ADO Recordset and assign the value returned by the recordset to a variable and then use the varaible in your Case construct.

Just assigning a Select statement to a variable will not execute the query. The type mismatch is from trying to assign a string value (The Select Statement) to an integer.

Try searching on ADO and see what you can find.

It's obvious that I don't know what I'm doing. I tried to create the ADO recordset (rs) using this code and it won't allow me to assign the results of the Select statement to it.

Public Function CheckInv()

'declare variables and assign address to object variables
Dim strId As String
Dim strNum As Integer
Dim strSelect As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

strId = InputBox(prompt:="Enter the product id:", title:="Product ID")
strNum = InputBox(prompt:="Enter the number of units customer ordered:", title:="Units Customer Ordered")

rs = "SELECT UnitsInStock FROM Products WHERE ProductID ='" & strId & "'"

I've continued to work on this. I even tried to run a simple "Select * FROM Products" statement and can't get it to show the contents of the table. I've used examples from books but I think I am still missing something very basic here. If I could get it to just show the table, I could build on that.

This is what I tried to run. Why doesn't it work?

Public Function CheckInv()

'declare variables and assign address to object variables
Dim strSelect As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

strSelect = "SELECT * FROM Products"
rs.open strSelect

End Function

This should return the value in the Units in stock field for the Product ID Entered
[code]Public Function CheckInv1() As Long

'declare variables and assign address to object variables
Dim strSelect As String
Dim rs As ADODB.Recordset
Dim strId As String
Dim intNum As Integer
Set rs = New ADODB.Recordset

strId = InputBox(prompt:="Enter the product id:", title:="Product ID")
intNum = CInt(InputBox(prompt:="Enter the number of units customer ordered:", title:="Units Customer Ordered"))

strSelect = "SELECT UnitsInStock FROM Products WHERE ProductID ='" & strId & "'"

With rs
.Source = strSelect
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open options:=adCmdText
CheckInv1 = !UnitsInStock
.Close
End With

Set rs = Nothing

End Function

Thanks Truitt,

I added the last section with the different messages for the quantity ordered and it works very well. I really appreciate your help.

There is no way that I could have figured out that "rs" section. Now I have to explain how this works to my daughter.

In the hours I spent struggling with this, I learned that the value a SELECT statement comes up with is only in text. So, in order for the result to be used in any calculation, it has to be converted to numeric. Is that the purpose of the recordset that is created in the "rs" section - to change the text strSelect to the numeric CheckInv1? Does the exclamation point in front of "UnitsInStock" make it numeric?

Thanks again for all your help Truitt.

dbJunkie2 (Brent Smith)

The !UnitsInStock is referencing the field from the query. If you had additional fields you would reference them the same way. !YourField. There are 4 ways to reference the fields you should be able to look up the ways in Access Help.

The Select statement itself is a string. The values that it returns can be anything, it depends on the underlying table and any conversion in the SQL.

You are welcome.