Message based on select query

[jabenj]

Thanks in advance for any help. I'm trying to do something that should be simple enough but it's not working. I'm trying to display a text in a field based on a select query. If a value exists in a field in my database (LotNum in Blendsheet input), then i want my text box on a form to say "finalled". I keep getting an object required error message.


Private Sub TankNum_Change()

Dim stSql As String
Dim rs As Object
Dim con As Object
Dim lotn As Variant



Set con = Application.CurrentProject.Connection
stSql = "Select [LotNum] FROM [Blendsheet Input] as lotn"
stSql = stSql & " WHERE [TankNum] = '" & cboTankNum.Value & "'"

Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1

TxtStatus.SetFocus
lotn = Me![LotNum]


If lotn Is Null Then
TxtStatus.Text = ("Not Finalled")

End If

If lotn = True Then
TxtStatus.Text = ("Finalled")

End If


Exit_Close_Click:
Exit Sub
End Sub

 

[PWise]

On what line do you get this error

 

[PHV]

Replace this:
lotn = Me![LotNum]
with this:
lotn = rs![LotNum]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jabenj]

Thanks for the help.

I'm getting the message on the line:

stSql = stSql & " WHERE [TankNum] = '" & cboTankNum.Value & "'"

I tried changing to rs![LotNum] but didn't work. I think it may have something to do wit the way I'm defining the variable "lotn" but i'm not sure

 

[PWise]

try changing

stSql = stSql & " WHERE [TankNum] = '" & cboTankNum.Value & "'"
to
stSql = stSql & " WHERE [TankNum] = " & cboTankNum.Value

 

[PHV]

Private Sub [!]TankNum[/!]_Change()
...
stSql = stSql & " WHERE [TankNum] = '" & [!]cboTankNum[/!].Value & "'"

You see the problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886