Last Part of code not working

[mejia.j88]

Hi guys.

i Have written code for a program with the following goal:

upon clicking the 'save button' it should check for null fields that are not supposed to be null , alert the user via message box that field is null, then set the focus on that field. after field is filled in, if the user clicks save again, it should then save the record, and call a function used to print the information just saved.

problem is: when i hit save and all is filled it, the record is being saved twice, and my label is not printing the iformation just saved.
it "prints" a blank label.



i cannot figure out what is wrong. code below:

Private Sub btnsaverecord_click()
Dim oktoclose As Boolean
Dim strid As String
Dim rs As DAO.Recordset


Set rs = CurrentDb.OpenRecordset("Tbl_leadbond_yield", dbOpenDynaset)


strid = ""


oktoexit = False
oktoexit = checkbeforesaving()


If oktoexit = True Then
    Call updatevalues
    With rs
        .AddNew
        ![Dicing_lots_used] = Me.TxtDicingLots
        ![Date_Time] = Now()
        ![operator] = Me.CboOperator
        ![Work_Order] = Me.CboWorkorder
        ![total_leadbonded] = Me.TxtTotalLeadbonded
        ![Boats] = Me.txtBoats040
        ![Preform_Lot] = Me.CboPreformLot040
        ![Pin_Lot] = Me.CboPinLot040
        ![Lead_Lot] = Me.CboLeadLot040
        .Update
    End With
    rs.Close
    Call PrintLabels
    DoCmd.Close acForm, Me.Name
End If
End Sub

update values only does some math. i dont think that is the problem.

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.CboPreformLot040) Then
MsgBox "Please Enter Preform Lot information."
Me.CboPreformLot040.SetFocus
checkbeforesaving = False
ElseIf IsNull(Me.CboPinLot040) Then
MsgBox "Please enter Pin Lot number."
Me.CboPinLot040.SetFocus
checkbeforesaving = False
ElseIf IsNull(Me.CboLeadLot040) Then
MsgBox "please enter Lead Lot number."
Me.CboLeadLot040.SetFocus
checkbeforesaving = False
ElseIf IsNull(Me.txtBoats040) Then
MsgBox "Please enter number of boats built."
Me.txtBoats040.SetFocus
checkbeforesaving = False
else checkbeforesaving=true
endif
end function

Private Function PrintLabels()      
      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", acViewPreview, , "[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

heeeellllppppppppppppppppppppppppppppppp

[pbaldy]

Is the form bound to the same table the code adds a record to? If so, that's the likely cause of your duplicate records, and would likely explain the labels problem too. You want a bound form or code, not both.

[mejia.j88]

thanks pbaldy,
i saved everything using code, but when i used
![ID]=me.txtID

i get an error in printlabels, where it calls out my ID to print that specific record

[pbaldy]

Is ID an autonumber field? If so, you'd get the one added in code by adding this after the .Update line:

.Bookmark = .LastModified
lngARKey = !ARKey

Replacing lngARKey with a variable of your own creation, and ARKey with the name of your field. Then use the variable in your printing code.

[mejia.j88]

Ok, it is saving only one record now, that is good.
it is not giving an error anymore, another good thing.
it is still printing blank records.

[mejia.j88]

ID is an autonumber field, it is also my primary key
what type of variable would lngARKey have to be? integer?

[pbaldy]

It should be Long to match up to an autonumber field.

[mejia.j88]

im sorry, that is not working for me, this is where i am at.
my form is now unbound completely, and saving correctly.
my ID field is also unbound on my form (and invisible) but it is saving nicely.
i have reverted the code for

Private Sub btnsaverecord_click() back to how it is shown in the original post.

the error i get comes up with this line:

DoCmd.OpenReport "rpt_leadbond_yield_label", acViewPreview, , "[ID]=" & Me.ID

on my code to print labels.

I'm assuming it does not know which ID to get.

[mejia.j88]

the error given is: Run-time eorror '3075'
syntax error (missing operator) in query expression '[ID]='.

[pbaldy]

Did you see this?

use the variable in your printing code.

[ssanfu]

PMFJI, but I saw a couple of things in your code...
In "Sub btnsaverecord_click()", you have a variable named "oktoclose" (unused), but you use the undeclared variable "oktoexit" to terminate the loop.

Do you have these two lines at the top of every code page?
Option Compare Database
Option Explicit


I modified you code a little. If you grab the new record id and pass it to the print routine, it makes it a little easier.

Private Sub btnsaverecord_click()
'   Dim oktoclose As Boolean
   Dim oktoexit As Boolean
   Dim strid As String
   Dim rs As DAO.Recordset

   Dim NewRecID As Long

   Set rs = CurrentDb.OpenRecordset("Tbl_leadbond_yield", dbOpenDynaset)

   strid = ""

   oktoexit = False
   oktoexit = checkbeforesaving()

   If oktoexit = True Then
      Call updatevalues
      With rs
         .AddNew
         ![Dicing_lots_used] = Me.TxtDicingLots
         ![Date_Time] = Now()
         ![Operator] = Me.CboOperator
         ![Work_Order] = Me.CboWorkorder
         ![total_leadbonded] = Me.TxtTotalLeadbonded
         ![Boats] = Me.txtBoats040
         ![Preform_Lot] = Me.CboPreformLot040
         ![Pin_Lot] = Me.CboPinLot040
         ![Lead_Lot] = Me.CboLeadLot040
         NewRecID = rs![ID]
         .Update
      End With
      rs.Close
      Call PrintLabels(NewRecID)
      DoCmd.Close acForm, Me.Name
   End If
End Sub

Private Function PrintLabels(pRecID As Long)
   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", acViewPreview, , "[ID]=" & pRecID
            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

[pbaldy]

Fair warning that your method of getting NewRecID doesn't work with linked SQL Server tables. That's why I use the method I posted; it works either way.

[ssanfu]

Fair warning that your method of getting NewRecID doesn't work with linked SQL Server tables. That's why I use the method I posted; it works either way.

True... my bad for assuming an Access table. The idea was to pass the ID to the print function. Your method or using the "@Identity" property would still work.

[mejia.j88]

hello guys,
i actually tried both methods and they worked.
Ssanfu, i hadn't realized i used the wrong variable, as you can see, they are similar in thought, unfortunately, VBA cannot tell what I am thinking. i used Pbaldy for linked SQL server tables.
thanks so much for both of your help!
-j

[ssanfu]

Great. I didn't realize you were using SQL server tables.

There is a setting in the VBA IDE - TOOLS / OPTIONS / Editor tab / "Require variable declaration" (check box) that will put "Option Explicit" at the top of every *new* code page that will (should) catch when you use undeclared variables.