Refresh Button creating a new record?

[mejia.j88]

hello



it is hard to word what is happening, but here is an attempt:

I have a load in which operators save the work they have done. Upon loading a form, a function called NextLotNumber is called and populates an invisible field with a new lot number, of form LAA####. if i open the form, fill in the data, it works great. There are times when i look in the table and see lot numbers saved in correct field but other fields will be missing data, or have incomplete fields.

i think this has something to do with the way the form is being refreshed; meaning that when i refresh the form or close it, the code i have is saving these false/in-concurrent/ incomplete records.

is it possible for someone to give the code a quick look?
it isnt TOO long of code.

thank you so much.

Option Compare Database

Private Sub BtnCloseForm_Click()
'Close button
    Me.Undo
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub form_load()
    'Leadbond Lot Number
    Me.txtleadbondlot = NextLotNumber()
End Sub

Private Sub btnsaverecord_click()
'To save a record
    If checkbeforesaving = True Then
        Call totalbonded
        DoCmd.RunCommand acCmdSaveRecord
        Call PrintLabels
        DoCmd.Close acForm, Me.Name
    End If
End Sub

Private Sub cboworkorder_afterupdate()
'This auto-populates the following fields:
    Me.TxtPartNumber = Me.CboWorkorder.Column(1)
    Me.TxtDiffusionLot = Me.CboWorkorder.Column(3)
    Me.TxtEvaporationLot = Me.CboWorkorder.Column(2)
    Me.TxtCutSize = Me.CboWorkorder.Column(5)
    Me.TxtStackSize = Me.CboWorkorder.Column(4)
    Me.lstdicinglots = Null
    Me.lstdicinglots.Requery
End Sub

Private Function checkbeforesaving()
'this code checks for nullity of specific fields
    If IsNull(Me.TxtDicingLots) Then
        MsgBox "Please add dicing lots used."
        Me.TxtDicingLots.SetFocus
        checkbeforesaving = False
    ElseIf IsNull(Me.CboOperator) Then
        MsgBox "Please enter operator number."
        Me.CboOperator.SetFocus
        checkbeforesaving = False
    ElseIf IsNull(Me.CboPreformLot) Then
        MsgBox "Please Enter Preform Lot information."
        Me.CboPreformLot.SetFocus
        checkbeforesaving = False
    ElseIf IsNull(Me.CboPinLot) Then
        MsgBox "Please enter Pin Lot number."
        Me.CboPinLot.SetFocus
        checkbeforesaving = False
    ElseIf IsNull(Me.CboLeadLot) Then
        MsgBox "please enter Lead Lot number."
        Me.CboLeadLot.SetFocus
        checkbeforesaving = False
    ElseIf IsNull(Me.txtBoats) Then
        MsgBox "Please enter number of boats built."
        Me.txtBoats.SetFocus
        checkbeforesaving = False
    Else
        checkbeforesaving = True
    End If
End Function

'code provided by http://support.microsoft.com/kb/827423
'with alterations
Private Sub Form_frm_Leadbond_Yield()
    Dim oItem As Variant
    Dim bFound As Boolean
    Dim sTemp As String
    Dim sValue As String
    Dim sChar As String
    Dim iCount As Integer
    Dim iListItemsCount As Integer
    
    sTemp = Nz(Me!lstdicinglots.Value, " ")
    iListItemsCount = 0
    bFound = False
    iCount = 0

    Call clearListBox
        
    For iCount = 1 To Len(sTemp) + 1
    sChar = Mid(sTemp, iCount, 1)
        If StrComp(sChar, ",") = 0 Or iCount = Len(sTemp) + 1 Then
            bFound = False
            Do
                If StrComp(Trim(Me!lstdicinglots.ItemData(iListItemsCount)), Trim(sValue)) = 0 Then
                    Me!lstdicinglots.Selected(iListItemsCount) = True
                    bFound = True
                End If
                iListItemsCount = iListItemsCount + 1
            Loop Until bFound = True Or iListItemsCount = Me!lstdicinglots.ListCount
            sValue = ""
        Else
            sValue = sValue & sChar
        End If
    Next iCount
End Sub
    
Private Sub clearListBox()
    Dim iCount As Integer
    For iCount = 0 To Me!lstdicinglots.ListCount
        Me!lstdicinglots.Selected(iCount) = False
    Next iCount
End Sub

Private Sub btndicinglots_Click()
    Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer
    
    iCount = 0
            
    If Me!lstdicinglots.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!lstdicinglots.ItemsSelected
            If iCount = 0 Then
                sTemp = sTemp & Me!lstdicinglots.ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp = sTemp & "," & " " & Me!lstdicinglots.ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If
    
    Me!TxtDicingLots.Value = sTemp
End Sub
    
Private Sub clrList_Click()
    Call clearListBox
    Me!TxtDicingLots.Value = Null
End Sub

Private Function PrintLabels()
' to print label information

      Dim Show_Box As Boolean
      Dim Response As Variant
      Dim i As Integer
      
      ' Set the Show_Dialog variable to True.
      Show_Box = True

      ' Begin While loop.
      While Show_Box = True

         ' Show the input box.
         Response = InputBox("Enter the number of labels to print or press Cancel to skip printing.", "Label Printing", 1)

         ' See if Cancel was pressed.
         If Response = "" Then
            ' If Cancel was pressed,
            ' break out of the loop.
            Show_Box = False
         Else
            ' Test Entry to find out if it is numeric.
            If IsNumeric(Response) = True Then
                For i = 1 To Response
                    DoCmd.OpenReport "rpt_leadbond_yield_label", acViewNormal, , "[ID]=" & Me.ID
                Next i
               Show_Box = False
            Else
               ' If the entry was wrong, show an error message.
               MsgBox "Please Enter Numbers Only"
            End If
         End If
      ' End the While loop.
      Wend

End Function

'***THIS CODE CREATES THE NEXT LEADBOND LOT NUMBER
Private Function NextLotNumber() As String

Dim strPrevious_Leadbond_lot As String
Dim strNext_leadbond_Lot As String
Dim strCharacters As String
Dim strNumbers As String
Dim intNumbers As Integer
Dim strLetter1 As String
Dim strLetter2 As String
Dim numLetter1 As Integer
Dim numLetter2 As Integer
Dim blnRecordExist As Boolean

Const LetterD = "L"
Const zeros3 = "000"
Const zeros2 = "00"
Const zeros1 = "0"

strPrevious_Leadbond_lot = DMax("[leadbond_Lot]", "tbl_leadbond_Yield")
strCharacters = Mid(strPrevious_Leadbond_lot, 1, 3)
strNumbers = Mid(strPrevious_Leadbond_lot, 4, 7)
intNumbers = Val(strNumbers)
strNext_leadbond_Lot = strCharacters

intNumbers = intNumbers + 1

    If intNumbers >= 9999 Then
        intNumbers = 1
        strLetter1 = Mid(strCharacters, 3)
        numLetter1 = Asc(strLetter1)
        numLetter1 = numLetter1 + 1
        If numLetter1 > 90 Then
            strLetter1 = "A"
            strLetter2 = Mid(strCharacters, 2, 1)
            numLetter2 = Asc(strLetter2)
            numLetter2 = numLetter2 + 1
            strLetter2 = Chr(numLetter2)
            strNext_leadbond_Lot = LetterG & strLetter2 & strLetter1
        Else
            strLetter1 = Chr(numLetter1)
            strLetter2 = Mid(strCharacters, 2, 1)
            strNext_leadbond_Lot = LetterG & strLetter2 & strLetter1
        End If
    
    End If

    If intNumbers < 10 Then
        strNext_leadbond_Lot = strNext_leadbond_Lot & zeros3 & CStr(intNumbers)
    ElseIf intNumbers < 100 Then
        strNext_leadbond_Lot = strNext_leadbond_Lot & zeros2 & CStr(intNumbers)
    ElseIf intNumbers < 1000 Then
        strNext_leadbond_Lot = strNext_leadbond_Lot & zeros1 & CStr(intNumbers)
    Else
        strNext_leadbond_Lot = strNext_leadbond_Lot & CStr(intNumbers)
    End If

    NextLotNumber = strNext_leadbond_Lot

End Function

'***THIS CODE CALCULATES THE TOTAL OF DIODES LEADBONDED
Private Function totalbonded()
    If IsNull(Me.txtPartials) = True Then
        Me.txtPartials = 0
    End If
    If Me.TxtCutSize = 0.04 Then
        Me.TxtTotalLeadbonded = Val(Me.txtBoats) * Val(1056) + Val(Nz(Me.txtPartials, 0))
    ElseIf Me.TxtCutSize = 0.063 Then
        Me.TxtTotalLeadbonded = Val(Me.txtBoats) * Val(527) + Val(Nz(Me.txtPartials, 0))
    ElseIf Me.TxtCutSize = 0.125 Then
        Me.TxtTotalLeadbonded = Val(Me.txtBoats) * Val(405) + Val(Nz(Me.txtPartials, 0))
    ElseIf Me.TxtCutSize = 0.25 Then
        Me.TxtTotalLeadbonded = Val(Me.txtBoats) * Val(302) + Val(Nz(Me.txtPartials, 0))
    End If
