Can not get form to work

[Guest]

I have a table for Items I'm selling, a table for Customers, and a table for
Sales information. I am trying to create a form where I can select a
customer from a drop down menu, which will then fill in all the customer's
information (the way Northwind's sample database does). I believe I have all
my links correct, but no matter what I do I cannot select a customer for a
sale. I haven't even gotten to the point of selecting an item for each sale.
At this point I will GLADLY pay someone to create a form that will allow me
to select a customer and then select the specific item(s) for each sale (I've
been at this for days, and I have gone over Northwind's database with as fine
a toothed comb as I have at my disposal and I have to throw in the towel!).
Anyone out there willing to look at my database and quote me a price?

 

[Allen Browne]

Lets clarify what you need, and the options available.

Firstly, there's a basic database rule that says you must not store the same
info in multiple places. So things like the customer name must be stored in
the customer table only.

But there could be times when a customer wants an order to go to a
particular place that is not their normal address. To handle this, you DO
need to store the address associated with each order, like Northwind does.

So, how does it fill in the address fields as soon as you select a customer
in the Orders form? If you open the Orders form in design view, and look at
the form's RecordSource, you will see that it is a query that combines the
Orders table and the Customers table. As a result, the default customer
address is available in the fields of the form. It then uses the AfterUpdate
event of the form to assign the address fields. To see how it does that,
right-click the CustomerID combo and choose Properties. Click the Build
button beside the AfterUpdate property (Event tab of Properties box.) You
are taken to the VBA window where you can see how each of the fields is
assigned, from the address fields that are supplied by the form's source
query.

Hopefully that will help you understand what's happening, and let you create
it for yourself.

 

[Tom Lake]

But there could be times when a customer wants an order to go to a

particular place that is not their normal address. To handle this, you DO
need to store the address associated with each order, like Northwind does.

What I do is have both a residence address and mailing address in the
Customer table then on each transaction I have a yes/no field asking Ship to
residence? If the flag is Yes, I use the residence address from the
Customer table, if no, I use the mailing address. Then I don't have to
store address data in the transactions (orders) table.

Tom Lake

 

[Allen Browne]

Oaky, that's great. You don't need to store the address in the orders at
all.

Now, presumably you want to print out the order and show the home address if
ShipToResidence is checked, or the mailing address if the box is not
checked. On your report, you will have text boxes that have this kind of
thing in their Control Source property:
=IIf([ShipToResidence], [ResidenceAddress], [MailingAddress])
and then on the next line another text box:
=IIf([ShipToResidence], [ResidenceCity], [MailingCity])

The actual content will depend on your field names.

 

[Guest]

Actually, my customer data is stored in my customer table and nowhere else.
I only want to retrieve that information for each order, and my limited
understanding of Access was that I did that through a form. After I print
out an invoice for the customer and a copy for my file I don't care any more
if the association of customer to item ordered is saved - the reports that I
will want to generate will be for types of books that are and are not selling
and when; my customer base will be large and although reorders from the same
person are nice, they're not expected, because my business is web based.

I've tried doing a sales form with a subform to select a customer, but that
doesn't work because the relationship is backwards - 1 customer could have
many orders, but each order will have only 1 customer. I tried doing a
customer form and then adding in a sales subform so that I could select the
items, price, date sold, etc., but I couldn't get that to work, either. I've
been at this for more than a week now, and I've run through every book on
Access I have (7), gone through every self-paced course Microsoft offers,
have looked at MS's sample databases and at Northwinds, and I'm just about to
the point of going back to my spreadsheets even though it will mean
duplicating information in several places. What I have is 1 customer could
place 1 or more orders for 1 or more items, but I want to create an invoice
for each order showing each item ordered (1 - ???), date, price paid,
shipping charges, and then have a command button that will print out 2 copies
of the invoice - 1 will ship with the order, 1 will stay with my files. (I
know - I should be getting away from paper filing systems, but I don't trust
computers - not even backups!)

I can get the customer's name or ID to show up on a sales form with the
items, but I can't figure out how to get all the customer's information
without manually typing it in, and then there's the problem of typos.

Allen and Tom - I haven't been able to fully digest your replies yet, so
maybe when I'm fresh tomorrow this will make more sense. If so, I'll let you
know. If not, I'll just keep digging.

Thanks for your help!
Tina

Oaky, that's great. You don't need to store the address in the orders at
all.

Now, presumably you want to print out the order and show the home address if
ShipToResidence is checked, or the mailing address if the box is not
checked. On your report, you will have text boxes that have this kind of
thing in their Control Source property:
=IIf([ShipToResidence], [ResidenceAddress], [MailingAddress])
and then on the next line another text box:
=IIf([ShipToResidence], [ResidenceCity], [MailingCity])

The actual content will depend on your field names.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What I do is have both a residence address and mailing address in the
Customer table then on each transaction I have a yes/no field asking Ship
to residence? If the flag is Yes, I use the residence address from the
Customer table, if no, I use the mailing address. Then I don't have to
store address data in the transactions (orders) table.