Results 1 to 11 of 11
  1. #1
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78

    Function string parameter problem using str!

    I have a function which I can call from any form and which logs the user's last action into a table when a form is exited



    I'm trying to include a name from a field on my forms into the the log table

    The form name inserts into the table using:

    rst![strFormName] = strFormName

    I've tried to extend that to another line of code which reads:

    rst![strNAME2] = Forms![strFormName].[NAME2]

    but the function fails without any error and no log entry appears

    If i put an actual form name in such as rst![strNAME2] = Forms![Member_entry].[NAME2] it works fine, but of course fails on a different form.

    what am I doing wrong please?

    I need a global syntax for any form where NAME2 is valid (most of the are)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    have it run an append query
    docmd.openquery "qaAddEvent"

    the query adds the items on the form, no recordsets needed.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    rather confused by your naming - are you saying your rst recordset has fields called 'strFormName' and strNAME2? and you have a form called 'strFormName'

    If i put an actual form name in such as rst![strNAME2] = Forms![Member_entry].[NAME2]
    just a guess but try rst![strNAME2] = Forms(strFormName).[NAME2]



  4. #4
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by ranman256 View Post
    have it run an append query
    docmd.openquery "qaAddEvent"

    the query adds the items on the form, no recordsets needed.
    thank you for suggestion. I will investigate that

    i have the log working. Just have to see if it does what’s needed!

  5. #5
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by Ajax View Post
    rather confused by your naming - are you saying your rst recordset has fields called 'strFormName' and strNAME2? and you have a form called 'strFormName'


    just a guess but try rst![strNAME2] = Forms(strFormName).[NAME2]





    many thanks, that nailed it.
    I can get my user to try it!

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It failed because the bracketing on [strFormName] makes it a domain reference (field, bound control or table). The fact that you don't get any error message makes me think that somewhere prior to this, you turn off warnings. Is that the case? Depending on how such code is written, you can end up turning them off for the entire session without realizing it.
    Last edited by Micron; 10-02-2019 at 01:49 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by Micron View Post
    It failed because the bracketing on [strFormName] makes it a domain reference (field, bound control or table). The fact that you don't get any error message makes me think that somewhere prior to this, you turn off warnings. Is that the case? Depending on how such code is written, you can end up turning them off for the entire session without realizing it.
    yes, I had turned them off (I forgot). I’d been doing something else earlier and was getting warning message overload that I didn’t need!
    all better now and working.

  8. #8
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by Ajax View Post
    rather confused by your naming - are you saying your rst recordset has fields called 'strFormName' and strNAME2? and you have a form called 'strFormName'


    just a guess but try rst![strNAME2] = Forms(strFormName).[NAME2]




    Hi Ajax,

    Thank you for your earlier help.

    strFormName etc are field names in a (work in progress) table logging user actions. I can assign the form/subform names in the call

    I now have the logs pretty much as needed.
    I'm struggling with calls from a subform.
    The code you gave me, I have used to add a name to the log -rst![NAME2] = Forms(strFormName).[NAME2] works fine direct from a master form.
    I also want to pull information using a call from it's subform to add a number. This direct coding works - rst![Pnumber] = Forms![Member_entry]![Member_entry_subform].Form.[APERMNO]

    I'd like to translate that so that strSubFormName is also variable so that I can use elsewher, but I'm struggling with the right syntax

    I've tried various iterations along the lines of - rst![Pnumber] = Forms(strFormName.strSubFormName).Form.[APERMNO] All of which throw up an error of various sorts.

    can you help please?

    Many thanks
    Pete



  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    see this link for how to reference controls in other forms
    http://access.mvps.org/access/forms/frm0031.htm

    Not sure of the context of your code but probably you need something like

    Forms(strFormName).Form.strSubFormName.[APERMNO]

    If you pass a control as a control to your function you can determine the form it is on with

    ctl.Parent.name

    or if not, providing it is the active control

    screen.activecontrol.parent.name

  10. #10
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by Ajax View Post
    see this link for how to reference controls in other forms
    http://access.mvps.org/access/forms/frm0031.htm

    Not sure of the context of your code but probably you need something like

    Forms(strFormName).Form.strSubFormName.[APERMNO]

    If you pass a control as a control to your function you can determine the form it is on with

    ctl.Parent.name

    or if not, providing it is the active control

    screen.activecontrol.parent.name
    Hi Ajax,

    Thank you for replying so swiftly.

    Unfortuately that code didn't work. it threw out Runtime error 2465 application defined or object defined error

    What I'm doing with success from a main form is using rstFormName and rstSubFormName as fieldnames in a event log table.
    My Function is called giving the correct form names and inserts these into those above.

    From both forms I pull values from controls using individual calls from each form:
    from the main form rst![logname2] = Forms(strFormName).[NAME2] That works fine.

    From the subform, i want to put the value held in the control [APERMNO] into the Pnumber field in my log table.
    When I used the normal syntax for referencing a subform control to test it outl; rst![Pnumber] = Forms![Member_entry]![Member_entry_subform].Form.[APERMNO] that worked

    as it's possible to put a main form name into a variable (strFormName) and subForm name into (strSubFormName) I can't the syntax right to get both together with the control [APERMNO] to make it work like the normal syntax

    does that make sense?

    regards and thanks
    Pete

  11. #11
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Hi Ajax
    Many thanks
    Ignore last post, I cracked it.
    a little memory crept back back into my mind from something similar.

    Forms(strFormName)(strSubFormName).Form.[APERMNO] works absolutely fine

    Many thanks for your help

    regards
    Pete

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-05-2017, 04:31 AM
  2. Replies: 6
    Last Post: 06-20-2016, 01:29 PM
  3. Replies: 3
    Last Post: 03-04-2016, 10:36 AM
  4. Parameter string being cutoff
    By bryanwank in forum Programming
    Replies: 1
    Last Post: 07-09-2013, 08:45 PM
  5. How To Pass a String to a Parameter in a Report
    By hammer187 in forum Programming
    Replies: 1
    Last Post: 09-17-2012, 01: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