Results 1 to 15 of 15

Thread: Row Value

  1. #1
    Join Date
    Aug 2002
    Posts
    28

    Question Unanswered: Row Value

    hello,

    I am trying to simulate an online processing system in access.

    The idea is that I have a main form which has the standard unbound search box and a command button. when you type a value in to the text box it displays a continuous sub form of the results.

    This I have done ok. however. I want a button on each row of the continuous subform which will add the product ID from that row to a temporary table (Virtual shopping cart).

    Any Ideas on how this can be done.

    thanks in advance

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    have you tried adding the button to the subform? In continus form setting I think that it will repeat the button on every row, though thats just me therorizing seeing how i haev neaver done it, and I still havent walked threw how the button knows wich record to add. First step see if the button is duplicated or not, because if that doent work you may be able to put a comand on the on click event of the product id text box to make it add it that way then make it look like a hyperlink.
    anways let us know
    Jim

  3. #3
    Join Date
    Aug 2002
    Posts
    28
    It is possible to add a unique text box to every row. I think the problem will be A) how to get the product ID for every row passed on to the text box. And B) which function to put the code behind. I.E Onclick.

    Any ideas?

    Thanks for your reply

  4. #4
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by Paul Niland
    It is possible to add a unique text box to every row. I think the problem will be A) how to get the product ID for every row passed on to the text box. And B) which function to put the code behind. I.E Onclick.

    Any ideas?

    Thanks for your reply
    put the code in OnClick

  5. #5
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by JDionne
    have you tried adding the button to the subform? In continus form setting I think that it will repeat the button on every row, though thats just me therorizing seeing how i haev neaver done it, and I still havent walked threw how the button knows wich record to add. First step see if the button is duplicated or not, because if that doent work you may be able to put a comand on the on click event of the product id text box to make it add it that way then make it look like a hyperlink.
    anways let us know
    Jim
    If the common button is added to the subform it is repeated for each record. If you want to add the record corresponding with the button you would type this code under OnClick or OnEnter
    In this code the Query or Table is the Query or Table that the subform is link to.

    DoCmd.RunSQL "INSERT INTO TableName ( FieldName )SELECT Query or Table.FieldName FROM Query or Table WHERE (((Query or Table.Textbox)=" & [Forms]![FormName]![subform]![TextBox] & "));"

    good luck!
    tjacobs

  6. #6
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by tjacobs
    If the common button is added to the subform it is repeated for each record. If you want to add the record corresponding with the button you would type this code under OnClick or OnEnter
    In this code the Query or Table is the Query or Table that the subform is link to.

    DoCmd.RunSQL "INSERT INTO TableName ( FieldName )SELECT Query or Table.FieldName FROM Query or Table WHERE (((Query or Table.Textbox)=" & [Forms]![FormName]![subform]![TextBox] & "));"

    good luck!
    tjacobs
    correction!

    DoCmd.RunSQL "INSERT INTO TableName ( FieldName )SELECT Query or Table.FieldName FROM Query or Table WHERE (((Query or Table.fieldname)=" & [Forms]![FormName]![subform]![TextBox] & "));"

  7. #7
    Join Date
    Aug 2002
    Posts
    28
    Thanks. i will give it a try

  8. #8
    Join Date
    Nov 2002
    Posts
    14

    Re: Row Value

    In you subform, you cannot use datasheet view, you must use continuous as you say. In the subform detail, add a command button and add code for the "On Click" event like the following:
    '-------------------------
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblShoppingCart ( ProductID ) SELECT " & Me.ProductID & " AS Expr1;"
    DoCmd.SetWarnings True
    '-------------------------

    The SetWarnings prevents the user from getting the standard message telling them that they are appending a record.

    The RunSQL appends a record to the table tblShoppingCart, field ProductID. Me.ProductID is the name of the field on the subform that contains the Product ID.

  9. #9
    Join Date
    Aug 2002
    Posts
    28

    Talking

    thanks this code is great. It works perfectly fine.

    However I would like to ask for some help, one last time.

    Now I have got this to work I would like to add some other values into my table. Currently it just fires the productID into the temporay table. I would also like to add the CUSTOMERNO and the ORDERID which is located on the Main form.

    I tried :

    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ProductID ) SELECT " & Me.ProductID & " AS EXPR1;"

    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ORDERNO ) SELECT " & FORMS!MAIN!ORDERNO & " AS EXPR2;"

    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( CUSTOMERNO ) SELECT " & FORMS!MAIN!CUSTOMERNO & " AS EXPR3;"

    This works fine but puts the values in seperate rows. How can I nest this SQL so it all goes in to one row.

    Thanks Again.

    Paul

  10. #10
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by Paul Niland
    thanks this code is great. It works perfectly fine.

    However I would like to ask for some help, one last time.

    Now I have got this to work I would like to add some other values into my table. Currently it just fires the productID into the temporay table. I would also like to add the CUSTOMERNO and the ORDERID which is located on the Main form.

    I tried :

    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ProductID ) SELECT " & Me.ProductID & " AS EXPR1;"

    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ORDERNO ) SELECT " & FORMS!MAIN!ORDERNO & " AS EXPR2;"

    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( CUSTOMERNO ) SELECT " & FORMS!MAIN!CUSTOMERNO & " AS EXPR3;"

    This works fine but puts the values in seperate rows. How can I nest this SQL so it all goes in to one row.

    Thanks Again.

    Paul
    Try This

    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ProductID, ORDERNO,CUSTOMERNO ) SELECT " & Me.ProductID & " AS EXPR1, FORMS!MAIN!ORDERNO & " AS EXPR2, FORMS!MAIN!CUSTOMERNO & " AS EXPR3;"

    tjacobs

  11. #11
    Join Date
    Aug 2002
    Posts
    28

    Question

    Thanks for your quick reply. However I had no joy, the code only got as far as:

    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ProductID, ORDERNO,CUSTOMERNO ) SELECT " & Me.ProductID & " AS EXPR1, FORMS!MAIN!ORDERNO & " AS

    This part would not go in for some reason.

    EXPR2, FORMS!MAIN!CUSTOMERNO & " AS EXPR3;"

    Any ideas?

    Thanks,

    Paul

  12. #12
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by Paul Niland
    Thanks for your quick reply. However I had no joy, the code only got as far as:

    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ProductID, ORDERNO,CUSTOMERNO ) SELECT " & Me.ProductID & " AS EXPR1, FORMS!MAIN!ORDERNO & " AS

    This part would not go in for some reason.

    EXPR2, FORMS!MAIN!CUSTOMERNO & " AS EXPR3;"

    Any ideas?

    Thanks,

    Paul
    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ProductID, ORDERNO,CUSTOMERNO ) SELECT " & Me.ProductID & " AS EXPR1, "& FORMS!MAIN!ORDERNO &" AS EXPR2, "& FORMS!MAIN!CUSTOMERNO &" AS EXPR3;"


    Maybe this will work
    tjacobs

  13. #13
    Join Date
    Aug 2002
    Posts
    28

    Cool

    Magiiiiiiiiic. Thankyou for all of you help and the quick replys. Mabe when I learn to code. I will return the favour

    Paul

  14. #14
    Join Date
    Nov 2002
    Posts
    14
    Originally posted by Paul Niland
    Magiiiiiiiiic. Thankyou for all of you help and the quick replys. Mabe when I learn to code. I will return the favour

    Paul
    Paul, here's something to keep in mind for future reference. You have to be aware of the data type to properly write these statements.

    For instance, let's say ORDERNO is text (not a number). You then have to add ' in front of the first " and behind the second " like this:
    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ProductID, ORDERNO,CUSTOMERNO ) SELECT " & Me.ProductID & " AS EXPR1, '"& FORMS!MAIN!ORDERNO &"' AS EXPR2, "& FORMS!MAIN!CUSTOMERNO &" AS EXPR3;"

    Or is CUSTOMERNO is a DATE instead of a number, you would use a # like this:
    DoCmd.RUNSQL "INSERT INTO tblShoppingCart ( ProductID, ORDERNO,CUSTOMERNO ) SELECT " & Me.ProductID & " AS EXPR1, "& FORMS!MAIN!ORDERNO &" AS EXPR2, #"& FORMS!MAIN!CUSTOMERNO &"# AS EXPR3;"

  15. #15
    Join Date
    Aug 2002
    Posts
    28
    Thankyou. That was very usfull I will bare it in mind.

    Cheers.

    PNILAND

Posting Permissions

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