VBA Autofill Subform Fields Locked on AddNew

[DragonTech]

I am trying to update subform fields when a field on the main form is changed. It works well with the first record in the multiple items subform I wish to update. However, I want it to add additional records to the subform when the field on the main form contains multiple values. The problem I'm running into here is that when I use .Form.Recordset.AddNew in the If/Then statements, all of the subforms in the form become locked. The error I am receiving is "Could not update; currently locked by another session on this machine." I've tried .Form.Recordset.Update, but it produces the same error along with a different error. I've also tried disabling locks in Access Options and all of my forms, but all of my forms' locks were already disabled and the AO tick box did not change anything. Here is the relevant code:

Private Sub ComBox_Change()
If [Field] Like "*Data1*" Then
    Me!Subform.Form.Recordset.AddNew
    Me!Subform.Form.Subfield = "Data1"
End If
If [Field] Like "*Data2*" Then
    Me!Subform.Form.Recordset.AddNew
    Me!Subform.Form.Subfield = "Data2"
End If
End Sub

I'm lost as to what to do now. I've been researching this for quite a while.

[June7]

I am concerned by 'field on main form contains multiple values'. Is this a multi-value field? I NEVER use multi-value fields. Fields should not have multiple values.

I do not understand why data needs to be automatically entered into subform dependent on value of main form.

Is the main form bound?

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

[DragonTech]

So, you're telling me that if I want, say, the ethnicity of someone, and they have multiple ethnicities, I am supposed to create fields for every known ethnicity in the case that there is someone with descent from every nation? Or are you telling me I should make just one field with dropdown options that contain every possible combination of every possible ethnicity? I really don't see the efficiency in either option.

Data needs to be automatically entered into the subform because people need to be put under the same categories. For instance, we have a group leader, and I want every person under him to automatically have similar data.

Yes, the main form is bound.

I cannot provide the database because its contents and structure are sensitive. Removing confidential data will render the database unanalyzable.

[June7]

A normalized structure would be a related table with a record for each ethnicity for each person. This is essentially what a multi-value field does, Access just hides the related table. However, if your multiple values is just a string of text separated by commas, that presents its own set of headaches for managing the data which are usually worse than the other options.

Confidential data would be phone, email, address, SSN. Removing those details usually does not make db non-functional.

I've never used code like you posted so am not sure why the locking. Perhaps it has to do with trying to write records to the form RecordSet. Possibly should use RecordsetClone.

[DragonTech]

I'm not sure what you're saying by "normalized structure" and "related table." Are you able to check from a dropdown box each ethnicity that applies to a person? Or are you entering the data via keyboard?

There's confidential data in more than just the tables. Besides, I'm working voluntarily for a company and I can't share my work.

RecordsetClone does not work. It doesn't give me the error I've been experiencing, but it only fills in one record with the results of the last If/Then statement. Without RecordsetClone, all of the information fills in, but it gets locked before that and doesn't apply it to the table.

Let me refresh us on what I'm trying to do. I have a multi-value field on the main form, which is in a tab. On the other tab, I have the subform, which data I want to be automatically filled in when I select certain people types in the main form multi-value field. I do not want to see the subform right away when I confirm my selection in the multi-value field, but I do want certain information from regular fields on the main form to copy over to certain regular fields in the subform when I confirm my selection. Is that clear?

[June7]

If you have dropdown box with checkboxes, then that is saving to a multi-value field. I have never used multi-value field. Conventional normalization would be a related table and subform for data entry of the ethnicity. Select ethnicity from a combobox that allows only one item selection.

So does 'people types' mean ethnicity or is there something else involved? If multiple 'people types' are selected there should be a record in subform for each?

Instead of trying to append records to the subform recordset, option may be an sql action to append records directly to table followed by code to requery the subform.

Main form and subform are bound to different tables?

[DragonTech]

I know that a dropdown box with checkboxes means that it is saving to a multi-value field. I have to use a multi-value field in order for the database to serve its purpose properly and efficiently.

For this particular multi-value field, people types means only ethnicity. But in some cases there may be people with multiple ethnicities, such as black-white, black-asian, white-hispanic, etc. There are 462 different possible combinations from my current ethnicities list. I do not want to have a combobox with an option for every different combination. I just want to have several mainstream ethnicities and be able to select the ones that apply.

How would I go about creating

an sql action to append records directly to table followed by code to requery the subform.

?

Yes. The main form and the subform are bound to different tables, but one field in the subform table has a relation to the PK in the main form table.

[June7]

I disagree that you HAVE to use multi-value field - this is a design choice. Access actually stores the multiple values in a hidden related table. A conventional structure has related table but doesn't hide it. Multi-value structure can make some processes more complicated.

Example of executing an INSERT sql action in VBA for text, date, number data:

CurrentDb.Execute "INSERT INTO tablename(fieldname1, fieldname2, fieldname3) VALUES('" & Me.textbox1 & "', #" & Me.textbox2 & "#, " & Me.textbox3 & ")"

[DragonTech]

Well, I'm fairly new to Access, so I don't really exactly know why I wouldn't want to use multi-value fields or even how to work around using multi-value fields. But I must absolutely have a textbox or combobox on the form that allows the user to select multiple values. I can't work around that.

I don't fully understand how to use your example of an sql action. Where do I put it and what does it do?

I'm unsure as to what happened, but I am no longer receiving an error with the code I'm using. I put a On Error GoTo line in a different form's VBA code, but I don't know how that could be affecting the primary form. However, the subform in the primary form is still locked because it won't update the table after updating the multi-value field. I'd rather go with the code I'm using, but I want a fix for the locking bug.

Thank you for your help thus far. I'm really learning a lot trying to get this sorted out.

[June7]

I would have to work with db to analyze issue.