Using VBA to look for and edit certain records

[element32d]

I have code that fills in a field named "volume" by looking at forms and calculating some values and whatnot.

The problem I'm having is, after all this calculating and updating is done, I want a line of code to look for a certain part # in a table, look at the volume that was just calculated for that part #, and then make it equal to another part #'s volume in that same table. I want this done for multiple part #'s.



Basically, I'm not sure how to look up specific entries and then proceed to make one of it's fields equal to another entries field with vba.

Help would be appreciated, thanks!

[June7]

Something like:

Z = Inputbox("Enter First Part Number") & "'")
X = DLookup("volume","tablename","PartNo='" & Z & "'")
Y = DLookup("volume","tablename","PartNo='" & Inputbox("Enter Second Part Number") & "'")
CurrentDb.Execute "UPDATE tablename SET volume=" & Y & " WHERE PartNum='" & Z & "'"

Automating this for multiple records gets more complicated.

Why are you doing this? Why is one part volume dependent on another?

[element32d]

Actually this is my mistake, that table is actually a form. So below, "Inventory" is a form that has the volume that needs to be equal to both part numbers. Does that change anything? Here's the converted code to the real version:

Z = InputBox("1234" & "'")
X = DLookup("Calculated Average Volume", "Inventory", "Part#='" & Z & "'")
Y = DLookup("Calculated Average Volume", "Inventory", "Part#='" & InputBox("9876") & "'")

If X <> Y Then
CurrentDb.Execute "UPDATE Inventory SET Calculated Average Volume=" & Y & " WHERE Part#='" & Z & "'"
End If

If it doesn't change anything, I ran the code with no errors, but it didn't work correctly.

[June7]

Names with spaces need to be enclosed in [], like [Calculated Average Volume]. This is why I advise no spaces, special characters (@ # $ % & * ' " !), punctuation (underscore is exception) in names, nor reserved words as names.

I assume Part# (PartNum would be better) is a text field.

[element32d]

Yea I actually realized I didn't use []'s right after I posted here, it still doesn't run correctly though.

Z = InputBox("1234" & "'")        
X = DLookup("[Calculated Average Volume]", "[Inventory]", "[Part#]='" & Z & "'")
Y = DLookup("[Calculated Average Volume]", "[Inventory]", "[Part#]='" & InputBox("9876") & "'")

If X <> Y Then
CurrentDb.Execute "UPDATE [Inventory] SET [Calculated Average Volume]=" & Y & " WHERE [Part#]='" & Z & "'"
End If

Part# is usually a number but stored in the form as plain text.

[June7]

What is not 'correct' - error message, wrong results, nothing happens?

Why the concatenated apostrophe in the first InputBox message? Should still work but just unnecessary.

Is the Part# field in Inventory table a text datatype?

[element32d]

My code runs just as it did before I inserted this code in the middle of it. So the volumes are staying the same as they were for the part numbers I chose to put in your code, they are not equaling each other as the code should theoretically do. 1234s volume is still 0 while 9876s volume is 20,000 or whatever you want it to be. They should both be 20,000 if this code worked correctly.

No error comes up.

Inventory is a form, not a table. Both the Part# and volume are located in this form. Of course, part# is originally being taken from a table (text) along with many other parameters but that shouldn't affect this code and the form associated with it. But yes part# is a text datatype.

[June7]

These SQL actions must be performed on table or editable query, not form.

If the form has the record you want modified and it is selected as the current record then:

Me!Volume = DLookup("[Calculated Average Volume]", "[tablename]", "[Part#]='" & InputBox("Enter Part Number to get volume from") & "'")

Problem with input prompts is can't easily validate the entry. I would use an unbound text or combo box for criteria input. Then:
Me!Volume = DLookup("[Calculated Average Volume]", "[tablename]", "[Part#]='" & Me.textbox & "'")

[element32d]

Hey, I've created a very limited functionality version of what I'm talking about attached.


In "home", when you click the button it should create a volume for 9876 but not for 1234. After it creates the volume for 9876, I that volume inserted for 1234 as well. Other calculations are then based off that volume as you can see, but can't go through because there is a 0 for 1234 (also because i limited the functionality of this version).




There are multiple part#'s I want to do this for, I'm not against repeating the same code for each one individually.


Hopefully you understand it better now, I appreciate the help!EqualVolumes.zip

[June7]

The procedure wants to open form [Ave volume per month] and a popup prompts to enter Order Key. Why? What do I do?

[element32d]

You just have to hit ok and it will continue.

[June7]

The code also wants to open form [Future Inentory] which doesn't exist.

In "home", when you click the button it should create a volume for 9876 but not for 1234. After it creates the volume for 9876, I that volume inserted for 1234 as well. Other calculations are then based off that volume as you can see, but can't go through because there is a 0 for 1234 (also because i limited the functionality of this version).

I don't understand this. The code sets [Calculated Average Volume] to 0. If that's not what you want then what should it be?

I ran procedure and when form reopened the values for 9876 were changed. I would need sample starting values for each record and then sample of what the values should be after procedure runs.

I suggest you step debug, follow the code as it executes. See where deviates from expected behavior, fix, repeat. Link at bottom of my post has guidelines for debugging techniques.

[element32d]

Once you click 'click me', you can see the volume is 0 for both part numbers. Once you click 'ok', the volume for 9876 becomes 17248 and the volume for 1234 remains 0. Ignore the future inventory error.

After the step occurs where volumes are calculated, I want 1234's volume to equal 9876's volume through code. So right before the future inventory error, both volumes should be 17248.

[June7]

Why do you have code open form that is already open and has focus? You do this for both [Ave volume per month] and [Future Inventory].

This seems to work:

    DoCmd.OpenForm "Ave volume per month"
    DoCmd.GoToRecord , , acLast
    lastpart = Forms![Ave Volume per month]![Part#]
    Volume = Forms![Ave Volume per month]![Volumepermonth]
    Counter = 1
    Do
        DoCmd.OpenForm "Inventory Base1"
        DoCmd.GoToRecord , , acGoTo, Counter
        Part1 = Forms![Inventory Base1]![Part#]
        Forms![Inventory Base1]![Part#].SetFocus
        DoCmd.FindRecord Part1
        Forms![Inventory Base1]![Calculated Average Volume] = Volume
        Counter = Counter + 1
    Loop Until Part1 = lastpart