how to execute a query from form event

[rog]

I have a form that extracts unbilled clients from my database via a query.
The form has a button whose event handler sends the client an invoice via
email.
I'd like to set the Invoiced field in the Billing table to true from inside
the event handler but don't understand the Access/VBA object hierarcy well
enough to do so.

Would I execute an update query on the Billing table? Do I use ADO to do
it? Will it work since the database is open? A code snip would be nice...

 

[fredg]

I have a form that extracts unbilled clients from my database via a query.
The form has a button whose event handler sends the client an invoice via
email.
I'd like to set the Invoiced field in the Billing table to true from inside
the event handler but don't understand the Access/VBA object hierarcy well
enough to do so.

Would I execute an update query on the Billing table? Do I use ADO to do
it? Will it work since the database is open? A code snip would be nice...

If I understand you correctly, you wish to update a field in your
table when you have sent the email.

1) What is the datatype of the [Invoiced] field?
How do you know which record to update?

I'll assume [Invoiced] is a Yes/No field.
If it is not a Yes/No field you will need to adapt the code
accordingly.

I'll let you figure out the correct criteria of which record to update
as I have no indication of what it is. As written below, [SomeField]
is a Number datatype.

Add code to the end of event that you use to send the email:

CurrentDb.Execute "Update YourTable Set YourTable.[Invoiced] = -1
Where YourTable.[SomeField] = " & Me![SomeField] & ";", dbFailOnError

Change your field and table names as needed.

2) Be very careful. Just because the code runs is no reason to assume
that the email has been successfully sent. I would update the field
separately after I was sure the email went OK.

 

[Guest]

Some Questions
Is the Invoiced field bound to a control on your form?
What data type is it?
This will make some difference in the syntax, and whether we need to add it
to the form.
If it is not already in the form, you will need to add a control to your
form and bind it to the Invoiced field to show whether the client has been
invoiced. If you don't want a user to update this field manually, you can
make it invisible or Lock it. For example purposes, I will call it
txtInvoiced and I will assume it is a Yes/No (boolean) data type.

Once you have the above setup, it is a simple one line addition to the event
handler that sends the Emails:

Me.txtInvoiced = True
(if it is not a boolean field, then = whatever value indicates it has been
invoiced.)

 

[rog]

On Fri, 24 Feb 2006 13:20:22 -0600, rog wrote:

CurrentDb.Execute "Update YourTable Set YourTable.[Invoiced] = -1
Where YourTable.[SomeField] = " & Me![SomeField] & ";", dbFailOnError

This worked nicely. I didn't know about the CurrentDB object.

Do you happen to know, if it's possible, how to program the events that must
be generated by the record control that access puts at the bottoms of the
forms?
The record-control object doesn't seem to be listed in the object drop down
of the code editor.

 

[rog]

Thanks Klaatu.
I will keep the me object in mind for this and other purposes. I have not
been writing much VB lately and I tried to use This instead of Me.

 

[fredg]

On Fri, 24 Feb 2006 13:20:22 -0600, rog wrote:

CurrentDb.Execute "Update YourTable Set YourTable.[Invoiced] = -1
Where YourTable.[SomeField] = " & Me![SomeField] & ";", dbFailOnError

This worked nicely. I didn't know about the CurrentDB object.

Do you happen to know, if it's possible, how to program the events that must
be generated by the record control that access puts at the bottoms of the
forms?
The record-control object doesn't seem to be listed in the object drop down
of the code editor.

2) Be very careful. Just because the code runs is no reason to assume
that the email has been successfully sent. I would update the field
separately after I was sure the email went OK.

Do you mean an event that occurs when you navigate from one record to
another?
If so, you can use the Form's Current event

 

[rog]

Just what I was looking for...
Thanks,
R

On Fri, 24 Feb 2006 13:20:22 -0600, rog wrote:

CurrentDb.Execute "Update YourTable Set YourTable.[Invoiced] = -1
Where YourTable.[SomeField] = " & Me![SomeField] & ";", dbFailOnError

This worked nicely. I didn't know about the CurrentDB object.

Do you happen to know, if it's possible, how to program the events that
must
be generated by the record control that access puts at the bottoms of the
forms?
The record-control object doesn't seem to be listed in the object drop
down
of the code editor.

2) Be very careful. Just because the code runs is no reason to assume
that the email has been successfully sent. I would update the field
separately after I was sure the email went OK.

Do you mean an event that occurs when you navigate from one record to
another?
If so, you can use the Form's Current event