Customer Lookup

[dancole42]

I'm self-taught in Access, and as such I'm missing large chunks of
knowledge, so I'm hoping someone here with some training can help me.

Right now I have an Invoice form with a Customer subform.

Right now it's set up so that someone can enter a customer ID, say,
12345, into the invoice form and customer 12345's name, address, etc.
shows up in the Customer subform.

How do I create a system where a user could start a new invoice, then
go over to the Customer subform and type the letter A into the last
name field, then a list of all customers with last names starting with
A pops up, the users clicks on the name they want, and then the
Customer subform gets filled in THAT way, rather than having to know
the exact customer ID?

Ideally I'd have filters for Last Name and Company Name.

Then if they don't find what they're looking for, they can just create
a new customer record.

Your help is appreciated! Thanks!

[Nbene]

Hi self-taught! :)

not sure exactly what you have currently but the way i would work this
is to have a combo box which looks up the list of customers and you
select them that way.

try that first.

use the wizard (good tool) to make a combo box on the form to look
up customer name.

Also make a switchboard, one button to open a form to add an invoice,
one button to open a form to add a customer.

Not quite sure what the relationship is between the two but i imagine
that it is 1 Customer to MANY invoices.

N
On Feb 28, 2:45 am, [e-mail] wrote:

I'm self-taught in Access, and as such I'm missing large chunks of
knowledge, so I'm hoping someone here with some training can help me.

Right now I have an Invoice form with a Customer subform.

Right now it's set up so that someone can enter a customer ID, say,
12345, into the invoice form and customer 12345's name, address, etc.
shows up in the Customer subform.

How do I create a system where a user could start a new invoice, then
go over to the Customer subform and type the letter A into the last
name field, then a list of all customers with last names starting with
A pops up, the users clicks on the name they want, and then the
Customer subform gets filled in THAT way, rather than having to know
the exact customer ID?

Ideally I'd have filters for Last Name and Company Name.

Then if they don't find what they're looking for, they can just create
a new customer record.

Your help is appreciated! Thanks!

[dancole42]

Thanks, N!

I thought about that, but 4,000+ customers is a lot to have in a combo
box :)

And yes, 1 customer to many invoices.

[Don Leverton]

Hi,

I haven't posted in this group for a couple of years now, but felt like
"chiming in" here because I was also self-taught starting with Access 2.0.
I'm still happily churning out small apps for my own use, and still using
Access97 on Win XP :)
================================================== =============
Anyway ... to get to the topic:

IMHO, the best way to design a form like this is to follow the same layout
as the data you are entering ...
in other words use the same 1-to-Many structure as the tables that the form
is based on.

The main form should be frmCustomers, and should include 2 subforms: (1
Customer =Many Invoices)
sbfInvoice (1 Invoice =Many Items)
sbfInvoiceItems

This strategy would allow you to find (or add) a customer.
View previous (or add new) invoices.
View previous (or add new) invoice items.

You could use a combo-box (cboCustomer) to "lookup" an existing customer
(sorted by "LastOrCompanyName".)
You could use the combo box's NotInList event to add a new record without
having to re-type.

sbfInvoice could include code, and be designed to go to a new record by
default ...
but still have the ability to scroll upward to view previous invoices.
(sbfInvoice uses "CustID" in the LinkMaster and LinkChild, and the CustID
from the main form gets inserted into new records automatically)

sbfInvoiceItems (datasheet style) could use a combo-box to lookup and insert
existing stock items along with pricing.
(Yes, a datasheet-style form CAN use a combo-box.) <smile>
(sbfInvoiceItems uses InvoiceID in the LinkMaster and LinkChild, and the
InvoiceID from "sbfInvoice" gets inserted into every new record
automatically)

Think "flexibility" when doing form design.
Draw out your ideas on paper, first ... then design your form.

One more thing ... you CAN put subforms and associated command buttons on a
tab control.
Doing so maximizes the useability of the form and reduces confusion ... as
long as the design is well-planned and the user interface is intuitive.

HTH,
Don
<da*******@gmail.comwrote in message
news:11**********************@h3g2000cwc.googlegro ups.com...

I'm self-taught in Access, and as such I'm missing large chunks of
knowledge, so I'm hoping someone here with some training can help me.

Right now I have an Invoice form with a Customer subform.

Right now it's set up so that someone can enter a customer ID, say,
12345, into the invoice form and customer 12345's name, address, etc.
shows up in the Customer subform.

How do I create a system where a user could start a new invoice, then
go over to the Customer subform and type the letter A into the last
name field, then a list of all customers with last names starting with
A pops up, the users clicks on the name they want, and then the
Customer subform gets filled in THAT way, rather than having to know
the exact customer ID?

Ideally I'd have filters for Last Name and Company Name.

Then if they don't find what they're looking for, they can just create
a new customer record.

Your help is appreciated! Thanks!