Insert data into 2 tables

[Syphonics via AccessMonster.com]

I had a form name "Purchases" which is based on table "Purchases"
and a Table name "Transactions"

In my form "Purchases", I have a "AmountPaid". I would like to do a "On Click
Event" and then the value in "AmountPaid" will be recorded into my
Transactions Table which has a field called "Withdrawal"

I did something like this but it won't work, does autonumbering in my
transaction table affect this?


Private Sub Outstanding_Click()

If (Outstanding = "Paid") Then

Test = "INSERT INTO Transactions.(withdrawal) VALUES '" & Me.[AmountPaid]
CurrentDb.Execute Test

End If


End Sub


Thanks for the help

 

[UpRider]

Test = "INSERT INTO Transactions (withdrawal) VALUES (" & Me.[AmountPaid] &
");"

UpRider

 

[Syphonics via AccessMonster.com]

Thanks it is able to work.
But if i have more fields to be added in, how do i do so?

Test = "INSERT INTO Transactions (withdrawal) VALUES (" & Me.[AmountPaid] &
");"

UpRider

I had a form name "Purchases" which is based on table "Purchases"
and a Table name "Transactions"

[quoted text clipped - 20 lines]

Thanks for the help

 

[Syphonics via AccessMonster.com]

I would like to insert 3 fields into the Transactions table.

 

[UpRider]

What are the names of the fields in the table, and what are the names of the
controls on the form?

UpRider

 

[Syphonics via AccessMonster.com]

I am able to get the fields in already, but I have a little problem. I
couldn't get the PaymentDate in.
the format for the date is 20/10/07 and the in the table it appears the
system calculate as a division instead of date
value = 0.285


If (Outstanding = "Paid") Then

Amount = "INSERT INTO Transactions (withdrawal, description, Tdate)
VALUES (" & Me.[AmountPaid] & ", " & Me.[SupplierName] & ", " & Me.
[PaymentDate] & ");"
CurrentDb.Execute Amount



End If

 

[UpRider]

Try
& ", " & "#" & Me.[PaymentDate] & "#);"

UpRider

 

[Syphonics via AccessMonster.com]

Thanks Uprider I am able to do so. But the date it gave me is in this format
07/10/20. Is it possible to switch it to 20/10/07?

And how do I add a text into the value. I need the field name Accounts to be
a text called Purchases

Thank you

Amount = "INSERT INTO Transactions (withdrawal, description, Account)
VALUES (

Try
& ", " & "#" & Me.[PaymentDate] & "#);"

UpRider

I am able to get the fields in already, but I have a little problem. I
couldn't get the PaymentDate in.

[quoted text clipped - 8 lines]

[PaymentDate] & ");"
CurrentDb.Execute Amount

 

[UpRider]

Try
& ", " & Chr$(39) & Me.[PaymentDate] & Chr$(39) & ");"

UpRider

Thanks Uprider I am able to do so. But the date it gave me is in this
format
07/10/20. Is it possible to switch it to 20/10/07?

And how do I add a text into the value. I need the field name Accounts to
be
a text called Purchases

Thank you

Amount = "INSERT INTO Transactions (withdrawal, description, Account)
VALUES (

Try
& ", " & "#" & Me.[PaymentDate] & "#);"

UpRider

I am able to get the fields in already, but I have a little problem. I
couldn't get the PaymentDate in.

[quoted text clipped - 8 lines]

[PaymentDate] & ");"
CurrentDb.Execute Amount

 

[UpRider]

Amount = "INSERT INTO Transactions (withdrawal, description, Accounts)
VALUES (" & Me.[AmountPaid] & ", " & Chr$(39) & Me.[SupplierName] _
& Chr$(39) & ", " & Chr$(39) & Me.[Purchases] & Chr$(39) & ");"

You need to add the Chr$(39) at each end of the text fields. Chr$(39) is a
single quote.

UpRider

 

[Syphonics via AccessMonster.com]

Thanks Uprider able to solve the date issue.

If suppose I wanted a values from Me.[Descriptions] which is a text am I able
to do so?

Amount = "INSERT INTO Transactions (withdrawal, description, Tdate, Account)
VALUES (" & Me.[AmountPaid] & ", ' & Me.[Descriptions] & ', " & Chr$(39) & Me.
[PaymentDate] & Chr$(39) & ", ' Expenses ');"
CurrentDb.Execute Amount

Me.[Descriptions] is a text, but I am unable to do so. the error it gave me:
Run time error 3061
Too few parameters. Expected 1

Amount = "INSERT INTO Transactions (withdrawal, description, Accounts)
VALUES (" & Me.[AmountPaid] & ", " & Chr$(39) & Me.[SupplierName] _
& Chr$(39) & ", " & Chr$(39) & Me.[Purchases] & Chr$(39) & ");"

You need to add the Chr$(39) at each end of the text fields. Chr$(39) is a
single quote.

UpRider

 

[Syphonics via AccessMonster.com]

Thanks for the help, i am able to do it after a bit of trial and error

' " & Me.[Descriptions] & " '

Thanks Uprider able to solve the date issue.

If suppose I wanted a values from Me.[Descriptions] which is a text am I able
to do so?

Amount = "INSERT INTO Transactions (withdrawal, description, Tdate, Account)
VALUES (" & Me.[AmountPaid] & ", ' & Me.[Descriptions] & ', " & Chr$(39) & Me.
[PaymentDate] & Chr$(39) & ", ' Expenses ');"
CurrentDb.Execute Amount

Me.[Descriptions] is a text, but I am unable to do so. the error it gave me:
Run time error 3061
Too few parameters. Expected 1

 

[UpRider]

Glad to help. Good luck witth the rest of your project.
UpRider

Thanks for the help, i am able to do it after a bit of trial and error

' " & Me.[Descriptions] & " '

Thanks Uprider able to solve the date issue.