Selecting a new record

[Guest]

Hello,

I have a form where, in the top left corner, I have a text box called
order_number. What I would like to do, is to type in an order number in that
box, and, when tabbing out of the field, access should jump to the
corresponding record in the database.

This should happen even if a different record is currently populating the
form.

What is the best way of going about this?

thx

Baard

 

[Van T. Dinh]

That TextBox should be unbound ... and you can use the TextBox_AfterUpdate
Event to navigate to the Record that has matching order_number.

HTH
Van T. Dinh
MVP (Access)

 

[Guest]

Thank you, it didn't occur to me to use an unbound text box.

But could you opint me in the right direction as to what code to use to
display the correct record after the order number has been entered into the
text box?

It is probably very basic, but I am not very experienced, unfortunately

thx again

Baard

 

[John Vinson]

Hello,

I have a form where, in the top left corner, I have a text box called
order_number. What I would like to do, is to type in an order number in that
box, and, when tabbing out of the field, access should jump to the
corresponding record in the database.

This should happen even if a different record is currently populating the
form.

What is the best way of going about this?

The simplest way would be to use an unbound Combo Box rather than a
textbox; the combo box wizard has an option to "use this combo to find
a record". Otherwise, as Van suggests, use an unbound textbox's
AfterUpdate event. If you have a textbox bound to Order_Number, DON'T
use it (or you'll overwrite the current record's Order_Number);
instead create a second unbound textbox I'll call txtFindOrderNumber.
Try code like

Private Sub txtFindOrderNumber_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the form's recordsource
rs.FindFirst "[Order_Number] = '" & Me.txtFindOrderNumber & "'"
' leave off the ' and the "'" if Order_Number is a Number field;
' I'm assuming it's Text
If rs.NoMatch Then
Msgbox "Order number not found, moving to new record", vbOKOnly
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
Else
Me.Bookmark = rs.Bookmark ' go to found record
End If
Set rs = Nothing
End Sub

John W. Vinson[MVP]

 

[Guest]

Thank you John, that was really helpful

Baard

Hello,

I have a form where, in the top left corner, I have a text box called
order_number. What I would like to do, is to type in an order number in that
box, and, when tabbing out of the field, access should jump to the
corresponding record in the database.

This should happen even if a different record is currently populating the
form.

What is the best way of going about this?

The simplest way would be to use an unbound Combo Box rather than a
textbox; the combo box wizard has an option to "use this combo to find
a record". Otherwise, as Van suggests, use an unbound textbox's
AfterUpdate event. If you have a textbox bound to Order_Number, DON'T
use it (or you'll overwrite the current record's Order_Number);
instead create a second unbound textbox I'll call txtFindOrderNumber.
Try code like

Private Sub txtFindOrderNumber_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the form's recordsource
rs.FindFirst "[Order_Number] = '" & Me.txtFindOrderNumber & "'"
' leave off the ' and the "'" if Order_Number is a Number field;
' I'm assuming it's Text
If rs.NoMatch Then
Msgbox "Order number not found, moving to new record", vbOKOnly
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
Else
Me.Bookmark = rs.Bookmark ' go to found record
End If
Set rs = Nothing
End Sub

John W. Vinson[MVP]