Difficulty adding new records using search form (1 Viewer)

Hello all, I'm fairly new to Access but I've been working hard at learning it. I appreciate any help you guys can offer. You have an amazing forum here, and I've already found it invaluable to my Access learning.

I’m working on making a fairly simple automotive repair order database for a friend who is using some very old/legacy software and is now having issues. I'm uploading my DB, I've scrubbed it, for you to have a look at. I’ve created a search form (CustomerSearch) that works and displays the appropriate info, as well as a Repair Order form (NewRepairOrder) that I’m able to fill out and works appropriately.

My problem is I’m not able to use my Repair Order form to create a new RO (Repair Order), I'm stuck. It’s data source is a select query, which works great for editing an existing RO, but not creating a new one. I can use the search form and select one of the top two customers, that I’ve manually created an RO for, and click the Open R.O. Button on the search form and have the RO form open to the correct Customer and RO, which I can then edit. But if I select the third customer in the search results, who has no Repair Orders created for them, I can’t figure out how to open the RO form using this customers CustID (to populate their address, phone, etc) and create a brand-new RO for them. I was thinking I needed to use an append query to create a new Repair Order for the customer and then pass that new ROID onto the form, but I’m not sure if that’s the correct solution and how to do that.

I’ve tried to normalize this DB as much as possible so my Customers table is joined to a RepairOrders table and a Vehicles Table. RepairOrders is joined to tables: Labor, Parts, ContractLabor, etc.

Thanks again for all your help!

Bump...
A minor glitch in the forum software where by it occasionally marks a post as moderated.

In other words it is not visible to anyone except the moderators. Unfortunately this happened to this post, so I'm Bumping it so it receives some attention ...

Your create button is trying to go to an existing record. You want a new record, so:

DoCmd.OpenForm "NewRepairOrder", , , , acFormAdd

You can then push the customer value to the just-opened form. Personally I wouldn't have the form based on the 2-table query, just on the RO table. As is, you get a wonky error when pushing the customer ID. I'd use this for the customer related data:

http://www.baldyweb.com/Autofill.htm

Oh, and the pushing of the value looks like:

Forms!NewRepairOrder.CustIDNm = Me.[CustomerSearchSubForm].[Form]![CustID]

Thanks Paul!!!

I think I tried some combinations of that but didn't hit on the right thing.
Thank you for all the suggestions and the fast response, greatly appreciated.

I'll work on implementing them now.

-Dustin

No problem Dustin, post back if you get stuck.

Hey Paul, I've been working on redesigning my Form, NewRepairOrder, with your suggestions, including basing it on the RepairOrders table. You were spot on there.

Everything works correctly when I open an existing Repair Order from the CustomerSearch form.

I implemented "DoCmd.OpenForm "NewRepairOrder", , , , acFormAdd", and that works great! But, I am not able to get my SubForm on the NewRepairOrder that contains the Customer info to work correctly. I've tried using a select query and setting the CustID criteria for the CustID from the Main Form and this works correctly when I open an existing Repair Order, but when I create a new repair order the info never shows up. The CustID shows correctly on the main form, and the new Repair Order that is created is storing the correct CustID value with the new Repair Order entry created in the RepairOrders table, it's just not coming up in the subform.

I was thinking this had to do with the Customers Subform loading before the Mainform and not having a CustID value for the criteria. I did some reading on getting the subform to load after the mainform, but I'm still not entirely sure how to make that happen.

I tried your suggestion from: baldyweb.com/Autofill.htm
That worked, but I need to be able to edit the customer info in the Repair Order form so I decided to go the Subform direction.

I've attached the updated DB file for easy review.
Thanks again for all your help!

-Dustin