Using a combobox to go to a selected form record

[dozingquinn]

Hello,

I have a button [cmdPatientfind] which is beside a combo box [cboPatientfind]. The user selects the patient ID (integer) from the combo box, then presses the button. The user is then taken to another form [frm_Patients] which is filtered to the selected patient ID.

However once they view the filtered record, the user can't scroll forward or backward to review the other records around it. Is there a way to alter the code behind my button to allow this? The code I currently have is below:

Private Sub cmdPatientfind_Click()
Dim stLinkCriteria As String
     stLinkCriteria = "tpPatient = " & Me.cboPatientfind
     stDocName = "frm_Patients"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Thanks for your help.

[RuralGuy]

Your code uses the WhereCondition of the OpenForm command which simply applies a filter to the form. In order to do as you want there are several approaches. I prefer to pass the data in the OpenArgs argument of the OpenForm command and then move to the right record in the OnOpen event of the next form.

[dozingquinn]

Thanks for the tip RuralGuy. As I'm a newbie to all this, would you be able to show me an example of how the code should be?

Thanks again.

[ADezii]

This is actually a 2 step process:

1. In the Click() Event of cmdPatientFind, place the following code:
   

   Private Sub cmdPatientFind_Click()
   Dim strMsg As String
   
   strMsg = "You must first select a Patient ID from the Drop Down List!"
   
   'See if a value exists in cboPatientFind
   If IsNull(Me![cboPatientFind]) Then     'No value
     MsgBox strMsg, vbExclamation, "No Patient ID to Find"
       'Set Focus to Find Combo Box and Drop it Down
       Me![cboPatientFind].SetFocus
       Me![cboPatientFind].Dropdown
   Else    'value exists in cboPatientFind, pass ID in OpenArgs
     DoCmd.OpenForm "frmPatients", acNormal, , , acFormEdit, _
                     acWindowNormal, Me![cboPatientFind]
   End If
   End Sub

2. In the Open() Event of frmPatients, place the following code:
   

   Private Sub Form_Open(Cancel As Integer)
   'I have no idea of the size of your Database, so what I have given you
   'is the most efficient Find mechanism there is, as far as it relates to
   'your situation
   Dim rst As DAO.Recordset
    
   Set rst = Me.RecordsetClone     'Clone of the Form's Recordset
   
   rst.FindFirst "[PatientID] = " & Me.OpenArgs
   
   If rst.NoMatch Then     'Not a Matchamundo!
     MsgBox "No Record(s) found for Patient ID: (" & Me.OpenArgs & ")", _
             vbExclamation, "No Patient ID Found"
     DoCmd.Close acForm, "frmPatients", acSaveNo       'Close frmPatients
   Else
     Me.Bookmark = rst.Bookmark
   End If
   End Sub

3. Any questions, please feel free to ask

[dozingquinn]

Thanks for your detailed response ADezii. Very much appreciated.

I have entered the following code

Private Sub cmdPatientFind_Click()
Dim strMsg As String
 
strMsg = "You must first select a Patient ID from the Drop Down List!"
 
'See if a value exists in cboPatientFind
If IsNull(Me![cboPatientfind]) Then     'No value
  MsgBox strMsg, vbExclamation, "Please select a patient to view."
    'Set Focus to Find Combo Box and Drop it Down
    Me![cboPatientfind].SetFocus
    Me![cboPatientfind].Dropdown
Else    'value exists in cboPatientFind, pass ID in OpenArgs
  DoCmd.OpenForm "frm_Patients", acNormal, , , acFormEdit,
                  acWindowNormal , Me![cboPatientfind]
End If
End Sub

However Access is giving me a 'compile error: syntax error' for the code on line 13. (the DoCmd bit). I've double checked and [frm_Patients] is the name of the form, so that's not the problem. Could you please take a look for me.

Thanks again.

[RuralGuy]

One too many commas maybe.

DoCmd.OpenForm "frm_Patients", , , , acFormEdit, , Me![cboPatientfind]

[dozingquinn]

Thanks RuralGuy. I amended the code to your suggestion and it seems to have fixed the error. Now I'm having difficulty with the Form_open function:

Private Sub Form_Open(Cancel As Integer)
'I have no idea of the size of your Database, so what I have given you
'is the most efficient Find mechanism there is, as far as it relates to
'your situation
Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone     'Clone of the Form's Recordset
 
rst.FindFirst "[tpPatient] = " & Me.OpenArgs
 
If rst.NoMatch Then     'Not a Matchamundo!
  MsgBox "No Record(s) found for Patient No. (" & Me.OpenArgs & ")", _
          vbExclamation, "No Patient Number Found"
  DoCmd.Close acForm, "frmPatients", acSaveNo       'Close frmPatients
Else
  Me.Bookmark = rst.Bookmark
End If
End Sub

I'm getting the error: "Compile error: User-defined type not defined" relating to line 5 of the code (the rst As DAO.Recordset bit). Any ideas?

[ADezii]

Thanks RuralGuy. I amended the code to your suggestion and it seems to have fixed the error. Now I'm having difficulty with the Form_open function:

