I have a bound form with a subform bound to a different recordSource, although these recordSources can be linked through a field.
I have an unbound text-box control in my main form and a button control, with custom vba code attached to its OnClick event.
What I want to achieve is something like this: (pseudo-code for easier reading)
private sub button_Click()
'Find the record with the content of the text-box control as its value in FieldA
find(mainForm.RecordSource, fieldA, text-box.value)
if not found then
'Create a record in the subform.recordSource with PK value = text-box.value
createDefaultRecord(subform.Form.RecordSource,text-box.value)
end if
'load the values in the record in the main record source onto the record in the subform recordSource
Me!subform.Form.value = mainForm.recordThatMatched.value
Exit Sub
And I actually get it to work, but with a minor inconvenience: if I introduce a code that has not yet generated its record in subform.RecordSource, it creates it and then fails to load it BUT if you try to load a different code or close the form and then reload the first code again, IT WORKS!
Any idea as to why this is happening or how to code a work-around to this situation? It really is bugging me. I have tried to run acSaveRecord and acSave before loading the record but the ending remains the same: I cannot load on the subform a record recently created without navigating first through the main form's records (which is kinda stupid and counter-intuitive)
I may be wrong in my diagnosis and that would be OK to point out, too. I don't have that much experience with Access and VBA.
Solution was to add this:
Me!subform.Form.Requery
after adding the record.
A form (or subform) doesn't show newly added records in its recordsource unless it is requeried (manually with Shift+F9 or via code).