Text directed to correct field based on values

[Spujr]

Hello,

I am pretty new to VBA and mostly figure things out through trial and error and borrowing google-searched codes. I am running into a few problems with what I want to do, found ways around some of these problems, but now stuck.:banghead:

I have an Access Form with 3 textboxes:
1. Textbox name "Location"
2. Textbox name "Bag_No" (bag number)
3. Textbox name "Text7" (only used to set the focus on).

The goal of this form is to allow users to enter data blindly (barcode scanner) and based on the text value it will be entered into a table correctly, thereby eliminating the human error of scanning the wrong thing at the wrong time in the wrong place. The table consist of 3 columns: Timestamp, Location, and Bag_No. All Location text begin with the letter "C". All bag numbers begin with the letter "B".

Users will begin by scanning (entering) the Location in the Location Textbox. If they accidentally scanned something not a location (doesn't begin with "C*") then error:

Private Sub Location_Change()
If Location.Text Like "C*" Then
Focus = Bag_No
Else
Location.SetFocus
Location.SelStart = 0
Location.SelLength = 9999
MsgBox "Scan location first!"
End If
End Sub

Correctly scanning the location moves the cursor to the Bag_No textbox. This is where I am having problems. There are 3 possibilities of what can happen:
1. User correctly scans a bag (text begins with "B*"). The text in the Location and Bag_No are appended to the table "Tbl_Master_Bulb_Location" and the focus is returned to the Bag_No textbox with text selected and ready for another bag number to be entered.

Private Sub Bag_No_AfterUpdate()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from Tbl_Master_Bulb_Location")
 
If Bag_No.Text Like "B*" Then
rec.AddNew
rec("Location") = Me.Location
rec("Bag_No") = Me.Bag_No
rec("Timestamp") = Now
rec.Update
 Set rec = Nothing
Set db = Nothing

2. User scans a new "Location" (text beginning with "C*"). This is where I am having trouble because the location was scanned while the focus was on the bag number textbox and I need it to go into Location textbox. This is what I have so far:

If Bag_No.Text Like "C*" Then
Me.Bag_No.Value = Me.Location.Value
Me.Text7.SetFocus

This code is not working for me because I don't know how to do the multiple if statements properly.

Note, I have the following code under the OnFocus event for Text7 to move the cursor back to the Bag_No.

Private Sub Text7_GotFocus()
Bag_No.SetFocus
Bag_No.SelStart = 0
Bag_No.SelLength = 9999
End Sub

3. Lastly, the user could accidentally enter something else entirely (not beginning with B or C) and I need a message box to appear letting them know they screwed up. I don't know how to write, "textbox starts with anything but B or C.

At the end of the day I hope the table will look like the following:

ID Location Bag_No Date
1 C1 B1234 12/12/16
2 C1 B1244 12/12/16
3 C2 B5678 12/12/16
4 C67 B9998 12/12/16
....
..
.



Sorry for the long message, any help would be greatly appreciated. I realize this is sloppy coding, so please excuse my ignorance.

Below is the complete code on the afterupdate event for the Bag_No (where I am having problems):

Private Sub Bag_No_AfterUpdate()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from Tbl_Master_Bulb_Location")
 
If Bag_No.Text Like "B*" Then
rec.AddNew
rec("Location") = Me.Location
rec("Bag_No") = Me.Bag_No
rec("Timestamp") = Now
rec.Update
 Set rec = Nothing
Set db = Nothing
 If Bag_No.Text Like "C*" Then
Me.Bag_No.Value = Me.Location.Value
Me.Text7.SetFocus
 
ElseIf Not Bag_No.Text Then
 'I don't know what to put here but looking for logic statement stating if Bag_No text does not begin with B or C, then
 
MsgBox "Scan bag number!"
 End If
End If
End Sub

 

[Minty]

I would force the operator to always do things in the same order, not try and correct the issue if they mis-scan an item.

I would try something like

Private Sub Bag_No_AfterUpdate()
    Dim db              As Database
    Dim rec             As Recordset

    If Not Me.Bag_No Like "B*" Then
        MsgBox "Scan bag number!"
        Me.Bag_No = ""
        Me.Bag_No.SetFocus
        Exit Sub            [COLOR="green"] 'This ends the routine and puts the operator back on the bag txtbox ready to try again[/COLOR]
    End If

    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * from Tbl_Master_Bulb_Location WHERE ID = 0")    '[COLOR="Green"]This will open a small empty recordset rather than every record[/COLOR]

    rec.AddNew
    rec("Location") = Me.Location
    rec("Bag_No") = Me.Bag_No
    rec("Timestamp") = Now
    rec.Update
    Set rec = Nothing
    Set db = Nothing
End Sub

I would do the same for the location code, much simpler logic, if it's not right reset the form and start again.

 

[MarkK]

I would just have one textbox into which to scan values. Then read each value, and determine, after the scan, what to do. If you want, you can display the scanned values in other textboxes, and you can print your own bar codes to issue other commands, so imagine you print a barcode with a code like "S123," so if your user scans that into the single scanner sensitive textbox, that is a command to save the current data and clear the record for the next set of scans.

Then your users' work-flow can be
1) scan the location
2) scan the bag
3) scan the command card
4) goto 1)
... with no other keyboard, mouse, or scanner inputs.

But moving the focus back and forth like that seems like a headache to me.

Also, doesn't the Textbox.Change event fire for every character in the scanned barcode? What I've done before is put a termination character on the barcode, so that using the change event I can read every character, wait for the termination character, and then perform the action immediately without tabs, or enters, or any other keyboard input. So perform the command, then clear the scanner textbox ready for the next scan.

 

