Form design question

J

James Martin

Suppose we have a customer form where the first field on the form is
Customer Name. (There's a Customer Code as well which is the actual key of
the file, but that is hidden from the user.) The Customer Name field is a
combo box that the user can use to jump to a specific customer.

The only catch is that, on the rare occasion when the user needs to modify
the customer name, we need to alert Access that their typing is meant to
change the customer name, not jump to another customer. So we've added a
button for "Edit customer name". We've used the button for editing the name
since the primary concern is the ability to jump to other records easily.

My question is, can anyone suggest a smoother way to handle these kind of
forms?

Thanks.

James
 
J

Jeff Boyce

James

Perhaps I'm missing something...

I thought the combobox was for SELECTING a customer. How could you change
the customer name before selecting the customer?

Besides, if the combobox actually captures the CustomerCode, you really
REALLY don't want to be pulling up Customer A and changing THAT customers
name, do you?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

James Martin

Hi Jeff,

The combo box is for the customer name. I have the customer code hidden from
the user. So the first field on the form is the customer name which, 99% of
the time, the user will use as a drop-down selector to jump to the customer
they want to see. But occasionally they will need to change a customer name,
so I have an edit button that they use is that instance.

The form works as it is. I was just wondering if there is a smoother or more
common way to handle these kinds of forms. It's more of a design question
from the point of view of ease of use for the user.

Thanks.

James
 
J

Jeff Boyce

James

Since I'm not there, I can only guess about how you have set this up.

What I can do is describe a common approach, and you can see if that's what
your form is doing...

I create a query that returns customer info. I create a form that uses the
query. At this point, the form loads ALL of the customers, and I can scroll
back/forth through the customers.

To make it load faster, and to help find a specific customer, I add an
unbound combobox in the header of the form. This combobox is based on a
query which returns ONLY CustomerID and CustomerName (for me, this second
field is usually the concatenation of LastName & ", " & FirstName from my
tblCustomer). I sort ascending on CustomerName.

In the combobox's properties, I set the column width of the first column
(CustomerID) to 0 so it doesn't display. What does display is an
alpha-sorted list of customers, last name first. This gives the user a way
to either scroll & select or start typing-to-select a specific customer.
NOTE: if I have more than one Smith, John in my db, I need to add
additional identifying info in the combobox.

Now I need to tell the query that feeds the form to look to the combobox to
get the CustomerID of the record I want the query to return (and the form to
display). Open the query in design view, set the selection criterion to
something like:
Forms!MyForm!cboSelectCustomer (use your form and combobox names)

The last step is to tell Access to refresh the form after the selection in
the combobox. Open the AfterUpdate event procedure for the combobox and
put:
Me.Requery

Is this what you are doing?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

James Martin

Hi again,

Yes, that sounds very similar to what you've outlined.

Just one question: do you essentially have the customer name appearing twice
on the form? Once in the unbound drop-down navigator and once in the actual
data fields? I realize that in this case they aren't identical because the
unbound drop-down field is a combination of the first and last name fields.
But, in general, on a form where there is only field for name (for example,
a company name, rather than two fields, one for first name and one for last
name), would you have the name appearing twice - once in the drop-down
navigator and once in the actual field where the user can modify it?

James
 
J

James Martin

Hi again,

Yes, that sounds very similar to what you've outlined.

Just one question: do you essentially have the customer name appearing twice
on the form? Once in the unbound drop-down navigator and once in the actual
data fields? I realize that in this case they aren't identical because the
unbound drop-down field is a combination of the first and last name fields.
But, in general, on a form where there is only field for name (for example,
a company name, rather than two fields, one for first name and one for last
name), would you have the name appearing twice - once in the drop-down
navigator and once in the actual field where the user can modify it?

James
 
J

Jeff Boyce

James

Since my combo box is unbound, I DO have a control on the form for LastName
and FirstName.

I'll urge you to reconsider your single field holding two (different)
names -- good database design is "atomic" fields -- one fact, one field.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top