Microsoft Access, 1 or 2 employee accounts are fine (IDnumber,LastName,FirstName) however 3+ accounts sends garbage to textboxes.

Question

Hello, good morning.

I am trying to construct a database to use on the shop floor at my job location. I have successfully tested the database/interface and it works great with 2 employee numbers. However, once I upload more employee numbers the textboxes suddenly start showing garbage. I do pictures of the code and form/tables I am using. I will upload them once my account is verified.

The code on my 'LookUp' button is below:

Private Sub Look_Up_Click()


If IsNull(Me.cbobMachine + Me.cbobPart + Me.cbobOperation) Then
   MsgBox "You MUST enter the Part," & vbCrLf & _
   "Operation, AND Machine Numbers.", _
   vbCritical, _
   "Canceling Look Up"
   
            If IsNull(Me.cbobPart) Then
                Me.cbobPart.SetFocus
            ElseIf IsNull(Me.cbobOperation) Then
                Me.cbobOperation.SetFocus
            Else: Me.cbobMachine.SetFocus
            End If
            
   Cancel = True

        If Cancel = True Then
            Exit Sub
         End If
           
End If



If IsNull(Me.txtSerialNumber + Me.txtOperatorNumber) Then
   MsgBox "The Serial AND Operator" & vbCrLf & _
   "Numbers MUST all be entered.", _
   vbCritical, _
   "Canceling Look Up"
   
            If IsNull(Me.txtSerialNumber) Then
                Me.txtSerialNumber.SetFocus
            Else: Me.txtOperatorNumber.SetFocus
            End If
            
   Cancel = True

        If Cancel = True Then
            Exit Sub
        End If

End If

        
x = DCount("*", "[EmployeeNumbers]", "[IDNumber] = '" & Me.txtOperatorNumber & "'")

If x > 0 Then
    Dim strInsert As String
    strInsert = "INSERT INTO [Op/SerialNumber](SerialNumber, OperatorNumber) VALUES ('" & Me.txtSerialNumber & "', '" & Me.txtOperatorNumber & "')"
    Debug.Print strInsert
    CurrentDb.Execute strInsert, dbFailOnError


    Dim rs As Object

        Set rs = Me.Recordset.Clone
        rs.FindFirst "[MachineNumber] = '" & Me![cbobMachine] & "' And [PartNumber] = '" & Me![cbobPart] & "' And [OperationNumber] = '" & Me![cbobOperation] & "'"
                
                
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Else
    MsgBox "The Operator Number MUST" & vbCrLf & _
    "be a valid Employee Number.", _
    vbCritical, _
    "Canceling Look Up"
    Me.txtOperatorNumber.SetFocus
    Cancel = True

If Cancel = True Then
Exit Sub
End If

End If


Me!SetupSheets.ControlSource = "[SetupSheets]"
Me!ToolSheets.ControlSource = "[ToolSheets]"
Me!NCProgramReference.ControlSource = "[N/CProgramReference]"
Me!OperatorInstructions.ControlSource = "[OperatorInstructions]"
Me!PartHandlingInstructions.ControlSource = "[PartHandlingInstructions]"
Me!OperatorNotes.ControlSource = "[OperatorNotes]"
Me!InspectionTools.ControlSource = "[InspectionTools]"
Me!OperationSketches.ControlSource = "[OperationSketches]"
Me!BuyOut.ControlSource = "[BuyOut]"

Me.Look_Up.Enabled = False


End Sub

Answer 1

There's quite a bit that seems wrong with this, but it's difficult to pinpoint anything without knowing more about what you're trying to do, and to see the table structure.

Several things:

You refer to "Cancel = True" at various places, but the event you're using (the Click event) doesn't support Cancel. You may be using that as a Variable, but if so it's not declared where we can see it, so we can't really determine exactly what you're doing with that. For example, in this block:

       If IsNull(Me.cbobPart) Then
                Me.cbobPart.SetFocus
            ElseIf IsNull(Me.cbobOperation) Then
                Me.cbobOperation.SetFocus
            Else: Me.cbobMachine.SetFocus
            End If
            
   Cancel = True

        If Cancel = True Then
            Exit Sub
         End If

