Using a combobox to go to a selected form record

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.

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.

This is actually a 2 step process:

1. In the Click() Event of cmdPatientFind, place the following code:
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub cmdPatientFind_Click()
   2. Dim strMsg As String
   3.  
   4. strMsg = "You must first select a Patient ID from the Drop Down List!"
   5.  
   6. 'See if a value exists in cboPatientFind
   7. If IsNull(Me![cboPatientFind]) Then     'No value
   8.   MsgBox strMsg, vbExclamation, "No Patient ID to Find"
   9.     'Set Focus to Find Combo Box and Drop it Down
   10.     Me![cboPatientFind].SetFocus
   11.     Me![cboPatientFind].Dropdown
   12. Else    'value exists in cboPatientFind, pass ID in OpenArgs
   13.   DoCmd.OpenForm "frmPatients", acNormal, , , acFormEdit, _
   14.                   acWindowNormal, Me![cboPatientFind]
   15. End If
   16. End Sub
2. In the Open() Event of frmPatients, place the following code:
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub Form_Open(Cancel As Integer)
   2. 'I have no idea of the size of your Database, so what I have given you
   3. 'is the most efficient Find mechanism there is, as far as it relates to
   4. 'your situation
   5. Dim rst As DAO.Recordset
   6.  
   7. Set rst = Me.RecordsetClone     'Clone of the Form's Recordset
   8.  
   9. rst.FindFirst "[PatientID] = " & Me.OpenArgs
   10.  
   11. If rst.NoMatch Then     'Not a Matchamundo!
   12.   MsgBox "No Record(s) found for Patient ID: (" & Me.OpenArgs & ")", _
   13.           vbExclamation, "No Patient ID Found"
   14.   DoCmd.Close acForm, "frmPatients", acSaveNo       'Close frmPatients
   15. Else
   16.   Me.Bookmark = rst.Bookmark
   17. End If
   18. End Sub
3. Any questions, please feel free to ask

Thanks for your detailed response ADezii. Very much appreciated.

I have entered the following code



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.

One too many commas maybe.

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:



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?

Quote: 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: Expand|Select|Wrap|Line Numbers 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

Quote: 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.

Quote: 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.

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



..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?

Quote: 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 Expand|Select|Wrap|Line Numbers 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?

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

Quote: 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.