Solved VBA Record Editing issues (1 Viewer)

[NearImpossible]

There are 8 records in the table and then an unbound field I am using for the Servings value, but for some reason, its only using the quantity value of the last record entered for the quantity value of all the other records, or if I select a row in the table and then click the update button, then it will use that value for all the records vs using each records value.

db attached

Set rs = CurrentDb.OpenRecordset("Select Quantity from [RecipesBuild]", dbOpenDynaset, dbSeeChanges)

    While Not rs.EOF
        With rs
            .Edit
            .Fields("Quantity") = Quantity / Servings
            .Update
        End With
        rs.MoveNext
    Wend

Any help is appreciated

thank you
Kevin

 

[Gasman]

So qualify the quantity with the Fields declaration again?

 

[NearImpossible]

So qualify the quantity with the Fields declaration again?

not sure what you mean

 

[Gasman]

not sure what you mean

.Fields("Quantity") = .Fields("Quantity")  / Me.Servings

I always add the Me to ensure I use the form control.

Not sure your code is actually advisable anyway?
Wouldn't a new field be set to Quantity/Servings?

So if I have 60 sausages and there is 2 to a serving. The 'heads' would be 60 /2 ?

 

[GPGeorge]

The code does NOT select records from the subforms recordsource to provide the different quantities. It's stays on the first record in the subform's recordsource. You need to add a second step to get the quantities from the different records. I'm puzzled by this whole thing anyway.

It looks like you are trying to take a quantity from the RecipeBuild table and multiple it by the servings, and then update that same quantity. All that does is change each RecipeBuild, based on what you used the previous time you used it. In other words, if you start with 8 tbs of Olive Oil and multipl that by 5 servings, the quantity is now 40. But if you run it again, that 40 will turn into 40 x 5, or 200 tbs and so on. I doubt that's the goal is it?

 

[GPGeorge]

The code does NOT select records from the subforms recordsource to provide the different quantities. It's stays on the first record in the subform's recordsource. You need to add a second step to get the quantities from the different records. I'm puzzled by this whole thing anyway.

It looks like you are trying to take a quantity from the RecipeBuild table and multiple it by the servings, and then update that same quantity. All that does is change each RecipeBuild, based on what you used the previous time you used it. In other words, if you start with 8 tbs of Olive Oil and multipl that by 5 servings, the quantity is now 40. But if you run it again, that 40 will turn into 40 x 5, or 200 tbs and so on. I doubt that's the goal is it?

Actually, I got that backwards, you're dividing, but the result is the same thing. Each time you execute the update, you make the quantity smaller and smaller and smaller.

 

[GPGeorge]

Ah, the light might be flickering a bit. You need a "Standard" recipe table with base quantities for each ingredient. Than each time you build a new recipe from that you would use the base quantity, not the unique recipebuild quantity, would you not?

 

[NearImpossible]

.Fields("Quantity") = .Fields("Quantity")  / Me.Servings

I always add the Me to ensure I use the form control.

Not sure your code is actually advisable anyway?
Wouldn't a new field be set to Quantity/Servings?

So if I have 60 sausages and there is 2 to a serving. The 'heads' would be 60 /2 ?

Code worked perfect, thanks !!

in seeing the code, I see why I would have to list it that way now.

Just a FYI, the quantity listed is the total per servings listed, i'm updating the quantity to be reflective of 1 serving, i guess I could create another field to hold that value, but after the conversion, i don't need the original values as everything else will be calculated by the single serving amounts.

thank you again !!

 

[Gasman]

Well after sleeping on it I realised that if it was a calculation, then it should not be stored anyway.