Force data entry of Main form AND sub form

[RockKandee]

I am working with Access 2013 in Windows 8

Due to changes I made to make another form work, I have created myself a new problem with a different form.

I have 2 tables
Table 1 = parent
Table 2 = child

The parent table must have at least 1 child.

On my form, I am trying to figure out how to prevent the main form from closing or going to a new record unless the sub has also created a record.

I have tried numerous methods that have failed.

I have been focused on an event for my close button. Once this is working I will include it with my new record button. I have the form set so these buttons are the only option to close or get a new record.

None of my research has given me enough information to make this work. Every time I try to get an event to look at the sub form I receive the following error....

Error Message: The object doesn't contain the Automation object 'Forms.' You tried to run a Visual Basic procedure to set a proper or method for an object. However, the component doesn't make the property or method available for Automation operations.

'Forms' is replaced with what ever control of the sub form I have tried. This error was from attempting to use an If statement in a macro:

DCount((([Forms]![MainFormName]![SubFormName].[Forms]![NameofField])<1),1,0)

I have tried to use - IsNull also

Not sure if I should be using an If statement and I know DCount is for tables and queries, but I didn't know what else to try.

I am using
IF "whatever I can think of to try"
then message box
then go to control (the one I want filled in on the sub form, though I don't care where it goes)
Else close form

I am using macros.

I would love some insight...

Thanks for all of the help so far.

[xzorxx]

Hi ,I didn't get what you wan't really but what I have understand
is that you trying to
when create new record in the main form sub form get a new record
and actually I can't see where is the problem is
ms access will make new record when you make one on the main
you can try to make query then create form to it ,and that's it.

[RockKandee]

My access form is not creating a new record in the sub form unless I enter data in the sub form.

If data is entered in the main form and then the form is closed (or goes to a new record) before entering anything into the subform, then I have no child record in table 2 related to the record created in table 1.

I would like to know how this works for you.

Thanks

[xzorxx]

can you please tell me what are you using, table to form or query to form ??
if it's query to form I believe that there is problem in your query can you write what did you do..@!

[zmbd]

xzorxx :
ms access will make new record when you make one on the main
you can try to make query then create form to it ,and that's it.

This is not exactly true, just because the parent has a record does not mean that the the child will have a related record.

xzorxx :
can you please tell me what are you using, table to form or query to form ??
if it's query to form I believe that there is problem in your query can you write what did you do

More than likely this has absolutly nothing to do with the query what so ever.

xzorxx, just exactly how much experience you have with Access?


@RockKandee
IN the before update, check for "new record" and set a flag at the form declarations (the very top of the form's code).
In the afterupdate event of the parent form, if the flag was set have the code either open a recordset or use the "INSERT" db.execute method.

It's quite late here in the states or I'd bash you some code and an example; however, it's time for my nap, I just don't function well w/o 3 or 4 hours of sleep a day (I've made at least a dozen typos already).
Let me get that and clear my desk in the morning. ADezii may be along here in a moment, I sware "A" can just exhale and the code will write itself for him! d(^_^)b

[NeoPa]

I'm struggling to understand exactly what it is you're trying to achieve here. You mention that a sub record isn't created when you create the main record, but not why that would surprise you. That seems to be standard behaviour with sub-forms. Any time you attempt to move focus away from the main part of the form (which includes pages - or tabs - but not any sub-forms, whether these are found on pages or on the main form itself) the main record will be saved - unless you abort that in your code. If you abort it, of course, you also abort the focus change.

I hope some of this helps you to understand why things aren't behaving as you'd like, but I'm still a bit confused so we'll just have to see.

[zmbd]

Neopa,
I am Guessing here.
Take a situation I have in the lab.
When I receive a new item, the parent table has a record generated that has the details (serial, lot etc) and related information. The child table has to have the record created that has today's date and the fact that the item was received.

[NeoPa]

I get the idea of related tables, Z. What I'm struggling to understand is the precise problem as reported. There seems to be some sort of expectation that both related records might be created at the same time somehow, but exactly what is required or expected is unclear to me.

[zmbd]

RockKandee
in your court.

[RockKandee]

I understand that the way the form is behaving is normal.

I understand that as soon as a record on the main form is created, it will be saved in the table. I am not trying to prevent the save of the main form record.

I am trying to prevent the close of the form or moving to a new main form record unless the subform has a record created.

I would like to force the user to create a record in the subform before the user can go to a new record on the main form or close the form.

This could be as simple as auto filling in the main forms ID number into the subform on an after update event on the main form.

I am not sure how else to explain.

Thanks for your help :D

[RockKandee]

When a field is required in a table, then the form will not save the record if the required field is blank.

I want to require that a subform record has been created (by the user) or the form will not close (or go to new record).

Thank you Z for trying to explain for me. *:

[zmbd]

On Parent:
Before update event:
Check for new record
(optional)Validate the entries if needed
(optional)Confirm parent record entry is intended
Set variable at module/form level that new record was created, I use PK value for the new record.

Afterupdate event
Check if the module/form level variable has a value>0 and if so then simple insert SQL to insert a new record into the child table with the value from the variable, set the variable back to a 0 or negative value, and requery the subform, set the focus to the first field/control in the subform.

[NeoPa]

Thanks Kandee. That's clearer now :-)

You can stop the form closing by using the Form_Unload() event procedure and setting Cancel=True.

I'm not sure there is a way to stop the focus moving from one record to another. You could use Form_Current() to see if it has done and then go back. That would be a way to handle it. Not too clean, but workable.

[RockKandee]

Thanks zmbd and NeoPa.

I am trying to work out what you both are saying so I can apply it.

I have been trying to figure out how to search for new record for 2 days now. I am feeling very blond, well I am , so I should, but still feeling like a bit too much bleach soaked into my head. LOL

I will keep at it. Thanks for your help. One of these days I will actually ask a clear question.

[RockKandee]

My temporary solution until I can figure out the above information....

I have a form for the 2 tables that doesn't allow new records.

I added a pop up form that closes this form and opens a form described below using no subforms.
When this form is closed successfully, it re opens the previous form where the user can then add information to the new records created in the pop up form.

POP UP Form
I selected the one required data entry field from each table
Plus Id fields - Table 1 - primary key / table 2 - primary key and foreign key from table 1
Keys are hidden on the form leaving only Table 1 Name field and Table 2 Type field visible.

I set the form so the only option for closing or going to a new record is to use buttons added to the form.
Form - Format - Navigation Buttons - No
Form - Format - Close Button - No
Add Button - ctrlNewRecord
Add Button - ctrlClose

The Close Button:

ctrlClose button - Event - On Click - Macro
If - IsNull([Type]) Then
GoToControl - Control Name - Type
MessageBox - Type cannot be left blank
Else
CloseWindow
OpenForm
EndIf
If - IsNull([Name]) Then
CloseWindow
OpenForm

The New Record Button:

ctrlNewRecord button - Event - On Click - Macro
If - IsNull([Type]) Then
GoToControl - Control Name - Type
MessageBox - Type cannot be left blank
Else
GoTo Record - New
If - IsNull([Name]) Then
GoTo Record - New

This prevents the form from closing or going to a new record
IF the Type field is empty
UNLESS no record was created in Table one (No Name entered).

I am going to keep attempting using zmbd's and NeoPa's suggestions and will update when I have it working. Until then, if anyone else figures this out and can dumb it down for me, that would be awesome!

[zmbd]

I have a few family obligations here; however, once I get those done I'll bash something togeither for you to look at...

[zmbd]

here's a fish...

ACC2010 format

ON open the parent form should open.

THe three buttons at the top for record movement are not foolproof, yet they work fairly well and the add new will take you to the new record entry.

Here's what happens, current record, it calls the buttons and the buttons call the code too.
UPon new, the focus shifts to the first name.
Now if the user dirties the controls (these are bound) then the before update event triggers. IF they select yes then the parent record is saved, the child record created and the focus is set on a control within the subform.

I've tried it several times and it appears to work, has a few more bells and whistles than I had intended...
good night.... time for somzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

[RockKandee]

Hope you had sweet dreams. Thank you! I am in process of dissecting my fish now. You're the best.