VBA SQL Select statement based on content in a text box

[shabbaranks]

I dont suppose you can help with this please .....

Im trying to get it so if the combo is blank or there is no data in the text box it errors, but at the moment its just going around in a loop :

Private Sub TotalHoursOk_btn_Click()
Dim MySQL As String
Dim sGroupID As Integer
Dim sUserID As String
Dim sProjectRef As String
Dim sTotalhours As String
sUserID = "Forms![TimesheetForm]![LoggedInUser]"
sGroupID = DLookup("DepGroupID", "UserNames_tbl", "[sUser]=" & sUserID)
sProjref = Me.ProjectRef_txtbox
sTotalhours = Me.TotalHours_Combo
If Me.ProjectRef_txtbox = "" Then
    MsgBox "You must enter a project number", vbInformation
    Me.ProjectRef_txtbox.SetFocus
    Exit Sub
End If
If Me.TotalHours_Combo = "" Then
    MsgBox "You must make a selection from the combo box", vbInformation
    Me.TotalHours_Combo.SetFocus
    Exit Sub
End If

Thansk again

 

[Kiwiman]

Howzit

Try

if me.yourcontrol = "" or isnull(me.yourcontrol) then
      'Your code
Else
     ' your code
End if

 

[shabbaranks]

Still having a couple of issues with this. The All employees works fine, its just the current user which prompts for sFormUser when it should get it from the form - any ideas?

Dim sProjectRef As String
Dim sTotalhours As String
Dim MySQL As String
Dim sGroupID As Integer
Dim sUserID As String
Dim sFormUser As String
sFormUser = Me.CurrentUser
sUserID = "Forms![TimesheetForm]![LoggedInUser]"
sGroupID = DLookup("DepGroupID", "UserNames_tbl", "[sUser]=" & sUserID)
'If Me.StartDate_totalhours_txtbox And Me.EndDate_totalhours_txtbox = Null Then
If Me.ProjectRef_txtbox = "" Or IsNull(Me.ProjectRef_txtbox) Then
MsgBox "Please Enter a Project Number"
Else
If Me.TotalHours_Combo = "" Or IsNull(Me.TotalHours_Combo) Then
MsgBox "Please Select from the drop down list"
End If
Select Case Me.TotalHours_Combo
Case "All Employees"
    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, change your selection to current user", vbInformation
End If
    Me.TotalHours_Combo.SetFocus
    
Case "Current User"
    MySQL = "Select * From TimesheetTable WHERE [ProjectRef] LIKE '*" & Me.ProjectRef_txtbox & "*' AND " & sFormUser & ""
    QDef (MySQL)
End Select
End If
End Sub

 

[Kiwiman]

Howzit
Sorry still away and have limited access. Doing this from my phone so not sure how successful it will be.

Try

 Dim SProjectRef As String
Dim sTotalhours As String
Dim MySQL As String
Dim sGroupId As String
Dim sUserId As String
Dim sFormUser As String
Stop
sFormUser = Me.CurrentUser
'Forms![TimesheetForm]![LoggedInUser]

' Set sUserID variable - the form "Forms![TimesheetForm] must be open
sUserId = Nz(Forms![TimesheetForm]![LoggedINUser], "")

' Check to see if sUserId has a valid string - i.e. is not ""
If sUserId = "" Then
    MsgBox "There is no current user logged in?", vbOKOnly, "No logged in user"
    Exit Sub
Else: End If

' Set sGroupId
'    Lookup the FIELD DepGroupID
'    in TABLE UserNames_tbl
'    where FIELD sUser in TABLE UserNames_tbl = VARIABLE sUserID
'
sGroupId = DLookup("[DepGroupId]", "UserNames_tbl", "[sUser]='" & sUserId & "'")

' Check to see if the Project Number control is filled
If Me.Projectref_txtbox = "" Or IsNull(Me.Projectref_txtbox) Then
    MsgBox "Please enter a project number", vbOKOnly, "Project Number Required"
    Me.Projectref_txtbox.SetFocus       ' Set focus to control in issue
    Exit Sub                            ' As project number is required, exit sub
    
' Check to see if a valid selection has been made in the combobox
ElseIf Me.totalhours_combo = "" Or IsNull(Me.totalhours_combo) Then
    MsgBox "Please select from the drop down list.", vbOKOnly, "Selection Required"
    Me.totalhours_combo.SetFocus        ' Set focus to control in issue
    Exit Sub                            ' as combo selection is required exit sub
Else
    ' You are good to go...
    Select Case Me.totalhours_combo
        Case "All Employees"
            If sGroupId = "2" Then
                MySQL = "..."
                QDef (MySQL)
            Else
                ' Something else or nothing
            End If
        Case ""      ' Add other case as required
            ' Other code
        Case Else
            ' Other code
    End Select
    
End If

 

[mindbender]

Hi Kiwiman,

I was hoping you could help me with this response as well. I am new to VBA and probably messing it up all on my own?

I am using MS Access 2010
I create a new module called qryQueries and a query called qryQueries.
In the module I pasted the code you put in the thread.
When I run it I get the following error:
Runtime error 3131syntax error in the from clause and it highlights the line
qdfTemp.SQL = strSql

what I have is a form with a dropdown with values of JAN(YTD), FEB(YTD) etc.

On the afterupdate of the field I was trying to enter the following:
Private Sub YTD_output_AfterUpdate()

If YTD_output = "JAN(YTD)" Then
MySQL = "Select * From qry_Monthly Bulk YOY WHERE [Month] ='JAN'"
QDef (MySQL)

ElseIf YTD_output = "FEB(YTD)" Then
MySQL = "Select * From qry_Monthly Bulk YOY WHERE [Month] in('JAN','FEB')"
QDef (MySQL)

Else: Msg = "Not a valid selection"
End If

End Sub

Basically trying to return YTD data based on the value the user selects from the drop down

Am I completely lost?

Thanks