Selecting a record

[J.Bennett]

I have a database I developed in which I have 3 forms created. The first
form is for customer information (name, phone, address, etc.), the second
form is for specific information on the customers specific purchase, and the
third form is the output (which has the computed prices, taxes, etc). The
first two forms used to store the information in the customer table. The
third form reads from a query.

Not being well versed in access, I have set up a field on the first form
that allows the user to check the "Select" box. When you click on the button
set up to close the first form and open the second form, it searches for the
record with the "Select" field checked. The query also only pull the records
with the "Select" field checked.

My question is how can I set up my first form so that when the "Select"
field is checked for the current record, the "Select" field for all other
records is changed to be unselected? Is there a way to do this with a Macro?


My goal is to only have one record ever "Selected" at a time. As it stands
now, I must go back and uncheck an records that were already checked
previously.

Any suggestions or guidance would be appreciated.

 

[Tom Wickerath]

Hi J. Bennett,

Is your goal to open the second form (information on the customers specific
purchase [purchases?]) from the first form (customer information). If so,
there is a much easier way than trying to use a "select" box (check box?) as
you have described. Instead, you can use the optional WhereCondition argument
of the DoCmd.OpenForm method. But before we go any further, can you confirm
that my guess is correct? If so, please provide some structual information
about your database: table and field names involved along with data types
(text, number).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[J.Bennett]

Tom,
I believe you are correct. What I have created is a database to automate
the process of selling storage buildings. The first form that is opened is
named "Customer Info" Form. This has the basic information of name, phone,
address, etc. I also added to this form a check box "to use this record check
here". I have added a macro that runs on opening the form to search for the
first record with the check box selected.

I created a macro and added it to a button to the top of the "Customer
Info" form. When the button is selected, it closes the "Customer Ino" Form
and opens a "Building Info" form. The form is set to search for the first
record with the check box selected, just like the "Customer Info" form.

This is repeated for the third (and last form) named "Ouput Info" form.
While the first two forms are storing information in the "Customer Info"
table, the third form reads information from the query named "Calculation
Query". This query is set to only use the records that have the "Use this
Record" check box selected.

The final step, and the ultimate reason for creating this database, is to be
able to print the forms with all this information (including the calculated
fields from the query) onto forms that I have designed to mimick those
required by the manufacturer of the buildings.

I have this all working now, but the user must be sure to uncheck any
records that were checked prior to the addition of a new record. If there
was a way to change all the "to use this record check here" so that they are
not checked, this would solve my issue.

However, I am open to any suggestions. The basic information regarding my
database is as follows:

