Turning string into integer?

[Johnny]

How can I make the value of form text box (txtUser) be evaluated as a integer? My problem is that idEmpTbl is my PK column in tblEmployee, so it must remain a number type. When txtUser is evaluated there is a type mismatch. I have tried declaring txtUser as a integer and that didn't work.

It will work just fine if I create a duplicate column in tblEmployee to idEmpTbl and put duplicate data in it plus make it a text type but having duplicate information in that table as a workaround just annoys me and goes against "the rules". Can anyone help me with this?

Private Sub txtUser_AfterUpdate()
Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim rst As DAO.Recordset
    Dim sSQL As String
        sSQL = "SELECT * FROM tblEmployee WHERE idEmpTbl = '" & Me.txtUser & "'"
 
    Set dbs = CurrentDb()
    Set qdf = dbs.CreateQueryDef("", sSQL)
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset
 
    Me.txtPassword = rst!Password
 
    'clean up
    rst.Close
    Set rst = Nothing
    qdf.Close
    Set qdf = Nothing
    Set dbs = Nothing
End Sub

 

[pbaldy]

How about

CInt(Me.txtUser)

You might want to make sure the textbox contains something, or use the Nz() function to prevent an error.

 

[Johnny]

Could I do that in the select statement? I would need to use NZ just in case. Sorry I'm a newbie at VBA I know just enough to be dangerous.

 

[pbaldy]

Did you try? I wouldn't have suggested it if it wouldn't work in that situation. By the way, you don't want the single quotes around the value if it's a numeric data type.

 

[Johnny]

Yea I tried, got runtime error. I'll keep plugging away at it though I appreciate your help.

 

[Johnny]

Okay it was the single quotes, thanks SO MUCH it works perfectly now.

 

[pbaldy]

Happy to help.