Combo box difficulties in selecting record (1 Viewer)

[majordoc]

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.

 

[Ranman256]

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)

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

 

[bob fitz]

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?

 

[Pat Hartman]

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.