Form and Subform new entry problems

[Reese]

I have a form for entering event information with a subform for information on the client that is booking the event. The record source for the event form is an event table and the record source for the client form is a client table.

I created the subform as it's own form initially and as it's own form I can create new entries as normal. In the event information table I can associate the two forms using the Client_ID field. However, when creating an entirely new entry using the event form and I start filling out the client subform with a new client there is no autonumber generated in the Client_ID field. Then, once I leave the subform and move back to the main form I get the message: "Index or Primary Key cannot contain a Null value".

What is going on? How can I create a new event in the main form, enter a new client in the subform, and create a relationship between the two all in one system with out having to open up the client form first, open the event form, then open up the event table to create the link?

Also, suddenly the Client_ID autonumber in the client information table went from 9 to 129670. Any idea why? (I know, this last point may be for a different forum, but the whole situation is one confusing bundle for me.)

 

[bikermo]

try forcing a new record in the main form after your first entry (tab stop zero). put this code on the "on enter" event:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

once a new record is created in the main form, you should be able to create a new record on the subform.

 

[Reese]

Unfortunately that doesn't seem to work. I tried using the code in both the first entry of the main form and the first entry of the sub form. Neither worked.

Just to make sure we're on the same page, the main form creates a new entry with an autonumber for the Event_ID without a problem. It's the subform that has a problem with creating a new entry. When the sub form first loads the Client_ID text box has the default (New) but then as soon as I start typing in the first textbox of the sub form the Client_ID field goes blank, not generating an autonumber.

 

[Solo712]

Unfortunately that doesn't seem to work. I tried using the code in both the first entry of the main form and the first entry of the sub form. Neither worked.

Just to make sure we're on the same page, the main form creates a new entry with an autonumber for the Event_ID without a problem. It's the subform that has a problem with creating a new entry. When the sub form first loads the Client_ID text box has the default (New) but then as soon as I start typing in the first textbox of the sub form the Client_ID field goes blank, not generating an autonumber.

The Client_ID is the PK on the main form and master field in the link, and FK in the subform and defined as a child key, correct ? You have not by any chance entered the sub's PK in the child's field have you ? Because that is what it looks like. The sub's autonumber has no significance in the relationship.

Best,
Jiri

 

[Reese]

The primary key for the main form is Event_ID and the primary key for the sub form is Client_ID. The parent/child relationship is the Client_ID in the subform (primary key) and a field called Client_ID in the main form (not primary key). Are you saying that is incorrect?

 

[Solo712]

The primary key for the main form is Event_ID and the primary key for the sub form is Client_ID. The parent/child relationship is the Client_ID in the subform (primary key) and a field called Client_ID in the main form (not primary key). Are you saying that is incorrect?

There is your problem right there. The main form needs to link with the sub via master field on entry, but if you choose a PK for the sub, the value will not be known until the autonumber is generated. Hence your problems. Not to worry, I have learned this in the school of hard knocks myself !

Best,
Jiri

 

[Reese]

Does that mean that each entry (ID) of the sub form needs to be linked to each entry (ID) of main form before the form is even opened?

Reason why I'm asking is because my goal is to be able to open up one form (comprised of a main form and subforms) that can create a new event, enter in a client (either creating a new client or choosing a past client), calculate the appropriate price and have all components automatically or manually but easily linked together. The event, client and cost information are all in separate tables.

 

[Reese]

The reason why this needs to be done on one form is because the end users (including myself) would be entering this information while speaking to clients over the phone. Since we're already multitasking by entering information and talking over the phone the data entry part needs to be as smooth as possible.

And sometimes, just in conversation, clients will bounce back and forth with questions or information that would be handle by different tables and forms. So I don't want to have to keep opening and closing three different forms while booking a program.

 

[Solo712]

Does that mean that each entry (ID) of the sub form needs to be linked to each entry (ID) of main form before the form is even opened?

You are opening a subform to one value in the master field. If this value is not known to the main form there is nothing to link.

Reason why I'm asking is because my goal is to be able to open up one form (comprised of a main form and subforms) that can create a new event, enter in a client (either creating a new client or choosing a past client), calculate the appropriate price and have all components automatically or manually but easily linked together. The event, client and cost information are all in separate tables.

This sounds confused to me. How are going to choose 'a past client' if the client form is a sub to another form ?

You want to link the two forms by Client_ID. Whether you like it or not, this design decision forces you to either have the Client table with its the primary key as the main table, or to have the Event table save the Client_ID (as a non primary Key) before it can link to a subform by this key. That subform cannot be the Client table with Client_ID as the primary key. You need another design. End of story.

Best,
Jiri

 

[Reese]

I'll try having the client form be the main form with the event and cost forms being sub forms within it. I don't care about how things are set up within the background (including what's a subform vs. main form). All I care about is for the end user to be able to enter and calculate all three parts with only opening up one screen--not having to manually bounce back and forth between forms and tables.

I'll let you know how things work and if I have any other questions.

 

[Solo712]

I'll try having the client form be the main form with the event and cost forms being sub forms within it. I don't care about how things are set up within the background (including what's a subform vs. main form). All I care about is for the end user to be able to enter and calculate all three parts with only opening up one screen--not having to manually bounce back and forth between forms and tables.

I'll let you know how things work and if I have any other questions.

Good luck with it, Reese ! BTW, you do not need to set up the relationship between the tables as form-subform. Your listbox(es) referencing one or more tables can be unbound in the design, loaded from the table when the form opens and the link to the other form table(s) supplied by selection from the box(es) at run-time.

Best,
Jiri