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

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.

using the tblStaff, create a new query

Creating the form

For this part – use a Blank Form

Save the blank form as frmSearchStaff

Editing the form

First add a couple of labels
Part of the grade can come from it looking good. It has to be functional AND useable
You can choose what you type BUT make sure it is informative and instructional.

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.
Still in Design View on the form, add two unbound text boxes underneath the labels.

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 ToCombo Box

Do the same for the unbound box for the days.

view in Form View
You should have two menus BUT nothing in them at the moment.

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.

In Property Sheet, set Name to jobRoleSelect

Select the unbound field for days, the combo box.

In Property Sheet, set Name to daysSelect

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

Click on the icon for the Builder
in  the Expression Elements – find the form under All Forms

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

the criteria will appear in the Criteria area under JobRoleID

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

At the moment under Availability is the search for Both Days.
 
Place you cursor after “Both Days”

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

The issue is that we need the Forename and Surname to be together in one field for display purposes. This can be set in the query.

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.

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.

Click on Event tab
After Update – choose Macro option
Select the option Requery
Do the same again for the daysSelect – the other menu 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

Share

Written by:

18 Posts

View All Posts
Follow Me :

Leave a Reply

Your email address will not be published.