OK, I came up with *might* be a workable solution. I say might because
I know very little about your app, so there may be reasons why this won't
work for you. If you decide to try this method, backup your db first and
make a test copy to work on (hopefully you're already doing that anyway).
Make sure you test it thoroughly before you try to implement it in your
actual application. There could be some fatal flaw in my logic that I'm not
seeing. If so, hopefully one of the experts in the group will pop in and
point out my colossal stupidity
. Even if you can't do it this way for
some reason, maybe it will give you some ideas to try.
Anyway, I created a simple db with one table and one form. I decided to use
an unbound form, on the premise that it may be better not commit any
values to the table until the user clicks a "Save" button.
My table has three fields (FirstNumber, SecondNumber, ThirdNumber) that
are all long integer data type
My form has the following controls;
Three unbound text boxes (txt1, txt2, txt3)
Three hidden check boxes (chkStep1, chkStep2, chkStep3) to help control
the process of moving through the form
Ten "keypad" command buttons number 0 - 9 with names like;
cmd1 (with a caption of 1)
cmd2 (with a caption of 2)
etc.
Three other command buttons (cmdContinue, cmdUndo, cmdSave)
Now for the code. Keep in mind this code is a little rough. It could be
cleaned up a bit, but it does work (at least in my test db). And of course
you will have to change the naming of all the fields/controls. Also, I
put in some code to change the back color of each text box as you
move through the steps, which you may not want.
1) First, at the top of the module (right after Option Compare DB and Option
Explicit) put the following declaration
Dim strInputString As String
2) Code for the forms Open event;
Private Sub Form_Open(Cancel As Integer)
Me.chkStep1 = True
Me.chkStep2 = False
Me.chkStep3 = False
Me.txt1.BackColor = vbYellow
Me.txt2.BackColor = vbWhite
Me.txt3.BackColor = vbWhite
End Sub
3) Code for the "keypad" buttons;
Private Sub cmd1_Click()
strInputString = strInputString & Me.cmd1.Caption
If Me.chkStep1 = True Then
Me.txt1 = strInputString
ElseIf Me.chkStep3 = True Then
Me.txt3 = strInputString
Else
'we are on step 2 so do nothing
End If
End Sub
All other keypad buttons use the same code, with the name changed to
match the associated button.
4) Code for the "Continue" button;
Private Sub cmdContinue_Click()
strInputString = "" 'Reset the string variable to nothing before moving on
If Me.chkStep1 = True Then
Me.chkStep1 = False
Me.chkStep2 = True
Me.txt1.BackColor = vbWhite
Me.txt2.BackColor = vbYellow
ElseIf Me.chkStep2 = True Then
Me.chkStep2 = False
Me.chkStep3 = True
Me.txt2.BackColor = vbWhite
Me.txt3.BackColor = vbYellow
ElseIf Me.chkStep3 = True Then
Me.chkStep3 = False
Me.chkStep1 = True
Me.txt3.BackColor = vbWhite
Me.txt1.BackColor = vbYellow
End If
End Sub
5) Code for the "Undo" button;
Private Sub cmdUndo_Click()
strInputString = "" 'Reset the string variable to nothing
If Me.chkStep1 = True Then
Me.txt1 = Null
ElseIf Me.chkStep2 = True Then
Me.txt2 = Null
ElseIf Me.chkStep3 = True Then
Me.txt3 = Null
End If
End Sub
6) Code for the "Save" button;
Private Sub cmdSave_Click()
Dim con As adodb.Connection
Dim strSQL As String
If Nz(Me.txt1, "") = "" Or Nz(Me.txt2, "") = "" Or Nz(Me.txt3, "") = ""
Then
MsgBox "Please enter a value in each box before saving"
Exit Sub
End If
Set con = CurrentProject.Connection
strSQL = "Insert Into
Table1([FirstNumber],[SecondNumber],[ThirdNumber])" & _
" Values(" & Me.txt1 & "," & Me.txt2 & "," & Me.txt3 & ")"
con.Execute strSQL, dbFailOnError
strInputString = "" 'Reset the input string to nothing after saving
Me.txt1 = Null
Me.txt2 = Null
Me.txt3 = Null
Me.chkStep1 = True
Me.chkStep2 = False
Me.chkStep3 = False
Me.txt1.BackColor = vbYellow
Me.txt2.BackColor = vbWhite
Me.txt3.BackColor = vbWhite
End Sub
Finally, you will need to add your own error handling. Also, since the
second step of your procedure gets the value from a bar code scanner,
I didn't tackle that issue, so you'll have to add your own code for that.
(I'm not that familiar with using a bar code scanner with an Access db)
Plus, the e-mail editor will probably screw up the line wrap in the code
for the "Save" button, so that may need to be fixed.
Other random thoughts - You may want to change some things. For example
you may want to have the "Continue" button disabled when the user moves
to step three, so they can't accidently move back to step 1 before saving
the record, then enable it again in the save procedure. Or you may want
to add a "Clear" button so the user can clear the entire form and start over
if needed.
Anyway, that's my two cents worth. Happy coding
--
_________
Sean Bailey
Nick T said:
Hi, thanks for taking the time to read through - complex & frustrating i know.
My database will be used on touch screen handheld pc's (samsung Q1) which
needs to be extremly user friendly - its designed to minimise user input
errors so i need it as simple as poss (for the end users).
Basically, a user shall enter an 'order number' into a text box (using the
10 command buttons numbered 0-9). Then they shall press an 'accept' button,
which will SetFocus to another text box. They then scan a barcode which will
put a number into another text box (which AfterUpdate, will SetFocus to
another text box). In this last text box, they shall enter the quantity of
the product they have prevousley scanned using the same "number key pad" they
used to input the order number into the first text box.
Where prevousley i have used the following code to input more than one of
the same number into a text box:
Me.TextBoxName = Me.TextBoxName & "1"
However this code in a command button's event is obvousley only going to put
the number into one text box (in this case being one named 'TextBoxName')
I hope this makes my situation a little clearer as any help you could offer
would be greatly appreciated.
many thanks
Nick
Ps. sorry for long winded explanation - you did ask!.
