Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Posts
    13

    Unanswered: Able to insert mutiple records but I would like to print them as well

    I have an access database with vba code in it to insert multiple records into my underlying sql table. This portion is working fine. However, I would like to print a report (which is a label) for each record created. When the record is created, the field Serial_No is automatically assigned and this is what I'm tyring to use in the where condition to print the report. I keep getting an error 'Object Required' on the DoCmd.Open Report line. How can I do this so it works? Thanks!

    Private Sub Command14_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim cntr As Integer
    Dim ctl As Control

    Set db = CurrentDb
    Set rs = db.OpenRecordset("dbo_psi_serial_tags", dbOpenDynaset, dbSeeChanges)

    If IsNull(Me.fldOrder) Or Me.fldOrder = "" Then
    MsgBox ("Enter an Order Number")
    GoTo TheEnd
    ElseIf Me.fldOrder > 0 Then

    With rs
    For cntr = 1 To Me.Qty
    .AddNew
    .Fields("Order_No").Value = Me.fldOrder
    .Fields("Model").Value = Me.fldModel
    .Fields("Capacity").Value = Me.fldCap
    .Fields("Volts").Value = Me.fldVolt
    .Fields("Phase").Value = Me.fldPhase
    .Fields("Hz").Value = Me.fldHz
    .Update
    DoCmd.OpenReport "rpt_Serial_Tag", acViewPreview, , "Serial_No = " & dbo_psi_serial_tags.Serial_No
    DoCmd.PrintOut
    DoCmd.Close acReport, "rpt_Serial_Tag"
    Next cntr

    End With
    End If

    TheEnd:

    rs.Close
    db.Close

    'For Each ctl In Me.Controls
    ' Select Case ctl.ControlType
    ' Case acTextBox
    ' ctl.Value = ""
    ' End Select
    'Next

    Exit Sub
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well what you could do is add an additional column to the table that identifies if a label needs printing, then pick up that value at a later date. when you've seen the report labels then run a query which clears that column
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Alternatively, declare a variable and populate it with these 2 new lines immediately after .Update:

    .Bookmark = .LastModified
    VariableName= !Serial_No

    and then

    DoCmd.OpenReport "rpt_Serial_Tag", acViewPreview, , "Serial_No = " & VariableName
    Paul

  4. #4
    Join Date
    Jan 2009
    Posts
    13
    Thanks for all your help! Your replies gave me an idea that will work better for what I'm doing.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Might be helpful to other users to see your method. By the way, if you drop the acViewPreview from your OpenReport, it will print right out and you don't need the next two lines.
    Paul

  6. #6
    Join Date
    Jan 2009
    Posts
    13
    Because of how this application is going to be used, I don't need to keep any history. I'm using the sql table to give me auto number for our serial numbers. Because multiple users could be logged into the database at once, I only want their labels to print out. Therefore, I created a module in access to grab the user name. Then I'm using that user name to tag the records they create and only print those labels. When everything has printed successfully, their records are deleted. Code below:

    Option Compare Database

    Private Sub Command14_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim cntr As Integer
    Dim ctl As Control
    Dim AResponse As Integer
    Dim BResponse As Integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("dbo_psi_serial_tags", dbOpenDynaset, dbSeeChanges)

    If IsNull(Me.fldModel) Or Me.fldModel = "" Then
    MsgBox ("Enter a Model")
    GoTo TheEnd
    Else
    If IsNull(Me.Qty) Then
    MsgBox ("Enter a Qty")
    GoTo TheEnd
    Else

    With rs
    For cntr = 1 To Me.Qty
    .AddNew
    .Fields("User_Name").Value = Me.UName
    .Fields("Model").Value = Me.fldModel
    .Fields("Capacity").Value = Me.fldCap
    .Fields("Volts").Value = Me.fldVolt
    .Fields("Phase").Value = Me.fldPhase
    .Fields("Hz").Value = Me.fldHz
    .Update
    Next cntr

    End With

    DoCmd.OpenReport "rpt_Serial_Tag", acViewPreview, , "User_Name = " & "'" & Me.UName & "'"
    DoCmd.PrintOut
    DoCmd.OpenReport "rpt_List_Serial_Numbers", , , "User_Name = " & "'" & Me.UName & "'"

    LabelPrint:
    AResponse = MsgBox("Did labels print ok?", vbYesNo)
    If AResponse = vbYes Then
    GoTo RptUpdate
    Else
    BResponse = MsgBox("Reprint Labels?", vbYesNo)
    If BResponse = vbYes Then
    DoCmd.PrintOut
    GoTo LabelPrint
    Else: GoTo RptUpdate
    End If
    RptUpdate:
    DoCmd.Close acReport, "rpt_Serial_Tag"
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE From dbo_psi_serial_tags where dbo_psi_serial_tags.user_name = '" & Me.UName & "'"
    DoCmd.SetWarnings True
    End If

    End If
    End If

    TheEnd:

    rs.Close
    db.Close

    Me.fldModel.Value = ""
    Me.fldCap.Value = ""
    Me.fldVolt.Value = ""
    Me.fldPhase.Value = ""
    Me.fldHz.Value = ""
    Me.Qty.Value = ""

    Exit Sub
    End Sub

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    autonumber for serial numbers... bad idea, a seriously bad idea (unless your organisation doesn't care if there are gaps in the sequence). if the serial number is just a number then fine, but if the numbers have to be contiguous forget it... use your own function to get the next available number

    why create a module to capture the username, when its available already (as the network logon (see "dev ashish API" at your favourite tax dodging search engine)

    also could I suggest:-
    when posting code please top and tail it with [ c o d e ] & [ / c o d e ] (remove the spaces so [ / c o d e ] becomes [/code]

    alos you will find it easier to indent code
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2009
    Posts
    13
    They don't care if there are gaps. Thanks for caring so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •