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!
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