SQL Syntax for VBA

Question

I have been working on a simple VBA function to check what type of User the current user is and opening the appropriate form for them at startup of my Access db. I have already defined a GetWindowsUserName function to get the username correctly as seen when I do Debug.Print, I used the following code:

Public Function CheckUser()
Dim UserName As String
Dim SQLType As String
UserName = UserNameWindows()
SQLType = "SELECT tbl_Users.[UserType] FROM tbl_Users WHERE tbl_Users.[SOEID]='" & UserName & "';"
Debug.Print SQLType
DoCmd.RunSQL SQLType
If SQLType = "Admin" Then
DoCmd.OpenForm "frm_AdminLandingPage"
DoCmd.Close acForm, "frm_Loading"
Else
DoCmd.OpenForm "frm_LandingPage"
DoCmd.Close acForm, "frm_Loading"
End If
End Function

Debug.Print results in the following for the SQL Statement, I also ran it in Access's SQL View and it runs just fine:

SELECT tbl_Users.[UserType] FROM tbl_Users WHERE tbl_Users.[SOEID]='GS429';

When I try to run the function however, it results in your typical "A RunSQL action requires an argument consisting of an SQL statement."

Thoughts? Thanks again for your help!

RunSQL is not for select queries. See this: stackoverflow.com/questions/27421873/… — jpw
– jpw, Commented Oct 30, 2015 at 14:56

SunKnight0 · Accepted Answer · 2015-10-30 15:00:36Z

3

Replace this:

SQLType = "SELECT tbl_Users.[UserType] FROM tbl_Users WHERE tbl_Users.[SOEID]='" & UserName & "';"
Debug.Print SQLType
DoCmd.RunSQL SQLType

With this:

SQLType=DLookup("UserType","tbl_Users","SOEID='" & UserName & "'")

answered Oct 30, 2015 at 15:00

SunKnight0

3,3611 gold badge12 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HansUp Over a year ago

I don't think you need bother with the UserName variable. Just use the function which supplies that variable's value: DLookup("UserType","tbl_Users","SOEID=UserNameWindows()")

Community · Accepted Answer · 2017-05-23 12:29:57Z

2

DoCmd.RunSQL only works for "action" queries like UPDATE, INSERT, DELETE. It doesn't work with a standard SELECT. You could open up a connection and use that to populate a RecordSet.

See this link: VBA New Database Connection

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Oct 30, 2015 at 14:56

Mark He

7417 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL Syntax for VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related