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

  • 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


    • Edited by TheDMinor Monday, July 31, 2017 2:35 PM
    Monday, July 31, 2017 2:30 PM

All replies

  • 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

    Monday, July 31, 2017 3:03 PM
  • 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.
    Monday, July 31, 2017 3:47 PM
  • 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]"


    • Edited by TheDMinor Monday, July 31, 2017 4:24 PM
    • Proposed as answer by Terry Xu - MSFT Thursday, August 3, 2017 8:39 AM
    Monday, July 31, 2017 4:23 PM
  • 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


    Tuesday, August 1, 2017 8:32 AM
  • 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

    Tuesday, August 1, 2017 11:28 AM
  • 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 = " "

    Tuesday, August 1, 2017 12:46 PM
  • 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

    Tuesday, August 1, 2017 8:29 PM
  • 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

    Thursday, August 3, 2017 8:40 AM