Converting SQL - VBA 2

[SmallTime]

I have a button on a form that tests for records, by running a select query, before opening another form. (See below)

Could someone be kind enough and show me how to combine the query into the code behind the button so that I could get rid of Qry_test altogether.


Code behind the button:

If DCount("*", "Qry_test") < 1 Then
MsgBox "There are no cases awaiting allocation", vbOKOnly
Else
DoCmd.OpenForm "Frm_AllocateMain"
End If


SQL view from the select query (Qry_test)

SELECT Tbl_Referral.Caseworker
FROM Tbl_Referral
WHERE (((Tbl_Referral.Caseworker) Is Null) AND ((Tbl_Referral.SiteName)=[Forms]![Frm_MMenu]![CmbSite])) OR (((Tbl_Referral.Caseworker) Is Null) AND (([Forms]![Frm_MMenu]![CmbSite]) Is Null));

Many thanks in advance

 

[THWatson]

SmallTime
Here is your SQL to be used, behind your command button, in VBA.

Dim strSQL As String

strSQL = "SELECT Tbl_Referral.Caseworker " _
& "FROM Tbl_Referral " _
& "WHERE (((Tbl_Referral.Caseworker) Is Null) AND ((Tbl_Referral.SiteName)=[Forms]![Frm_MMenu]![CmbSite])) OR (((Tbl_Referral.Caseworker) Is Null) AND (([Forms]![Frm_MMenu]![CmbSite]) Is Null));"

DoCmd.RunSQL strSQL

Tom

 

[TheAceMan1]

or perhaps:

[blue]   Dim SQL As String
   
   SQL = "SELECT Caseworker " & _
         "FROM Tbl_Referral " & _
         "WHERE (([Caseworker] Is Null) AND " & _
               "(([SiteName]=[Forms]![Frm_MMenu]![CmbSite]) OR " & _
               "([Forms]![Frm_MMenu]![CmbSite] Is Null)));"
   DoCmd.RunSQL SQL[/blue]

See Ya! . . . . . .

 

[SmallTime]

Ok, There’s obviously a trick to this I just can’t figure out.

Where do I put this code in relation to If Then statement? I’m sure it’s obvious to most but I tried and failed abysmally

Thanks for your time guys

 

[Remou]

Hi
Maybe:

Dim rs As DAO.Recordset
Dim strSQL

If Trim([Forms]![Frm_MMenu]![cmbSite]) & "" = "" Then
    strSQL = "SELECT Tbl_Referral.Caseworker, Tbl_Referral.SiteName " _
    & "FROM Tbl_Referral WHERE Caseworker Is Null"
Else
    strSQL = "SELECT Tbl_Referral.Caseworker, Tbl_Referral.SiteName " _
    & "FROM Tbl_Referral WHERE Caseworker Is Null AND SiteName='" _
    & [Forms]![Frm_MMenu]![cmbSite] & "'"
End If

Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF() Then
    MsgBox "There are no cases awaiting allocation", vbOKOnly
Else
    DoCmd.OpenForm "Frm_AllocateMain"
End If

 

[TheAceMan1]

SmallTime . . . . .

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT Caseworker " & _
         "FROM Tbl_Referral " & _
         "WHERE (([Caseworker] Is Null) AND " & _
               "(([SiteName]=[Forms]![Frm_MMenu]![CmbSite]) OR " & _
               "([Forms]![Frm_MMenu]![CmbSite] Is Null)));"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      MsgBox "There are no cases awaiting allocation", vbOKOnly
   Else
       DoCmd.OpenForm "Frm_AllocateMain"
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

Note: The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

BTW . . . [blue]DoCmd.RunSQL[/blue] would fail in prior posts as its prescribed for [blue]action queries only![/blue] . . . sorry about that . . .

See Ya! . . . . . .

 

[SmallTime]

Beautifully done.
Many thanks