VBA SQL Select statement based on content in a text box

[shabbaranks]

Ok guys - yep me again. Im bambuzzled as to how VBA and sql statements are supposed to work. It seems you use different command depending on what you are trying to achieve - is this correct?

What I am trying to do is query a table based on the input of a textbox. Based on below the error U get is Syntax error (missing operator) in query expression '[ProjectRef] = & Me.ProjectRef_TotalHours &'.

Private Sub TotalHoursOk_btn_Click()
Dim MySQL As String
MySQL = "Select * From TimesheetTable WHERE [ProjectRef] = & Me.ProjectRef_TotalHours & "
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
End Sub

What am I doing wrong?

 

[Kiwiman]

Howzit

You want (on the basis it is a number)

MySQL = "Select * From TimesheetTable WHERE [ProjectRef] =" & Me.ProjectRef_TotalHours

 

[shabbaranks]

Thanks for the quick reply, its not a number its a text field, but I do this I get the error a RunSQL action requires an argument consistsing of an SQL statement.

I'll give it an argument in a minute

 

[Kiwiman]

Howzit

It may be a text field, but what is teh data type of the text field. If it is a number then the syntax is what I said, but if it is Text then it would be:

MySQL = "Select * From TimesheetTable WHERE [ProjectRef] ='" & Me.ProjectRef_TotalHours & "'"

In saying that, to run that RunSQL command the SQL statement needs to be an action statement (Delete \ Update or Append), not a Select statement that you have.

To get around this I have used the following method to run a Select statement

Create a Public Module and a query called qryQueries (any valid Select stmt is fine)

Public Sub QDef(strSql As String)

    Dim db As DAO.Database
    Dim qdfTemp As QueryDef
    Dim strSeason As String
    Dim strDocName As String
    

    Set db = CurrentDb

    strDocName = "qryQueries"



    db.QueryDefs.Delete strDocName
    Set qdfTemp = db.CreateQueryDef("qryQueries")
    qdfTemp.sql = strSql

    DoCmd.OpenQuery strDocName


End Sub

Then your code would be:

MySQL = "Select * From TimesheetTable WHERE [ProjectRef] ='" & Me.ProjectRef_TotalHours & "'"
QDef(MySQL)

 

[shabbaranks]

Thanks for that, works like a treat. Although is there a way I can get it to accept wildcards for the criteria within the text box? For example I have multiple records which start PP190 and then - test, -record, so they would read PP190-test, PP190-record etc but if I do a search with PP190* it returns nothing - any ideas?

Thanks

 

[Kiwiman]

Howzit

Try

MySQL = "Select * From TimesheetTable WHERE [ProjectRef] LIKE '*" & Me.ProjectRef_TotalHours & "*'"

 

[shabbaranks]

Cool, thanks worked like a charm.

You couldnt help me with this code please?

Private Sub TotalHoursOk_btn_Click()
Dim MySQL As String
Dim sGroupID As String
sUser = Forms![TimesheetForm]![LoggedInUser]
sGroupID = DLookup(DepGroupID, UserNames_tbl, sUser)
If sGroupID = "2" Then
MySQL = "Select * From TimesheetTable WHERE [ProjectRef] LIKE '*" & Me.ProjectRef_txtbox & "*'"
QDef (MySQL)
Else
End If
End Sub

Im trying to do it step by step. Am I correct that the above code is looking within UserNames_tbl and checking sUser against the current logged in user which is in the TimesheetForm. And if sUser has a number 2 in thier DepGroupId column then it will do the select statement?

Thanks for all your help

 

[Kiwiman]

Howzit

I think this part needs to change

sGroupID = DLookup(DepGroupID, UserNames_tbl, sUser)

to

sGroupID = DLookup(DepGroupID, UserNames_tbl, "[Userid]='" & sUser & "'")

You need to tell the dlookup which field to compare the sUser string to. You will need to use your own fieldname - i have used "Userid"

 

[shabbaranks]

Thanks Ive made those changes, and also Im sure sUserID should be integer as its a stored number as thats what it is in the table? so I have changed that. But now I get error type mismatch :S

