Search using a text box.

[neiljw24]

Can somebody please help me with some coding.

I have designed a DB to hold all the invoices that my company recieves.

What i want to do is:

• Have the user input a new invoice number into an unbound textbox.
• Have the DB search for any matching records.
• If matching records are found have a msgbox open with two options for the user. A. View this record on the current form B. Return to the current form to enter a new invoice number

4. If matching records are not found then the msgbox will only give one option of continuing to enter the details for the present invoice number.

I found this code which I have tried to change for my needs but I’m afraid I’m in way over my head with this one.

Private Sub SearchInvoice_AfterUpdate()
On Error GoTo SearchInvoice_Err

With CodeContextObject

DoCmd.GoToControl "[InvoiceNo]"
DoCmd.FindRecord SearchInvoice, acEntire, False, , False, acCurrent, True
Me.InvoiceNo.SetFocus

End With

If Me.InvoiceNo <> Me.SearchInvoice Then
If Msgbox ("No matching records found. Would you like to create a new record for this project?",vbyesno, "Notice!") = vbYes Then
Docmd.gotorecord, , acNewRec
Me.BrokerName.SetFocus

Else

Me.SearchInvoice.SetFocus
me.SearchInvoice.value=Null
End If
End If
SearchInvoice_Exit:
Exit Sub

SearchInvoice_Err:
Exit Sub

End Sub

The only thing that happens after an update in the searchInvoice field is the focus goes to the invoiceNo

Any help would be greatly appreciated.
Thanks

 

[AFKillbait]

I just recently did something like this for one of my databases.

Used a combo box with its RowSource set to the Field and Table I wanted it to search, and set it to Ascending Order (can do all of that using the Combo Box Wizard); then created an AfterUpdate event and use SetFocus. This made it automatically switch to that record in my Form when they hit Enter however, so not sure how to make it pop up with a confirmation box.

This is the code I used for the AfterUpdate however, to answer part of your question:

Private Sub Combo93_AfterUpdate()
    Me![Field Name].SetFocus
    DoCmd.FindRecord Me!Combo93, acEntire
 
End Sub

Hope this helps a bit at least.