Subform search box to select record - master/child field problems (1 Viewer)

jellyberg

New member
Local time
Today, 10:09
Joined
Aug 25, 2015
Messages
5
Hey folks

Situation

I have two relevant tables: Orders and Customers.

I'm creating a big long form to enter a new order. One part of this form is entering the customer details. The customer and order are linked by AutoNumber CustomerID.

I have a subform called Edit Customer within the New Order form. The subform and master form are linked by a control in the New Order form called CustomerID and a control in the Edit Customer form called CustomerID.

In the header of Edit Customer form I have a search box that uses FindFirst to go to the first record with the name entered, and if that name is not found it does AddNew. I don't want the users to have to concern themselves with CustomerID at any point.

Problem

I want the CustomerID controls to be set by the subform's search box, but when I click inside the subform I get an error:

The Microsoft Access Database Engine cannot find a record in the table 'Customers' with key matching field(s) CustomerID

Of course, CustomerID is currently set to null because I haven't searched for a customer yet!

Any ideas?
 

spikepl

Eledittingent Beliped
Local time
Today, 11:09
Joined
Nov 3, 2010
Messages
6,144
Your thing is upside down: the one-to-many is one customer with many orders, and then you'd have the customer in your main form and the customer's orders in the subform. Further, usually an order comprises some common stuff and then multiple order details or order lines.
 

jellyberg

New member
Local time
Today, 10:09
Joined
Aug 25, 2015
Messages
5
Thanks for replying spikepl. I see what you mean, however for the situation I'm in, users will only ever be inputting one order at a time. In reality the only reason customer details are being stored in a separate table to order details is so the customer details can be automatically filled out if they have shopped with us before. The entire database's purpose is simply to generate invoices and track monthly revenue.

I see now that I have done it a bit upside down - but given the situation (I can't start again now, deadline is looming!) is there any way to get this working with the current db design?

Thanks again.
 
Last edited:

Users who are viewing this thread

Top Bottom