insert not working

[rjoshi2]

Why is the insert (inseertID) not working? It always inserts 0 even when IDnumber equal 2 or another number. Any help would be appreciated.

Thank You,
rjoshi2

Option Compare Database
Option Explicit
Dim IDNumber As Integer

Private Sub Document__AfterUpdate()
Dim insertID As String
MsgBox ("User ID: " & IDNumber)
insertID = "INSERT INTO FY03COMREG( [User ID] ) " & _
"SELECT '" & IDNumber & "' ;"
MsgBox ("User ID: " & IDNumber)
CurrentDb.Execute insertID
End Sub

Private Sub Form_Load()
MsgBox ("Hello: " & CurrentUser & "!&quot
Dim getuserID As String

'use dlookup not select
IDNumber = DLookup("[User ID]", "Users", "[User Name] = '" & CurrentUser & "'&quot
MsgBox ("User ID: " & IDNumber)
End Sub

 

[ajdesalvo]

You have dimensioned IDNumber as an Integer, therefore the single quotes are not needed in the select statement:

You have:
insertID = "INSERT INTO FY03COMREG( [User ID] ) " & _
"SELECT '" & IDNumber & "' ;"

Should be:
insertID = "INSERT INTO FY03COMREG( [User ID] ) " & _
"SELECT " & IDNumber & " ;"

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[rjoshi2]

I have change my code again buecause I got rid of the users table but it is still not working. Any help would be appreciated.

Thank You,
rjoshi2

In this section of code I am getting an error at line:
"'" & FY03COMREG.[User ID] & " = '" & CurrentUser & "'"
It says variable not defined (referring to FY03COMREG). How do I define that table?

Option Compare Database
Option Explicit

Private Sub Form_Load()
Dim getdata As String

MsgBox ("Hello: " & CurrentUser & "!&quot

getdata = "Select * from FY03COMREG where" & _
"'" & FY03COMREG.[User ID] & " = '" & CurrentUser & "'"

Me.RecordSource = getdata
Me.Requery

End Sub

In this next section I am getting a run time error (3061) "Too few parameters. Expect 1' at line:
CurrentDb.Execute insertID

Private Sub OCB_AfterUpdate()
Dim insertID As String

MsgBox ("User ID: " & CurrentUser)
insertID = "INSERT INTO FY03COMREG( [User ID] ) " & _
"VALUES(" & CurrentUser & &quot ;"
MsgBox ("User ID: " & CurrentUser)
CurrentDb.Execute insertID

End Sub