Query problem in regards to Item Collection

[Stacey]

I wrote a function that will take a value and compare it
to the values of a record in a query I have created. When
the compiler reaches the record in the query I want to
compare too, I get an item not found in collection
message. I am not sure what I am doing wrong. Here is
the function I have coded and the item not found in
collection is rec!badge_#:

Public Function CheckOpenLog(db As Database, qry As
QueryDef, frm As Form, badgenumber As Integer) As Integer

Dim badge As Integer
badge = badgenumber
Dim rec As Recordset
Set qry = db.QueryDefs("BadgesOut")
Set rec = qry.OpenRecordset()
DoEvents
Do While Not rec.EOF

If badge = rec!Badge_# Then

MsgBox ("This badge is still in use, log out
before reissuing!"), vbOKOnly, "Card Issuing Error"

Exit Function
End If
rec.MoveNext
Loop

End Function

 

[Gerald Stanley]

The likeliest reason for this error is that there is no
column called Badge_# in the output from the query 'BadgesOut'

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

I actually have 2 columns that are produced from the
query, badge # and time out. Do you have to do anything
other than what I did to link up the query output to the
function?

 

[Gerald Stanley]

Is the column called "Badge #" or "Badge_#" in the query.
The code is expecting the latter but your reply indicates
the former. I would advise renaming both as Badge# as
spaces in column names are troublesome.

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

I renamed the query column and I still get the same
problem. For some reason the query doesn't seem to be
loading the records properly into the function. I have
the query output open in front of me, but I still get the
item not found in collection message when I run the
function. When I put the cursor over the variable used to
compare against the value is there, but when I scroll over
the badge# it says <item not found in this collection>.
I'm really confused, especially since I just did a similar
thing in another application and it worked fine.

 

[Gerald Stanley]

Try using a column name without #.

Gerald Stanley MCSD

 

[Guest]

That was it, thank you very much. I really appreciate the
time you took to walk me through this, I'm sort of on an
island at work and don't have anyone to ask for help with
anything other than the basics. Thanks again.