Still having problems with comparison

[Nike]

I posted before and I am sorry about posting again, but I
have new info that I need to submit for assistance.

I have 5 tables (actually more but this states my case)
tblCustomer
LAST4SSAN these three are my primary keys
FName
LNAME

tblClass
ID
Class and other reasons

tblScheduleClass
id
ClassID
Date
Time
etc.

tblStudentsAndClasses
ID
ScheduleClassID
LAST4SSAN/FNAME/LNAME

tblVisits
ID
LAST4SSAN/FNAME/LNAME
etc. other info related to the visit

Now on my form I have a combo box. When they select an
item from the combo box for classes being held that day
then I want it to compare to the tblStudentsAndClasses to
see if in fact this person is registered for that class.
If not then I want to pop a msg box for them to see a
staff member for assistance. If so then proceed and
register them in the tblVisits. I hope this makes it a
little clearer.

So here is what I think I need to do, please advise.

Dim vClass As Variant
Dim vSSAN As Variant
Dim rst3 As RecordSet
Dim dbs As Database
If Me!Reasonlist.Column(1) <> "Something other than class
for today" Then

vClass = "select ClassID from tblStudentsAndClasses "
& "WHERE ClassID =(select ScheduleClassID from
tblScheduleClass where =" & "Class = Me!REASONLIST.Column
(1))"
vSSAN = Me.SSAN &" "& Me.FName & " "& Me.LName
Set dbs = Currentdb
Set rst3 = OpenForSeek("tblStudentsAndClasses")
rst3.Index = "SSAN"
rst3.Seek "=", vSSAN
rst3.Index = "ClassID"
rst.Seek "=", vClass
If NoMatch then
MsgBox "Bad", vbInformation, "Is your selection correct?"
else
MsgBox "Good", vbInformation, "Is your selection correct?"
End If
End If

I keep getting Run-Time error 3421
Data type conversion error.

then it stops at rst.Seek "=", vClass

Any advice would be greatly appreciated. Thanks in advance

 

[Nike]

OK I am close to solving this can some someone shed light
on why one works but not the other?

Private Sub ReasonList_Change()

If DCount("[StudentClassID]", "qryReasonSIgnIN", "[SSAN]'"
& Me.SSAN &" "& Me.FName & " "& Me.LName &"' And [Date]
=date()") = 0 Then
MsgBox "Bad", vbInformation, "Is your selection correct?"
else
MsgBox "Good", vbInformation, "Is your selection correct?"
End If

This one doesn't work

Private Sub ReasonList_Change()

If DCount("[StudentClassID]", "qryReasonSIgnIN", "[SSAN]'"
& Me.SSAN &" "& Me.FName & " "& Me.LName &"' And ' &
[Class] = " Me!ReasonList And [Date]=date()") = 0 Then
MsgBox "Bad", vbInformation, "Is your selection correct?"
else
MsgBox "Good", vbInformation, "Is your selection correct?"
End If

Thanks

 

[Nike]

Never mind figured it out.

If Dcount("[StudentClassID]", "qryReasonSIgnIN") = "0" then
MsgBox "Bad", vbInformation, "Is your selection correct?"
else
MsgBox "Good", vbInformation, "Is your selection correct?"
End If
I put all the parameters in the query itself.


-----Original Message-----

OK I am close to solving this can some someone shed light
on why one works but not the other?

Private Sub ReasonList_Change()

If DCount

("[StudentClassID]", "qryReasonSIgnIN", "[SSAN]'"

& Me.SSAN &" "& Me.FName & " "& Me.LName &"' And [Date]
=date()") = 0 Then
MsgBox "Bad", vbInformation, "Is your selection correct?"
else
MsgBox "Good", vbInformation, "Is your selection correct?"
End If

This one doesn't work

Private Sub ReasonList_Change()

If DCount

("[StudentClassID]", "qryReasonSIgnIN", "[SSAN]'"

& Me.SSAN &" "& Me.FName & " "& Me.LName &"' And ' &
[Class] = " Me!ReasonList And [Date]=date()") = 0 Then
MsgBox "Bad", vbInformation, "Is your selection correct?"
else
MsgBox "Good", vbInformation, "Is your selection correct?"
End If

Thanks
.

 

[tina]

looks like your syntax is incorrect, s/b

DCount("[StudentClassID]", "qryReasonSIgnIN", "[SSAN]'"
& Me.SSAN &" "& Me.FName & " "& Me.LName & "' And [Class] = '" &
Me!ReasonList & "'And [Date]=date()") = 0

if [Class] is a numeric field, take out the single quotes around
Me!ReasonList.

hth