Update two tables with save action

[Keystrokes]

I have a database I have been creating for a number of weeks and have entered the next stage of development.

The system is to manage student property, rents room etc on a termly basis.

Whilst it is all relational, I am strguling with the next bit.

I have a form that stores an entry in the tblCommunicationLog structed as...
log_id (primary index)
tenant_id (index) related to tblTenants
log_date
transaction (currency)
post_ledger (yes/no)
who (notes)
attachment
notes (memo)
method (text - realted to communication tpye i.e. sms, email)

The idea is that when the landord receives or sends any communication he logs it in the above form/table.

If the communication was about a bounced cheque (check) then he can apply a charge to the ledger if he ticks the box (post ledger yes/no)

The tblLedger strcuture is
trans_id (primary key)
tenant_id (realted to tblTenants and indexed)
trans_date
transaction(currency)

Therefore if there is a charge applicable I want the save button on the "add communication" form to also add an entry to the tblLedger

The standard update query works outside of the form (this was created using the wizard)

INSERT INTO tblLedger ( tenant_id, [date], [transaction] )
SELECT tblCommunicationLog.[tenant_id], tblCommunicationLog.[date], tblCommunicationLog.[transaction]
FROM tblCommunicationLog;

This is the code I have so far with help from another forum...

Private Sub CmdSave_Click()
DoCmd.Save , "Add Communication Log"

Dim connDB As ADODB.Connection
Dim qry As New Command
Dim sql As String

Set connDB = CurrentProject.Connection
qry.ActiveConnection = connDB

sql = "INSERT INTO tblLedger ( tenant_id, trans_date, transaction ) &"
"VALUES (" & Me.tenant_id & ",#" & Me.log_date & "#, " & Me.transaction & ")"
qry.commandText = sql
qry.Execute

End Sub

I have a syntax error when it runs, anyone any ideas.

Also at present it will update tblLedger regardless of whether post_ledger=true.

Once the code executes correclty I then need to add the condition. What would the condition stement be?

Kind regards

Lloyd

 

[KeithG]

Change values to Select in your SQL Statement.

 

[Keystrokes]

Change values to Select in your SQL Statement.

Hi Keith, thanks for your help

Is this what you mean..

Private Sub CmdSave_Click()
DoCmd.Save , "Add Communication Log"

Dim connDB As ADODB.Connection
Dim qry As New Command
Dim sql As String

Set connDB = CurrentProject.Connection
qry.ActiveConnection = connDB

sql = "INSERT INTO tblLedger ( tenant_id, trans_date, transaction ) &"
"SELECT (" & Me.tenant_id & ",#" & Me.log_date & "#, " & Me.transaction & ")"
qry.commandText = sql
qry.Execute

End Sub

I get a syntax error so I have tried

Private Sub CmdSave_Click()
DoCmd.Save , "Add Communication Log"
Dim connDB As ADODB.Connection
Dim qry As New Command
Dim sql As String
Set connDB = CurrentProject.Connection
qry.ActiveConnection = connDB
sql = "INSERT INTO tblLedger ( tenant_id, trans_date, transaction ) SELECT (" & Me.tenant_id & ",#" & Me.log_date & "#, " & Me.transaction & ")"
qry.commandText = sql
qry.Execute
End Sub

but to no avail.

Any other ideas

 

[KeithG]

try running your SQL statement with Docmd.RunSQL SQL