After adding data on subform

[Nancy]

Hi,



I have a main form based on tbl_Prep and a subform based upon tbl_WorkOrder. Not all tbl_Prep records have a related record in tbl_WorkOrder.

My problem is that when a new record is created with data entered on both main and subform upon "leaving" the subform, a blank subform screen is displayed. How can I prevent this from happening? Note that there can only be 1 record on subform to 1 record on main form but I cannot have a 1 to 1 relationship.

Appreciate help!
Nancy

[jzwp11]

Note that there can only be 1 record on subform to 1 record on main form

If there can only be at most 1 record in tblWork_Order for a record in tblPrep, then that describes a 1-to-1 relationship. I generally would just not have a separate table but rather just put the fields of tblWork_Order in tblPrep. You would leave those fields blank for a tblPrep record that does not have a work order. In a form, you would not need a subform, but you could hide the controls related to the Work order for those records that don't have one.

Hopefully I am understanding your application; if not, could you provide more detail of what you are doing?

[Nancy]

The problem is that the main form & sub form tables are in 2 different databases handled by 2 different groups that now want to be able to update both tables at the same time. The data in both tables are used on various reports and forms throughtout the company and I don't want to have to update these if I don't have to.

The tbl_Prep and tbl_WorkOrder have data that is not necessarily in both tables. The only link is the WO_number field. I need a form with a subform where the user can enter data and search to update.

The main form & sub form are updating the tables correctly. The problem is with the form screen. After the user enters data on the main form and sub form, I want the data on the subform to remain in view (and not provide a new record sub form screen) until the user wants to add a new record or search a field on the main form.

I hope this helps!

[jzwp11]

I'm still not real clear about why you are using a subform.

In tbl_prep, do you have a field that references the work order # from the work order table?

If so ,the better approach might be to use a combo box on a form based on tbl_Prep. The combo box would be based on the work order table. If the work order number the user wants to enter is not in the database, you can invoke the Not in List event of the combo box that would open a data entry form to allow the addition of a new work order. This will allow the entry of new Work order data and at the same time associate that work order with the particular record in tbl_prep.

Additionally, you could reference other fields brought in via the combo box on your form using the expression =comboboxname.column(x). If a user wants to edit a particular work order's info (from the form based on tbl_Prep) as displayed in the combo box, you can include a button that opens a form filtered to the work order record for editting.

Can you provide the structure of both tables?

[Nancy]

I've tested putting all fields from both tables in a single form and this works fine. However, I would like the WO_number field (key field in tbl_WorkOrder) to be autmatically copied to the WO_number field in the tbl_Prep on the form. What is the best way to do this?

[jzwp11]

I'm not sure how you are getting that to work. I would still go with my earlier suggestion of using a combo box based on the work order table. You can bring in the other fields from the work order table via the combo box and display the info in text boxes by setting the record source for each to a different column of the combo box. You would then have to have some Visual Basic for Application (VBA) code in the after update event of the combo box that would allow your users to add new work orders to the work order table. You would also need an "edit work order" button that would take a user to a form based on the work order table for the current work order displayed in the combo box to allow for editting.

[Nancy]

Thank you for the information. I've read a tutorial on combo boxes and I cannot visualize how it would work. Is there a sample database that I can download that has your suggestions?

[jzwp11]

I did not know of a sample database, so I created one for you. It is attached. Just open frmPrep. For the Work Order combo box either select a work order from the list or just type in a new work order.

[Nancy]

Thank you so much for your help. I was able to use examples in your db example to make my forms work properly.

Happy Holidays!

[jzwp11]

You are welcome. And best of the holidays to you and your family.