Updating a field in one table based on the updates in the field of another table (1 Viewer)

[D-Angle]

Hello all, first time poster and just getting into the more advanced end of Access, so be gentle. I'll try and explain this as best I can.

I am building a database for archiving paper records. The records are kept in boxes, each of which has a unique box number. I have created a database with 2 main tables of information, 1 for the details of the box and 1 for the details of each individual record. I have created a one-to-many relationship between the tables using the box number as the common field.

If we need to bring records back from storage, we can request individual records be pulled and sent back to us, we don't have to have the whole box. We can request whole boxes as well if we need to. So in each table there is a 'status' field, which will have the following options to select from: In Storage, Pre-Storage, Pickup Requested, Retrieval Requested, Retrieved, and Lost.

So what I want to do, is if I update the status of a box, that update is applied to all records therein as well. But if I update the status of an individual record, the status of the box itself and the rest of its contents remains unaffected. For example, if a box was lost, I could change its status to Lost and all the records contained in the box would be updated to Lost as well. But if we went to a box and couldn't find a record, I could mark that individual record as Lost without changing the status of the box or the other records contained in it.

Does that make sense?

 

[CJ_London]

makes sense - just not clear what you are asking - you would need a status field in both tables and the box status would have a higher 'authority' than a file status.

You may also want to lock changes to file status if the box status requires it, or when changing the box status you would check whether any file status would then 'infringe' your business rules

 

[D-Angle]

Users will update the database using a form with a combo box, offering a list of values for status managed by a separate table. Sorry, I forgot to put that in my original post.

The form will give them the option to update the status of a box, and in doing so I want that to update the status of all the files in the box as well without the user having to do so separately. But aside from that, the form will allow them to update the status of an individual record as well, but this won't in turn update the status of the box or of the other records in that box.

 

[sneuberg]

You could have the after update of the status combo box on the box update the related file records. I wonder it you want that without a warning when the files have a mix of statuses. Also what's the status of the box when there's a mixes of statuses in the files. Shouldn't it be "See Files".

If you need help with the code to do this let us know what the field names are and what the rules are and I'll try to give you some suggested code.

 

[D-Angle]

Sorry for taking so long to come back, I was off work so didn't have access to the information asked for.

The table TBL_Records has a field called Record_Status, and the table TBL_Boxes has a field called Box_Status. The list of status options is in a field called lkuptbl_Status and the options are :

In Storage
Pre-Storage
Pickup Requested
Retrieval Requested
Retrieved
Lost
Destroyed

 

[sneuberg]

I'll post some code for you but I would like answers to the following first:

Should the code give a warning if any records in the box have a status different than the one being set for the box. For example if a record in the box has a status of Lost do you want that status set to In Storage if the box's status is set to In Storage without any warning?

What should the status of the box be when the records in the box have a mixture of statuses? If nulls are allowed in the Box_Status it could be set to that it so that it wouldn't show a status. Is that what you'd like? Then maybe you just want it left alone and changes in the records in the box don't affect the box's status. It that what you'd like?

 

[D-Angle]

Every box and every record will have a status. When a new box is created and records added against that box, the box will start out as 'Pre-Storage' and the records should start out as that too. The status of individual records can change, but the status of the box should override anything that is still in that box, ie. any record that shares the box number and status of that box.

Thinking about it, some statuses will only follow particular other ones in practice:

Lost will only be selected on boxes that are marked as In Storage.
Retrieval Requested will only be selected on boxes that are marked as In Storage.
Destroyed will only be selected on boxes that are marked as In Storage.
In Storage will only be selected on boxes that are marked as Pickup Requested.
Pickup Requested will only be selected on boxes that are marked as Pre-Storage.
Retrieved will only be selected on boxes that are marked as Retrieval Requested.

Would that make it easier or harder, updating all records in the box in relation to their current status?

 

[sneuberg]

Would that make it easier or harder, updating all records in the box in relation to their current status?

