Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281

    Getting Error Trying to run a Query from an Embedded Macro

    Greetings...

    I'm trying to run a Sub Routine from an embedded Macro in Access...

    In the macro I'm using the Action: RunCode which requires you to build a Function.

    So I have built a function to call the VBA routine, which happens to be a query.

    The Error I'm getting is:

    "The object doesn't contain the automation object 'SendToPrintTable'
    You tried to run a Visual Basic procedure to set a property or method for an object. However the component doesn't make the property or method available for Automation operations
    Check the components documentation for information on the properties and methods it makes available for Automation operations."





    Here is my Function:
    Code:
    Function SendToPrintTable()
    Call CreatePrintRecord
    End Function
    And here is my sub Routine
    Code:
    Private Sub CreatePrintRecord()
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblUINPrintRecords VALUES('" & Me.TxtUIN.Value & "','" & Me.TxtFaxNum.Value & "','" & Me.TxtRecipient.Value & "', #" & _
    Me.TxtRqstDate.Value & "#,'" & Me.TxtRqstProvName.Value & "','" & Me.TxtPatName.Value & "','""" & "');"
    DoCmd.SetWarnings True
    End Sub
    Thanks for any help with this one....

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, SendToPrintTable would have to be a public function in a standard module. The other one would also have to be public to be called from outside the form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hello Pbaldy - Good to hear from you...

    I changed both my function and sub routine to public, but I'm still getting the same error

    I copied the Query into the click event on a button to test and it works perfectly outside of the macro.

    Head, still shaking... embedded macro's it seems are both amazing and a bane...

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Where is the function called by the macro? It has to be in a standard module, not a form/report module.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Yes, I did place both the Function & the Sub Routine in a standard module

    Perhaps if I explain further what I am trying to do - maybe there is a better way to do this

    The button being clicked is a button the user clicks when they are done entering a record and the embedded macro is the 'New Record' macro

    The query at the end of this macro is supposed to take certain data values of the main record and put them into another table from which other users will review and then print the records.

    All of the research I have done has led me to using this method of using a query within a macro... but, if you can think of a better way, please for the Love of God let me know.

    Thanks PBaldy...

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm confused, because CreatePrintRecord can't be in a standard module (uses "Me"). Would a flag (yes/no field, status field) in the main record work for your review? It's unusual to put copies of a record in another table, and normally shouldn't be done.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    My bad - I misunderstood your original instructions....

    Okay, so I now have the function in a standard module, and CreatePrintRecord in the Form module

    I have also moved the query to the top of the firing order in the macro, still can't shake the error.

    As to your question, Yes! A Yes/No - True/False in the main record would work but I have no idea how to do that from within an embedded macro (I thought it would be easier the way I was doing it...obviously not)

    The thing is, is that all of the No/False records would show on a query to the agents whom do the reviewing and printing but then once the record is printed it would have to change the value in the main record from No to Yes or from False to True

    Trying to figure out how to do that inside an embedded macro left me dangling from a bridge

    If you have some idea's to get me started - I'm listening intently...Thanks Again...

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it's part of the main record you don't have to do anything with the macro. It's just another field in your main record. You could have the default value be True or False as appropriate. Then on a form that pulled only those records, you could change the value when the agent was done with it, which would prevent it from showing up for review again. In other words, if the default value was False, your edit form would pull records where that field was false. When the agent reviews it, you change the value on the form to True.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Ok, I set it up as you said and so far it works great! - Thank You PBaldy...

    What I did was simply add the Yes/No field with the default of No I then created a splash form with a subform which populates with all of the non-printed records

    Now - how to select a record in this subform then click a button and trigger the print sequence... At the moment I am getting two Parameter requests

    The first parameter request is for the value of the RefNum field on the SubForm (which I have tried to reference in the query as the field name and as the column number)

    The second parameter request is for the value of the TxtBox on the data entry form - That one I have no idea why it is looking for that TxtBox value except perhaps because that control on the DEntry form

    Is linked to that field in the table I'm trying to create the report from??

    Anyway, Now that I have all the non-printed records showing up in the subform I just want to select them (one at a time for now - I'll batch print later) and click a button and print

    So I guess my question is now how to get rid of these pesky parameter requests?

    Thanks Again PBaldy...

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I lean towards this method:

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

    but form references in the report's query should work, providing the form is open and the form/subform reference is correct.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    BaldyWeb... Very cool...

    I think your last sentence provides the problem... The DEntry form where the TxtRefNum control resides won't be open.

    The way this process is set-up is we have one group of agents doing the data entry and another group of agents doing the reviewing and printing

    Thus when the reviewing/printing agents open the interface I would like for them to do their reviewing/printing without ever opening the actual data entry form.

    So, there will be a data entry form and another form (the subform at this point) where the non-printed records can be reviewed then printed.

    I hope that makes sense - honestly, though, I'm not sure anything about this project makes sense

    Thanks Paul (finally saw your name )

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you want the report to work from different places, I'd use the method in the link to pass the criteria. Otherwise you'll need to use a form that will be open all the time, and perhaps copy values to hidden controls on it that the query points to.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Paul...

    Not entirely sure what you mean by 'pass the criteria' (I'm thinking this is the WHERE criteria) - but I'm not sure how to pass it to make what I am trying to do - work

    In any case, I have abandoned the idea of using a sub form in favor of using a ListBox and a click event

    Thus, the issue I am having now is the following error which I can't seem to get rid of.

    "Run-Time error '3085' - Undefined Function '[Forms]![FrmUINPortSplash].ListUnPrinted.Column' in expression"

    It seems no matter how I write the below line of code I receive the same error - even if the function is not written as it appears in the error message.

    Here are the two ways I have tried to write this line of code... (I actually did write it with the [Forms] reference as well...same results)
    Code:
    DoCmd.OpenReport "RptUINFaxDocPP", acViewPreview, , "TblUINPort.RefNum = Me!ListUnPrinted.Column(0)"
    DoCmd.OpenReport "RptUINFaxDocPP", acViewPreview, , "TblUINPort.RefNum = '" & Me!ListUnPrinted.Column(0) & "'"
    Thanks Paul...

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By passing the criteria I mean taking them out of the query and using this method. The second should work if the field is text, but did you take the form references out of the query?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Update...

    At least you know I'm trying to win this thing...

    Okay - so I found the culprit to the error - the function referred to in the error message was in the criteria of the query to the report - once I cleared it - error gone

    But now, back for its 15th encore is the parameter request for the value of the textbox TxtRefNum found on the data entry form which, of course is not open and thus has a value of Null...

    So now I'm back to what I believe you were mentioning above - passing the criteria -

    I've tried the following 2 lines of code - the first one produced the parameter request 24 times

    The second one produced the Undefined Function error again - I'm thinking an abacus would be faster than Access - sorry, I'm just beyond the end of my wits

    Code:
    DoCmd.OpenReport "RptUINFaxDocPP", acViewPreview, , "[Forms]![FrmUINPortDataEntry].TxtRefNum = '" & Me!ListUnPrinted.Column(0) & "'"
    DoCmd.OpenReport "RptUINFaxDocPP", acViewPreview, , "[Forms]![FrmUINPortDataEntry].TxtRefNum = Me!ListUnPrinted.Column(0)"
    Thanks for any help I can get with this

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-11-2018, 12:57 PM
  2. Embedded Macro
    By balajigade in forum Macros
    Replies: 6
    Last Post: 04-02-2014, 05:28 AM
  3. VBA to Embedded Macro
    By mrmims in forum Macros
    Replies: 4
    Last Post: 03-11-2014, 01:28 AM
  4. Replies: 2
    Last Post: 06-30-2013, 09:59 AM
  5. Run Embedded Macro
    By smikkelsen in forum Forms
    Replies: 0
    Last Post: 07-07-2010, 09:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums