Inserting Data into 2 Tables

[pedromac73]

Hope you can help, I have searched the forums but cannot find anything that will serve my needs but if you know can you guide me to the relevant place it would be appreciated.

I am trying to create an auto invoicing system that will create a new invoice after a date has been updated within the current record but the data will go into 2 tables for Invoice and Invoice Details. This will be applied to several date fields within the record.

The fields required on the current record:-

tblCandidates

lngCandidateID
strCandidateFirstName
strCandidateLastName
lngClientID

tblCourseRegistrations

lngCourseRegID
lngCandidateID
lngCourseID
lngTAID
strCourseRegSkillDate (afterupdate command to create auto invoice)

tblServices

lngServiceID

The fields being updated:-

tblInvoices

strInvoiceDesc = (strCandidateFirstName & strCandidateLastName)
(Concatenated from tblCandidates)

dtmInvoiceDate = tblCourseRegistrations.strCourseRegSkillDate

lngClientID = tblCandidates.lngClientID

lngCourseID = tblCourseRegistrations.lngCourseID

lngTAID = tblCourseRegistrations.lngTAID

tblInvoiceDetails

lngServiceID = tblServices. lngServiceID
(This will change as a default depending on the date field being updated i.e. skilldate = 1, 1stvqdate=2, additionalvq=3)

I have tried using an append query but cannot get it to go into the second table so I believe that VBA would be best.

INSERT INTO
tblInvoices ( lngClientID, dtmInvoiceDate, lngTAID, strInvoiceDesc )
SELECT
tblCandidates.lngClientID, tblCourseRegistrations.dtmCourseRegSkillMatchDate, tblCourseRegistrations.lngTAID, [tblCandidates.strCandidateFirstName] & " " & [tblCandidates.strCandidateLastName] AS InvoiceDesc
FROM
tblServices, tblCourses INNER JOIN (tblCandidates INNER JOIN tblCourseRegistrations ON tblCandidates.lngCandidateID = tblCourseRegistrations.lngCandidateID) ON tblCourses.lngCourseID = tblCourseRegistrations.lngCourseID;

Kind Regards in advance

Peter

 

[GaryPanic]

have you tried the .add method look at the samples on this for more details
g

 

[johndoomed]

I use this simple code for a lot of stuff:

    With CurrentDb.OpenRecordset("tablename")
        .AddNew
        ![tablefield1] = myVar1
        ![tablefield2] = myVar2
        .Update
        .Close
    End With

 

[pedromac73]

Thanks for the help in guiding me to the best approach I really appreciate it.

I have managed to get the correct code for the tblInvoices from the form that I am using but I am having difficulty setting a set value of 1 into the lngServiceID field on the tblInvoiceLines sub form.

This is what I have tried, can you please advise.

Private Sub dtmCourseRegSkillMatchDate_AfterUpdate()

On Error GoTo ErrorHandler

With CurrentDb.OpenRecordset("tblInvoices")

.AddNew
![strInvoiceDesc] = "SkillDate Registration"
![dtmInvoiceDate] = Forms![frmCourseRegistrations]![dtmCourseRegSkillMatchDate]
![lngClientID] = Forms![frmCourseRegistrations]![lngClientID]
![lngCourseID] = Forms![frmCourseRegistrations]![lngCourseID]
![lngCandidateID] = Forms![frmCourseRegistrations]![lngCandidateID]
![lngTAID] = Forms![frmCourseRegistrations]![lngTAID]
.Update
.Close

End With

With CurrentDb.OpenRecordset("tblInvoiceLines")

.AddNew
![lngServiceID] = 1
.Update
.Close

End With

ExitProcedure:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume ExitProcedure

End Sub

Regards

Peter

 

[pedromac73]

Finally got this to work (with a little patience and no sleep ) so thanks for the guidance GaryPanic and johndoomed.

Regards

Peter