Results 1 to 3 of 3

Thread: Batch Insert

  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: Batch Insert

    Hi all,
    I am not a access vba programmer, the last time I touched it was almost 8-10 years ago.

    I have been stuck with a task to insert results into a table.

    I need your help as I do not understand.

    The instructions are:
    We need JobNo to be incremented only after each job is sent and sequence to be reset at start of job e.g.

    START
    1.1
    1.2
    1.3
    NEW JOB
    2.1
    2.2
    2.3

    This allows Jobs to be managed effectively by the Print Spooler program i.e. start, stop, re-print delete. cancel etc.


    First up.

    If PrintSpooler Mode
    YOUR CODE
    Else
    Do what went before
    EndIf

    You should be writing code to:

    Determine next JobNo + 1
    Open Forward Only Snapshot RecordSet (lets call it 'Records') ON
    SELECT 2 AS Expr1, 2 AS Expr2, [Tbl_Workstation Settings].Directory, dbo_Tbl_Visitors.Visitor_ID, Now() AS Expr3
    FROM dbo_Tbl_Visitors LEFT JOIN [Tbl_Workstation Settings] ON dbo_Tbl_Visitors.Show_ID = [Tbl_Workstation Settings].[Show ID]
    WHERE dbo_Tbl_Visitors.Visitor_ID = strCriteria;

    Open Dynamic RecordSet on PrintSpooler table
    Set SequenceNo = 1
    Loop on Records till EOF
    Insert record in PrintSpooler RecordSet
    Increment SequenceNo
    EndLoop
    Update/Commit
    Close RecordSet
    this is what i have done:

    Sub Go_Print_Badges(intPrintType, strCriteria, str_Mode, str_Size)
    Dim nJobNo As Integer
    Dim nSequenceNo As Integer
    Dim rstSpool As Recordset

    If bln_Use_Print_Spooler Then
    nJobNo = nJobNo + 1

    CurrentDb.OpenRecordset "SELECT nJobNo, nSequenceNo, [Tbl_Workstation Settings].Directory, dbo_Tbl_Visitors.Visitor_ID, Now() " & _
    "FROM dbo_Tbl_Visitors LEFT JOIN [Tbl_Workstation Settings] ON dbo_Tbl_Visitors.Show_ID = [Tbl_Workstation Settings].[Show ID] WHERE " & strCriteria & ";", , dbAppendOnly

    Set rstSpool = CurrentDb.OpenRecordset("SELECT JobNo, SequenceNo, [Database], RecordNumber, SubmittedTimestamp FROM RegPrintQueue, dbo_Tbl_Visitors LEFT JOIN [Tbl_Workstation Settings] ON dbo_Tbl_Visitors.Show_ID = [Tbl_Workstation Settings].[Show ID] WHERE " & strCriteria & ";")

    nSequenceNo = 1

    Do Until rstSpool.EOF
    rstSpool.AddNew

    CurrentDb.Execute "INSERT INTO RegPrintQueue ( JobNo, SequenceNo, [Database], RecordNumber, SubmittedTimestamp )" & _
    "SELECT nJobNo, nSequenceNo, [Tbl_Workstation Settings].Directory, dbo_Tbl_Visitors.Visitor_ID, Now() " & _
    "FROM dbo_Tbl_Visitors LEFT JOIN [Tbl_Workstation Settings] ON dbo_Tbl_Visitors.Show_ID = [Tbl_Workstation Settings].[Show ID] WHERE " & strCriteria & ";"

    rstSpool.Update

    nSequenceNo = nSequenceNo + 1
    rstSpool.MoveNext
    Loop
    rstSpool.Close

    Else
    Select Case str_Mode
    Case "BATCH"
    DoCmd.OpenReport "Batch Print Badge " & str_Size, intPrintType, , strCriteria
    Case "SINGLE"
    DoCmd.OpenReport "Badge " & str_Size, intPrintType, , strCriteria
    End Select
    End If
    End Sub
    Last edited by catalepticstate; 06-14-11 at 08:56.

  2. #2
    Join Date
    Jun 2011
    Posts
    3

    tried this

    I have been trying to make this work and I cannot.

    The closest I have got is:

    Sub Go_Print_Badges(intPrintType, strCriteria, str_Mode, str_Size)
    Dim nJobNo As Integer
    Dim nSequenceNo As Integer
    Dim rstSpool As Recordset
    Dim Records As Recordset
    Dim nRecordNumber As Integer


    If bln_Use_Print_Spooler Then
    nJobNo = nJobNo + 1

    Set Records = CurrentDb.OpenRecordset("SELECT [Visitor_ID] FROM dbo_Tbl_Visitors LEFT JOIN [Tbl_Workstation Settings] ON dbo_Tbl_Visitors.Show_ID = [Tbl_Workstation Settings].[Show ID] WHERE " & strCriteria & ";")
    nRecordNumber = " & [Visitor_ID] & "
    ' Open Dynamic Recordset for Spooler.mdb
    Set rstSpool = CurrentDb.OpenRecordset("SELECT JobNo, SequenceNo, [Database], RecordNumber, SubmittedTimestamp FROM RegPrintQueue")

    nSequenceNo = 1
    Do Until rstSpool.EOF
    rstSpool.AddNew
    rstSpool.JobNo = nJobNo
    rstSpool.SequenceNo = nSequenceNo
    rstSpool.Database = "S:"
    rstSpool.RecordNumber = " & nRecordNumber & "
    rstSpool.SubmittedTimestamp = Now()

    ' CurrentDb.Execute "INSERT INTO RegPrintQueue ( JobNo, SequenceNo, [Database], RecordNumber, SubmittedTimestamp )" & _
    '"SELECT nJobNo, nSequenceNo, [Tbl_Workstation Settings].Directory, dbo_Tbl_Visitors.Visitor_ID, Now() " & _
    '"FROM dbo_Tbl_Visitors LEFT JOIN [Tbl_Workstation Settings] ON dbo_Tbl_Visitors.Show_ID = [Tbl_Workstation Settings].[Show ID] WHERE " & strCriteria & ";"

    rstSpool.Update

    nSequenceNo = nSequenceNo + 1
    rstSpool.MoveNext
    Loop
    rstSpool.Close

    Else
    Select Case str_Mode
    Case "BATCH"
    DoCmd.OpenReport "Batch Print Badge " & str_Size, intPrintType, , strCriteria
    Case "SINGLE"
    DoCmd.OpenReport "Badge " & str_Size, intPrintType, , strCriteria
    End Select
    End If
    End Sub


    Thanks for your help

  3. #3
    Join Date
    Jun 2011
    Posts
    3
    The code I have does not work, I have a missing parameter missing on the first sql statement:

    Set Records = CurrentDb.OpenRecordset("SELECT [Visitor_ID] FROM dbo_Tbl_Visitors LEFT JOIN [Tbl_Workstation Settings] ON dbo_Tbl_Visitors.Show_ID = [Tbl_Workstation Settings].[Show ID] WHERE " & strCriteria & ";")

    It says its expecting 1

    I need this code to work, if I remove strCriteria and put in :

    [Show ID] = 1 AND [Visitor ID] = 2013

    It goes thru all the process but does not insert the data into:

    rstSpool.AddNew
    rstSpool.JobNo = nJobNo
    rstSpool.SequenceNo = nSequenceNo
    rstSpool.Database = "S:"
    rstSpool.RecordNumber = " & nRecordNumber & "
    rstSpool.SubmittedTimestamp = Now()

    ' CurrentDb.Execute "INSERT INTO RegPrintQueue ( JobNo, SequenceNo, [Database], RecordNumber, SubmittedTimestamp )" & _
    '"SELECT nJobNo, nSequenceNo, [Tbl_Workstation Settings].Directory, dbo_Tbl_Visitors.Visitor_ID, Now() " & _
    '"FROM dbo_Tbl_Visitors LEFT JOIN [Tbl_Workstation Settings] ON dbo_Tbl_Visitors.Show_ID = [Tbl_Workstation Settings].[Show ID] WHERE " & strCriteria & ";"

    rstSpool.Update

    It just completes without inserting.

Tags for this Thread

Posting Permissions

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