Getting Error Trying to run a Query from an Embedded Macro

[pbaldy]

The form reference must be concatenated. The first argument is simply the field name in the report's source. If that field name is RefNum:

DoCmd.OpenReport "RptUINFaxDocPP", acViewPreview, , "RefNum = '" & Me!ListUnPrinted.Column(0) & "'"

If you're still stuck, can you attach the db here?

[RunTime91]

Paul...Thank You so much for not giving up on me...

I guess we were typing at the same time...

No matter how I write this code and whether I use a subform or a listbox - Access continues to want the value of a textbox from a form that is not even open.

The only connection here is that that txtbox has as its controlsource the field in the table from which the report is created

So do I have to create two tables? One strictly for running reports and one strictly for storing data??? -

Paul can you help me understand why Access is relentlessly asking for this parameter - which at this point is not referenced in any query any report and in any code...

In my mind I am bypassing all of that - but apparently I'm not...

[pbaldy]

Did you change the code to what I showed in post 16? Can you attach the db here?

[RunTime91]

Hey Paul...

Yes, I did change it - worked great! But I stopped trying to get it to work from within an embedded macro. I created new queries designed to work specifically with the new form.

There was still an error popping up but I fixed that by changing the reference in the queries criteria from .Column(0) to .Value (not sure why that worked, but it did??)

At the moment nearly everything is working as I need it to. I've just a couple more barriers to completion

The first one of which is I am back to wrestling with trying to get an update query to run from...Yep, an embedded macro.

Here is what I have in a simple standard module

Public Function ResetPrintStatus()
Call ResetPrintStatus
End Function

And here is what I have in the form module of the form which has the button I am clicking

Private Sub ResetPrintStatus()
  Dim MySQL As String
    MySQL = "UPDATE [tblUINPort] SET [Printed]= False WHERE [RefNum]= '" & Me.TxtRefNum.Value & "'"
  CurrentDb.Execute MySQL
End Sub

The idea here is pretty simple

If any changes are made to a record which was already printed I want to update the Print status (Thank you for that, Paul) back to False so that it will show up on the records not printed search.

The embedded macro is a simple Save macro and the Action I added is the RunCode Action - Which of course asks for the name of a function.

As for downloading the Db - Yes, I will send you a copy - I will need to export it out as it is part of a larger (Huge actually) corporate database. Fortunately, all tables at the moment are local.

Thanks Paul

[RunTime91]

Update

I created a simple UPDATE Query and changed to Macro Action to: OpenQuery

Works great! Except now I have to deal with Microsoft's Update Notices - which for me is no big deal but for the users - That's another story.

Anyway to suppress the notices form within a macro?

Thanks...

[pbaldy]

SetWarnings are also in macros:

http://www.baldyweb.com/SQLWarnings.htm

[RunTime91]

Paul - Good Morning...

After several attempts I have abandoned my efforts to try to get this to work within an embedded macro.

It would appear the macro action item for setting warnings is simply not available (probably something to do with corporate firewalls)

I have instead opted for the below code which, at least with limited testing seems to work well.

DoCmd.SetWarnings False
  DoCmd.RunCommand acCmdSaveRecord
  DoCmd.OpenQuery "qryUINPrintStatusUpdate"
DoCmd.SetWarnings True

I'll let you tell me if the above is the best way to do this - hopefully it is a suitable replacement method for trying to accomplish this through an embedded macro.

Also, I have exported all the objects into a stand alone Db and will send it to you for your review

I know there are instructions here on how to do this, If I get stuck I will reach out for help.

Paul - Can't Thank You enough - Although I'm sure I'm not done getting beat-up with this project so please don't run too far.

[pbaldy]

I think you're better off learning VBA anyway; I never use macros. That said, there's a "Show all actions" icon on the ribbon that may have made the warnings action appear. I don't think the firewall affects those options.

I'm happy to have helped!