Macro Not running Correctly 1

[kentwoodjean]

I have 2 Append Queries that I am attempting to run on a Macro. The macro reads as below:

Set Warning (warnings on - no)
Open Query (invenProfFac)
Open Query (CountOfShelfComb)
Set Warning (warnings on - yes)

Problem is that it will only one 1 of the two queries listed above, (CountOfShelfComb)and not the 2nd query. Each one however, will run correctly by itself. Why will they not run under the 1 macro?

 

[ZmrAbdulla]

If you are using Access 2000 or higher then I'd suggest to convert the macros to VBA so that you can add error handler with it.
TOOLS > MACRO > CONVERT MACRO...

Zameer Abdulla
Visit Me (New Look & style)

 

[kentwoodjean]

Did as you indicated. When I run the macro, the query that is not populating appears and I have to close it. What might that mean. PS.........If you respond and I don't get back to you right away, it is beause I am hopping a plane to Florida in a few hours for the week. Was hoping to fix this so the running of the append queries would be a bit easier for my sub.

 

[ZmrAbdulla]

I think I need more information on this.
Are the queries appending the data to a single table? '
How do you run this macro?
Can you post the code that runs the queries?

Zameer Abdulla
Visit Me (New Look & style)

 

[kentwoodjean]

Each query is appending to a a different table. Here are the codes for each of the queries:

INSERT INTO tblFacProf ( FromDate, Prof, Fac )
SELECT [Count Of Pending Prof].FromDate, [Count Of Pending Prof].Prof, [Count Of Fac Pending].Fac
FROM [Count Of Fac Pending], [Count Of Pending Prof];

INSERT INTO tblCountOfShelf ( [Date], Profess, Facility )
SELECT [Count Of Shelf Profess].Date, [Count Of Shelf Profess].Profess, [Count of Shelf Facility].Facility
FROM [Count Of Shelf Profess], [Count of Shelf Facility];

 

[ZmrAbdulla]

I asked the VBA code that you got after converting the macro to VBA

Zameer Abdulla
Visit Me (New Look & style)

 

[kentwoodjean]

sorry. Here it is:

Option Compare Database

'------------------------------------------------------------
' Macro11
'
'------------------------------------------------------------
Function Macro11()
On Error GoTo Macro11_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "Count Of Shelf Comb", acViewNormal, acEdit
DoCmd.OpenQuery "InvenFacProf", acViewNormal, acEdit
DoCmd.SetWarnings True


Macro11_Exit:
Exit Function

Macro11_Err:
MsgBox Error$
Resume Macro11_Exit

End Function

 

[ZmrAbdulla]

Try removing the, acEdit part from the code

DoCmd.SetWarnings False
DoCmd.OpenQuery "Count Of Shelf Comb", acViewNormal
DoCmd.OpenQuery "InvenFacProf", acViewNormal
DoCmd.SetWarnings True

Zameer Abdulla
Visit Me (New Look & style)

 

[kentwoodjean]

I am back from Florida and made the change you suggested. Works great now. Thanks for the tip!

 

[ZmrAbdulla]

Great!! So It is better to have VBA in the first choice for your Access db, Is in't it?


________________________________________
Zameer Abdulla
Visit Me
Hold your child's hand every chance you get.
A time will come when he or she won't let you.