create record (in subform) automatically

  • Thread starter Thread starter Mats
  • Start date Start date
M

Mats

Hi all
I'm beginning to think in the wrong way, so I need help. In an Access
2000 database I have two (very) related tables, Invoices and Amount
payed.
They appear in the Invoices form with Amount payed in a subform.
I need a record to be created automatically in the Amount payed table
as soon as a record af a new invoice is created in the Invoices table,
Invoice # being the key.
If no record is created, unpaid invoices will not be shown correctly,
i.e. I need a record with a value of zero for every invoice with no
amount payed.
I suspect this has to bee done in VB, and I'm not very good at VB.
(But quite handy with VB script.)
Any suggestions?
Mats
 
Use the AfterInsert event procedure of the main form to Execute an append
query on the related table:

strSQL = "INSERT INTO [Amount Payed] ...
dbEngine(0)(0).Execute strSQL, dbFailOnError

To get the append query statement, create a new query, switch to append
(Append on Query menu), and create a dummy one. Then swith to SQL View (View
menu) to see the string you need to create.
 
Thanks for reply.
However it does not work as wanted.
In case it matters, we use Access 2000 and ADO.
This is what I've tried:
Note: Both the field in the table and the control in the form are
named Invoiceno.
The result is the same when I use the AfterUpdate event of another
control.

Private Sub Invoiceno_AfterUpdate()

strSQL = "INSERT INTO Payed ( Invoiceno ) SELECT Invoices.Invoiceno
FROM Invoices;"
dbEngine(0)(0).Execute strSQL, dbFailOnError

End Sub

This sub does something.
It adds a new record in the Payed table for all previously existing
invoicenumbers, but not a new record for the new invoiceno that just
was written into the form, and the underlaying invoices table.
Of course this was not the wanted outcome.
My knowledge of VB is limited, I can read a statement like above and
(at least to some extent) understand what it is supposed to be doing,
but writing statements is another matter, so I'm lost. I know Access
reasonably well, and I do beleive that the solution has to be found in
VB.
Mats

Allen Browne said:
Use the AfterInsert event procedure of the main form to Execute an append
query on the related table:

strSQL = "INSERT INTO [Amount Payed] ...
dbEngine(0)(0).Execute strSQL, dbFailOnError

To get the append query statement, create a new query, switch to append
(Append on Query menu), and create a dummy one. Then swith to SQL View (View
menu) to see the string you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Mats said:
Hi all
I'm beginning to think in the wrong way, so I need help. In an Access
2000 database I have two (very) related tables, Invoices and Amount
payed.
They appear in the Invoices form with Amount payed in a subform.
I need a record to be created automatically in the Amount payed table
as soon as a record af a new invoice is created in the Invoices table,
Invoice # being the key.
If no record is created, unpaid invoices will not be shown correctly,
i.e. I need a record with a value of zero for every invoice with no
amount payed.
I suspect this has to bee done in VB, and I'm not very good at VB.
(But quite handy with VB script.)
Any suggestions?
Mats
Click to expand...
Click to expand...
 
The Execute method is DAO.
Try RunSQL, and drop the dbFailOnError switch.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Mats said:
Thanks for reply.
However it does not work as wanted.
In case it matters, we use Access 2000 and ADO.
This is what I've tried:
Note: Both the field in the table and the control in the form are
named Invoiceno.
The result is the same when I use the AfterUpdate event of another
control.

Private Sub Invoiceno_AfterUpdate()

strSQL = "INSERT INTO Payed ( Invoiceno ) SELECT Invoices.Invoiceno
FROM Invoices;"
dbEngine(0)(0).Execute strSQL, dbFailOnError

End Sub

This sub does something.
It adds a new record in the Payed table for all previously existing
invoicenumbers, but not a new record for the new invoiceno that just
was written into the form, and the underlaying invoices table.
Of course this was not the wanted outcome.
My knowledge of VB is limited, I can read a statement like above and
(at least to some extent) understand what it is supposed to be doing,
but writing statements is another matter, so I'm lost. I know Access
reasonably well, and I do beleive that the solution has to be found in
VB.
Mats

"Allen Browne" <[email protected]> wrote in message
Use the AfterInsert event procedure of the main form to Execute an append
query on the related table:

strSQL = "INSERT INTO [Amount Payed] ...
dbEngine(0)(0).Execute strSQL, dbFailOnError

To get the append query statement, create a new query, switch to append
(Append on Query menu), and create a dummy one. Then swith to SQL View (View
menu) to see the string you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Mats said:
Hi all
I'm beginning to think in the wrong way, so I need help. In an Access
2000 database I have two (very) related tables, Invoices and Amount
payed.
They appear in the Invoices form with Amount payed in a subform.
I need a record to be created automatically in the Amount payed table
as soon as a record af a new invoice is created in the Invoices table,
Invoice # being the key.
If no record is created, unpaid invoices will not be shown correctly,
i.e. I need a record with a value of zero for every invoice with no
amount payed.
I suspect this has to bee done in VB, and I'm not very good at VB.
(But quite handy with VB script.)
Any suggestions?
Mats
Click to expand...
Click to expand...
Click to expand...
 
Back
Top