Problem with access messages on queries

[gstylianou]

Good evening to all,




Guys I'm stuck on an issue that I am sure for many of you it may seem slightly " deaf " but unfortunately either my mind is stuck or....i don't know whats happened.


Well , I have written the below vba code so that, firstly to run the first without any messages and then the second one as well as without the access messages.


The problem is while I've written correct the code ( I think ) so to run the queries messages without the access messages, however the messages still displayed and indeed the first query displayed normal..


Following, is the code and i will appreciated if i would have your lights again

Private Sub LabelNewCustomers_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTransferCmrDetails2", acViewNormal
DoCmd.SetWarnings True

DoCmd.RunCommand acCmdRefresh

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTransferData2", acViewNormal
DoCmd.SetWarnings True
DoCmd.OpenForm "fTempTable", acNormal, , , , acHidden

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmCustomerDetails"
stLinkCriteria = "[Customer No]=" & Me![Customer No]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec

End Sub


Thanks in advanced

[ranman256]

FYIs
1st....DoCmd.OpenQuery "qryTransferCmrDetails2"
the acViewNormal is not needed.

2.
The: DoCmd.SetWarnings False
is only needed once at the beginning of the process
and a single DoCmd.SetWarnings True at the end. No need to keep turning them on and off every step.

3. A good custom is to use query names that show the action of it... qryTransferData2 ..the qry tells you nothing of the query in the code..
better would be quTransferData2 for update, qsTransferData2 for select, qm for make, qa for append, etc.

Now the above is only FYI, but I still did not understand your question above. What exactly is the problem?

[gstylianou]

Hi my friend ramman,

Thanks for the information's. The problem is: I have two queries which i wanted to run without to give any access messages and of course without to open as usual mode (like a table). In my case when i'm trying to do that with the attached code i didn't take any messages but the first query appear normally (as a table mode..) and of course i don't want it..

Any idea?

[pbaldy]

Is the first query just a SELECT query that perhaps the other 2 depend on? If so, you don't need to open it at all. Access will do that internally.

[June7]

As Paul says, SELECT query objects don't have to be opened.

If the query objects are action queries (UPDATE, INSERT, DELETE) can use:

CurrentDb.Execute "qryTransferCmrDetails2"

And the DoCmd.SetWarnings lines are then not needed at all and no warnings will pop up with the Execute method.

[pbaldy]

can use:

CurrentDb.Execute "qryTransferCmrDetails2"

And the DoCmd.SetWarnings lines are then not needed at all and no warnings will pop up with the Execute method.

Not if there are form references in the query.

[June7]

Good point.

Since I don't use dynamic parameters in queries, not an issue I deal with.

I also use an SQL statement instead of referencing a query object.

[Geo21]

What kind of messages do you get?

[June7]

The messages are warnings that edits to table will occur - such as insert, delete, update records. These are not error messages but are nonetheless often not appreciated.

[Geo21]

But is he getting these warnings, or error warnings?