Force an empty form to get inserted, in background (without changingfocus, selection, etc.)

[S P Arif Sahari Wibowo]

Hi!

Do you know how to put a form's Access-Visual-Basic-code that will force
the form to be inserted, while the user has not type anything in the form,
without changing focus, selection, etc.?

Here is the story.

I have this structure:

Table1 <-one-many-> Table2 <-one-many-> Table3

The forms follow that structure:

Form2 (of Table2) subform of Form1 (of Table1)
Form3 (of Table3) subform of Form2 (of Table2)

Now, the records in Table2, although have their own fields, that fields
not always filled by users, and sometime just act as "holder" for a group
of Table3's records. So in the form, sometime users have nothing to type
in Form2, but want to type into Form3 directly. But since Form2 is not
typed in yet, the record is not created yet, and therefore what they type
in Form3 will just gone (not linked to anything).

So what I tried to do, is but an event handler (of BeforeInsert event) in
Form3, calling another code in Form2 or other place if necessary, get that
form "inserted" (get the record created). However, since this will happen
in the middle of the user typing in Form3, it should happen without
changing focus and selection.

So far the only thing can do the insertion, is by having a code moving
some data into 1 or more fields in Form2, and then call the method Requery
or Refresh from Form2. Unfortunately, this reset the Forms display, with
Requery the focus shifted to the first field in Form2, with the Refresh,
either the typing hairline change into selection of that particular field
(in Form3), or Form3 get reset (focus to first field), and the single
character typed disappear. I don't know why it does one or the other.

I tried using Recordset's Update, and RunCommand acSaveRecord, to no
avail.

Any other suggestion?

I use MS Access 2003, BTW.

Thank you!

 

[HJ]

I believe I can make two observations:

1. Your data may need restructuring or your forms needs to be restructured.
If form3 can be used without using form2, then why do you need to show form2
in the first place?
2. Without restructuring anything, you can have an AfterInsert event for
form3 that checks whether any records exist for form2. Then, if no records
exist for form2 you can add records directly to the underlying table and
then refresh form2. After that you can return the focus to form3 or any
other place.

HJ

 

[Larry Linson]

I do not understand what you mean by "force the form to be inserted". We do
not normally describe forms in Access being "inserted", so it would be
helpful if you could clarify this for us. And, alas, I am confused when I
read your additional description. (Of course, there are those here who would
think it not unusual for me to be confused -- and they might not even
qualify that with a .)

Larry Linson
Microsoft Access MVP