Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: How to pass a form value to a query column to have math operation?

    Hi Gurus,

    I have a initial form to let user enter some required information. After user entering the data and click continue button, it will open another access form with various functions. There is one button in the functional form to display the query.

    I have a query with one column called "result". What I want to do is to pass the value "correction" from the initial form into the query column "result" so the query column will display "result - frminitial.txtcorrection" instead "result" only.

    My purpose is get the "corrected result" --- "result - frminitial.txtcorrection"
    from the query, then pasted the corrected result to Excel worksheet.

    My question is how to pass the form value to the query to do the math operaiton?

    I have tried to make a module with public variables to take the form values.
    If I want pass the module variable to the query to perform math operation, how to do that.

    Thank you so much!

    NewGuy

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Your post would be a lot more readable if you named things, so I edited and made up some names.

    Quote Originally Posted by newguyinaccess
    I have a initial form, InitialForm, to let user enter some required information. After user entering the data and click continue button, it will open another access form, VariousFunctions, with various functions.

    There is one button, DisplayQuery, in the functional form, VariousFunctions, to display the query, SomeQuery.

    I have a query, SomeQuery, with one column called "result". What I want to do is to pass the value "correction" from the initial form, InitialForm, into the query column "result" so the query column will display "result - frminitial.txtcorrection" instead "result" only.
    There are a few ways of passing data from one form to another. But we need to know more about the form the data is coming from.

    I'm guessing that IntialForm is associated with a table that has a primary key. In that case, look at the OpenArgs paramter for the open form method of DoCmd. Once you call that method from InitialForm, code in VariousFunctions can access Me.OpenArgs. It's then a matter of looking up the values from the table. Alternatively, you can pass the actual values, but it's usually easier to just look them up than to encode all that stuff into OpenArgs.

  3. #3
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by sco08y
    Your post would be a lot more readable if you named things, so I edited and made up some names.


    There are a few ways of passing data from one form to another. But we need to know more about the form the data is coming from.

    I'm guessing that IntialForm is associated with a table that has a primary key. In that case, look at the OpenArgs paramter for the open form method of DoCmd. Once you call that method from InitialForm, code in VariousFunctions can access Me.OpenArgs. It's then a matter of looking up the values from the table. Alternatively, you can pass the actual values, but it's usually easier to just look them up than to encode all that stuff into OpenArgs.
    Hi sco08y,

    Thanks for response and let me know the OpenArgs method. I think variousFunctions can get the value from InitialForm by simply do a variable assignment : strVar=[forms]![InitialForm].[txtCorrection.value]????

    What I want to do is not passing various from one form to another. The button to display the query in VariousFunctions form is just for my debugging purpose to see whether the query works. I want to pass the variable from InitialForm to query1 to do math operation.
    There is one column in query1 called result. I want it do "result - correction". How can I achieve it in the query design mode? In the query column result, I type : [table1].[result] - [forms]![InitialForm].[txtCorrection.value] in the query design mode. However, when I click the debug button to display the query data, it doesn't seem to work. that column just display #NAME in that column. I even tried to do a public variable in a module : Public correction as long
    then in the InitialForm, I assign correction =[forms]![InitialForm].[txtCorrection.value], and tried do do that in the query column "result - correction" but still doesn't work.

    Thanks again for your help and time.

    NewGuy

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by newguyinaccess
    [table1].[result] - [forms]![InitialForm].[txtCorrection.value]
    Ak! You're going all crazy with the square brackets. I'm not even sure if this is causing the problem, and outside of a few standard scenarios, it's annoying to pass information from one form to another in Access.

    [txtCorrection.value] should be [txtCorrection].value or just txtCorrection.value. In fact, you don't need square brackets if a name of something meets two criteria: 1. it starts with a letter or _ and everything else is a letter, _ or number (especially: no spaces) and 2. it's not a reserved word. You could name a field or column "txtCorrection.value", so when you say [txtCorrection.value] you're not saying "get me the value property of txtCorrection" but "look up the name txtCorrection.value". Capiche?

    It's a little complicated because VB in Access isn't quite the same as Visual Basic anywhere else... no other software uses the square bracket notation. I suspect that [txtCorrection].[value] would work just fine because Access would just throw out the square brackets after parsing everything... but I don't feel like testing it, and probably never will.

    If that doesn't work, I'll check on an Access project where I figured this out. I think I wound up making an independent code module and stashed stuff there to administrate between various forms. It made me think I should use something other than Access, to be honest.

  5. #5
    Join Date
    Aug 2007
    Posts
    88

    Thanks for educating me.

    Quote Originally Posted by sco08y
    Ak! You're going all crazy with the square brackets. I'm not even sure if this is causing the problem, and outside of a few standard scenarios, it's annoying to pass information from one form to another in Access.

    [txtCorrection.value] should be [txtCorrection].value or just txtCorrection.value. In fact, you don't need square brackets if a name of something meets two criteria: 1. it starts with a letter or _ and everything else is a letter, _ or number (especially: no spaces) and 2. it's not a reserved word. You could name a field or column "txtCorrection.value", so when you say [txtCorrection.value] you're not saying "get me the value property of txtCorrection" but "look up the name txtCorrection.value". Capiche?

    It's a little complicated because VB in Access isn't quite the same as Visual Basic anywhere else... no other software uses the square bracket notation. I suspect that [txtCorrection].[value] would work just fine because Access would just throw out the square brackets after parsing everything... but I don't feel like testing it, and probably never will.

    If that doesn't work, I'll check on an Access project where I figured this out. I think I wound up making an independent code module and stashed stuff there to administrate between various forms. It made me think I should use something other than Access, to be honest.
    Thanks for educating me regarding the square brackets. I learned that from one Access book and I thought that might be a good practice until now. Thanks for letting me know that.

    Actully, I tried to change that query column to "result: table1.result - InitialForm.txtCorrection" (without the quotation mark)
    ,but I still can not get it right.

    Thank you so much for your times and efforts in helping me!

    newguy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •