By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Force data entry of Main form AND sub form

RockKandee
P: 89
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 19 '13 #1
Share this Question
Share on Google+
17 Replies


P: 12
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.
Dec 19 '13 #2

RockKandee
P: 89
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
Dec 19 '13 #3

P: 12
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..@!
Dec 19 '13 #4

zmbd
Expert Mod 5K+
P: 5,397
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
Dec 19 '13 #5

NeoPa
Expert Mod 15k+
P: 31,487
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.
Dec 19 '13 #6

zmbd
Expert Mod 5K+
P: 5,397
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.
Dec 19 '13 #7

NeoPa
Expert Mod 15k+
P: 31,487
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.
Dec 19 '13 #8

zmbd
Expert Mod 5K+
P: 5,397
RockKandee
in your court.
Dec 19 '13 #9

RockKandee
P: 89
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
Dec 19 '13 #10

RockKandee
P: 89
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. *:
Dec 19 '13 #11

zmbd
Expert Mod 5K+
P: 5,397
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.
Dec 19 '13 #12

NeoPa
Expert Mod 15k+
P: 31,487
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.
Dec 20 '13 #13

RockKandee
P: 89
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.
Dec 20 '13 #14

RockKandee
P: 89
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:
Expand|Select|Wrap|Line Numbers
  1. ctrlClose button - Event - On Click - Macro
  2. If - IsNull([Type]) Then
  3. GoToControl - Control Name - Type
  4. MessageBox - Type cannot be left blank
  5. Else
  6. CloseWindow
  7. OpenForm
  8. EndIf
  9. If - IsNull([Name]) Then
  10. CloseWindow
  11. OpenForm
The New Record Button:
Expand|Select|Wrap|Line Numbers
  1. ctrlNewRecord button - Event - On Click - Macro
  2. If - IsNull([Type]) Then
  3. GoToControl - Control Name - Type
  4. MessageBox - Type cannot be left blank
  5. Else
  6. GoTo Record - New
  7. If - IsNull([Name]) Then
  8. 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!
Dec 20 '13 #15

zmbd
Expert Mod 5K+
P: 5,397
I have a few family obligations here; however, once I get those done I'll bash something togeither for you to look at...
Dec 20 '13 #16

zmbd
Expert Mod 5K+
P: 5,397
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
Attached Files
File Type: zip BytesThread_953656_createchildrecord.zip (112.2 KB, 235 views)
Dec 21 '13 #17

RockKandee
P: 89
Hope you had sweet dreams. Thank you! I am in process of dissecting my fish now. You're the best.
Dec 21 '13 #18

Post your reply

Sign in to post your reply or Sign up for a free account.