Problem with search form - need to search by unique ID?

[kate10123]

Hi there,

I have been using the following search code which allows a user to enter either a surname, first name or student id as a search term and click on any matches.

In the event that there are two matches, the only thing to uniquely identify which student this is, is via the student id.

The code has the problem that even though in the code I am saying, display the unique record according to the unique student ID when clicked it displays the first of many records according to the surname.

This is not ideal.

Here is my code:

Option Compare Database

Private Sub back_Click()

DoCmd.close acForm, "frmStudentSearch", acSaveYes
  DoCmd.OpenForm "frm_FrontStudentScreen"
End Sub

Private Sub cmdReset_Click()
On Error GoTo Err_Reset_Click

Me.Search = ""
 Me.Search2 = ""
  Me.QuickSearch.Requery
   Me.QuickSearch.SetFocus

Exit_Reset_Click:
    Exit Sub

Err_Reset_Click:
    MsgBox Err.Description
    Resume Exit_Reset_Click
End Sub

Private Sub Label20_Click()
DoCmd.close acForm, "frmStudentSearch", acSavePrompt
End Sub

Private Sub Label22_Click()
DoCmd.close acForm, "frmStudentSearch", acSavePrompt
End Sub

Private Sub QuickSearch_Click()
DoCmd.OpenForm "frm_student", , , "Surname = '" & Me.QuickSearch & "'"
DoCmd.close acForm, "frmStudentSearch", acSaveYes
DoCmd.close acForm, "frm_FrontStudentScreen", acSaveYes
End Sub

Private Sub Search_Change()

Dim vSearchString As String
 

 
If Me.Search.text = "" Then
Me.QuickSearch.RowSource = ""
Else

Me.QuickSearch.RowSource = "student"
End If

 vSearchString = Search.text
 Search2.Value = vSearchString
 
 Me.QuickSearch.Requery

End Sub


Private Sub QuickSearch_AfterUpdate()
    
DoCmd.Requery
Me.RecordsetClone.FindFirst [StudentID] = "'" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
   Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If

End Sub

 

[MStef]

Look at "DemoSearchnigOnFormA2000.mdb" (attachment, zip).
Open Form1 and try.

 

[kate10123]

Hi,

Thanks for that, had a look and made some changes to my code to look at the StudentID as opposed to the surname but the problem I am having now is when the I click on a search result it says the studentID and then brings up a parameter box.

I have attached it to show you what I mean,

 

[kate10123]

Has anyone else got any ideas?

 

[MStef]

Hello Kate!
Look at frmStudent (Form header, combo box), try.

 

[kate10123]

Hi Mstef,

Thanks for your efforts but unfortunately this type of solution is not suitable for the amount of information that users will be dealing with.

Having the search facility on the actual record form would make the performance for the user very slow given that on loading the form all existing records for students would have to load (this could be thousands) and also a combo box is not great because users might not know a full name of a student and would have to scroll through, thus taking time.

The search function that I have is great, I don't need to change that. I just need to look at how to get the click method on the listbox to just show the record selected.

Has anyone else got any ideas?

Private Sub QuickSearch_Click()
DoCmd.OpenForm "frm_student", , , [B]"[StudentID] = [" & Me![QuickSearch].Column(2) & "]"[/B]
DoCmd.close acForm, "frmStudentSearch", acSaveYes
DoCmd.close acForm, "frm_FrontStudentScreen", acSaveYes
End Sub
Private Sub Search_Change()
Dim vSearchString As String
 
 
If Me.Search.text = "" Then
Me.QuickSearch.RowSource = ""
Else
Me.QuickSearch.RowSource = "student"
End If
 vSearchString = Search.text
 Search2.Value = vSearchString
 
 Me.QuickSearch.Requery
End Sub

Private Sub QuickSearch_AfterUpdate()
    
DoCmd.Requery
Me.RecordsetClone.FindFirst [B][StudentID[/B]] = [B]Me![QuickSearch]
[/B]If Not Me.RecordsetClone.NoMatch Then
   Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate [" & Me![QuickSearch].Column(2) & "]"
End If
End Sub

 

[MStef]

OK, Kate, Look at a new one.
I added two new fields in Form Header, (Surename and Student ID).
Try to enter first leter of surename. I think it is what you need.
Look at VBA, Query1.
The form is linked on Query1.