Record shouldn't appear

[Guest]

Good afternoon,

***************************************
Goal:
I am trying to set up a form that searches Customer orders by name and
timeframe (between a start date and end date) and starts by a prompt to enter
a start date, end date, and a Customer's First Name, Last name and show this
Customer's name in the main form, and all their orders between start date and
end date in a subform.
***************************************
Structure: I currently have 2 tables I am using to store data, first is
Customer Information table which includes First Name, Last Name, email, etc,
and Order Table which includes, order specifics and description. I am using
autonumber in Customer Info table as primary key and joined this to Order ID
in Order table (set for duplicates OK) creating a 1 to many relationship.

I am also using a parameter queries to prompt a user to enter First name,
Last Name for main query, and a second parameter query to prompt for Start
Date and End Date to be used on subform.
***************************************
Issues: I can't seem to join these parameter queries in a form and subform.
I tried to create a form from these but got a message saying "these objects
cannot be joined by the wisard". So I had to either make referentially
unenforceable join between them in the relationships or not build a form.
But, I found I could create a form using my Customer Info table for primary
form, and paremater query for orders for a subform. That seems to be working
except I tried to enter wrong data in the prompt to see if it handled error
checking ok and my subform parameter query comes up blank for no matching
records fine, but my primary form (Customer Info table) automatically
populates data for the first record in the First and Last name boxes. What
should I do so that if no records are returned by the query, these boxes will
appear blank instead of showing the first record data? Or am I using the
right methodology to create such a form and subform mentoned in first
sentence of this post? Thanks.

 

[Allan Murphy]

Brent

You do not have a relationship between your Customer Table and Order Table

You need the Customer_ref field, your autonumber, in Customer Info as a
field in your Order Table. Customer_ref, non autonumber, in your Order Table
will be your foreign key. Then you can link Customer_ref in your Customer
Info to Customer_ref in your Order Table.

To join the Main and subform use the Customer_ref field as the link

 

[Guest]

Great. I'll try that. But I am curious, doesnt' an autonumber need to join to
an object of sametype, autonumber? I don't think an autonumber can join to a
non autonumber. Can you advise? I really appreciate your assistance Allan.

 

[Guest]

I forgot to ask, Order ID in my Customer Table should be joined to Order ID
in my Order table as a 1 to many relationship. (E.g one Customer plancing
many orders.) Correct? I assume I should set properties for Order ID in
Customer table to (yes, no duplicates) and Order ID in Order Table to (Yes,
duplicates OK) to produce this 1 to many relationship. Correct?

 

[Rick Brandt]

Great. I'll try that. But I am curious, doesnt' an autonumber need to join to
an object of sametype, autonumber? I don't think an autonumber can join to a
non autonumber.

Normally an AutoNumber is joined to a LongInteger. AutoNumber is really just a
LongInteger with a special default value. Joining two AutoNumbers would be a
nonsensical thing to do because any enforced relationship would likely conflict
with the AutoNumber assignment for new records.

 

[tina]

if we assume the following tables, as

tblCustomers
CustomerID (primary key, Autonumber)
FirstName
LastName
(etc, etc)

tblOrders
OrderID (primary key, Autonumber)
CustomerID (foreign key from tblCustomers, Long Integer)
OrderDate
(etc, etc)

then your tables should be linked on the common CustomerID field, which
results in a one-to-many relationship: one customer may have many orders,
BUT each order belongs to only one customer.
the above basically covers what Allan and Rick told you.

a mainform (Customers) / subform (Orders) setup should work, with the
LinkChildFields and LinkMasterFields each set to CustomerID.

hth