Private Sub Form_Open(Cancel As Integer)
'I have no idea of the size of your Database, so what I have given you
'is the most efficient Find mechanism there is, as far as it relates to
'your situation
Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone     'Clone of the Form's Recordset
 
rst.FindFirst "[tpPatient] = " & Me.OpenArgs
 
If rst.NoMatch Then     'Not a Matchamundo!
  MsgBox "No Record(s) found for Patient No. (" & Me.OpenArgs & ")", _
          vbExclamation, "No Patient Number Found"
  DoCmd.Close acForm, "frmPatients", acSaveNo       'Close frmPatients
Else
  Me.Bookmark = rst.Bookmark
End If
End Sub

I'm getting the error: "Compile error: User-defined type not defined" relating to line 5 of the code (the rst As DAO.Recordset bit). Any ideas?

Make sure you have a Reference set for the Microsoft DAO X.XX Object Library

[dozingquinn]

Make sure you have a Reference set for the Microsoft DAO X.XX Object Library

Sorry ADezii, but I really don't know what this means, or how to go about setting the reference.

[ADezii]

Sorry ADezii, but I really don't know what this means, or how to go about setting the reference.

1. Select a Form, Report, or Module in the Database Window.
2. From the Menu Bar, select View ==> Code.
3. From the Menu Bar, select Tools ==> References.
4. The Microsoft DAO X.X Object Library needs to be selected, if not:
   1. Scroll down until you see the Microsoft DAO X.X Object Library in the List Box.
   2. Select this Library by clicking in the Check Box.
      Click OK.
5. Now, run the code.

[dozingquinn]

Excellent. Thanks ADezii and RuralGuy - the solution now works, as requested.

However it's had a minor flow on affect to another button on the same form. The other (main) button on the form is used to simply open the form [frm_patients] at its first record. However, now that we've altered the form_open code to frm_patients, I'm getting the error:

Runtime error 3077: "Syntax error (missing operator) in expression"

It then shows the code

Private Sub Form_Open(Cancel As Integer)

Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone     'Clone of the Form's Recordset
 
rst.FindFirst "[tpPatient] = " & Me.OpenArgs
 
If rst.NoMatch Then     'Not a Matchamundo!
  MsgBox "No Record(s) found for Patient No. (" & Me.OpenArgs & ")", _
          vbExclamation, "No Patient Number Found"
  DoCmd.Close acForm, "frmPatients", acSaveNo       'Close frmPatients
Else
  Me.Bookmark = rst.Bookmark
End If
End Sub

..and highlights line 7. Is there a simple way to alter this code, so that when I push the separate button [cmdPatient], the form [frm_Patients] is opened to its first record?

[ADezii]

Excellent. Thanks ADezii and RuralGuy - the solution now works, as requested.

However it's had a minor flow on affect to another button on the same form. The other (main) button on the form is used to simply open the form [frm_patients] at its first record. However, now that we've altered the form_open code to frm_patients, I'm getting the error:

Runtime error 3077: "Syntax error (missing operator) in expression"

It then shows the code

Private Sub Form_Open(Cancel As Integer)

Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone     'Clone of the Form's Recordset
 
rst.FindFirst "[tpPatient] = " & Me.OpenArgs
 
If rst.NoMatch Then     'Not a Matchamundo!
  MsgBox "No Record(s) found for Patient No. (" & Me.OpenArgs & ")", _
          vbExclamation, "No Patient Number Found"
  DoCmd.Close acForm, "frmPatients", acSaveNo       'Close frmPatients
Else
  Me.Bookmark = rst.Bookmark
End If
End Sub

..and highlights line 7. Is there a simple way to alter this code, so that when I push the separate button [cmdPatient], the form [frm_Patients] is opened to its first record?

Private Sub Form_Open(Cancel As Integer)
'process the following code, only if a value exists in the
'OpenArgs Argument, otherwise simply fall through
If Me.OpenArgs <> "" Then          'ADDED 7/6/2008
  Dim rst As DAO.Recordset
 
  Set rst = Me.RecordsetClone     'Clone of the Form's Recordset
 
  rst.FindFirst "[tpPatient] = " & Me.OpenArgs
 
  If rst.NoMatch Then     'Not a Matchamundo!
    MsgBox "No Record(s) found for Patient No. (" & Me.OpenArgs & ")", _
            vbExclamation, "No Patient Number Found"
    DoCmd.Close acForm, "frmPatients", acSaveNo       'Close frmPatients
  Else
    Me.Bookmark = rst.Bookmark
  End If
End If          'ADDED 7/6/2008
End Sub

[dozingquinn]

Apologies for the late response to your solution ADezii. I have just tested it and it works perfectly. Thank you for helping me out with your thorough explanations. You are a true asset to the bytes community.

Quinn

[ADezii]

Apologies for the late response to your solution ADezii. I have just tested it and it works perfectly. Thank you for helping me out with your thorough explanations. You are a true asset to the bytes community.

Quinn

Thank you for your kind Reply.