SQL statement in VBA

[whyme6181]

HI. I have a table called dbo_Users1

I would like to run a SQL command to filter it down to the specific user working with the database and then get their numeric ID (BenTypeID). Here is my code:

getNTID = Environ("USERNAME")
strSQL2 = _
    "SELECT dbo_Users1.* " & _
    "FROM dbo_Users1 " & _
    "WHERE dbo_Users1.[Username]='" & getNTID & "';"
    Debug.Print strSQL2
DoCmd.RunSQL (strSQL2)
BenTypeID = "userid", "dbo_users1"
 
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
With rs
    .AddNew
    !TemplateTypeID = cboTemplateType.Value
    !COCID = cboCOCSeries.Value
    !DocumentTypeID = cboDocType.Value
    !BenefitTypeID = cboBenefitType.Value
    !ImportedBy = BenTypeID
    .Update
    'intBatchID = !ImportBatchID
End With

I get a runtime error 2342 every time I try to do this. I put the SQL directly into Access and it works. Can someone point out what I am doing wrong?

 

[Joe4]

I don't understand what this line is supposed to be doing:

BenTypeID = "userid", "dbo_users1"

It looks like you are trying to set that variables equal to two different text strings, separated by a comma, which I am not even sure is legal.

Are you trying to set "BenTypeID" to be an array?
How have you declared it in your code?

 

[whyme6181]

I have declared BenTypeID as a string. I was trying to set that variable to the value in the userid column from the dbo_users1 table. I admit that step is a shot in the dark, but my problem is my code won't go any further than the DoCmd.RunSQL (strSQL2) line.

 

[Joe4]

Why not just use DLOOKUP to lookup the value you want?
xenou provided the syntax of that function in a recent post: http://www.mrexcel.com/forum/showthread.php?t=638772&highlight=DLOOKUP

 

[whyme6181]

Thanks so much for the link, but now I am getting another error. Here is my code

Private Function CreateImportBatch() As Long
Dim intBatchID As Integer
Dim BenTypeID As String
Dim getNTID As String
'Dim intBatchID As Long
Dim strSQL As String
Dim strSQL2 As String
strSQL = "Select * From dbo_ImportBatch"
getNTID = Environ("USERNAME")
BenTypeID = DLookup("[UserID]", "[dbo_Users1]", "[Username]='getNTID'")

getNTID matches values in the Username Column
dbo_Users1 is the name of the table.
UserID is the value I want.

 

[Joe4]

Where is getNTID coming from?
How is it being set?

If it is a field on your form, then I think you want something like:

BenTypeID = DLookup("[UserID]", "[dbo_Users1]", "[Username]='" & Me.getNTID & "'")

 

[whyme6181]

getNTID is coming from here:

getNTID = Environ("USERNAME")

It is set as a string

 

[Joe4]

OK, I must have overlooked that at the beginning of your code.
In any event, now that I know what you are doing, I was able to recreate and get the value you are looking for like this:

BenTypeID = DLookup("[UserID]", "[dbo_Users1]", "[Username]='" & getNTID & "'")

<!-- / message --><!-- sig -->

 

[whyme6181]

You ROCK!! Thanks, that did the trick. Still weak on syntax when it comes to Access.