Force subform data entry based on main form combo box

[BGuidry]

I have a main form with a combo box, and a subform which is linked to the main form combo box, as a Child link. I have already synchronized the form to the combo box, through a "SetValue" macro. I have an identical field in both forms, based on a lookup table. I would like to limit the choice for selection of this field in the subform (in datasheet view) based on the current selected record in the main form. This field is also linked in the query as a one-to-many join (the subform being the many portion, as it's purpose is to write multiple entries to a particular table). Any help greatly appreciated.

 

[BGuidry]

Here is how I did it:

Created a validation rule (below) on the field in the subform to be equal to the value in the combo box (or Null).

=[Forms]![frmWellMaintenance]![Combo34] Or Is Null

 

[BGuidry]

Now the problem is when entering data in the matching field in the subform it displays in the datasheet, but when exiting the form it recognizes the value as null.

 

[MajP]

Why do it this way? Why not link the subform to the combobox in a more traditional main form / sub form approach?

 

[BGuidry]

Maybe I need to extrapolate on the issue...I would like to create an on-click event in a subform's field that will copy the (visible) value from a combo box on the main form. The way I have attempted it (above) displays the text value in the subform field but when tabbing out states that it contains a null value.

Anyone know how to create a VBA script to copy values from one field to another?

 

[MajP]

Again, why?

Assume I have combo "cmbo34" and it has a field that returns a wellID. (or some field that links to a subform). Lets assume the subform has a foreign key to the wellID "wellID_Fk".

Then if I set the subform control to
linkMasterFields: [combo34]
linkChildFields: [wellID_FK]

Then if I change the value in the combobox it will filter the subform to the values with wellID_FK that match the combo. If I enter a new record in the subform it gets the foreign key from the value of the combo. No key needed. Use the traditional approach not some weird "setvalue" macro.

 

[BGuidry]

Yes Sir, you are correct. But the problem I am having is that when entering a new record in the subform it allows any value in the combo list to be added. What I would like is that it will only allow one to enter the same value as the current record (or value which is visible in the combo box), simply to keep it neat. - Thanks

 

[MajP]

I am not sure if I understand what you are saying. Can you post the linkmasterfields and linkchildfields property of your subform control.

In my example you do not have a choice of values, any new value is automatically given a foreign key to the value in the combo.

 

[BGuidry]

I fixed that issue now, just adjusted the Action Arguments in the SetValue macro...now it defaults to the same record in the combo box when attempting to type in the subform.

Now, though, if there are no values in the child linked field (in subform) for a value in the master linked field (and main form), a value does not display in the combo box (of the main form), therefore when entering data in the child an error message states cannot enter a null value (because I have that field property as "Required" in the table properties). It allows entry only after hitting OK on the error box. I would simply suppress the error, but would like to actually have the combo box show the next record even though it does not exist in the child yet.

I have even broken all relationships and joins between the tables.

 

[BGuidry]

Well, I now don't believe the problem lies in the Macro, but the form/combo box. I have tried relating the
tables, joining them in queries (plural because I had one query with only the combo box field and another querty with multiple fields for the datasheet).
I now have them linked in the form (with the combo box being the Parent and the datasheet as the Child). For some reason, when selecting a value in the combo box, which does not yet have a record for in the datasheet-based table,
the selection changes to a null value following it's selection. The problem with this is that the setvalue macro is setup to populate the field in the datasheet, based on the value of the current combo box selection, when
clicked. It returns an error on the null values discussed above, stating that cannot enter a null value. I have broken all links in queries, emptied the OnClick event completely, and the combo box still reacts the same.

 

[BGuidry]

I have converted the macro to VBA because I was getting an error message that "Echo=Off" or "Setwarnings=No" could neither suppress. I then changed the "On Error GoTo" line set to "0", which resolved the error message problem. Now, though, it does not force the Well Location value to equal that of the value in Comb box 34 (Combo34) any longer. Anyone know what I may add to the script (below) to force the Well Location value to equal what is visible (selected) in the combo box?

Function Forms_UpdateWellID()
On Error GoTo 0

DoCmd.Echo False, ""
DoCmd.SetWarnings False
' Updates new Well Location record in frmWellMaintenanceSub based on what is visible in Combo box 34
Forms!frmWellMaintenance!frmWellMaintenanceSub.Form![Well Location] = Forms!frmWellMaintenance!Combo34


Forms_UpdateWellID_Exit:
Exit Function

End Function