how to lookup data from a text box

[Daniel M]

I have a text box that will have a string of numbers in it separated by a
comma. I need to do a lookup in a table for each number string. ie, the
string maybe 1230,1323,1223,1322 and i need to lookup 1230 from a table, then
1323 from the same table, and so forth. the data will ALWAYS be the same
length. for this example 4 digits, for my actual data it will be 9 digits.

can someone help with how i can do this? Thanks.

 

[Stuart McCall]

I have a text box that will have a string of numbers in it separated by a
comma. I need to do a lookup in a table for each number string. ie, the
string maybe 1230,1323,1223,1322 and i need to lookup 1230 from a table,
then
1323 from the same table, and so forth. the data will ALWAYS be the same
length. for this example 4 digits, for my actual data it will be 9 digits.

can someone help with how i can do this? Thanks.

Well one way would be to use the Split function to get the numbers into an
array, like this:

Dim arr As Variant, item As Variant

arr = Split("1230,1323,1223,1322", ",")

Then you can iterate the array with the For...Each construct:

'Probably open a recordset here
For Each item In arr
'Do your lookup
Next
'Close your recordset
'(don't forget to set to Nothing)

 

[Daniel M]

I understand the concept of what you are talking about but could use a
little bit more help on the syntax. Also, can i use the split command with
out knowing the text? you had the text in "" but the text will really be
textbox7 or something like that on the form.

thanks.

 

[Stuart McCall]

I understand the concept of what you are talking about but could use a
little bit more help on the syntax. Also, can i use the split command with
out knowing the text? you had the text in "" but the text will really be
textbox7 or something like that on the form.

thanks.

Unfortunately "I need to do a lookup in a table for each number string"
doesn't convey enough info for help with syntax. That's why my message was a
little vague. I was leaving it for you to 'fill in the blanks'.

Please try to explain what you mean by 'do a lookup'. Lookup which field
from which table or query? Also, what do you want to do with the looked-up
data once you have it?

 

[Daniel M]

Stuart/All,

Ok here is the deal...

I have a text field on a form that i want to scan in barcodes to. From here
i need to take the string of data and process it once a command button is
pressed.

For instance. i scan in the following data... "1000, 1001, 1002..."

Take the first serialnumber (1000) and do a lookup, for instance... select *
from table1 where serialnumber = stringdata (1000)

The table has 3 columns. serialnumber, phonenumber, productid. I need to
take the data in all 3 fields and put them into variables i can use. I will
then submit and http post including the 3 selected pieces of data from the
column.

Now i repeat with serialnumber 1001.. and so forth.

any help on any piece will be appreciated!

Daniel.

 

[Stuart McCall]

Stuart/All,

Ok here is the deal...

I have a text field on a form that i want to scan in barcodes to. From
here i need to take the string of data and process it once a command
button is pressed.

For instance. i scan in the following data... "1000, 1001, 1002..."

Take the first serialnumber (1000) and do a lookup, for instance... select
* from table1 where serialnumber = stringdata (1000)

The table has 3 columns. serialnumber, phonenumber, productid. I need to
take the data in all 3 fields and put them into variables i can use. I
will then submit and http post including the 3 selected pieces of data
from the column.

Now i repeat with serialnumber 1001.. and so forth.

any help on any piece will be appreciated!

Daniel.

Right, here's a bit of air-code that should get you started:

Dim arr As Variant, item As Variant


'Get the contents of textbox7 into an array
arr = Split(Me.Textbox7, ",")

'Open the recordset
With CurrentDb.OpenRecordset("Select * From Table1;")

'Iterate the array arr
For Each item In arr
'Search for the item from the array
.FindFirst "SerialNumber = '" & item & "'"

'If found, grab the field values
If Not .NoMatch Then
Variable1 = !phonenumber
Variable2 = !productId

'**Submit your http post here**
End If
Next

'Close the recordset
.Close
End With

This code is intended to run in your form's class module. If you need to run
it elsewhere, substitute:

Me.Textbox7

with:

Forms!NameOfForm!Textbox7

Also you probably don't need to use Variable1 & Variable2; just use the
field values directly (!phonenumber etc.).

 

[Daniel M]

Stuart/all,

It works great. now i just have one more step! i can get the data to pull
out if it is comma delimited (ie: 1234, 1233...) but what about if the data
is not delimeted? (ie: 12341233) I would want it to return 1234 and 1233. In
excel i would use the mid function but i'm not sure how to get it to work
here.

Thanks for all the help so far!

dm.

 

[Stuart McCall]

Stuart/all,

It works great. now i just have one more step! i can get the data to pull
out if it is comma delimited (ie: 1234, 1233...) but what about if the
data
is not delimeted? (ie: 12341233) I would want it to return 1234 and 1233.
In
excel i would use the mid function but i'm not sure how to get it to work
here.

<SNIP>

The Mid function works in Access precisely the same as in Excel.

Get the length of the string and divide by 4 to determine the number of
'elements'. Then loop over the string like this:

For i = 1 to (Len(theString) \ 4)
MyVariable = Mid(theString, i * 4, 4)
'Do whatever with MyVariable
Next

 

[Daniel M]

Sweet! It all works like a champ!

Thanks for all the help...