SQL statement in VBA

whyme6181

New Member
Joined
Oct 26, 2009
Messages
39
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:

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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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?
 
Upvote 0
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.
 
Upvote 0
Thanks so much for the link, but now I am getting another error. Here is my code

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.
 
Upvote 0
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:
Code:
BenTypeID = DLookup("[UserID]", "[dbo_Users1]", "[Username]='" & Me.getNTID & "'")
 
Upvote 0
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:
Code:
BenTypeID = DLookup("[UserID]", "[dbo_Users1]", "[Username]='" & getNTID & "'")
<!-- / message --><!-- sig -->
 
Upvote 0

Forum statistics

Threads
1,211,869
Messages
6,104,470
Members
447,910
Latest member
abrook1308

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top