Query in SQL won't recognize an integer variable

[SalmonDB]

Hi everyone,

I've been working on a form for my salmon database. Each DNA collection has a range of sample numbers (they are historical, I need to keep them as they are), so based on the location ID selected, I want to query the DNA collection codes already in use, and automatically enter the next code in the sequence. For some reason the SQL refuses to co-operate with me on this.

Here's the part of the code that is causing problems:

Dim LX As Integer

LX = Forms.tblDNA_Kit_Prep.Location_ID.Value
      
Dim SQL1 As String
    
SQL1 = "SELECT MAX (tblDNA_Kit_Prep.VialStart) FROM tblDNA_Kit_Prep" & _
" WHERE ((tblDNA_Kit_Prep.Location_ID)=LX)"
    
rs.Open SQL1

I get the following error:
Run-time error: No Value given for one or more required parameters.

If I type this into the Immediate window

? LX
15

this shows that the LX variable is finding the number (selected Location_ID on the form in-use) that I expect it to, and if I replace the 'LX' with the number in the SQL:

SQL1 = "SELECT MAX (tblDNA_Kit_Prep.VialStart) FROM tblDNA_Kit_Prep" & _
" WHERE ((tblDNA_Kit_Prep.Location_ID)=15)"

then the form does what I want it to do. The problem of course being I don't want to go and enter the Location ID in the code everytime I use the form.

I'm new with VBA so I'm guessing I messed up some brackets or something, but any advice would be appreciated.

Thanks so much again!
E

 

[Isskint]

Hi Salmon

The error is in this line: " WHERE ((tblDNA_Kit_Prep.Location_ID)=LX)"

You are assigning LX literally, not the value. it should be

" WHERE ((tblDNA_Kit_Prep.Location_ID)=" & LX & ")"

 

[John Big Booty]

Try;

Dim LX As Integer

LX = Forms.tblDNA_Kit_Prep.Location_ID.Value
      
Dim SQL1 As String
    
SQL1 = "SELECT MAX (tblDNA_Kit_Prep.VialStart) FROM tblDNA_Kit_Prep" & _
" WHERE ((tblDNA_Kit_Prep.Location_ID)= [COLOR="Red"]" &[/COLOR] LX [COLOR="Red"]& "[/COLOR])"
    
rs.Open SQL1

 

[SalmonDB]

Thanks John and Isskint!

It worked perfectly, plus I think I have a better understanding of the how it all works too!