Locating a record from unbound text box

[Earl G via AccessMonster.com]

My only experience in programming is procedural and in Foxpro (DOS) so this
is my only point of comparison and reference. I want to do the following in
Access/VBA - In Fox, I would take input (e.g. cust_id) from the screen and
'seek' in the indexed customer table with the inputed cust_id. This would
find the unique record and all of the data would be available to me for
various uses. Just a simple lookup to display the information or to edit the
data. How do I find the record from data entered into an unboud text box and
then have it available to display and edit?

Thanks in advance.
Earl

 

[tina]

one solution is to create a form based on the customer table. in the form's
header, add an unbound textbox, which i'll call txtFind. add the following
code to the textbox's AfterUpdate event, as

Private Sub txtFind_AfterUpdate()

Me.Recordset.FindFirst "cust_id = " & Me!txtFind
If Not Me.Recordset.NoMatch Then
Me!ControlName.SetFocus
Me!txtFind = Null
End If

End Sub

the above assumes that cust_id is a number data type. if it's a text data
type, then the correct syntax is

Me.Recordset.FindFirst "cust_id = '" & Me!txtFind & "'"

in either case, replace "ControlName" (3rd line of code in the sub) with the
name of whatever control you'd like to move focus to, when the record is
found.

an alternate solution is to substitute the unbound textbox with an unbound
combo box, with the RowSource property based on the customers table. that
would make all the valid cust_id values available for the user to choose
from - cutting down on unsuccessful "finds" due to typos. it's also useful
if you want to search by the table's primary key value, but show the user
another value to choose from instead - such as customer name.

hth

 

[Earl G via AccessMonster.com]

Thank you. This worked immediately and set me on a better research path.
However, the same cust_id that worked began to set NoMatch to true. I
checked the table and it is properly indexed and the data is there. After 2-
3 hours of researching and trying I need to give it up for now. Any pointers
would be greatly appreciated. FYI, the data is input by barcode scan and the
advantage of the combo box that you mentioned would not be utilized.

Earl

one solution is to create a form based on the customer table. in the form's
header, add an unbound textbox, which i'll call txtFind. add the following
code to the textbox's AfterUpdate event, as

Private Sub txtFind_AfterUpdate()

Me.Recordset.FindFirst "cust_id = " & Me!txtFind
If Not Me.Recordset.NoMatch Then
Me!ControlName.SetFocus
Me!txtFind = Null
End If

End Sub

the above assumes that cust_id is a number data type. if it's a text data
type, then the correct syntax is

Me.Recordset.FindFirst "cust_id = '" & Me!txtFind & "'"

in either case, replace "ControlName" (3rd line of code in the sub) with the
name of whatever control you'd like to move focus to, when the record is
found.

an alternate solution is to substitute the unbound textbox with an unbound
combo box, with the RowSource property based on the customers table. that
would make all the valid cust_id values available for the user to choose
from - cutting down on unsuccessful "finds" due to typos. it's also useful
if you want to search by the table's primary key value, but show the user
another value to choose from instead - such as customer name.

hth

My only experience in programming is procedural and in Foxpro (DOS) so this
is my only point of comparison and reference. I want to do the following in

[quoted text clipped - 7 lines]

Thanks in advance.
Earl

 

[tina]

This worked immediately and set me on a better research path.

However, the same cust_id that worked began to set NoMatch to true.

are you saying it worked when you first tried it, but then subsequently
stopped working? did you change anything in the form, the code, or the
table? if not...

i'm not familiar with the ins-and-outs of populating controls with data from
a barcode scanner. it's possible that what you're "seeing" in the textbox is
not the same as what Access is reading when the FindFirst code runs. i
wonder if there could be a leading or trailing space in the textbox's value;
i'm not sure how that would affect a numeric comparison, but it would
definitely affect a text comparison.

you might try running a message box to show the number of characters in the
textbox, as

MsgBox Len(Me!txtFind)

if you're seeing 7 characters (for instance), but the message box returns 8,
then you have something to work on. using a Trim() function on the textbox
value might take care of that.

other than that, i can only suggest stepping through the "find" code, to
check the value of txtFind when the code is running.

hth

Thank you. This worked immediately and set me on a better research path.
However, the same cust_id that worked began to set NoMatch to true. I
checked the table and it is properly indexed and the data is there. After 2-
3 hours of researching and trying I need to give it up for now. Any pointers
would be greatly appreciated. FYI, the data is input by barcode scan and the
advantage of the combo box that you mentioned would not be utilized.

Earl

