I tried to use the code you gave in https://www.myonlinetraininghub.com/searching-for-data-with-user-form but it is not working on my form when I adapt it for my workbook
Dim RecordRow As Long
Dim RecordRange As Range
' Turn off default error handling so Excel does not display
' an error if the record number is not found
On Error Resume Next
' Find the row in the table that the record is in
RecordRow = Application.Match(CLng(Me.txtPatientName.Value), Range("Requests[Patient Name]"), 0)
' Set RecordRange to the first cell in the found record
Set RecordRange = Range("Requests").Cells(1, 2).Offset(RecordRow - 1, 0)
' If an error has occured i.e the record number was not found
If Err.Number <> 0 Then
Me.lblErrorNoPatient.Visible = True
On Error GoTo 0
Exit Sub
End If
' Turn default error handling back on (Let Excel handle errors from now on)
On Error GoTo 0
' If the code gets to here the record number was found
' Hide the error message 'Not Found'
Me.lblErrorNoPatient.Visible = False
' and populate the form fields with the record's data
Me.txtPatientDOB.Value = RecordRange(1, 2).Offset(0, 1).Value
Me.txtPatientPhone.Value = RecordRange(1, 2).Offset(0, 2).Value
Me.cboPrimarySurgeon.Value = RecordRange(1, 2).Offset(0, 4).Value
Me.cboSecondarySurgeon.Value = RecordRange(1, 2).Offset(0, 5).Value
....
I had been using this code, but yours looked cleaner and I wanted to use your tutorials to build the insert/update macros. My existing code has issues, for instance if I have 2 rows with the same patient name, the update macro updates both rows.
If IsNull(Me.txtPatientDOB.Value) Then
pt_DOB = ""
Else
pt_DOB = Me.txtPatientDOB.Value
End If
lastrow = Worksheets("Procedures").Cells(Rows.Count, 1).End(xlUp).Row
Dim SurgeryTime As String
For i = 2 To lastrow
If (Worksheets("Procedures").Cells(i, 3).Value = pt_DOB Or pt_DOB = "") And Worksheets("Procedures").Cells(i, 2).Value = UCase(patient_name) Then
lblErrorNoPatient.Visible = False
ProcedureID = Worksheets("Procedures").Cells(i, 1).Value
lblProcedureID.Caption = "Procedure ID: " & ProcedureID
Me.txtPatientDOB.Value = Worksheets("Procedures").Cells(i, 3).Value
Me.txtPatientPhone.Value = Worksheets("Procedures").Cells(i, 4).Value
Me.cboPrimarySurgeon.Value = Worksheets("Procedures").Cells(i, 6).Value
Me.cboSecondarySurgeon.Value = Worksheets("Procedures").Cells(i, 7).Value
....
Else
lblErrorNoPatient.Visible = True
End If
Next i
Hi Rebecca,
Can you please supply your workbook, or at least some sample data in the same structure. I need to run the code and the form against data to see what is not working.
Can you also please elaborate on 'but it is not working on my form' - how exactly? Is it just that it updates all rows where the patient name is the same or is there more?
Regards
Phil
*Can you also please elaborate on 'but it is not working on my form' - how exactly? Is it just that it updates all rows where the patient name is the same or is there more?
It does not find the row when I try to run the code. It just displays the error label.
I've attached a sample of the workbook I have been working with for you to use. Many of the fields are blank, feel free to edit as needed.
Hi Rebecca,
The MATCH function requires an exact match when the last parameter is 0 and in my blog post I used it this way to look up values that I knew were unique, like ID numbers.
To lookup names like you want to do you should use Find and FindNext
Because your searches can return multiple matches (people with the same name) you need to decide how you want to handle such a situation. In the attached file I've put a loop in the Lookup() sub that returns every match. You can either display them all or you could use a drop down list to allow the patient name (and unique identifier?) to be chosen, and avoid having to do this searching altogether.
Hope this points you in the right direction.
Regards
Phil
Thanks!!