Option Compare Database
Private Sub TotalHoursOk_btn_Click()
Dim MySQL As String
Dim sGroupID As String
Dim sUserID As Integer
sUserID = Forms![TimesheetForm]![LoggedInUser]
sGroupID = DLookup(DepGroupID, UserNames_tbl, "[sUser]='" & sUserID & "'")
If sGroupID = "2" Then
MySQL = "Select * From TimesheetTable WHERE [ProjectRef] LIKE '*" & Me.ProjectRef_txtbox & "*'"
QDef (MySQL)
Else
End If
End Sub

 

[Kiwiman]

Howzit

Ok - try this - I treated the UserID as a string, so now I have removed teh apostophes.

sGroupID = DLookup(DepGroupID, UserNames_tbl, "[sUser]=" & sUserID)

 

[shabbaranks]

Just so I have got this straight as I might be leading you up the garden path. Is my understanding of this right

The sUserID is pulled from the form and stored. The DLookup is then looking for the sUserID in the UserNames_tbl and pulling out the relevant DepGroupID for that user based on the sUSerID?

There are only 3 different numbers possible and in this case I am looking for number 2.

Dim sGroupID As Integer
Dim sUserID As String
sUserID = Forms![TimesheetForm]![LoggedInUser]
sGroupID = DLookup(DepGroupID, UserNames_tbl, "[DepGroupID]=" & sUserID)

Sorry if I am confusing the situation, but Im litterally getting my head around this as we speak or trying anyway

 

[vbaInet]

By the way, what does this mean??

[COLOR=Red]QDef (MySQL)[/COLOR]

 

[Kiwiman]

Howzit

You have this wrong

sGroupID = DLookup(DepGroupID, UserNames_tbl, "[DepGroupID]=" & sUserID)

You need what we have in the previous post

sGroupID = DLookup(DepGroupID, UserNames_tbl, "[sUser]=" & sUserID)

This will return the DepFroupID from table UserNames_tbl where the SUser is the user in the form Forms![TimesheetForm]![LoggedInUser].

 

[Kiwiman]

By the way, what does this mean??

See post 4 - it opens a Select query and changes the sql behind it.

 

[vbaInet]

See post 4 - it opens a Select query and changes the sql behind it.

Ah, I see it now. I was just wondering if that was something shabbaranks was experimenting with.

Carry on!

 

[shabbaranks]

Hi guys,

Im still having a few issues with this, as per the code below Im getting the error " You entered an invalid argument in a domain aggregate function". So I have used the Immediate window to query what sGroupID is and its returning 0 but its 2 in the table.

I even changed the DLookup to point directly to the LoggedInUser and still get the same result -sGroupID - 0 when it should be 2.

Dim sGroupID As Integer
 
sGroupID = DLookup(DepGroupID, UserNames_tbl, "[sUser] = " & Forms![TimesheetForm]![LoggedInUser])

 

[Kiwiman]

Howzit
Sorry my mistake - somehow got rid of quotes. Try

     sGroupID = DLookup("[DepGroupID]", "UserNames_tbl", "[sUser]=" & sUserID)

 

[shabbaranks]

Hmmm its still causing issues. Its not using the sUserID which is the LoggedInUser to lookup the value for the sGroupID from the table. Its referencing the sUserID

Dim MySQL As String
Dim sGroupID As Integer
Dim sUserID As String

sUserID = Forms![TimesheetForm]![LoggedInUser]
sGroupID = DLookup("DepGroupID", "UserNames_tbl", "[sUser]=" & sUserID)
If sGroupID = "2" Then
MySQL = "Select * From TimesheetTable WHERE [ProjectRef] LIKE '*" & Me.ProjectRef_txtbox & "*'"
QDef (MySQL)
Else
MsgBox "You are only allowed to query your own hours", vbInformation
Me.TotalHours_Combo.SetFocus
End If
End Sub

Obviously its not right as it would work - but I cant see what the problem is??

Ta

 

[shabbaranks]

Solved - put quotes around the sUserID to read

sUserID = "Forms![TimesheetForm]![LoggedInUser]"

And ot works fine now.... Not too sure why

 

[Kiwiman]

Howzit
Sorry away on business and don't have good access to help. Well done