Search records using unbound textbox

[Eric the Viking]

I have a table of names and unique ID numbers which I want to search from an unbound textbox on a form and return the result to a listbox.

I can do it using name but would like to use either name or ID to return either a list of people with the same name or a person with a particular ID number. The code is in the afterupdate event:

Private Sub TextSurname_AfterUpdate()
On Error Resume Next
ListPts.RowSource = "Select tblpatients.PatientNumber,tblpatients.Surname,tblpatients.Firstname1,tblpatients.DateofBirth,tblpatients.NHSnumber " & _
"FROM tblpatients " & _
"WHERE tblpatients.Surname = '" & TextSurname.Value & "' " & _
"ORDER BY tblpatients.Firstname1;"
Dim getListPtsCount As Integer
Dim setHeight As Integer
getListPtsCount = Me.ListPts.ListCount
setHeight = 240
Me.ListPts.Height = getListPtsCount * setHeight

End Sub

The user can then click a record in the listbox which opens the full record in a new form and at the same time clears the contents of the textbox and listbox ready for a new search.

I am new to VBA so forgive me if the above looks clunky but it does seem to work. Any advice on refinements gratefully received.

Cheers

Eric

 

[pr2-eugin]

Hello Eric,

Welcome to AWF.

For starters, Please do not post Questions in the Introduction section, as it might not always be looked up for answering questions. Any question will be answered (eventually) if placed in the appropriate sections.

Second.. Your code can be modified as per your requierment, all you need is a simple IF condition that will check if the entered value is a Number, if so the where condition will change else, looks up to the Surname.. This is just a simple coding, not tested.. can be optimised.. but for now it will do..

Private Sub TextSurname_AfterUpdate()
    If ISNumeric(Me.TextSurname) Then
        ListPts.RowSource = "Select tblpatients.PatientNumber,tblpatients.Surname,tblp atients.Firstname1,tblpatients.DateofBirth,tblpati ents.NHSnumber " & _
                        "FROM tblpatients " & _
                        "WHERE tblpatients.PatientNumber = " & TextSurname.Value & _
                        " ORDER BY tblpatients.Firstname1;"
    Else
        ListPts.RowSource = "Select tblpatients.PatientNumber,tblpatients.Surname,tblp atients.Firstname1,tblpatients.DateofBirth,tblpati ents.NHSnumber " & _
                        "FROM tblpatients " & _
                        "WHERE tblpatients.Surname = '" & TextSurname.Value & "' " & _
                        "ORDER BY tblpatients.Firstname1;"
    End If
    Dim getListPtsCount As Integer
    Dim setHeight As Integer
    getListPtsCount = Me.ListPts.ListCount
    setHeight = 240
    Me.ListPts.Height = getListPtsCount * setHeight
End Sub

As you might notice I have deleted the On Error statement you had put.. This is because, using Resume Next is a very bad way of handling error.. What you are doing is asking the Compiler to play blind and assume that even if an error occurs just ignore it.. To properly handle errors have a look at C Pearson's page on Error Handling.. Hope this helps..

 

[Eric the Viking]

Many thanks I thought something like that would be the answer but every time a tried I seemed not to use the correct syntax after the If.

Didnt realise I had posted in the wrong place was finding the site difficult to find my was round.

Once again thanks your suggestion works perfectly.

Regards

Eric

 

[pr2-eugin]

You are most welcome.. Glad to help..