End Function

Private Sub cbopreformlot_afterupdate()
    If Left(Me.TxtCutSize, 4) <> Left(Me.Preform_Lot, 4) Then
    MsgBox ("Preform size does not match cut size. Please select correct preform lot.")
    Me.CboPreformLot.SetFocus
    End If
End Sub

Private Sub cbopinlot_afterupdate()
    If Me.TxtCutSize = 0.25 Then
    Exit Sub
    ElseIf Left(Me.TxtCutSize, 4) <> Left(Me.Pin_Lot, 4) Then
        MsgBox ("Pin size does not match cut size. Please select correct pin lot.")
        Me.CboPinLot.SetFocus
    End If
End Sub

Private Sub cboleadlot_afterupdate()
    If Left(Me.TxtCutSize, 4) <> Left(Me.Lead_Lot, 4) Then
        MsgBox ("Lead size does not match cut size. Please select correct lead lot.")
        Me.CboLeadLot.SetFocus
    End If
End Sub

[June7]

That is a lot of code to read through and nothing pops out at me as an issue. Where is the Refresh happening? Have you step debugged? Want to provide project for analysis? Follow instructions at bottom of my post.

[mejia.j88]

june7, can i post using box.com? not sure if you use it or not.

[June7]

Yes, I like box.com

Post link to the file.

[mejia.j88]

https://www.box.com/s/f5d0cde30815e77033ae please let me know if this upload doesn't work.

thanks for looking into this.

[June7]

I have the file. Now what forms are involved in this issue? What steps should I follow to replicate the issue? I am on the switchboard form. Where do I go from there?

[mejia.j88]

Hi June7
I would open the table that says tbl_leadbond_yield to see what the last LAA#### is; then hit the new record button on the switch board, it should open form: frm_leadbond_yield.

i noticed that the refresh button save the record without having pushed the save record button on the form, although it only does it once. in other words, if you hit refresh it will only save the record once, then you can keep hitting refresh and nothing will happen.

thanks,
Jorge

[June7]

Don't use the Refresh or else change the form.

Here is what I am thinking happens: The form is set for DataEntry Yes, which means it can open only to a New Record. The Refresh commits the record to table, refreshes the form RecordSource which because of DataEntry Yes must be only a 'New' record but this record does not have LAA#### generated because the form is already open so no field is yet populated and clicking Refresh again does nothing because there is no record to commit. (Is that a mouthful!) Make the ID and Leadbond_lot fields visible and can see this behavior. Note that using the Navigation bar to move to New record leaves the first available to move back to.

I seldom set forms as DataEntry Yes. I use VBA code to control the state form opens to. I also disable the ribbon, shortcut menus, Navigation pane, X close button, and sometimes navigation bar so users can't get messed up like you are experiencing. Command buttons control user interaction.

[mejia.j88]

Hi June7,

again, thanks for taking a look. i wasnt sure what was happening and it seemed strange given that i have created a few DBs and none of them have shown this behavior.
what do you mean you use VBA code to control the state form opens to? Does this relate to having DataEntry set to Yes?

I use a copy of the original form to display previous records. i set the control source of the "browse records" form to a query that pulls from the table. i do this because i want the operator to open a form that only takes in new records.
do you have suggestions on how to handle this problem better?

[June7]

I use the same forms for both data entry and viewing existing records. I don't set DataEntry Yes. Examples of code to open form:

DoCmd.OpenForm "AddEditSpecifications", , , , acFormAdd, acDialog, .lbxProjects.Column(0) & ";" & Nz(.cbxItem, "") & ";" & .Name
DoCmd.OpenForm "AddEditProject", , , , acFormAdd, acDialog, NewData

The following 3 lines all open the same forms from various procedures to either view or edit data.
DoCmd.OpenForm strData, , , "Submit.LabNum='" & Me.tbxLABNUM & "'", , , "EditTransfer"
DoCmd.OpenForm strData, , , "Submit.LabNum='" & Me.tbxLABNUM & "'", acFormReadOnly, , "ViewData"
DoCmd.OpenForm strRptDataForm, acNormal, , "Submit.LabNum='" & strLabNum & "'", , acDialog, "EditReport"

Since the ribbon and navigation bar are disabled and the filter toggle is not available, users can't 'unfilter' the form's RecordSource to view all records.