Complex form problem (1 Viewer)

Hi Form experts,

I have a relatively complex form based on one table. It organizes and collects data on the main form and on several forms that are launched from a combobox. The forms that are launched from the combobox have the same record source as the main form but simple focus on the subject matter selected in the Combobox. Now, to the problem. For some reason as data is entered into some of the forms that are launched from the Combobox instead of simple filling in the table values into the current row it create a new row. How can I prevent this from happening?

How are they launched? What is their Data Entry property? Have you considered a single form with tabs for the data now on separate forms?

This structure is not recommended. You really do not want to have two forms open to the same record, both of which will update it.

A better technique is to add a tab control to the main form. Separate the fields so that they are separated on to several tabs to organize them better. Even though not all controls will be visible at one time, there is only one record bound to the form and you will not be creating bogus records.

The problem with your current technique ( I hate to tell you because I want you to stop doing this) is that you MUST save the current record BEFORE you open the dialog forms and you MUST pass in the PK of the current record on the main form to avoid creating new records. Make sure that the popups are dialogs so that code processing in the calling form is suspended until the popup closes. You will need to refresh the main form immediately after the Open Form method to ensure that the updates from the popup are reflected in the current form.

And finally, if you have so many controls that they won't comfortably fit on one form, you probably have repeating groups and should restructure the table.

Hi Pat,

What exactly do, I do to as you said "to refresh the main form immediately"?

Pat,

I added this code to the on click event of the button that launches the form: Forms!Donor_Intake_Form.Requery. It caused the cursor to move into the first field on the main form. So, I had to navigate back to where, I was but I still got another record inserted.:banghead:

Any thoughts on

pbaldy said:

How are they launched? What is their Data Entry property? Have you considered a single form with tabs for the data now on separate forms?

Click to expand...

Q - How are they launched? Answer - I have a combo box that has a VBA case statement to activate a button that in turn launches the form to collect the information needed. The fields on the pop-up form are simple columns from the same table that is the data source for the main form. As, I mentioned in the first thread each of these forms focus on a logical data group. I have used tabs with the same result. If the data was related by a PK to a FK it works easily. But, as I have said it is all in one table to simplify the structure.

Can you post a screenshot of your table in Design view so we can see all the fields?

I meant the actual code that opens the form, and you didn't address the Data Entry property of the form being opened.

Here is the table structure and the code that opens the forms. There is nothing special about opening the form for editing. The form is just opened to a blank row for data entry.

Private Sub btnTest_Click()
Select Case [CboGiftType]
Case "Pledge"
Me.btnTest.Caption = Me.CboGiftType.Value
DoCmd.OpenForm "frmPaySch"
Case "Pledge Payments"
Me.btnTest.Caption = Me.CboGiftType.Value
Case "Secondary Pledge Payments"
Me.btnTest.Caption = Me.CboGiftType.Value
Case "Outright Gift"
Me.btnTest.Caption = Me.CboGiftType.Value
Case "Benefit"
Me.btnTest.Caption = Me.CboGiftType.Value
DoCmd.OpenForm "frmBen-Evt"
Case "Gift-In-Kind"
Me.btnTest.Caption = Me.CboGiftType.Value
DoCmd.OpenForm "frmGift-In-Kind"

End Select
End Sub

What does this mean exactly, and again, what is the Data Entry property of that form?

Tupacmoche said:

The form is just opened to a blank row for data entry.

Click to expand...

I said refresh NOT requery. the popup form is not adding a record. It is updating an existing record.

Stop whatever it is you posted about and focus on your table structure. Access is not Excel With Forms--its a an actual database and databases require that you store your data properly. That process is called normalization, (https://en.wikipedia.org/wiki/Database_normalization) which you need to read up on and apply to your data.

Here are the big things I see that you did wrong:

Numerated fields - when you start adding numbers to field names (e.g. PP1_Amt, PP2_Amt, etc.) its time for a new table for that data. Instead of vertically (with more columns) you should accomodate data horizontally (with more rows). That means that PPX_Amt data should not be in 5 columns in your current table, but 5 rows of a new table.

Storgage of redundant data - This applies in many places, but lets focus on your Donor data as an example. You correctly have a DonorID field in your table, incorrectly though you also have every other piece of donor data. Instead, all that other data goes into a Donor Table and just the DonorID goes into this table. This is how foreign keys work--I see you have commented that, but you haven't properly done it.

Again those are just the big things I see going by just your field names alone. If I saw sample data I am sure I could find more issues. You need to structure your data properly first and foremost. Next you need to build the reports/queries to get your data out of your database. Finally, you work on forms. So again, forget whatever it is you posted about and focus on normalization of your data.

In orde for the pop up form to go to same record as in your main form add criteria when you open the form:

Select Case [CboGiftType]
Case "Pledge"
Me.btnTest.Caption = Me.CboGiftType.Value
DoCmd.OpenForm "frmPaySch", , , "[fk of popup]=" & [pk of main]