Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2000 update (Access 2000)

    Hello,
    I am new to Access 2000 and am having trouble with updating a single record. When I say update what I mean is I would like to take the original records data and add to it. This is for stock on hand adjustments. I am not sure if I should use an update query or do it programmatically. The table I would like to update to is called tblParts and the field within this table is called UnitsInStock. If at all possible I would like to include some sort of user criteria for selecting the PartNumber's quantities(UnitsInStock) that need to be updated. I am sorry if this is confusing but I HAVE NOWHERE ELSE TO GO. Any help will be very much appreciated.

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

    Re: Access 2000 update (Access 2000)

    I'm sorry, it isn't clear to me what you want.
    <hr>When I say update what I mean is I would like to take the original records data and add to it.<hr>
    Does this mean that you want to modify the existing data, or that you want to create a new record with the modified values?

    Normally, you would modify existing data in a form based on the table that contains the data.

  3. #3
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 update (Access 2000)

    Hans,
    Thank you very much for replying to my post. What I need to do is to be able to add or subtract from the UnitsInStock to reflect accurately what we have in stock. Again the table that holds the data is called tblParts and the field I would like to update is called UnitsInStock and the field that is unique is called PartNumber. Again any help you can give would be very much appreciated

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

    Re: Access 2000 update (Access 2000)

    Please bear with me, I'm probably going to ask a long series of annoying questions.

    Is the updating going to be a manual process, i.e. the user sits behind the PC, locates (in a way to be discussed) the appropriate PartNumber, then specifies the number to be added to or subtracted from UnitsInStock?
    Or is it something automatic or semi-automatic, i.e. the user receives a data table that holds the mutations that have to be applied some way or other to the data?

  5. #5
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 update (Access 2000)

    Hans,
    I have no problem with annoying questions especially because you are trying to help me out. (which is very much appreciated)
    The application we would like to use is the user picks a PartNumber then Adjusts the UnitsInStock. Again any help is greatly appreciated.

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

    Re: Access 2000 update (Access 2000)

    You can design a form based on the table. If you wish, you can use the AutoForm: columnar option or let the Wizard help you create the form. Make sure to include the PartNumber and UnitsInStock fields.
    Next, make sure that the Control Wizards are on (the "magic wand" button on the Toolbox)
    Place a combo box in the form header section of the form. The Combo Box Wizard will appear.
    Select the third option (Find a record ...) and click Next.
    Follow the instructions; the PartNumber is the field you want to use.

    The user can locate the record for a PartNumber through the combo box. He/she can then adjust the UnitsInStock field and either close the form or move on to another PartNumber. Access will automatically save the changes.

  7. #7
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 update (Access 2000)

    Hans,
    I have a form that already does exactly what you discribed. For some reason the people under the stairs want a form/query/whatever in which there are the fields: PartNumber (this is the unique field), UnitsInStock and AdjustmentQuantity. They then want the Form/Query/Whatever to do the calculations and then update the UnitsInStock field to express the adjustment in inventory. I really appreciate you answering my posts and any additional help you can give me would be very much appreciated.

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

    Re: Access 2000 update (Access 2000)

    How do they want to perform the adjustment? Should the form also contain a command button "Adjust Now" or something like that? If so, the On Click event procedure for this button could be

    Private Sub cmdAdjust_Click()
    If IsNull(Me.AdjustmentQuantity) Then
    MsgBox "You didn't enter a quantity, you ...!", vbExclamation
    Else
    Me.UnitsInStock = Me.UnitsInStock + Me.AdjustmentQuantity
    End If
    End Sub

  9. #9
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 update (Access 2000)

    Hans,
    As far as the form goes do I need to push or pull the data in. The reason I ask is because it seems that the PartNumber and the UnitsInStock need to be linked in some matter so that when I select the PartNumber via a combo box that it will also display, via a textbox the UnitsInStock. Also as far as the AdjustmentQuantity field. Does it need to be an entry in a table or can this be an unbound textbox. You have bee nof great help and any additional help you can provide will be very much appreciated.

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

    Re: Access 2000 update (Access 2000)

    You can use an unbound combo box to look up the PartNumber. The Control Source of this combo box is blank, and its Row Source is a query or SQL string that selects the PartNumbers from the table. There is code in the After Update event of the combo box to find a record. If you create the combo box with the Combo Box Wizard, it will take care of all the technical details for you.

    You should use a bound text box for UnitsInStock, i.e. the Control Source of the text box is the UnitsInStock field.

    There is no need to have a AdjustmentQuantity field in the table; you can use an unbound text box for this value (its Control Source is blank.)

  11. #11
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 update (Access 2000)

    Hans,
    Thanks I will try your suggestions

  12. #12
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 update (Access 2000)

    Hans,
    I get an error in the code when I run the command button here is the code:

    Private Sub Command6_Click()
    If IsNull(Me.AdjustmentQuantity) Then
    MsgBox "You didn't enter an adjustment quantity"
    Else
    Me.UnitsInStock = UnitsInStock + Me.AdjustmentQuantity
    End If
    End Sub

    The error is a Compile Error: Method or data member not found. and the vb editor highlights the line:

    Me.UnitsInStock = UnitsInStock + Me.AdjustmentQuantity

    Do you have any ideas.

    I am sorry about my ignorance and appreciate any help you can provide.

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

    Re: Access 2000 update (Access 2000)

    Of course I didn't know the exact names of the controls on your form when I posted the code. The highlighted line assumes that you have text boxes named UnitsInStock and AdjustmentQuantity on the form, so check very carefully, and substitute the actual names if necessary.

  14. #14
    Star Lounger
    Join Date
    May 2004
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 update (Access 2000)

    Hans,
    I am sorry I am still bugging you about my problem. I have changed all the names of the boxes and controls to what my code calls out. Now I am getting a run-time error which is: -2147352567(80020009). I was wondering if you have any answers for this problem. I know i've said it alot but I really do appreciate all the assistance you have provided for me

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

    Re: Access 2000 update (Access 2000)

    I don't think we can solve this without seeing the database. Could you post a stripped down copy of your database? That would allow Loungers to investigate the problem directly. See <post#=401925>post 401925</post#> for instructions.

Page 1 of 2 12 LastLast

Posting Permissions

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