Macro opens Update Query & Report?

[Randy]

I hope I'm in the right forum. I have a macro that first opens an update
query. I enter data and click ok, then a report opens. This is fine as
long as data is entered in the update query. What happens is occasionaly
data is not entered in the update query and the the "OK" box is clicked.
The report comes up anyway missing data. Is there a way to prevent the
report from opening if data is not entered in the query. This is causing me
problems. Or maybe there is another way of doing this without a
macro...Thanks for your help...Randy

 

[Steve Schapel]

Randy,

When you say "enter data", do you mean that you are prompted to enter a
selection criteria for the update query? If so, and if this happens by
way of a Parameter prompt being entered in the query criteria, I would
suggest you scrap this idea, and put an unbound textbox onto a form
instead, where the criteria can be entered. That way, you can use a
Condition in the macro to only open the report if the textbox Is Not Null.

 

[Randy]

The macro opens an update query first, prompting for a date "dateIssued" to
be entered. Second the macro opens a report, but I only want the report to
open if the "dateIssued" is entered in the update query. If the
"dateIssued" is not entered, lets say by mistake, the report should not be
allowed to open..I'm sure there is a better way to do this but I don't know
how...Thanks...RandyThanks randy

 

[Steve Schapel]

Randy,

As suggested in my earlier reply, the "better way" is to put an unbound
textbox onto a form, where the dateIssued can be entered, instead of via
a parameter prompt from the query. That way, you can use a Condition in
the macro to only open the report if the textbox Is Not Null.

 

[Randy]

I'm sorry, I'm not an expert at access but I'm trying to learn. I'm not
sure how an unbound test box would work. I need the "date Issued' to be
entered in 1 to 50 records, but it may be up to a week before the Date
Issued is entered, until that point the "Date Issued" is not entered. and I
need it entered in all records with a specific "Cert Number" I could have 50
records with the same "cert. number" Then I will run the update query to
enter the "date Issued" in all records with this "Cert Number"
This may be out of my ability built I thought I would give it a
shot...Thanks anyway...Randy

 

[Steve Schapel]

Randy,

I am assuming at the moment that if you look at the design view of your
update query, inthe Update To row there is entered something like [date
issued], and since there is no such field, the query issues you with a
prompt to supply the value. Am I correct?

Ok, what I'm suggesting is this... get a form which will be open at the
time that the update query will be run. In the design view of this
form, add a textbox. Easiest way to do this, is click on the textbox
icon on the Toolbox, and then click on the form and the textbox will be
put there. It is "unbound" bacause it is not linked to any data field
in a table. Now, in the update query, instead of the [date issued] or
whatever it is, you instead will put a reference to the textbox on the
form, using syntax such as...
[Forms]![NameOfForm]![NameOfTextbox]
(substituting, of course, the actual names of your form and textbox)

Then, in the macro that runs the Update Query, you need to enter a
Condition. If the Condition column is not visible in the macro design
window, select Conditions from the View menu. And in the Condition, you
will put the equivalent of...
[NameOfTextbox] Is Not Null
.... in the first row of the macro, and enter
...
in the Condition column of remaining rows of the macro. This means that
you have to enter the date you want the Update Query to use in the
textbox, and if it is not entered, the macro will not run.

 

[Randy]

Thank you so much, that did the trick...my appreciation...Randy

Randy,

I am assuming at the moment that if you look at the design view of your
update query, inthe Update To row there is entered something like [date
issued], and since there is no such field, the query issues you with a
prompt to supply the value. Am I correct?

Ok, what I'm suggesting is this... get a form which will be open at the
time that the update query will be run. In the design view of this
form, add a textbox. Easiest way to do this, is click on the textbox
icon on the Toolbox, and then click on the form and the textbox will be
put there. It is "unbound" bacause it is not linked to any data field
in a table. Now, in the update query, instead of the [date issued] or
whatever it is, you instead will put a reference to the textbox on the
form, using syntax such as...
[Forms]![NameOfForm]![NameOfTextbox]
(substituting, of course, the actual names of your form and textbox)

Then, in the macro that runs the Update Query, you need to enter a
Condition. If the Condition column is not visible in the macro design
window, select Conditions from the View menu. And in the Condition, you
will put the equivalent of...
[NameOfTextbox] Is Not Null
... in the first row of the macro, and enter
...
in the Condition column of remaining rows of the macro. This means that
you have to enter the date you want the Update Query to use in the
textbox, and if it is not entered, the macro will not run.

--
Steve Schapel, Microsoft Access MVP

I'm sorry, I'm not an expert at access but I'm trying to learn. I'm not
sure how an unbound test box would work. I need the "date Issued' to be
entered in 1 to 50 records, but it may be up to a week before the Date
Issued is entered, until that point the "Date Issued" is not entered. and I
need it entered in all records with a specific "Cert Number" I could have 50
records with the same "cert. number" Then I will run the update query to
enter the "date Issued" in all records with this "Cert Number"
This may be out of my ability built I thought I would give it a
shot...Thanks anyway...Randy
"dateIssued"

to
report

to