Combo box difficulties in selecting record

majordoc

New member
Local time
Today, 13:25
Joined
Sep 29, 2020
Messages
24
Hello all

Thanks for any help. See attached file.
First I know it's a crap database structure and I am endeavouring to change this. I am not a professional, just trying to set up a small database to help me look after patients.

On the "Select Patient" combo box (number 147) I have two problems:
1) If a patient has ' in name (see O'Hanlon in example database) then I cannot select.
2) If patient has same surname but different forename I can only select one of them.

Probably very easy solutions but I'm stumped!
Thank you for any help.
 

Attachments

you cannot use single quote to use on names with single quotes.
instead of the embedded macro , remove it and use EVENT PROCEDURE
use this code to get the patient. DONT use names, use a patientID (or use autonum as ID)
make sure the combo has PatientID in it (but doesnt have to be seen)

Code:
sub cboBox_Afterupdate()
If IsNull(cboBox) Then
  Me.FilterOn = False
Else
    'if numeric
  Me.Filter = "[PatientID]=" & cboBox
     'if string
' Me.Filter = "[PatientID]='" & cboBox & "'"

  Me.FilterOn = True
End If
end sub
 
majordoc
I do not use macros. In the attached db I have used VBA code to find the required record.
I have used "Health_and_Care_Number" (the Primary Key) to find records.
Otherwise, how would you possibly find the correct record if you have more than one person with the same name?
 

Attachments

Code:
sub cboBox_Afterupdate()
If IsNull(cboBox) Then
  Me.FilterOn = False
Else
    'if numeric
  Me.Filter = "[PatientID]=" & cboBox
     'if string
' Me.Filter = "[PatientID]=""" & cboBox & """"

  Me.FilterOn = True
End If
end sub

Because I hate to have to keep count of the ", I usually create a constant named QUOTE.

Me.Filter = "[PatientID]=" & QUOTE & cboBox & QUOTE

That makes it clearer that you are embedding a quote in a string.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom