Help!! VBA code error converting from 97 to XP

[xavier]

Hello

I got the task of converting some DB's I didn't write and
all went well except for one issue I need help with.
There is a form you go into and check the boxes of the
tests you need to request then click on a preview button
which gives you a printable report of those tests, this
action of clicking on the request button (preview form to
print) should automaticaly place new check boxes only on
those records which were checkd off before so they don't
come up again in the future and get duplicated etc.

Below is the code the previous writer used in 97 and it
works in 97 but not in XP.

The error comes up highlighting the dbs in the line that
says dbs.execute "update tests"

Is there an easy way to correct this so it works, in the
mean time I have them checking them manualy till I can
get it fixed.

Any help is greatly appreciated

Thank you
Xavier

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click

Dim stDocName As String

stDocName = "SPECIMEN REQUISITION FORM - Group H"
DoCmd.OpenReport stDocName, acPreview

dbs.Execute "UPDATE Tests " _
& "SET SpecimenRequestClosed = TRUE " _
& "WHERE SpecimenRequest = TRUE " _
& "AND SpecimenRequestClosed = FALSE;"

dbs.Close

Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub

 

[xavier]

John

I looked at the link you sent me and at the references
and could not find anything wrong, but then again I might
be missing something.

Would there be an easy way to do this if I were to start
from scratch on this issue, What I want to accomplish is
the following.

You open up a form which is a list of test to request and
do, when you select which ones you need and click to
preview and print them then exit back to the form these
that were checked off will have automaticaly get a second
check mark which, when you exit this request form then
come back in those items will not show up again on the
list.

I also have something else I want to do but cant figure
out how, if one has a lookup list how to add the "add
item" at the bottom so if an item is not listed you can
add it.

Thanks for the help
Xavier

-----Original Message-----
Hi Xavier,

The first thing I'd do is check that the object "dbs" is being properly
declared and instantiated. It appears to be a global or module-level
variable. Also, I'd check the references in the VBA project. See
http://members.rogers.com/douglas.j.steele/AccessReferenc eErrors.html

Hello

I got the task of converting some DB's I didn't write and
all went well except for one issue I need help with.
There is a form you go into and check the boxes of the
tests you need to request then click on a preview button
which gives you a printable report of those tests, this
action of clicking on the request button (preview form to
print) should automaticaly place new check boxes only on
those records which were checkd off before so they don't
come up again in the future and get duplicated etc.

Below is the code the previous writer used in 97 and it
works in 97 but not in XP.

The error comes up highlighting the dbs in the line that
says dbs.execute "update tests"

Is there an easy way to correct this so it works, in the
mean time I have them checking them manualy till I can
get it fixed.

Any help is greatly appreciated

Thank you
Xavier

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click

Dim stDocName As String

stDocName = "SPECIMEN REQUISITION FORM - Group H"
DoCmd.OpenReport stDocName, acPreview

dbs.Execute "UPDATE Tests " _
& "SET SpecimenRequestClosed = TRUE " _
& "WHERE SpecimenRequest = TRUE " _
& "AND SpecimenRequestClosed = FALSE;"

dbs.Close

Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Xavier,

If the database was working properly in Access 97 then it's probably
simpler to fix whatever is failing under Access 2002 than it is to start
from scratch. (It's a good idea before converting from 97 to make sure
that the database has been compacted and that all the code in it
compiles without errors.) In any case the general idea of running an
update query after printing the report is the right one (though there's
a flaw in the logic that I'll mention later).

You said that you were getting an error on the line

dbs.Execute "UPDATE Tests " _
& "SET SpecimenRequestClosed = TRUE " _
& "WHERE SpecimenRequest = TRUE " _
& "AND SpecimenRequestClosed = FALSE;"

and that you've worked through Doug Steele's steps to ensure there are
no bad references.

But you haven't said whether you're getting the error when the code is
compile or when it's actually running, or what the actual error message
is. Can you give us the details?

Also, did you check out the declaration and instantiation of the object
"dbs"? Make sure that there's an
Option Explicit
declaration at the beginning of each module (including form and report
modules) to require all variables to be declared and avoid potential
problems from typing mistakes. If necessary, use Edit|Find to search for
Dim dbs As
to find where it's declared, and
Set dbs =
to find where it's instantiated.


THe logic lapse I mentioned earlier is that as the code stands, it looks
as if the checkboxes will be checked even if the report is previewed and
then not printed, or if it is sent to the printer but the print job
fails. For safety's sake it might be better to set it up so that the SQL
UPDATE statement isn't automatically executed after the DoCmd.OpenReport
and instead to have a button for user to press, with a caption something
like "Printout OK".

It might be better still to store information about which items were
printed and when in a related table. That way, it would be easy to
"undo" or repeat a particular print job in future (e.g. if a document
was lost or the user erroneously clicked the "Printout OK" button.

John

I looked at the link you sent me and at the references
and could not find anything wrong, but then again I might
be missing something.

Would there be an easy way to do this if I were to start
from scratch on this issue, What I want to accomplish is
the following.

You open up a form which is a list of test to request and
do, when you select which ones you need and click to
preview and print them then exit back to the form these
that were checked off will have automaticaly get a second
check mark which, when you exit this request form then
come back in those items will not show up again on the
list.

I also have something else I want to do but cant figure
out how, if one has a lookup list how to add the "add
item" at the bottom so if an item is not listed you can
add it.

Thanks for the help
Xavier

-----Original Message-----
Hi Xavier,

The first thing I'd do is check that the object "dbs" is being properly
declared and instantiated. It appears to be a global or module-level
variable. Also, I'd check the references in the VBA project. See
http://members.rogers.com/douglas.j.steele/AccessReferenc eErrors.html

Hello

I got the task of converting some DB's I didn't write and
all went well except for one issue I need help with.
There is a form you go into and check the boxes of the
tests you need to request then click on a preview button
which gives you a printable report of those tests, this
action of clicking on the request button (preview form to
print) should automaticaly place new check boxes only on
those records which were checkd off before so they don't
come up again in the future and get duplicated etc.

Below is the code the previous writer used in 97 and it
works in 97 but not in XP.

The error comes up highlighting the dbs in the line that
says dbs.execute "update tests"

Is there an easy way to correct this so it works, in the
mean time I have them checking them manualy till I can
get it fixed.

Any help is greatly appreciated

Thank you
Xavier

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click

Dim stDocName As String

stDocName = "SPECIMEN REQUISITION FORM - Group H"
DoCmd.OpenReport stDocName, acPreview

dbs.Execute "UPDATE Tests " _
& "SET SpecimenRequestClosed = TRUE " _
& "WHERE SpecimenRequest = TRUE " _
& "AND SpecimenRequestClosed = FALSE;"

dbs.Close

Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.