Customer information is stored into table named "Customer Info Table"
I have fields named "First Name", "Last Name", "Select" (which is the check
box that is used for selecting that particular record for use".

The form names that look for the selected record are:
"Customer Info Form"
"Building Info Form"

The query that limits the records is named "Calculation Query"
The last form is the "Output Information Form", which reads information from
the "Calculation Query."

Sorry for the long explanation, but I really do appreciate any guidance that
you can provide. I am open to any suggestions.

Hi J. Bennett,

Is your goal to open the second form (information on the customers specific
purchase [purchases?]) from the first form (customer information). If so,
there is a much easier way than trying to use a "select" box (check box?) as
you have described. Instead, you can use the optional WhereCondition argument
of the DoCmd.OpenForm method. But before we go any further, can you confirm
that my guess is correct? If so, please provide some structual information
about your database: table and field names involved along with data types
(text, number).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a database I developed in which I have 3 forms created. The first
form is for customer information (name, phone, address, etc.), the second
form is for specific information on the customers specific purchase, and the
third form is the output (which has the computed prices, taxes, etc). The
first two forms used to store the information in the customer table. The
third form reads from a query.

Not being well versed in access, I have set up a field on the first form
that allows the user to check the "Select" box. When you click on the button
set up to close the first form and open the second form, it searches for the
record with the "Select" field checked. The query also only pull the records
with the "Select" field checked.

My question is how can I set up my first form so that when the "Select"
field is checked for the current record, the "Select" field for all other
records is changed to be unselected? Is there a way to do this with a Macro?


My goal is to only have one record ever "Selected" at a time. As it stands
now, I must go back and uncheck an records that were already checked
previously.

Any suggestions or guidance would be appreciated.

 

[Tom Wickerath]

Hi J. Bennett,

I am open to any suggestions.

Are you open to the idea of eliminating the Select field idea? Frankly, this
sounds pretty confusing to me; it places a burden upon either the user or you
to ensure that all other possible records are deselected for a given
customer. It just doesn't sound like a workable idea to me.

It sounds to me like you have a many-to-many (M:N) relationship between
Customers and Building Types. This would require a minimum of three tables: A
Customers table, a Building Types table, and a third linking or join table. A
M:N relationship is created using two one-to-many (1:M) relationships, with
the linking or join table having the foreign keys of each 1:M relationship.
Translated into English sentences, the relationships could be stated as:

1:M A customer can order zero to many building types.
and
1:M A building type can be purchased by zero to many customers.

The customary way of displaying this type of relationship is to have a main
form based on one of the tables, with a subform that displays records from
the join or linking table plus the other table. So, for your example, a form
that displays customers, with a subform that displays all building types that
a particular customer has ordered OR a form that displays building types with
a subform that displays all customers who have ordered that particular
building type. Of course, you can have a form that displays just customers,
without a subform, and a form that displays just building types.

Take a look at the sample Northwind database, which is likely already
installed on your PC (search for Northwind.mdb). You can download a copy from
here, if needed:

http://www.microsoft.com/downloads/...72-8dbe-422b-8676-c632d66c529c&displaylang=en

Open the Customer Orders form. You should see 91 records. This form includes
two subforms: the first subform displays all orders placed by each customer,
and the second linked subform displays order details for each selected order.
I'm thinking that you might want to aim for this type of design, which does
not involve any field that stores which record is in a selected state.

While the first two forms are storing information in the "Customer Info"
table,....

Whoa! If this is a true statement, then you have what is commonly known as
an "Access spreadsheet". You are not taking advantage of the relational
capabilities of Microsoft Access. My guess is that you would need to add a
new record, repeating Customer Info, each time a given customer orders
another storage building. A table should include one subject, similar to the
way in which a written paragraph should be based on one subject. Open the
relationship view (Tools | Relationships) for the Northwind sample database.
Here, you will see several different tables, including tables for Customers,
Orders, Order Details, Products, Suppliers, etc.

The final step, and the ultimate reason for creating this database, is to be
able to print the forms with all this information...

It is usually always much better to design reports for printing.

Customer information is stored into table named "Customer Info Table"
I have fields named "First Name", "Last Name", "Select" (which is the check
box that is used for selecting that particular record for use".

May I suggest instead that you not use any spaces or special characters in
the names of any fields? The same goes for controls on forms and reports,
along with objects in the database (tables, queries, forms, reports, macros
and modules). Yes, you can use spaces, but I think you will find your future
development efforts to be simplified by avoiding the use of special
characters.

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

You should also avoid using any reserved words for anything that you assign
a name to within Access. The word "Select" is a reserved word. Select is a
SQL (Structured Query Language) keyword, which would be found as the first
word in all SELECT queries. Access MVP Allen Browne maintains an extensive
listing of reserved words:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

He also has a very cool utility that is free, which you can download to scan
your existing database for the use of reserved words in field and table
names, among other issues.

I'd like to also refer you to an excellent collection of papers on database
design:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

If nothing else, make sure to read the first two articles written by
database design expert Michael Hernandez, who is the author of the book
"Database Design for Mere Mortals" (and co-author of "SQL Queries for Mere
Mortals").


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[J.Bennett]

Thanks, Tom.
Let me start by saying "Thank YOU!" for the response. I do appreicate the
guidance.

I will take a look at the articles and the database you referenced. One
thing to keep in mind, I do not expect repeat customers so I was not
concerned with the cross referencing capabilities. Your assessment that this
is an Access Spreadsheet is correct. I developed this database so that the
users (small family business) could very easily enter the information
regarding the customer, select the building and the options desired, enter in
the payment information, and then have the ability to print the forms with
the information. I designed the forms to mimmick the hand written forms
required by the building company. With the database computing all the
prices, along with "Error" messages I have installed on the various forms for
when something doesn't match, it virtually eliminates errors and ommissions
in the final printed order form. The reason I made two forms ("Customer
Info" and "Building Info") for inputting data was simply to make it easier
for the user and to be able to view all the information on the monitor at one
time without a considerable amount of scrolling. Keeping the customer
information on one form and the Building info on another form, allows the
user to only review the building information when a customer is asking about
the various options (prices for the various sizes, what if we add windows,
what about extra doors, what will my month).

One thing that I have included in the database is that all the options
(doors, windows, etc.) are in their own table, the roof color and type
(shingle vs. metal, and the 6 different choices for color) and various other
items are all in their own tables and are selected from Drop-Down list (combo
list).

One thing I would appreciate your response on is the comment below following
my original comment:
Your reply stated:

It is usually always much better to design reports for printing.

On the "Output" form, I have a button that previews a report, based on the
"Calculation Query", which is the same query the "Output" form is based on.
So, I am not actually printing the form that is displayed on the screen, I am
printing a report. Is this what you were referring is the better way to
print?

Finally, and the reason for my original post, requiring the user to
"deselect" all other records is confusing. That why I was hoping to find a
way around this and eliminate the user from being required to do this.

I will look further into the information you provided. If the information I
provided above changes any of your suggestions, I would appreciate your reply.

Again, thank you for all the assistance.

 

[Tom Wickerath]

I do not expect repeat customers so I was not

concerned with the cross referencing capabilities.

An unwanted side effect of such a design is that your user must type in the
data that is repeated each time. That can lead to spelling errors, which in
turn can lead to inaccurate results reported, for example, when a parameter
query-based report is run. In Access 2000-2003, click on Tools | Analyze |
Table. Read the first screen of the table analyzer wizard to see examples of
the issues involved. Make sure to click on each of the arrow buttons labeled
"Show me an example". After finished with this screen, I recommend canceling
the Table Analyzer wizard. Don't let it actually make any changes to the
design of your database by clicking on the Next button. This wizard will
happily create table lookup fields for you, which many, including myself,
consider evil. Take a look at the 2nd Commandment of Access to learn more
about the evils of lookup fields defined at either the table or query level:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

...it virtually eliminates errors and omissions in the final printed order form.
Congratulations!

The reason I made two forms ("Customer Info" and "Building Info") for
inputting data was simply to make it easier for the user and to be able to
view all the information on the monitor at one time without a considerable
amount of scrolling.

I agree that scrolling is bad. Have you considered the use of a tab control?
This would allow you to place customer information on one tab, and building
information on a second tab. A similar example is shown on the Northwind
Employees form.

So, I am not actually printing the form that is displayed on the screen, I am
printing a report. Is this what you were referring is the better way to
print?

Yes. (When you print a form, there is no way to suppress printing the detail
color, which just consumes printer ink unnecessarily. Also, printing just a
single record using a form takes some effort: File | Print > Selected
Records).

If the information I provided above changes any of your suggestions, I would
appreciate your reply.

My suggestion about eliminating the need for the user, or your VBA code, to
select a record remains the same. I would change this design to eliminate
such an operation.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________