updatating 5 records at once (2 Viewers)

[gizmogeek]

I'm looking for a way to fill out 5 records in one row in a table at once. I want to update a supplier and price per unit for supplier via a form. I have price per unit 1-5 and the price will be the same for all 5 records. Is there a way to do this through append or update query and if so which is best in my situation?

 

[MarkK]

Do you have five prices in one row?

 

[Monardo]

Hi,
Not 100% sure what you mean. You want "...to fill out 5 records in one row...", but one r0w is one record, so how could you fill 5. Could you please rephrase.

 

[gizmogeek]

sure I have a database with price_1. price_2, price_3, etc...

I want to update the whole row at once with the same $ amount. So say I am updating a supplier and his milk is $5 I want that price to appear in all 5 instances for that record.

 

[Monardo]

So it is not 5 different records, but 1 record with 5 fields, right?

If so, it is possible, but let me ask you, why do you need the same thing to appear 5 times? Just save it once and use it as many times as you want on forms, queries or reports.

 

[MarkK]

giz, that's a table design problem, that's why it's hard to work with. That would work better with two tables, with one parent record, and the five prices as five related child records. Then updating the child records it easy.

UPDATE tPrice SET Price = <update amount> WHERE ParentID = <ID of parent record>

 

[gizmogeek]

I have all 5 instances in one table now they had it all in one before. There are 5 instances of 5 different things so I had to break them all out into seperate tables.

 

[gizmogeek]

Is there a query or something that I can have behind the form that when I update 1 price on the form it's actually updating all 5 behind the scene?

Not prepared to invent the wheel today

 

[Catalina]

There are 5 instances of 5 different things so I had to break them all out into seperate tables.

That really sounds like a table design problem, like lagbolt already indicated.
May be you can tell a little more about what you need and why and someone will help you setup the correct design.

Catalina

 

[gizmogeek]

Thank you for replying. I'm not really looking to redesign as everything is working the way it should. I am trying to accomplish/minimize end user input by updating 5 records in one shot.

For instance: I want to see one field on the form called "Price". I want the value of whatever is entered into that field to update the table with records "Price1, Price2, Price3" with that single value. That way a user will not have to enter for all of them but just for one.
Thanks!

 

[Catalina]

It may work alright now but with a flawed table design you will run into trouble sooner or later.

May be you can use an Update Query for what you want to do.

Catalina

 

[gizmogeek]

Thanks for replying. I don't mean to be rude but it took me forever to normalize the database and on instruction from a few in this forum I took all of the 5 fields and broke them up into their own separate tables. So I have 5 places on my form that are order fields. Meaning I can order up to 5 times.

So I have OrderQuantity1 - 5, Price1 - 5, orderpartnumber, etc... then when I am through ordering each order gets totaled up then all are added together to form a final total. The problem with this is the price is always the same for the orderpartnumber (which is in a combo box drop down). Each part number corresponds with the price or if. Instead of filling in all 5 price fields to populate the table correctly I just want to fill in one price field and populate all prices1-5.

I hope this make sense.
Thanks!

 

[MarkK]

If all your records share a Foreign key ID, say they are all linked to the same parent, then the query SQL to update a single field in all of them, with the same value, is posted in #6 in this thread. Code to create a parameterized querydef to do the job would look like . . .

   with currentdb.createquerydef("", _
   "UPDATE tPrice " & _
   "SET Price = p0 " & _
   "WHERE ParentID = p1")
      .parameters(0) = < price to update to >
      .parameters(1) = < foreign key id of the parent >
      .execute dbfailonerror
      .close 
   end with