I don't think it will make any difference in update the related file records

I'm tied up right now. I'll try to get you something by tomorrow.

 

[sneuberg]

The attached zip file contains a word document with the code you need, instructions on how to install it, and a small database that demonstrates it. The database also includes a form with a combo box that can be tailored to act in accordance with the rules you specified in your previous post.

Let me know if this works out or if you have any problems.

 

[D-Angle]

This is awesome, thank you

I'm wondering, is there any way of getting it to only update records whose status matches the current status of the box? That would get around the problem of it updating all the records in the box regardless of status. Can I code this in the WHERE criteria?

 

[sneuberg]

You are right it's just a matter of extending the where clause. I've update the update events in the attached database so it does it the way I think you want it to. Please note that a DoCmd.RunCommand acCmdSaveRecord was added to the code and is required for this to work properly.

Let me know it this is what you wanted.

 

[D-Angle]

OK, the example database works perfectly but I'm having trouble translating it into my database. When I try to use it I get 'Run time error 3464: Data type mismatch in criteria expression'.

The code is as follows, with changes made to the correct field and form names:

Option Compare Database
Option Explicit
Private Sub Box_Status_AfterUpdate()
Dim strSQL As String
strSQL = "UPDATE [TBL_Records] SET [TBL_Records].[Record_Status] = '" & Me.Box_Status & "' WHERE [TBL_Records].[Box_No] = " & Me.Box_No & " AND [TBL_Records].[Record_Status] = '" & Me.Box_Status.OldValue & "'"
DoCmd.RunSQL strSQL
DoCmd.RunCommand acCmdSaveRecord
Me.SUBFRM_Records.Requery
 
End Sub

 

[sneuberg]

I suspect the bound column of you combo boxes is an autonumber. I'm not sure if this will be enough but you can try taking the single quotes out of the SQL, i,e,

strSQL = "UPDATE [TBL_Records] SET [TBL_Records].[Record_Status] = " & Me.Box_Status & " WHERE [TBL_Records].[Box_No] = " & Me.Box_No & " AND [TBL_Records].[Record_Status] = " & Me.Box_Status.OldValue

If that doesn't work post the row source SQL of the combo boxes and the field names and types in your tables.

 

[D-Angle]

I tried the above and the error changed to:

Run Time Error 3075: Syntax error (missing operator) in query expression '[name of selected status here]'

Under the row source it just says lkuptbl_Status, which is the name of the lookup table with the values for the combo box.

The field Record_Status is in TBL_Records and the field Box_Status is in the table TBL_Boxes. The common field joining both tables together is called Box_No.

 

[sneuberg]

I need to know the names and types of the all the field in these two tables. It suggest you upload a copy of your database or a strip down version of it. If you can't do that, then upload screen shots of the TBL_Boxes, TBL_Records, kuptbl_Status, tables in design view.

 

[D-Angle]

Database with sample data attached below. The form I'm trying to get it to work on is SUBFRM_Box_Contents.

 

[sneuberg]

strSQL = "UPDATE [TBL_Records] SET [TBL_Records].[Record_Status] = '" & Me.Box_Status & "' WHERE [TBL_Records].[Box_No] = '" & Me.Box_No & "' AND [TBL_Records].[Record_Status] =  '" & Me.Box_Status.OldValue & "'"

was the correct answer. The problem was that the [TBL_Records].[Box_No] is a text field. Thanks for uploading the database. That saved a lot of time.

This problem has been fixed in the attached database.

 

[D-Angle]

This works great! Thank you so much.

 

[D-Angle]

So I'm trying to replicate this by getting the 'Location' field in TBL_Boxes to update the 'Record_Location' field in TBL_Records by copying the code and changing the field names, is there anything I'm missing to get it to work, as I'm struggling to at the moment.

 

[sneuberg]

Should be pretty much the same code. Please post what you have so far and tell me what it's doing or not doing.