Results 1 to 13 of 13
  1. #1
    kmschepis is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    7

    Form Parameters Bug


    Hello all,

    I have an issue where queries are pulling empty values off of a form despite the fact the form has the value set.

    EX:

    the query "select [forms]![form_name]![field_name] as val]" returns a table with 1 column called val, and one row with a blank value.

    However in the the immediate window if I type:

    debug.print [forms]![form_name]![field_name] it print's the expected value that the field is set to.

    I can't for the life of me figure out what is going on here or why this query suddenly stopped working.

    Any help would be huge.

  2. #2
    kmschepis is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    7

    Form parameters not returning value (repost from programming)

    Hello all,

    This is a repost from the programming section as I think it may better fit here.

    I have an issue where queries are pulling empty values off of a form despite the fact the form has the value set.

    EX:

    the query "select [forms]![form_name]![field_name] as val]" returns a table with 1 column called val, and one row with a blank value.

    However in the the immediate window if I type:

    debug.print [forms]![form_name]![field_name] it print's the expected value that the field is set to.

    I can't for the life of me figure out what is going on here or why this query suddenly stopped working.

    Any help would be huge.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Val is a reserved word. Change to something else.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I see this occasionaly. I fix it by converting it to string:
    cstr([forms]![form_name]![field_name] )

    or a number : CINT(...)

  5. #5
    kmschepis is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    7
    Val is just used as an example here, in reality we are doing a join on this value, in debugging I was able to track the issue down to this being empty. the name of the column makes no difference. the issue is that it doesn't pull the value from the textbox that contains it.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    kmschepis is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    7
    The expression is typed incorrectly or is to complex to be evaluated....

    I checked the TypeName in the immediate window and got textbox
    then the TypeName of the value and got String

    My coworker who is trying to reproduce this was able to get your solution to work though.

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I merged your threads since both had answers. In the future, you can report your own post and ask that the thread be moved.

    It may be a typo but there's an extraneous bracket at the end:

    "select [forms]![form_name]![field_name] as val]"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    kmschepis is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    7
    TY @pbaldy and yes that's just a typo as I have abstracted the code a bit.

    When I stop the debugger, the query's behavior reverts to what is expected. There must be something in the subroutine that is messing with access's ability to pull values from forms.

    Maybe @micron is on the right path here... any idea what could cause this?

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I got this to work as a test:

    SELECT trim(forms!frmRegister!cbotrantype) as val

  11. #11
    kmschepis is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    7
    This is actually turning out to be weirder than I expected:

    This query runs after an GotFocus() subroutine.

    When I put a breakpoint at the first line in the subroutine I see this behavior from the query.
    However when I stop the VBA and return to the normal access state the query functions as expected.
    Further I can't pull any value from any loaded form

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    What does ‘after’ mean? Do you really mean as part of a got focus event? Or some other event?

    Difficult to provide sensible answers when very little information is provided and then only abstract

  13. #13
    kmschepis is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    7
    Yes, the subroutine is part of a GotFocus event, but that doesn't seem to be the real issue.
    I was in break mode trying to debug why a query that runs during this event was not inserting new rows into a SQL backend.
    When I manually ran the select portion of the insert query I saw that I was not able to return any rows. so I checked the conditions to see what one caused the error and noticed that I was not pulling values off of the form properly.
    The fact that You cannot retrieve value from a form in a query while in break mode threw me down the wrong path and was ultimately inconsequential, but is an interesting bug to take note of none the less.
    It seems the values are correctly populated when the queries are run from a doCmd.OpenQuery call, just not when manually run during break mode.

    The real issue was that the table we were inserting into had a DML trigger that was throwing an error. This error only bubbled back up to access as a warning(rather than an error which would have triggered our error handling) that rows were not inserted due to a Primary Key violation.
    Early in this process we turn warnings off, and as I did not see an error I thought it was an issue wit the query not fetching the proper results, an assumption that was compounded by the bug described above pointing me in that direction.\

    tldr
    Make sure you turn on warnings when debugging a query, they contain valuable information.

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

Similar Threads

  1. Replies: 11
    Last Post: 06-20-2020, 10:25 AM
  2. Replies: 1
    Last Post: 02-27-2018, 11:39 PM
  3. Replies: 1
    Last Post: 10-04-2013, 02:52 PM
  4. Getting parameters from a form
    By Abarency in forum Access
    Replies: 1
    Last Post: 10-19-2012, 11:38 PM
  5. Passing parameters from a form
    By rfs in forum Forms
    Replies: 1
    Last Post: 03-15-2011, 12:25 AM

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