RecordSource in Access SQL

Question

I have a form which allows the user to view all records with the LinkRef field equal to a specified value and also either the Clearance Applying For or Clearance Level a certain value.

LinkRef is a user ID which is pulled in using OpenArgs from the previous form. The code for the form_load I have presently is:

Private Sub Form_Load()

    'MsgBox Me.OpenArgs

    Me.C_LinKRef = Me.OpenArgs
    Me.chbToggleEdit.Value = False

    'MsgBox Me.C_LinKRef

    Dim mySQL As String
    mySQL = _
    "Select * " & _
    "From TabClearDetail " & _
    "Where (C_LinKRef = " & Me.C_LinKRef & ") " & _
    "And ([Clearance Applying For] = 'BPSS' " & _
          "Or [Clearance Applying For] = 'BPSS (EDF)' " & _
          "Or [Clearance Applying For] = 'BPSS (Magn)' " & _
          "Or [Clearance Applying For] = 'BPSS (Sella)' " & _
          "Or [Clearance Applying For] = 'BPSS Equiv' " & _
          "Or C_ClearanceLevel = 'BPSS' " & _
          "Or C_ClearanceLevel = 'BPSS (EDF)' " & _
          "Or C_ClearanceLevel = 'BPSS (Magn)' " & _
          "Or C_ClearanceLevel = 'BPSS (Sella)' " & _
          "Or C_ClearanceLevel = 'BPSS Equiv' " & _
          "Or C_ClearanceLevel = 'DESTROYED' " & _
          "Or C_ClearanceLevel = 'Lapsed' " & _
          "Or C_ClearanceLevel = 'NOT_FLWDUP' " & _
          "Or C_ClearanceLevel = 'NOT_SPECIFIED' " & _
          "Or C_ClearanceLevel = 'Refused' " & _
          "Or C_ClearanceLevel = 'Withdrawn');"

    Me.RecordSource = mySQL

    'MsgBox Me.RecordsetClone.RecordCount

End Sub

mySQL seems to behave as it should when there are matching records. But sometimes there won't be any records because the specified person doesn't have any of these clearance levels and hasn't applied for them, then I would like the form to come up blank or a message to appear saying that there is no matching records.

Presently though if there is no matching records the form will pull in the LinkRef but fill all the other text boxes with values from a completely different record (it seems to be the last record I viewed). Not to sure how to remedy this, I tried to use the RecordsetClone.RecordCount to say if it is equal to 0 then msgbox, but it seems to late to do that as it always seems to find at least 1 entry, as even if there should be 0 it has already populated the textboxes with data from another field so 1 is found.

The LinkRef textbox is populated from OpenArgs. All other textboxes are populated using a query which looks in the TabClearDetail table and pulls the values in. I'm starting to think I'd be better either just using Queries or just using Code, but I wasn't sure how to use OpenArgs in a query and for some things it's so much quicker to make a query than code.

Here is the code for my save dialog I refer to in reply to @Roland post. This code is called in the Form_Close() sub.

Private Sub SaveDialog()
Dim Msg, Style, Title As String
    Dim Response As Integer
    Msg = "Would you like to save your changes?"
    Style = vbYesNoCancel
    Title = "Save Changes"

    On Error GoTo Err_BackFromAddBPSSButton_Click
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
        'DoCmd.Close
        DoCmd.OpenForm ("Basic Personal Information")
    Else
    If Response = vbNo Then
        Me.Undo
        'DoCmd.Close
        DoCmd.OpenForm ("Basic Personal Information")
    End If
    End If


Exit_BackFromAddBPSSButton_Click:
    Exit Sub

Err_BackFromAddBPSSButton_Click:
    MsgBox Err.Description
    Resume Exit_BackFromAddBPSSButton_Click
End Sub

Apologies for the very wordy question, hopefully all the detail is necessary and it makes sense, any suggestions HUGELY appreciated!

What SQL is generated? When you run that query in a separate query window, do you get the results you want? What happens if you run your code in the `Open` Event instead of `Load`? — Brad, Jun 17 '14 at 19:50
also, you can simplify your Where clause like this if you want `"Where (C_LinKRef = " & Me.C_LinKRef & ") " & _:"And ([Clearance Applying For] IN ('BPSS', 'BPSS (EDF)', 'BPSS (Magn)', 'BPSS (Sella)', 'BPSS Equiv') " & _:"Or C_ClearanceLevel IN ('BPSS' ,'BPSS (EDF)','BPSS (Magn)','BPSS (Sella)','BPSS Equiv'" & _:",'DESTROYED','Lapsed','NOT_FLWDUP','NOT_SPECIFIED','Refused','Withdrawn')" & _:");"` — Brad, Jun 17 '14 at 19:51
Where do I get a separate query window in Access? Cheers @Brad for that rewrite, reads better. — hello123, Jun 17 '14 at 20:05
On the ribbon, `Create > Query Design > SQL View > paste in the SQL for your mySQL variable` — Brad, Jun 17 '14 at 20:07
I changed form_load() to form_open(), and now every time I attempt to open the form I get the error message "The OpenForm action was cancelled" — hello123, Jun 17 '14 at 20:09
The SQL seems to be returning the correct results. Thanks for telling me about this though, will make my life a lot easier going forward! — hello123, Jun 18 '14 at 07:10

score 1 · Answer 1 · answered Jun 17 '14 at 20:09

1

Try changing the order of events:

Don't set the TextBox value first. Pass the OpenArgs to the mySql string. With mySql open a recordset in VBA (OpenRecordset) and do a RecordCount. If it is zero then set the Recordsource to SELECT * FROM TabClearDetail WHERE 1=2 . Else set mySQl as the Recordsource (or pass the Recordset). Only then set the TextBox and CheckBox.

Private Sub Form_Load()

    Dim i as Integer
    i = Me.OpenArgs

    Dim mySQL As String
    mySQL = _
    "Select * " & _
    "From TabClearDetail " & _
    "Where (C_LinKRef = " & Me.C_LinKRef & ") " & _
    "And ([Clearance Applying For] IN ('BPSS','BPSS (EDF)','BPSS (Magn)','BPSS Sella)','BPSS Equiv') " & _
          "Or C_ClearanceLevel IN ('BPSS','BPSS (EDF)','BPSS (Magn)','BPSS (Sella)','BPSS Equiv','DESTROYED','Lapsed','NOT_FLWDUP','NOT_SPECIFIED','Refused','Withdrawn'));"

   Dim rst as Recordset
   Set rst = CurrentDB.OpenRecordset(mySQL)
   rst.MoveLast
   rst.MoveFirst
   If rst.RecordCount = 0 then 
       Me.RecordSource = "SELECT * FROM TabClearDetail WHERE 1=2"
       Me.C_LinKRef = ""
       Me.chbToggleEdit.Value = False
   Else
       Me.RecordSource = mySQL
       Me.C_LinKRef = i
       Me.chbToggleEdit.Value = False
   End If

   rst.Close
   Set rst = Nothing

End Sub

Sorry, cannot test it here so may be a little buggy. If any problems I will check tomorrow

answered Jun 17 '14 at 20:09

Roland

946
8
20

This seems to work the first time I do it, but then when I close the form I have a save dialog, so if a user checks chbToggleEdit they can make changes to the records and either save or not save these changes. Here the form is blank, but the save dialog still comes up and I press no, but then when I go to open the form again all the textboxes are filled. Also, when I ran through the code rst.RecordCount = 1 even when it should have been 0, but the textboxes were still blank. I will attach my save dialog to my initial question code in case that helps. Thanks – hello123 Jun 18 '14 at 07:24
I have commented out rst.MoveLast and rst.MoveFirst, still got a bit of testing to do, but I think that seems to be working. – hello123 Jun 18 '14 at 07:54
MoveLast/MoveFirst makes sure the complete recordset is loaded before counting. This was an issue in 2003, might not be one now, I am just used to it. Another way for cheking for an empty set is to check on open if `rst.EOF`. Regarding the Save Dialog you should maybe only call it if really changes were made, i.e. set a flag (since due to the unbound checkbox Me.Dirty won't work) – Roland Jun 18 '14 at 08:03
Strike that, once I've opened the form and it has a record, when I go to someone who doesn't have any records the textboxes are filling with the previous persons record still. What do you mean by set a flag? – hello123 Jun 18 '14 at 08:13

score 0 · Accepted Answer · answered Jun 18 '14 at 09:07

Using a query and passing [Forms]![BPSS Clearance].[OpenArgs] into that as well as the conditions on C_ClearanceLevel and Clearance Applying For has worked for me. No idea why the code didn't work because in theory it's doing the same thing, but I've got a solution so I'm happy. Thanks for all the suggestions

RecordSource in Access SQL

2 Answers2