Idiotproofing a form

[lizba]

I have two questions on the same form. The form (SaleMainFrm) has a subform (SalesSub). The records of the subform are entered via a Barcode scanner. I am trying to prevent the user from creating errors by entering things in the wrong places etc. So far, the error trapping that I have done is working, but there are still two things that are causing a problem:

Problem 1:
Once the sale is finished and the user has entered the “amount tendered” the focus automatically goes to a command button (“finish”). The user must then click this button (or press Enter) and the system will finish this transaction and open a new one. When a new sale is opened, the system automatically enters the information on the main form and then waits to receive the barcode in order to enter the record into the subrform. What they continually do, is forget to click the finish button, instead they scan the next barcode. This causes the system to hang for what seems like ages, but I’m sure its only a few seconds – then it goes to to the new form, where they need to scan the barcode agsin. Is there any way that I can set an event on the ‘finish’ command button that will:
1. Open a new sales form
2. Trap the barcode that the user has scanned and insert into the correct field on the new sales form.

Problem 2
Once the new sales form is created it is given an auto-number ID. This ID is used as the SaleID. I would like these numbers to run consequtively without any gaps. If the user decides after a new sales form is created, not to use it, the system will delete this ID on close. Obviously the next time they open the form, the system gives them the next number and there is now a gap in the numbers. I would like to reset the number to (1 + the last one used) . Is there a simple way to do this?
Any help would be appreciated.
Lizba

[jimatqsi]

Can you try making the Finish "button" not a button at all but a text box. Add the same "on-click" event code for the text box that you have for the existing button. And if they scan anything into that text box then you will automatically run the on-click event stuff. Part of that on-click event code should check the text box for content. If there is anything in there it automatically plugs it into the correct text box for the barcodes and runs the On-Update event code or whatever code should be run when the barcode is normally done in the correct manner.

For problem 2, there are plenty of examples here of maintaining Next ID numbers. Here's some code, I don't remember if I wrote this or if it came from another poster here. It is a function that returns a unique ID for the requested table.

Option Compare Database
Option Explicit
 
Public Function NextID(strTable, Optional strPrefix, Optional bVerify As Boolean) As String
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim lngNextID As Long
Dim rsTargetTbl As DAO.Recordset    '
Dim strSQL As String
 
' strTable is the table we want to form a new key for
' strPrefix is an optional prefix for the formulated key
' bVerify indicates whether to verify the key is unique or not
 
On Error GoTo Err_NextID
 
Set dbs = CodeDb
NextID = ""
Do Until NextID <> ""
    ' select only the row containing the ID for the selected table
    strSQL = "Select * from tblTableIDs where strTableName=""" & strTable & """"
    Set rs = dbs.OpenRecordset("tblTableIDs", dbOpenDynaset, dbSeeChanges)
        rs.Edit
            lngNextID = rs!NextID + 1
            rs!NextID = lngNextID
        rs.Update
 
        If Nz(strPrefix, "") = "" Then ' was a prefix supplied?
            NextID = Format(lngNextID, "000000") ' you might want a different format
        Else
            NextID = strPrefix & Format(lngNextID, "000000") ' add the prefix to the key
        End Function
    rs.Close
 
    If Nz(bVerify, False) = True Then
        strSQL = "select ID from " & strTable & " where ID=""" & NextID & """"
        Set rsTargetTbl = dbs.OpenRecordset(strSQL, dbReadOnly)
        If Not rstargetbl.EOF Then
            NextID = "" ' can't use this ID because it is already in the table
        End If
        rsTargetTbl.Close
    End If
Loop        ' until nextid is not ""
 
Exit_NextID:
    rs.Close
    rsTargetTbl.Close
    Set rs = Nothing
    Set rsTargetTbl = Nothing
    Set dbs = Nothing
Exit Function
 
Err_NextID:
    NextID = ""
    GoTo Exit_NextID

Jim

[lizba]

Brillian idea about the text box, that will definately work. I'll try out the code as well. Thanks for all you help.
Lizba