one solution is to create a form based on the customer table. in the form's
header, add an unbound textbox, which i'll call txtFind. add the following
code to the textbox's AfterUpdate event, as

Private Sub txtFind_AfterUpdate()

Me.Recordset.FindFirst "cust_id = " & Me!txtFind
If Not Me.Recordset.NoMatch Then
Me!ControlName.SetFocus
Me!txtFind = Null
End If

End Sub

the above assumes that cust_id is a number data type. if it's a text data
type, then the correct syntax is

Me.Recordset.FindFirst "cust_id = '" & Me!txtFind & "'"

in either case, replace "ControlName" (3rd line of code in the sub) with the
name of whatever control you'd like to move focus to, when the record is
found.

an alternate solution is to substitute the unbound textbox with an unbound
combo box, with the RowSource property based on the customers table. that
would make all the valid cust_id values available for the user to choose
from - cutting down on unsuccessful "finds" due to typos. it's also useful
if you want to search by the table's primary key value, but show the user
another value to choose from instead - such as customer name.

hth

My only experience in programming is procedural and in Foxpro (DOS) so this
is my only point of comparison and reference. I want to do the

following in
[quoted text clipped - 7 lines]

Thanks in advance.
Earl

 

[Earl G via AccessMonster.com]

FYI, I am typing in the cust_id for testing. (The barcode scan simply enters
the text as though it was entered by hand from the keyboard. It is actually
know as a keyboard wedge.)

For now I am just typing it in. Yes it worked at first (several times).
Yes , I validate all aspects of the input (length and content) and have
checked the values in the debugger. The value of txtfind is right on. On a
second point, I have changed a few things in the forms properties but not the
table. I have reveiwed the changes and properties a dozen times. It is
probably some simple thing that I am missing. A fresh start in the morning
may help. I appreciate, very much, you taking the time to help. Thanks much.

Earl

This worked immediately and set me on a better research path.
However, the same cust_id that worked began to set NoMatch to true.

are you saying it worked when you first tried it, but then subsequently
stopped working? did you change anything in the form, the code, or the
table? if not...

i'm not familiar with the ins-and-outs of populating controls with data from
a barcode scanner. it's possible that what you're "seeing" in the textbox is
not the same as what Access is reading when the FindFirst code runs. i
wonder if there could be a leading or trailing space in the textbox's value;
i'm not sure how that would affect a numeric comparison, but it would
definitely affect a text comparison.

you might try running a message box to show the number of characters in the
textbox, as

MsgBox Len(Me!txtFind)

if you're seeing 7 characters (for instance), but the message box returns 8,
then you have something to work on. using a Trim() function on the textbox
value might take care of that.

other than that, i can only suggest stepping through the "find" code, to
check the value of txtFind when the code is running.

hth

Thank you. This worked immediately and set me on a better research path.
However, the same cust_id that worked began to set NoMatch to true. I

[quoted text clipped - 42 lines]

 

[tina]

you're welcome, Earl. let me know how it goes.

FYI, I am typing in the cust_id for testing. (The barcode scan simply enters
the text as though it was entered by hand from the keyboard. It is actually
know as a keyboard wedge.)

For now I am just typing it in. Yes it worked at first (several times).
Yes , I validate all aspects of the input (length and content) and have
checked the values in the debugger. The value of txtfind is right on. On a
second point, I have changed a few things in the forms properties but not the
table. I have reveiwed the changes and properties a dozen times. It is
probably some simple thing that I am missing. A fresh start in the morning
may help. I appreciate, very much, you taking the time to help. Thanks much.

Earl

This worked immediately and set me on a better research path.
However, the same cust_id that worked began to set NoMatch to true.

are you saying it worked when you first tried it, but then subsequently
stopped working? did you change anything in the form, the code, or the
table? if not...

i'm not familiar with the ins-and-outs of populating controls with data from
a barcode scanner. it's possible that what you're "seeing" in the textbox is
not the same as what Access is reading when the FindFirst code runs. i
wonder if there could be a leading or trailing space in the textbox's value;
i'm not sure how that would affect a numeric comparison, but it would
definitely affect a text comparison.

you might try running a message box to show the number of characters in the
textbox, as

MsgBox Len(Me!txtFind)

if you're seeing 7 characters (for instance), but the message box returns 8,
then you have something to work on. using a Trim() function on the textbox
value might take care of that.

other than that, i can only suggest stepping through the "find" code, to
check the value of txtFind when the code is running.

hth

Thank you. This worked immediately and set me on a better research path.
However, the same cust_id that worked began to set NoMatch to true. I

[quoted text clipped - 42 lines]

Thanks in advance.
Earl