Recordset Issue

[Wes28]

I'm trying to pull a record out of a table.

In running through the code in debug. I cant seem to find out why or how to get rid of these quotes around my number when running this code.

Dim rs As Object
    
    Set rs = Me.Recordset.Clone
        
    rs.FindFirst "[EmpNumber] = " & Me.txtENumber.Value & ""
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
        
    
    If Me.txtENumber = Me.EmpNumber Then
        
        MsgBox "It Works", vbOKOnly, "Invalid Entry!"
        
    Else
            
        MsgBox "Serial Number Doesn't Exist", vbCritical, "Search Error"
        
    End If

when I step through the code.

Me.txtENumber is showing up as "185"

and

Me.EmpNumber is showing up as 185

I think those quotes are my issue.
Not sure how to get rid of them though. Both items in question are set to numbers.

 

[jamesmor]

try

rs.FindFirst "[EmpNumber] = " & Me.txtENumber.Value

 

[vbaInet]

Look into the DLookup() function. It requires three parameters.

 

[Wes28]

Thanks Jamesmor. Still doing the same thing. I've used the same code in two other forms the same way. Never had an issue with it. Now this is going on.. Been messing with it all day. I'm just totaly lost on it.

 

[SOS]

How about changing this line:

If Me.txtENumber = Me.EmpNumber Then

To

If CInt(Me.txtENumber) = Me.EmpNumber Then

That is assuming Me.EmpNumber is stored as an Integer. If it is LONG INTEGER instead, use

If CLng(Me.txtENumber) = Me.EmpNumber Then

 

[SOS]

I've used the same code in two other forms the same way. Never had an issue with it.

Make sure you don't have any formatting on that field in the table.

 

[Wes28]

Thanks, SOS

If CLng(Me.txtENumber) = Me.EmpNumber Then

This did the trick. Why would this work with this format? When it worked before the other way. Just trying to understand what I might be missing.

Thanks again.

 

[SOS]

One of your controls or fields is not set to the same datatype.

 

[LPurvis]

Just to comment on the earlier Find code too...
It's generated by the Access combo wizard - so I'm not, for one moment, attempt to criticise you for using it

Dim rs As Object

Set rs = Me.Recordset.Clone

rs.FindFirst "[EmpNumber] = " & Me.txtENumber.Value & ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

This format first came about in Oooo 2000 and then adapted slightly - but not corrected until recently.
It's an effort to abstract the Find code away from using either DAO or ADO so that forms with either source would work.
(Note that this isn't due to ADPs - the wizard in an ADP creates find code which is entirely appropriate).

The recordset is declared as an Object to allow either type and then is assigned to a clone of the form's recordset. That's fine.

However ADO and DAO differ in the Find method they support.
DAO uses FindFirst and ADO uses Find. So this code is clearly designed for a DAO recordset bound form.
However the subsequent check
"If Not rs.EOF "
would be the state to check after an ADO Find method (which would have found nothing and leave the cursor positioned at the end of the recordset).
However DAO, if it fails, is positioned at the start of the recordset (not something you can test for incase the first matching row happens to be at the start) and returns the NoMatch property to test for the outcome.

Consequently you should change that line to be
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

It was just an oversight in the wizard code which persisted for a while.

Cheers.

 

[gemma-the-husky]

what is txtenumber?

an unbound field?

if so it will default to a TEXT format

in formatting set it to fixed, zero decimals - then it will be a number, i think