Change records inside continuous form Runtime Error '3164': field cannot be updated

[stewegg]

I got a continuous form with multiple fields.
The field control source are directly from each tables.


Right now, I got a button click action that would apply the different info to the specific fields.
However, the evil Runtime Error '3164': field cannot be updated came up.....
Can't figure out why.....Help! Please!

Thank you very much!

    With Me.RecordsetClone
        .MoveFirst
    Do While .EOF = False
        .Edit
        .Fields("Status").Value = "Home"
        .Fields("LocationNumber").Value = 1 '(1 being at home)
        .Fields("Active").Value = 0 '(this is a true/false field)
        .Fields("ActionDate").Value = Date
        .Update
        .MoveNext
    Loop
    End With

Error @ .Fields("LocationNumber").Value = 1 '(1 being at home)

[June7]

Don't know enough about data structure to advise.

Why do you need code to enter data?

If you want to provide db for analysis, follow instructions at bottom of my post.

[stewegg]

This code is for a button at the footer of a continuous form.
When user click the button, it suppose to change the Status, LocationNumber, Active and ActionDate of all the records in the continuous form.

I tested the recordset using codes below without any issue.
msgbox gives me the correct information.
It's when I tired to change the fields it starts giving me errors

For i = 0 To 3
    With Me.RecordsetClone
        .MoveFirst
    Do While .EOF = False
        .Edit
        Select Case i
        Case 0
            test=.Fields("Status").Value
            MsgBox test
        Case 1
            test2=.Fields("LocationNumber").Value
            MsgBox test2
        Case 2
             test3=.Fields("Active").Value
            MsgBox test3
        Case 3
             test4=.Fields("ActionDate").Value
            MsgBox test4
         End Select
        .Update
        .MoveNext
    Loop
    End With
Next i

[June7]

That still doesn't help analyze issue. No idea why VBA rejects that field reference. If you can't or won't provide file, doubt I can help.

[stewegg]

Don't know if this helps but LocationNumber field is in a different table than Status. :|

[June7]

There you are. A form should do data entry/edit for only 1 table. So why is this field in this form RecordSource and why should it be changed? How are tables related?

[stewegg]

Would putting the tables in one query solve the problem?

The two tables are related by having one referring to another's unique ID.
Table #1 contains:
Field: Status
Field: Table2ID

Table #2 contains:
Field: LocationNumber
Field: Active
Field: ActionDate

[June7]

Not sure because I am not understanding data relationship.

Can a Table#2 record have multiple Table#1 status records associated? If not, why have a separate table?

The form design makes no sense with that data structure.

Are those actual table names? Poor naming convention if it is.

Again, this would probably go a lot faster if you provided db.

[stewegg]

I totally understand it's hard to work on things that you can't see.
However, due to confidential information, I can't really provide with you the database.
I am really sorry about that.

The records in Table #1 can have multiple Table #2 Locations records.
To make it easier to understand, table #1 contain the list of people with their current status (home, warehouse, office, vacation...etc)
Table #2 contain the history of their location/station (locationNumber, Active location or not, ActionDate (the date this person is change out of this location/station)

I hope this helps.

[June7]

You could make a copy and remove confidential info.

You don't show people info fields for Table1. Seems to me the relationship is backwards. Table1 ID should be saved into Table2.

Should use form/subform arrangement.

[stewegg]

I have taken out most of stuff in the database.
The button I am working on is the "Send All Home" in the footer of the form.
B/c it's larger than 500KB, I have uploaded to google drive in the link below:
https://drive.google.com/file/d/1b0x...ew?usp=sharing

Thank you very much

[June7]

A form should do data entry/edit for only 1 table. Which table should this form edit - tblProject?

LocationID is autonumber field in tblLocation so of course cannot edit that field. If you want to change LocationID in tblDeployment, that will require a separate action. Cannot edit that field through this form RecordSource.

Next time Zip the file and attach to post. Zip of 2MB allowed.

[stewegg]

Status is in tblEquipment
Active in tblDeployment
RemovalDate in tblDeployment

It's not changing the autonumber field LocationID in tblLocation, it should sending the selected equipment to LocationID=1 (which is default Home).

[June7]

Your code is attempting to change value of LocationID in tblLocation because that is the field in the form's RecordSource. So fix that and I retract my earlier statement. You will be able to edit LocationID from tblDeployment.

This confusion is common when using same field name in multiple tables.

[stewegg]

How do I fix that issue?
Why does the "Send Home" button works fine but "Send All Home" does not work?