Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update a set of products (Access 2000)

    I have a code that updates the field branch for a given product.Sometimes it happens however that i need to correct
    the branch not for one product, but for5 or even 8 products.i wonder how can i do that.The form is based on the tables
    TblProducts as a table that stores my correction.Obviously i have to refer to the second row and the next rows in this
    table.I am sending the attachment.Can you help me build the code ?
    Below is my present code for only one product:

    Dim strBranch As String
    Dim PN As String
    Dim strcartons As String
    PN = Me!ProductNumber
    strcartons = Me!cartons
    strBranch = "UPDATE Products SET Branch= " & strcartons & " WHERE ProductID = " & PN
    CurrentDb.Execute strBranch

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update a set of products (Access 2000)

    That's a very inefficient design. I would create a single or continuous form based on the Products table. The user can edit the Branch field directly.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a set of products (Access 2000)

    yes, you are right.I will have to have a recourse to a continous form in order to update more than one product.
    In order not to bother you with much details i have tried to summarize my problem.I think i will expand on it in order to receive the help i need.
    The process of our work is in two parts.Part one belongs to the superviser, and he looks after the condition
    of the branches in the different cities.When some product is not in order, he sends the correction via the present method.
    He sends only the table tblProducts,containing the number of the product and the number of the cartons or items.
    Then with the OnOpen event of the form the table Products is corrected depending on the chosen city.In my example for a simplicity i do not use the OnOpen event but OnClick event.
    Our method works fine,it is just that we cannot send a correction for more than one product.
    After your answer i understand that it is necesary to build a continous form.My question is, can you help me building the
    update code depending on the number of the products chosen?
    For example, i want to update the following products:

    product number cartons

    1 2

    14 8



    With our present method we have to send 2 mails.In the first mail we are wirting 1 and 2 and in the second mail 14 and 8
    But i want to be able to correct more than on product.



    Thank you in advance

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Update a set of products (Access 2000)

    I don't understand what you are trying to achieve.
    Have you got a head office and various branches around the country.
    Does the product at head office (I presume it's at HO) contain the details that need to be sent to the branches for them to update their product files.
    If I have described it correctly, then you need a yes/no flag on the product at HO that signals if a change has been made. You then extract only those products with the change flag set into an excel file. You then send that excel file to the various branches for them to update thier product files.

    If I am on the wrong tram, you had better set me straight.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a set of products (Access 2000)

    Yes, thats is right, we have a head office.I think i should work on basis of the suggestion made by Hans
    to build a continous form.I am giving an example with my new form called continous form in the attachment.here i am trying to
    apply the suggestion i have received.
    In my example the table tblproducts consists of the products 1,2 and 4.My question is, how can i include these product numbers in the variable PN
    If i succeed than the code behind the button update will work and i will send the right products for correction.
    Here is a part of the code, the rest of the code is under the control update in the form contious form.

    Dim PN As String
    Dim strcartons As String
    PN = Me!ProductNumber
    In short i want that the PN contains the product numbers of the table

    thank you in advance

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Update a set of products (Access 2000)

    Where is the attachment?

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a set of products (Access 2000)

    I am applying the attachment.If you use the form frmRefurbishProducts, you will see that i have chosen 2 cartons.When you click the button, you will receive 2 cartons for product Nr 1 in the table products.
    What i want now is to work with the second table called Continous form.In this table i have chosen the following

    number of product cartons

    1 1
    2 2
    3 4

    the above is only an example to show how i proceed

    So i want with the click event of this form to enter these cartons for these products into table products.To this end i have to modify my code and especially the variable Pn.

    i will be very grateful indeed if you help me create this code

    kind regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update a set of products (Access 2000)

    I would create and run an update query

    UPDATE TblProducts INNER JOIN products ON TblProducts.ProductNumber = products.Productid SET products.branch = TblProducts.cartons;

    If you save this query as qryUpdateBranch, you can run it directly from the database window, or from the On Click event of a command button on a form:

    Private Sub cmdUpdate_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryUpdateBranch"
    DoCmd.SetWarnings True
    End If

    (The form does not need to have a record source, it only needs the command button)

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a set of products (Access 2000)

    Dear Hans,

    I consider the suggestion as excellent.Really i do not need all those forms and methods as i used up to now
    and you were right to say at the beginning that my construction seems to you ineffiecient.
    I have made the update query on the onclick event of the form NewForm.However, i get the message
    "Too few parameters.Expected1.And on the field branch of the table products is not filled with the cartons of the table
    TblProducts in the correspponding productid number.
    What may be the reason for that ?
    I am applying the attachment.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update a set of products (Access 2000)

    The problem is that your attachments are not consistent.

    In the previous attachment (to <post#=476867>post 476867</post#>, the table TblProducts contained a field named ProductNumber. In the database attached to your most recent post, the same field is named ProductID. So you should use ProductID instead of ProductNumber in the SQL:

    SQL = " UPDATE TblProducts INNER JOIN products ON TblProducts.ProductID" & _
    " = products.Productid SET products.branch = TblProducts.cartons"

  11. #11
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a set of products (Access 2000)

    yes, i have tried it and of course everything went on its place ! I appreciate very much this approach because i have tackled it from the
    wrong end. Interesting, the more one doesnt know things ,as myself,the more comlicated the approach is.
    So,please accept my great appeciation

    regards

Posting Permissions

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