Update Multiple Records With Same Location ID

[tj007]

Hello, I have a form created from a table name Result. It holds all the data for bus stop locations (more than 6,200). Locations are used by more than one STOP.

When any field on the form created from the Result table is edited, I would like to have another form pop up after saving the edits, showing all the related Stops for the location number then ask the user to confirm the update to all the locations or allow the user to make edits to the form. Is this procedure possible?

 

[KenReay]

Hi

I feel I have already answered this:

Yes

DoCmd.RunSQl "UPDATE tblYourTableName SET LocationId = x WHERE Stop = y;"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tj007]

Thanks Ken

You answered but I needed more guidance with actual syntax.

 

[KenReay]

Hi

Have you checked help?

If you have and still cannot understand, post back with specif problems and/or sample of what you have so far

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tj007]

Yes, I have checked help. I am totally new to VB. I will learn but I am pressed at the moment. Below is the subform on a form name shared locations. The first row is data displayed above on the main form. The remaining three records with the same LOC_ID share the Stop location 2347. I do not want to change the STOP. I would like update all STOPS sharing the location if any changes are made to the other fields AMENITIES, ADA, DIST_STOP etc.


LOC_ID STOP DESCR AMENITIES ADA DIST_INT
2347 01001 AVENTURA MALL BPST A INFR AVENTURA MALL AVENTURA MALL
2347 01007 US1/PO B F OPPO US1

2347 01133 AVENTURA MALL BPST A INFR AVENTURA MALL AVENTURA MALL
2347 09155 AVENTURA MALL BPST A INFR AVENTURA MALL AVENTURA MALL

 

[KenReay]

Hi

Is this a case of a poorly designed table structure with data repeated instead of being held in the location table?

If yes, perhaps you should consider amending the structure

if this is not possible at this time perhaps you could do it via relationships and cascade update, have you looked at that?, more reliable than doing it via code

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tj007]

It was a poorly designed table structure. I redsigned the table.The unique ID field is the STOP field. There are no duplicates in the STOP field. However, as I stated before different stops use the same LOC_ID. These are bus stops and they must interconnect there-by use the same LOC_ID.

 

[KenReay]

Hi

yes, but waht I am saying is that it looks as if the columns AMENITIES, ADA, DIST_STOP are attributes of the Location, so there should be a location table and these columns should be in there

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tj007]

Yes there is a BS_LOC table with the autonumber field LOC_ID. Ken this is not easy.

 

[KenReay]

Hi

OK, I recognise that if you have an existing app, it may be hard work to change table structure, I was just trying to ensure I was on the right track, after all I am guessing from minimal information.

The cascade update via relationships may be the 'easiest' thing to do right now, but in the long term doing it properly 9ie correcting the table design) will probably be the best option

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tj007]

Here is a query I use to insert the new LOC_ID into the Result table when a new STOP is created from a new location.

INSERT INTO Result ( STOP, LOC_ID, DESCR, MUNIC, AMENITIES, ADA, DIST_INT, MAIN, [CROSS], RW, HISTORY, DATE1 )
SELECT bs_loc.Stop, bs_loc.LOC_ID, bs_loc.DESCR, bs_loc.MUNIC, bs_loc.AMENITIES, bs_loc.ADA, bs_loc.DIST_INT, bs_loc.MAIN, bs_loc.CROSS, bs_loc.RW, bs_loc.HISTORY, bs_loc.DATE1
FROM BS_LOC
WHERE BS_LOC.LOC_ID=[Please Enter Location ID];

 

[tj007]

I can change the structure. Do you have a specific recommendation?

 

[tj007]

Ken, thank for your input.