[Spujr]

Thanks Minty!

Part of the reasoning I want the cursor to automatically move to the Location textbox if text begins with C is to eliminate the need for the user to walk back to the computer and place the cursor in the Location textbox every time the location changes. In other words, the computer will be 100ft (or so) away from the person entering the data (with a scanner).

The users are very computer-illiterate so anything that can be done to set it up one time and "scan away" would be very helpful.

 

[Spujr]

Hi Mark,

Thanks, good points!

You are right about the Textbox.Change event. I was going to go back to that problem after I figured out the other problems . Fortunately Minty's code can be adapted to Textbox.Update event for Location textbox which I will do.

Let me consider your proposal regarding a single textbox more. Physically-speaking, the location barcode is on the bottom shelf whereas the bags could be on the top shelf so it would be extra work for the user to scan the location every time between each bag. Then there is the increased probability that the user forgets to scan the command card thereby not going back to #1 in the work flow.

But you're right, there is only so much one can do to correct human error. Let me think on this about how the code would work/look like.

Thanks again!

 

[MarkK]

so it would be extra work for the user to scan the location every time between each bag.

Obviously though, you can design this process to perform however you see fit. If the location data should persist through multiple bag scans, you can do that too no prob.

Do you control the bar codes? Can you put a termination character at the end? Alternatively, you can start a timer when a scan starts, wait long enough that you know the scan has completed, then run code that consumes the completed scan data and clears the textbox. Because AfterUpdate doesn't fire until focus leaves the textbox, but if you have one scan-sensitive textbox, you want to leave the focus there AND read the data when the scan is done.

private m_isPaused as boolean

private sub txtScan_Change()
   if not m_isPaused then  [COLOR="Green"]'if this is the first character then . . .[/COLOR]
      m_isPaused = true [COLOR="Green"]'set a flag[/COLOR]
      me.timerinterval = 1500 [COLOR="Green"]'start the timer[/COLOR]
   end if
end sub

private sub form_timer
   m_isPaused = false [COLOR="Green"]'clear flag[/COLOR]
   me.timerinterval = 0 [COLOR="Green"]'stop timer[/COLOR]
   HandleScan
end sub

private sub HandleScan
   select case left(me.txtScan, 1)
      case "C"
[COLOR="Green"]         'set location value[/COLOR]
      case "B"
[COLOR="Green"]         'set bag value[/COLOR]
      case "S"
[COLOR="Green"]         'handle ad hoc command, maybe save record[/COLOR]
   end select
   me.txtScan = null         
end sub

See what's going there? We handle the first Change event, and set a flag and start a timer, and then we let the rest of the change events in the scan happen with no action. When the timer fires--and you may need to tweak how long the timer runs--THEN you handle the data that was scanned into the textbox, and run whatever process you need to based on the scan.

Hope this gives you some ideas. If you can insert a termination character into the bar code though, then handle every change event, check the last char of the scan, and you're done. Or some barcodes have "*" characters at each end by default.

 

[Spujr]

I understand the code and I think I can modify it for my needs, but have a basic problem.

Private Sub HandleScan()
    Select Case Left(txtScan, 1)
      Case "C"
         'set location value
         MsgBox "work!"
         
      Case "B"
         'set bag value
          MsgBox "work!"  
      Case "S"
      MsgBox "test"
         'handle ad hoc command, maybe save record
   End Select
   Me.txtScan = Null

It appears after the timer goes out, it skips straight to "Me.txtScan = Null". It is not firing on anything beginning with C, B, or S.

Something simple I am overlooking...?

EDIT: also to add, yes, I can control the barcode text to end or begin with whatever character is needed.

 

[MarkK]

Well, if you control the barcodes, it's simpler to put an end-of-line character in your barcode, and then check each character in txtScan_Change. Consider . . .

private sub txtScan_Change()
   If Right(me.txtScan, 1) = "<your end of line character>" then HandleScan
end sub

So that's simpler than starting a timer and waiting for the rest of the characters to arrive at the control.

Also, if you call HandleScan from the change handler, AfterUpdate may not have fired yet, in which case you cannot use the txtScan.Value property, because it only updates at AfterUpdate. You may need to use the .Text property of the control, like . . .

    Select Case Left(Me.txtScan[COLOR="Blue"].Text[/COLOR], 1)

Hope this helps,

 

[Spujr]

Thanks again Mark and Minty! I got it to work by borrowing your ideas and code!

Here's the complete code in case someone else runs into a similar situation. It's remarkable how a few stringed sentences can really save us time and energy in our work!

Option Compare Database
Option Explicit
Private Sub Location_GotFocus()
Me.txtScan.SetFocus
End Sub
Private Sub Bag_No_GotFocus()
Me.txtScan.SetFocus
End Sub
 Private Sub txtScan_AfterUpdate()
    If Right(Me.txtScan.Value, 1) = "C" Then HandleScan1
   If Right(Me.txtScan, 1) = "B" Then HandleScan2
   
End Sub
Private Sub HandleScan1()
         txtScan.SetFocus
         Location.Value = txtScan.Text
         Me.txtScan = Null
         
End Sub
Private Sub HandleScan2()
         txtScan.SetFocus
         Bag_No.Value = txtScan.Text
         Me.txtScan = Null
         If Not IsNull(Bag_No.Value) And Not IsNull(Location.Value) Then AppendData
         
End Sub
Private Sub AppendData()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from Tbl_Master_Bulb_Location WHERE ID = 0")
rec.AddNew
rec("Location") = Me.Location
rec("Bag_No") = Me.Bag_No
rec("Timestamp") = Now
rec.Update
Set rec = Nothing
Set db = Nothing
End Sub