btec unit 6 Part B exam how to create a form .

btec unit 6 Part B data base exam Creating a form exam approach.

an input form to check staffs/employee availability

1. the form should not include validation for any field
2. The form should not incluse an automated routine to save the data
3. The user should be able to select either friday or saturday as the day they want to check availability for
4. after the job role and day have been selected, the form must display i) a list of the names of staff members who are available ii) the total number of staff available for the job role and day

Above is the extract from the exam paper. It is the second form. It should be done AFTER you have made and TESTED the first form. It is important to attempt this AFTER the testing of the first form to maximize the grades you could achieve.

Create the query

Start with a basic query.  This form is basically going to be an interface that the user can use to search for staff that are available for shifts. It has two criteria it wants to search – job role and days available.

Creating the form

For this part – use a Blank Form

Save the blank form as frmSearchStaff

Editing the form

The exam paper said…

• The user should be able to select the job role.
• The user should be able to select either Friday or Saturday as the day they want to check availability for.

Change the labels in the text box so that they are more descriptive, see example below:

Changing to menu Combo Box

Right click on the unbound box for the job role

Change To – Combo Box

Do the same for the unbound box for the days.

Name the fields

The menus are going to be important later on, so they need to be named.

Select the unbound field for Job Role, the combo box.

Select the unbound field for days, the combo box.

Adding a selection to the combo menu

At the moment, there are two menus with nothing in them.

In Design View on the form

Select the field jobRoleSelect

in property sheet, set Name to jobroleselec in the property sheet

Add the table tblJobRole

continue… jobRoleSelect

in Design View, on the form select daysSelect

In Form View, it will now have a list to choose from.

Connecting the form to the query

In the Criteria area for JobRoleID place your cursor

In the Expression Categories select and double click on jobRoleSelect – this is what you called the Combo Box on the form that lists all the job roles

Click on OK to close the Expression Builder

In the same query – something similar needs to be applied to the criteria area of Availability.

It will return the results as a table. It lists all that are Role ID of 1 and can either work on Friday or Both days

In the next available field in the query place your cursor and click on Builder.

In this example the name of the field is FullName

Time check

At this point – running out of time in the exam… GO and do some of the testing for this form.

The exam paper said…

After the job role and day have been selected the form must display:

• A list of the names of staff members who are available
• The total number of staff available for the job role and day.

In Design View, add an unbound text box. Right click and make it List Box

Edit the label – Staff Available.

in the Row Source – use the Query Builder

Macro

The staff list needs to be update after a selection is made.

You will be creating a MACRO on the drop menus – so when they are selected the query will run.

There is a function called Requery

Select the jobRoleSelect in Design View on the form.

Create a new unbound field at the bottom of the form.

DCOUNT – is a function if you want to count the number of names that have appeared in the list for staff available.

Type directly into the Unbound field

=DCount(“*”,”qrySearch”)

Dcount – is counting

“*” – this means ALL

“qrySearch” is the query that returns the names of the staff available.

CTRL+S to quick save

It will quickly tidy up the information on the form.

In the Property Sheet for the form – Select the Format tab and add a Caption. See example below:

There are other options that have been covered before in the Property Sheet that can be adjusted to make the form more presentable.

btec unit 6 Part B data base exam Creating a form exam approach.

Pop Up – set to Yes

Several options below which can be set to remove the default buttons

One issue the user will have is accidently selecting the area that returns the list of staff available.

Final view above.

The user will select from a job role, select days and then a list will appear of the names of people with a number of staff available appear below it.

btec unit 6 Part B data base exam Creating a form exam approach.

sign Dechronicle