You really don't need the Cancel = True line, or the If Cancel etc lines. Just exit the sub:

       If IsNull(Me.cbobPart) Then
                Me.cbobPart.SetFocus
            ElseIf IsNull(Me.cbobOperation) Then
                Me.cbobOperation.SetFocus
            Else
Me.cbobMachine.SetFocus
            End If
            Exit Sub

It seems like you're inserting records into the OP/SerialNumber table if the value entered in txtOperatorNumber is not found. Are you sure you want to do that? This would mean you could have invalid values in that table if a user enters an incorrect Operator Number. In most cases, you'd have a separate form to manage this sort of thing. Not saying it's wrong, just that it's somewhat odd to see new records inserted in this context.

Your last block of statements seem to set the ControlSource of various controls. That's generally not done in code, unless you're changing the ControlSource for some reason. Once you set the Controlsource of a control, you typically don't need to reset that value. 

---

-- Scott McDaniel, Microsoft Access MVP

Answer 2

The canceling statements I was using because I am new to MS Access and I was canceling the use of the 'LookUp' Button once used once so an employee can not keep on pressing it over and over. However, I did take out the cancel lines now. And the code where I insert the op/serial numbers, it only does that IF there is a value in IDNumber (field under EmployeeNumbers) matches the typed in employee number (txtOperatorNumber). Or so I thought that was what the code was doing. It has been successfully saving the number under Op/SerialNumber table.

Answer 3

I actually just figured out the problem. The values here were set to hyperlinks in the type field under the MachineSpecific table. I switched them to "text" and now I can use all 118 employee numbers. Thank you helping though!

Me!SetupSheets.ControlSource = "[SetupSheets]"
Me!ToolSheets.ControlSource = "[ToolSheets]"
Me!NCProgramReference.ControlSource = "[N/CProgramReference]"
Me!OperatorInstructions.ControlSource = "[OperatorInstructions]"
Me!PartHandlingInstructions.ControlSource = "[PartHandlingInstructions]"
Me!OperatorNotes.ControlSource = "[OperatorNotes]"
Me!InspectionTools.ControlSource = "[InspectionTools]"
Me!OperationSketches.ControlSource = "[OperationSketches]"
Me!BuyOut.ControlSource = "[BuyOut]"

Answer 4

Hi TheDMinor,

I'm glad to hear that you have solved the issue. I suggest you mark your solution as answer to close this thread. Thanks for understanding.

Best Regards,
Terry

Answer 5

Again, you really don't need to set the ControlSource for your controls. Unless you're changing the datasource of the form such that the names of the Columns/Fields are different, you really shouldn't be doing this.

---

-- Scott McDaniel, Microsoft Access MVP

Answer 6

I do have a clear button that clears all textboxes. That is why I set the controlsources to a field in a table after the lookup button is pressed. So the machinist can select the part/operation/machine numbers, then he/she can press the 'LookUp' button so the documents will be generated. Once done using, the 'Clear' button is used to reset the form itself by closing out and reopening the form. Here is where I set

Me!SetupSheets.ControlSource = " "
Me!ToolSheets.ControlSource = " "
Me!NCProgramReference.ControlSource = " "
Me!OperatorInstructions.ControlSource = " "
Me!PartHandlingInstructions.ControlSource = " "
Me!OperatorNotes.ControlSource = " "
Me!InspectionTools.ControlSource = " "
Me!OperationSketches.ControlSource = " "
Me!BuyOut.ControlSource = " "

Answer 7

To each his own, but that's about as arcane a method of "clearing" a form as I've seen. If you want to clear a form, set the filter to "1=0" ... 

That said, I'd strongly encourage you to review some of the templates provided by Microsoft (and others) that show proper design and usage. A little time now will reap enormous benefits down the road.

---

-- Scott McDaniel, Microsoft Access MVP

Answer 8

Hi TheDMinor,

Has your original issue been resolved? If it has, I would suggest you mark the helpful reply or provide your solution and then mark it as answer to close this thread. 
If not, please feel free to let us know your current issue.

Best Regards,

Terry