Creating record from subsubform

[green]

Hello All and Happy New Year!

I am working on a project where I need to create a record from a subsubform while referencing primary key data from the parent form. I am using the Link Master/Child Fields to filter the data on the subform. I would not have to use the subsubform except my subform exceeds the 22" limitation. I broke the form in to parts on a tab control. To keep the form from auto-scrolling to the first input field, I created a subsubform on each of the two tabs. There are no data fields on the subform. (I have tested the fields there and the record is created correctly.) I need help setting up the parent child links that will allow me to associate the subsubform back to the parent form.

I first tried to use the same data source and child/master link fields for both the subform and the subsubform, I got an error saying the record could not be created without a primary key. I tried a number of other configurations unsuccessfully. Now I get an "Object variable or With block variable not set" error when I try to define the link master/child fields on the subsubform.

As a side note, I cannot eliminate the subform because it also has a tab control with multiple subforms on it. My issue exists with this tab due to the length of the information I have to present.

Thanks in advance!

 

[vbaInet]

Hello All and Happy New Year!

Happy NEw Year to you too!

I need help setting up the parent child links that will allow me to associate the subsubform back to the parent form.

Is this the question? I think you have posted in the wrong section of the forum, it should have gone in the Forms section. Perhaps one of the Admins/Mods can move it to the right section.

In any case, is the subform not linked to the parent form?

 

[green]

I think you have posted in the wrong section of the forum, it should have gone in the Forms section. Perhaps one of the Admins/Mods can move it to the right section.

Apologies. If an admin can help, that is great!

is the subform not linked to the parent form?

I have tried it both ways. My first attempt was to link the subform to the parent and then link the subsubform to the subform.

 

[vbaInet]

I mean, is the first subform linked to the parent form via the Link Master/Child Fields properties?

 

[green]

Yes. (Sorry if unclear.) And I attempted to link the subform with subsubform using with the same fields. This is when I get the "object variable or with block variable" message.

 

[vbaInet]

The point I was going to make is, if the first subform is linked to the parent form and the subsubform is linked to the first subform, doesn't that indirectly mean that the subsubform is filtered by the parent form?

subsubform -> subform -> parent form

subsubform ~> parent form (implicit link via subform).

 

[green]

That makes sense to me and from that perspective I removed the link between the subform and the subsubform. Then when I attempted to input data in the form, I receive an error that the record could not be created without the primary key. The primary key fields (joint key) are only shown on the parent form.

 

[vbaInet]

It's either a flaw in your table design or the way you've created your forms and linked them up. You need to revisit the logic.

 

[green]

I think that is the issue at hand - how should the forms be linked between the parent form and subsubform so the data flows correctly via the subform which is only serving as a "container"?

 

[vbaInet]

Let me see a screenshot of your relationships, i.e. only the tables concerned.

And tell me which subform is bound to which table.

 

[green]

(attached) one to many (Loan > Transaction), one to one (Tran > RegZHELOC)

RegZHELOC is the subform with RegZHELOC1 and RegZHELOC2 being the subsubforms (not shown in the relationships).

 

[vbaInet]

There's no particular benefit of having RegZHELOC as a subform in Transaction if it's only a one-to-one. The only real benefit is that they are in one container.

Transaction should be a subform in Loans.

What do you mean by RegZHELOC1 and RegZHELOC2? What are they? Forms, tables or?

 

[green]

Each transaction is evaluated on up to 8 different regulations. These are presented to the user via tab control on the Transaction form. Each tab presents the information related to the respective reg on a subform. In the case of RegZHELOC the number of questions require two forms (22" form size limitation). These are defined as RegZHELOC1 & 2.

 

[vbaInet]

I don't follow. Can you upload a stripped down version of your db with some test data.

 

[green]

I have stripped the excess from the database and entered a couple records. Note: The same Reg Z HELOC record shows for each loan/tran. Use the New function in the upper right to add a new loan record. Check or uncheck the Reg checkboxes on the Detail screen to show or hide the Reg tab(s).

 

[green]

Any thoughts on this?