creating new record in table for each record in a query

[gangel]

Hi peoplez!



I have tables
[Batch] - which is a batch of scripts
[script] - many prescriptions of medicine for patients
[Invoices]

A batch has many scripts.

A script is then invoiced, thus a script has a BatchID and an Invoice ID

I have a query which pretty much shows me all SCRIPTS that have no invoiceID (so all non-invoiced scripts). This is grouped by their batch number

Therefore;

QUery(QuUninvoicedScripts) [BatchID] _ [ScriptNumber] _ [InvoiceID] _ [Ignore (yes/no) where invoice ID is null and ignore is False (ascript might be set to ignore as they are in a batch but i dont want them to be invoiced yet)


What i need to do now is create a new Invoice in table Invoices for EACH record in the above query.

So ill end up with amybe 10 invoices which relate to the 10 records i have in the table.

im assuming it will be something to do with DAO.recordset...

In MY words i want:
For each record in QuUninvoicedScripts

Create a new Record in [Invoices] with todays date

Therefore ill end up with my current scripts with an allocated InvoiceID for each of them.

I may also have some scripts from the invoiced batches that DONT have an invoiceID as they were marked as "ignore true" on their table....

I hope im clear in this. Any points in the right direction woudl be great, i dont expect to be spoon fed!

[ranman256]

You dont need DAO.recordset. That is for vb code, and you dont need code. Use queries.

You want an APPEND query, 1 field is todays date.
select the records you want to be invoiced, use this query and append it to the Batch table.

[ItsMe]

As mentioned, you could probably get what you need done using an action query to append your records. You could use the Query Builder to get your SQL and then copy your SQL to be used behind your form. You can manage errors using dbFailOnError. So you will want to add some code in case there is a problem.
https://msdn.microsoft.com/en-us/lib.../ff197654.aspx

Dim strSQL As String
strSQL = "SQL copied from Query Builder"
CurrentDb.Execute strSQL, dbFailOnError

DAO is a great tool and is very useful when you need control of things. Your case may not need DAO. But, it is good to look at it and understand it. It may open doors to great ideas.

Here is some code from one of my DB's. It sits behind an unbound form to update a table. This code Appends one record.

                Dim rcdAddPO As DAO.Recordset
                Set rcdAddPO = db.OpenRecordset("tblPurchOrd", dbOpenDynaset)
                With rcdAddPO
                    .AddNew
                    ![CustFK] = lngCustID
                    ![SoldToFK] = lngSoldTo
                    ![EnterUnit] = -1   'Let the program know we will be adding units and pieces
                    ![EnterPieces] = -1 'be adding units and pieces
                    ![PlantFK] = lngPlantID
                    ![PO] = strPO
                    ![CarNum] = strCarNum
                    ![Shipment] = strShipment
                    ![ShipDate] = dtShipDate
                    ![SalesOrder] = strSalesOrder
                    ![Lading] = strLading
                    lngPurchOrd = ![PurchOrdPK]   'Get the PK to place in FK later
                    .Update
                End With

If you were to implement DAO in your case, you would have to open two recordsets. One recordset would be iterated and the second would append. You would have to nest them.

It would probably look something like this.

'Create a query with WHERE criteria
Dim strSQL As String
strSQL = "SELECT BatchID, ScriptNumber, InvoiceID, Ignore " & _
         "FROM QuUninvoicedScripts " & _
         "WHERE ((InvoiceID) is null) AND Ignore=false"

'Open the new query so we can loop through it
Dim rcdAppendInvoice As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

Set rcdAppendInvoice = db.OpenRecordset(strSQL, dbOpenDynaset)

'Make sure we have some records
rcdAppendInvoice.MoveFirst
If rcdAppendInvoice.EOF = True Then
'TODO add some error trapping here
End If

'Open up a recordset for the table to append to
Dim rcdAddPO As DAO.Recordset
Set rcdAddPO = db.OpenRecordset("tblInvoices", dbOpenDynaset)

'loop through our records that need to be appended
While rcdAppendInvoice.EOF = False

        With rcdAddPO
            .AddNew
            ![CustFK] = rcdAppendInvoice![BatchID]
            ![SoldToFK] = lrcdAppendInvoice![ScriptNumber]
            ![MyDateField] = Date  'Setting default value at table level is probably better
            .Update
        End With
                
rcdAppendInvoice.MoveNext

Wend    'rcdAppendInvoice.EOF = False

'Tidy up
rcdAddPO.Close
Set rcdAddPO = Nothing
rcdAppendInvoice.Close
Set rcdAppendInvoice = Nothing

[ranman256]

No VB code needed.

[gangel]

hmmm this is what i made myself today. Follows similar to ItsMe.

I havent done any of that tidy stuff so thats prob a good idea for me to work on.

How is this looking for a first crack ever!?

Public bcode As IntegerPublic InvID As Integer






Public Sub Command0_Click()
Dim Srst As dao.Recordset
Dim BtcID As dao.Recordset




Set BtcID = CurrentDb.OpenRecordset("UnInvoicedBatches")




If BtcID.EOF Then
MsgBox "No Scripts to Invoice!"
Exit Sub
Else


BtcID.MoveFirst
Do Until BtcID.EOF
bcode = BtcID(0)


CreateInvoice


BtcID.MoveNext


Loop
End If


End Sub


Public Function CreateInvoice()
Dim Irst As dao.Recordset


Set Irst = CurrentDb.OpenRecordset("invoices")


Irst.AddNew
Irst!InvoiceDate = Date
Irst!StaffID = 1
InvID = Irst(0)
Irst.Update




AllocateInvID






End Function


Public Function AllocateInvID()
Dim Unscr As dao.Recordset
Set Unscr = CurrentDb.OpenRecordset("uninvoicedscripts")


Unscr.MoveFirst
Do Until Unscr.EOF
If Unscr!ScrBatchID = bcode Then
Unscr.Edit
Unscr!InvoiceID = InvID
Unscr.Update








End If
Unscr.MoveNext


Loop






End Function

[ItsMe]

It looks good. I like how you see the importance of encapsulating specific tasks into separate Functions. One thing that is important to understand when managing the separation of concerns is what the scope of each procedure is. So, unfortunately, it will be easiest to include most, if not all, of the code in a click event. What is happening is you are creating and destroying objects within a fraction of a second. For instance, every time you call CreateInvoice you are producing and destroying an instance of Irst.

It can get really complicated and that is why you will see the very large Sub Procedures in VBA. So, if you were compelled to manage this with separate Functions and Sub Procedures, it would probably be best to create a separate Class Module. However, we are already in a Class Module (the Form's Module) and you could make additional declarations that were available to the entire form's module.

What is your objective by using Functions, are you trying to follow the tenet od encapsulation?

[gangel]

actually i just thought if i put all the stuff in one sub people would say I shopuld simplify it for debugging by having functions.

But yes, looking at it, there is really no need, just a loop inside a loop inside a loop, am i correct?

SO i shoudl state everything at the top as DIM and not need globals, then cut and paste the functions to replace their 'call' and that would achieve an identical outcome, am i right?

Thanks for the replies ItsMe, im totally amazed ive cracked this "on my own" lol, at least im getting somewhere and not stuck using macros or something!

[ItsMe]

It is hard to follow best practices in Access and VBA. Sometimes you have to break the rules a little. You can make declarations in the header. However, when it comes to DAO, I prefer to keep it within the scope of the procedure. So I would probably opt for the longer sub procedure.

You could create two Sub's and call then from the Click event. One sub to Append and another to Update the uninvoicedscripts. The Append sub will require a nested loop. I might opt for executing some SQL in the Append. But, DAO may be easier to maintain in the long term.

Couple things on your declarations in the header. You have ...
Public bcode As IntegerPublic InvID As Integer

This will not work in VBA because you need a comma to separate the variables. I usually break it into separate lines.
Try ...
Dim bcode As IntegerPublic
Dim InvID As Integer

There is no need to use the Public keyword. Use the Dim statement. They will be available and in scope of all procedures within the Form's Module while the Form is open using the Dim statement.

[gangel]

oh that was a copy paste error, they are in fact on different lines