search function using combo list

  • Thread starter Thread starter datin
  • Start date Start date
D

datin

hi,
i hv data on staff . when I need to find specific staff, i dont want to
write down the staff ID. I want all staff ID will dispaly in combo list
search.

for current search, i just use the standard code

On Error GoTo Err_search_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_search_Click:
Exit Sub

Err_search_Click:
MsgBox Err.Description
Resume Exit_search_Click

thank you and regards
 
hi,
i hv data on staff . when I need to find specific staff, i dont want to
write down the staff ID. I want all staff ID will dispaly in combo list
search.

for current search, i just use the standard code

On Error GoTo Err_search_Click

    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_search_Click:
    Exit Sub

Err_search_Click:
    MsgBox Err.Description
    Resume Exit_search_Click

thank you and regards
Click to expand...

Just use the combobox wizard and select "Find a record..." Set the
source of the combobox to something like

"SELECT ID, LastName & ", " & FirstName As LastFirst FROM Employee
ORDER BY LastName, FirstName;"
 
thank you. here some code as suggestion

On Error GoTo Err_find_Click

Dim SQL As String
Dim ListForm As Form

Set ListForm = Forms![staff]

SQL = ""

If Not IsNothing(Me![Staff_ID]) Then
SQL = SQL & "[Staff_ID] LIKE '" & Me![Staff_ID] & "*' AND "
End If

If SQL = "" Then
MsgBox "NO STAFF ID SELECTED", 48, "Error"
Exit Sub
End If

If Right$(SQL, 4) = "And " Then
SQL = Mid$(SQL, 1, Len(SQL) - 4)
End If

ListForm.RecordSource = "Select * From staff where " & SQL & ";"

If ListForm.RecordsetClone.RecordCount < 1 Then
MsgBox "The search returned no records." & Chr(13) & " All records
will be displayed.", 48, "FIND PRODUCTION PLAN DATE"
ListForm.RecordSource = "SELECT * FROM staff;"
End If

DoCmd.Close
Exit_find_Click:
Exit Sub

Err_find_Click:
MsgBox Err.Description
Resume Exit_Command5_Click


and use as module:
Option Compare Database 'Use database order for string comparisons
Option Explicit
Function IsNothing(v As Variant) As Integer

' IsNothing starts out as FALSE. We
' determine if v is Nothing by checking
' its VarType

IsNothing = False
Select Case VarType(v)
Case V_EMPTY
IsNothing = True

Case V_NULL
IsNothing = True

Case V_STRING
If Len(v) = 0 Then
IsNothing = True
End If

Case Else
IsNothing = False

End Select

End Function
 
Back
Top