Update data in one table, but not the other (1 Viewer)

[meiteniite89]

Hello,

I have Form ALLDataUpdate based on query from two tables: tblData and tblArea.
My Query works nicely until I need to update info. tblData needs to be updated based on what user inputs, but tblArea should not be updated. tblArea is only there to provided 3 combo boxes to choose Area, Location, Location Name, Location Code. Location Code is the one that is stored in both tables, therefore works like a link between tables in query.

I have tried to set Area, Location, Location Name as DLookup option, but then they are locked for manual changes, for example, if I need to change Location or Area completely. I tried to use Me.Area.Undo, on button click event, but it doesn't do anything. It still overwrites info in tblArea giving it a new Location Code.

Currently after every update I have to delete all tblArea and using saved import, add it all again from Excel sheet. It is working, but slows everything down.

Is there any way to get it done?

Thank you

 

[plog]

tblArea is only there to provided 3 combo boxes to choose Area, Location, Location Name, Location Code

Then it doesn't need to be part of the recordset for the form. The form can just be based on tblData.

Further, you just need 1 combo box not 3. That one combo box will have the tblData.LocationCode as its control source, its row source will be based on a query built from tblArea and include all the fields you want to display as well as tblArea.LocationCode

Add a list box or combo box - Microsoft Support

When entering data on Access forms, it can be quicker and easier to select a value from a list than to type a value. Learn how to add a list box or combo box.

support.microsoft.com

 

[meiteniite89]

Then it doesn't need to be part of the recordset for the form. The form can just be based on tblData.

Further, you just need 1 combo box not 3. That one combo box will have the tblData.LocationCode as its control source, its row source will be based on a query built from tblArea and include all the fields you want to display as well as tblArea.LocationCode

Add a list box or combo box - Microsoft Support

When entering data on Access forms, it can be quicker and easier to select a value from a list than to type a value. Learn how to add a list box or combo box.

support.microsoft.com

Reason to have so many combo boxes is based on the fact that I have 10 Areas and each Area has about 5-15 Locations and each of them about the same amount of subLocations (LocationName) and only then they have a 1-1 link to Location Code.

Also if it's based only on one table it doesn't display all the other info in drop down combo boxes.

 

[plog]

I don't understand.

1. A combo box isn't limited to displaying just 1 piece of data. You can display Area / Location / SubLocation all together. You can either concatenate them in the query you build for the combo box or you can set the Column Count value to the number of fields you want to show and then set the Column Widths values to show them.

2. You can use cascading combo boxes to filter down the options. Search this forum for "Cascading Combobox" for examples on that.


All in all, the combobox(es) and what they show are not really related to the recordsource of the form--the combo boxes just need to include the value that will go into the form's recordsource.

 

[meiteniite89]

This combo box is like cascading, you choose Area, then it narrows it down to only specific Area you chose Location, and the same way going down on the list. My Data table only stores Location Code, that is assigned to Location Name and that's it.
Once I bring it back to get data or location updated (it is based on Query and control source is column in table Area), it stores old location code onto new chosen Area, Location, LocationName, therefore updates table Area completely.
See images attached

 

[plog]

This combo box is like cascading

No 'like' about it, you need a cascading combo box.

My Data table only stores Location Code

That is the correct way to do it and how the cascading combo boxes should work

it stores old location code onto new chosen Area, Location, LocationName, therefore updates table Area completely

Yes because tblArea is part of the forms recordsource. It shouldn't be. Your form should just be based on tblData.

Again, look up cascading combo box and/or making 1 combo box that just is based on tblArea.