Update Query

[Guest]

I would like to press a command button and have it check all unchecked boxes
in a form. Right now I have to be on the record for it to check. I want it
to check all of them

 

[Steve Schapel]

Chey,

Try it like this...

CurrentDb.Execute "UPDATE YourQuery SET YourField = Yes", dbFailOnError

.... where YourQuery is the name of the Form's Record Source table or query.

 

[Guest]

Okay I still don't understand
My query's name is cert
my check box to be updated is Certifyed
and I want it to update when Date inputed is less than the current date
Can you help

 

[Steve Schapel]

Chey,

As you said before, you have a comand button on the form, right? Well,
on the Click event of the command button, you need the code to update
your Certifyed field. What do you mean about the "Date inputed"? Is
there a date field in the 'cert' query? If I understand you, maybe the
code is more like this one...

CurrentDb.Execute "UPDATE cert SET Certifyed = -1 WHERE DateField <
Date()", dbFailOnError

 

[Guest]

Okay never mind I got it to work

 

[Guest]

Now Is there a way of attaching a date to the check mark after it has been
changed. I would like it all to happen in one shot. I made a field date
cert.

 

[Steve Schapel]

Chey,

Can you give an example, please. I do not understand what "attaching a
date to the check mark" means. What exactly do you want it to look like?

 

[Guest]

I already have the command button checking a box, now I want the date to
appear when the box was checked.

 

[Steve Schapel]

Chey,

Just the same technique as I already showed you.

However, if that means you will have a date in this date field if the
checkbox is ticked, and no date in this date field if the checkbox is
unticked, then I would say get rid of the checkbox and its underlying
field altogether, as the existence of the date will tell you everything
you want to know.

As I mentioned before, some details and examples would be necessary
before anyone could give you a clear answer.

 

[Guest]

CurrentDb.Execute "UPDATE Cert SET Date Cert by mercedes = Now()",
dbFailOnError
This is what I have in there. An error message
Run time error 3114
Syntax error in update statement

My query is Cert
Field is Date Cert by mercedes
I would like the date cert to be the current date/time
Thanks

 

[Steve Schapel]

Chey,

Since you have spaces in the name of the field, you will need to enclose
on []s like this...
"UPDATE Cert SET [Date Cert by mercedes] = Now()"

 

[Guest]

I am coming up with some more probeles with this.
When I go to run it, it is really slow.
Also I have tried writing a new one and now it comes up with a synax error

Private Sub Command11_Click()
CurrentDb.Execute "UPDATE CertbyCheyenne SET [Date Cert by cheyene] =
Now()", dbFailOnError
End Sub
This is what I have in there. I also made sure that my spelling was correct.
Any suggestions?

 

[Steve Schapel]

Chey,

I am sorry, I can think of no reason for this to be slow. I would
expect it to run almost instantaneously, unless you have a very large
number of records.

Also I see nothing wrong with the syntax. I assume the whole
CurrentDb.Execute statement is on one line in your actual code, and that
the wrapping onto 2 lines is done by the newsreader?

 

[Guest]

the syntax error I get is Run Time error 3061
To Few Parameters
Expected 2

I do have a parameter in there. I have it wher I have to type in a date.
Only those days I want to show, and only those dates I want to show.

I actually copied the other code and pasted it.

Chey,

I am sorry, I can think of no reason for this to be slow. I would
expect it to run almost instantaneously, unless you have a very large
number of records.

Also I see nothing wrong with the syntax. I assume the whole
CurrentDb.Execute statement is on one line in your actual code, and that
the wrapping onto 2 lines is done by the newsreader?

--
Steve Schapel, Microsoft Access MVP

I am coming up with some more probeles with this.
When I go to run it, it is really slow.
Also I have tried writing a new one and now it comes up with a synax error

Private Sub Command11_Click()
CurrentDb.Execute "UPDATE CertbyCheyenne SET [Date Cert by cheyene] =
Now()", dbFailOnError
End Sub
This is what I have in there. I also made sure that my spelling was correct.
Any suggestions?

 

[Guest]

Okay well I got it to work if I didn't put Like[Enter Date]&"*"
But I need that.
I want it to pull up a certain Date
How do I intermingle this?
Thanks
Chey

Chey,

I am sorry, I can think of no reason for this to be slow. I would
expect it to run almost instantaneously, unless you have a very large
number of records.

