Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
Trying to use multiple inputs of the same type into a form to update the relevant field in a table
I hope it's a basic prolem.... we're creating an inventory system for our plant. The scanner we have can scan either a single dataset then store it and then transfer it (I have a database that works for that.... the update simply sends the stored data to the textbox which is linked to my table)....
My problem:
The scanner can also store multiple inputs and hold them (say 10 at a time) so that it's more efficient.... however I don't know how to adapt my form to take in multiple datasets in the same 'textbox' or some variation of and then update the table with all relevent records
Example:
Currently:
Scanner - aaaddd
stored
uploaded to textbox [aaaddd]
saved
table gets updated to include [aaaddd]
Issue:
Scanner - a1 a2 a3 a4 a5 a6
stored
upload to............ single text box fails
save
table gets updated with a1 a2 a3 a4 a5 a6
**The scanner stores them as new rows ... ie:
[a1
a2
a3
a4]
My problem:
The scanner can also store multiple inputs and hold them (say 10 at a time) so that it's more efficient.... however I don't know how to adapt my form to take in multiple datasets in the same 'textbox' or some variation of and then update the table with all relevent records
Example:
Currently:
Scanner - aaaddd
stored
uploaded to textbox [aaaddd]
saved
table gets updated to include [aaaddd]
Issue:
Scanner - a1 a2 a3 a4 a5 a6
stored
upload to............ single text box fails
save
table gets updated with a1 a2 a3 a4 a5 a6
**The scanner stores them as new rows ... ie:
[a1
a2
a3
a4]
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 23
I am not sure i understood your problem...i guess you want to "exploit" the capability of your scanner's memory to do rapid scans (x10) and do an update /insert in the bulk....probably your scanner can append a special character at the end of each scan and send one big string delimited...You split the string (split command) and you do the necessary work afterwards...
You can use code to split text from scanner into records in your table
use split function with code like this:
use split function with code like this:
sub split_scanner(txtScanned as string)
Dim r() As String, i as integer
Dim rst as recordset
rst = CurrentDb.Openrecordset("yourtable")
r = Split(txtScanned, vbCrLf)
for i = 0 to ubound(r)
rst.addnew
rst!YourField = trim(r(i))
rst.update
next i
rst.close
end sub
I appreciate the help and will try to use your code als315....
To further summarize:
scanner scans 1 dataset which is then transferred to my form's textbox which can then be saved to my table...
My Issue:
With multiple scans, the scanner uses a delimiter (can be either a return or a tab delimiter)..... so now the scanner will transfer all the data to the single textbox [scan1 scan2 scan3 scan4] , with each scan being seperated by the delimiter.
Is there a way to code my save button so it essentially 'splits my multiple scans into individual inputs to then save as each individual scan'
i.e. Without code to handle the delimiter if I were to save the input then my input for the single record on the table would show : scan1 scan2 scan3 scan4
Whereas I need it to show
record1 scan1
record2 scan2
record3 scan3
record4 scan4
I hope this clears things up
To further summarize:
scanner scans 1 dataset which is then transferred to my form's textbox which can then be saved to my table...
My Issue:
With multiple scans, the scanner uses a delimiter (can be either a return or a tab delimiter)..... so now the scanner will transfer all the data to the single textbox [scan1 scan2 scan3 scan4] , with each scan being seperated by the delimiter.
Is there a way to code my save button so it essentially 'splits my multiple scans into individual inputs to then save as each individual scan'
i.e. Without code to handle the delimiter if I were to save the input then my input for the single record on the table would show : scan1 scan2 scan3 scan4
Whereas I need it to show
record1 scan1
record2 scan2
record3 scan3
record4 scan4
I hope this clears things up
als315
Where do I implement that code?
I tried to throw that code in my save button code to result in:
Now I get "Unexpected End Sub" while highlighting the Private Sub first line.... any ideas?
Where do I implement that code?
I tried to throw that code in my save button code to result in:
Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Sub split_scanner(Text17 As String)
Dim r() As String, i As Integer
Dim rst As Recordset
rst = CurrentDb.OpenRecordset("Main_Database_")
r = Split(Text17)
For i = 0 To UBound(r)
rst.AddNew
rst!DataIN = Trim(r(i))
rst.Update
Next i
rst.Close
Exit_Command16_Click:
Exit Sub
Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click
End Sub
Now I get "Unexpected End Sub" while highlighting the Private Sub first line.... any ideas?
You should place my sub into module and call it from button:
Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Text17 = "str1" & vbCrLf & "str2" & vbCrLf & "str3" & vbCrLf
split_scanner (Text17)
Exit_Command16_Click:
Exit Sub
Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click
End Sub
Sub split_scanner(Text17 As String)
Dim r() As String, i As Integer
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Main_Database_")
r = Split(Text17, vbCrLf)
For i = 0 To UBound(r)
If Len(Trim(r(i))) > 0 Then
rst.AddNew
rst!DataIN = Trim(r(i))
rst.Update
End If
Next i
rst.Close
End Sub
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.