You are here:

Using MS Access/Access Forms Default values


QUESTION: I put survey data into an Access 2007 database that I've made and improved over several years.  There are several things I'd like to change to make it easier to use --

1.  Forms default values: My survey information includes the survey ID# and response # as the first of many values.  My procedure is to enter the appropriate ID#, line no. and other data, then (using a "New Record" box at the head of the form)start a new record for subsequent entries.  At the present, when selecting "New Record" I then tab to the form's first entry (ID#) select the ID# from a drop-down box, then tab to the Line Number which I enter manually.

What I would like to have is when selecting "New Form" that (a) the new form's ID# line have a default value which is the same as the value of the previous form, and (b) the Line Number entry have a default value which is one higher than on the previous form.

Example --
When starting data entry --
First form:
Survey ID# - XX (my chosen entry)
Line # - 1
Complete the survey form data entry, select "New Form" button

Next form:
Opens using default value of:
Survey ID# - XX
Line # - 2 (ie, next set of survey data)

2. I use the Switchboard to guide the user - this requires that I use forms, since the Switchboard isn't set up to open queries.  When I create a form from a query, even if in the properties I make the default view "Datasheet" and mark the other views as "No", when the Switchboard opens the form it is in the "Form" view, not Datasheet.  How can I force the form to open as a datasheet when using the Switchboard?

3.  Would you suggest I abandon the switchboard and instead set up several custom groups which can guide other users through the steps for entering data and creating reports?  I've been reluctant to make this change because of other workload items.

ANSWER: First, if you are referring to using Switchboard Manager, no, you don't need to abandon it. Second, while the Switchboard Manager can be used to open queries, by creating Macros and running those macros, you shouldn't be using queries for data entry. Data entry should be done using forms. And you can open a form in datasheet mode (though I question why you would since a form can be laid out better and you can use continuous forms if you want to display multiple rows) by, again, using a macro and specify Datasheet mode.

Finally, the way you describe your forms, I suspect your database is not designed optimally for surveys. It sounds like you have the questions for your survey as fields in your table. That is not optimal design. A survey database requires a specific design similar to the following:

QuestionID (PK Autonumber)

RespondentID (PK Autonunber)
other info about respondent

ResponseID (PK Autonumber)
RespondentID (Foreign Key)

AnswerID (PK Autonumber)
ResponseID (FK)
QuestionID (FK)

To enter data you use a main form bound to tblRepsonse and a subform bound to tblAnswer. Each Answer should be a record in a table, not a field in record.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Scott -- thanks for the time and effort you put into your reply -- it's helpful to get your advice on setting up a survey database, especially for future projects, but on review, I think I'm close to your structure.

tblQuestion is my tblCreelsched -- the questions are always the same, but the each has a specific date and ID# (e.g., Creel 9-8 is the eighth survey in period #9.)

tblRespondent I call tblAnglerdata -- how many anglers, from where;

tblResponse is the link to the original question and includes date, time, weather, etc. parameters during the survey

tblAnswer is my tblFishdata - fishing how long, how many bass, crappie, bluegill, etc.

Further info on my "survey" -- I do a creel survey of anglers on local lakes, while they're still fishing.  I record details in 3 areas: Where they're from; how long they've been fishing, and what they've caught.  I record what time they started, the time of the interview, whether they're resident or non-resident, and the numbers of fish in various categories -- e.g., number of bluegill kept, no. released; number of bass over 24" length kept, released, etc.  There are 10 surveys every 28 days.  Because these are set categories the answers make a neatly organized spreadsheet from which it's easy to make reports.  

 However, I still have the generic questions --

1.  How can I open a new form and (a) have an entry box use the value from the preceding form as the default value, and (b) have another box use the value from the preceding form + 1 as the default value;

2.  How can I force a form to open as a datasheet when using the switchboard to select the form? (Even with the Form View property set to "No", initially the form opens as a form rather than a datasheet.)

First, I did answer question 2. I said, you can use the Switchboard Manager to run a macro. You can make a simple OpenForm macro that will have the View property set to Datasheet. To explain further, the Switchboard Manager's Open Form command does not set the View property, so it defaults to form view, overriding the form settings.

However, I'm not sure your structure is close to what I'm suggesting. Let me quote two things you said.
"tblAnswer is my tblFishdata - fishing how long, how many bass, crappie, bluegill, etc."
"the answers make a neatly organized spreadsheet"

The first quote indicates that you have fields in your table like bass, crappie, bluegill, fishing time, etc. If that's the case, then your structure is not properly normalized.  The second quote further indicates that Access is not a spreadsheet and shouldn't be used like one. It certainly sounds like you are using a horizontal structure when you should be using a vertical one. Each answer should be a record in a table that identifies the question and its answer along with a link back to the Response which then identifies the angler, when he fished, where, etc.

Getting back to question 1. If you have the correct structure, then you use a mainform/subform combination. The mainform bound to the Response table where you record the  angler and info about the fishing session. The subform, bound to the Answers table. shows a record for each question where you can fill in the answer.

However, if you want to stick to your denormalized structure. Use the After Update event of each control and set the default value to the control's current value. Something like

Me.Control.Default = Me.Control

The next new record added would use the default value

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.