Also I see nothing wrong with the syntax. I assume the whole
CurrentDb.Execute statement is on one line in your actual code, and that
the wrapping onto 2 lines is done by the newsreader?

--
Steve Schapel, Microsoft Access MVP

I am coming up with some more probeles with this.
When I go to run it, it is really slow.
Also I have tried writing a new one and now it comes up with a synax error

Private Sub Command11_Click()
CurrentDb.Execute "UPDATE CertbyCheyenne SET [Date Cert by cheyene] =
Now()", dbFailOnError
End Sub
This is what I have in there. I also made sure that my spelling was correct.
Any suggestions?

 

[Steve Schapel]

Chey,

I really think that you will receive the best possible help in the
newsgroups if you are able to provide complete and accurate information.
You gave some code, and then it turns out that this is not the code
that is causing the problem, so it's no wonder that we can't see the
problem. I really have no idea what Like[Enter Date]&"*" is supposed to
mean, and where you are using this.

Anyway, if you want to use a specified date in your work, the best way
is to put an unbound textbox on a form that will be open at the time
that the code is run, where the user can enter the required date, and
then refer to this textbox in your code.

 

[Guest]

I typed Like[Enter Date]&"*" into the criteria on a query.
I have 3 rows of information. Vedor number, the date I queried and Date
sent out.
The code that I wrote is for the date to appear in the "date sent out" row,
but only to fill in what i have queried.

This is just like what I had done with 2 other forms. The only diffrence
now is that I was to specify a certain date. When I open the form it askes
me for a date. I type it in and only that date (that was certifiyed) shows.
Now my new column is when the papers were sent out of the office. How can I
get around all of this.

Thanks for your help

Chey,

I really think that you will receive the best possible help in the
newsgroups if you are able to provide complete and accurate information.
You gave some code, and then it turns out that this is not the code
that is causing the problem, so it's no wonder that we can't see the
problem. I really have no idea what Like[Enter Date]&"*" is supposed to
mean, and where you are using this.

Anyway, if you want to use a specified date in your work, the best way
is to put an unbound textbox on a form that will be open at the time
that the code is run, where the user can enter the required date, and
then refer to this textbox in your code.

--
Steve Schapel, Microsoft Access MVP

Okay well I got it to work if I didn't put Like[Enter Date]&"*"
But I need that.
I want it to pull up a certain Date
How do I intermingle this?
Thanks
Chey

 

[Steve Schapel]

Chey,

Is this related in any way to the Update Query that we were discussing
earlier, and that you want to run in code?

 

[Guest]

Yes this all goes hand and hand
First I asked for the date to be put in with a click of a button
This was for certifying
now those dates that were certifyied I would like to recall and click a
button for when they were processed.
After I acomplish that then when they are returned back into the office.
The processed procedure is the same as when they return back into the office.
I will have to recall the date. We do one thing a day. So bringing up one
date at a time works the best. I do have my qurey built and now I just need
to know the best way to recall a single date.

I hope this makes sense.
Chey

Chey,

Is this related in any way to the Update Query that we were discussing
earlier, and that you want to run in code?

--
Steve Schapel, Microsoft Access MVP

I typed Like[Enter Date]&"*" into the criteria on a query.
I have 3 rows of information. Vedor number, the date I queried and Date
sent out.
The code that I wrote is for the date to appear in the "date sent out" row,
but only to fill in what i have queried.

This is just like what I had done with 2 other forms. The only diffrence
now is that I was to specify a certain date. When I open the form it askes
me for a date. I type it in and only that date (that was certifiyed) shows.
Now my new column is when the papers were sent out of the office. How can I
get around all of this.

Thanks for your help

 

[Steve Schapel]

Chey,

So, is CertbyCheyenne a query?

As I mentioned before, a good way to do this is to put an unbound
textbox on the form, where the date criteria is entered. Take the
[Enter Date] prompt out of the query. And then your code will be
something like this...

CurrentDb.Execute "UPDATE CertbyCheyenne" & _
" SET [Date Cert by cheyene] = Now()" & _
" WHERE [Date Queried]=" &
CLng(Me.TextboxName), dbFailOnError

Hope you can adapt this... I got lost with the "date I queried" and the
"date sent out" and the "date cert by cheyanne" to know which was
which... you sure seem to have a lot of date fields there!