110
5.5.4 Multi-purpose forms (hotel system)
In the hotel system we use frmStay for creating guest
and stay records, as well as editing existing records.
Using the same Form is an advantage to the user (for
recognition) and for the developer (he needs to main-
tain only one Form).
Now, how does the Form know what it should do?
OpenArgs is intended for such things. In the hotel sys-
tem, we use OpenArgs in this way:
•
OpenArgs < 0: Open an existing stay. The
WhereCondition parameter in the OpenForm
statement selects the right one.
•
OpenArgs = 0: Create a new guest and a new
stay.
•
OpenArgs > 0: Create a new stay for an existing
guest. OpenArgs is the guestID for the existing
guest.
Figure 5.5C shows the full solution. It deals also with
error cases where the user for instance clicks the
ShowStay button, but hasn't selected a stay. The Find
Guest screen has three buttons for opening a stay. The
figure shows the three click-event procedures.
cmdNewGuest
This is the simplest of the buttons. Independent of what
the user has chosen, the button should create a new
guest and a stay for him. What is needed is to open the
form with OpenArgs=0.
cmdNewStay
Here we have two situations. (1) The user has selected
a line, and thus a guest. (2) The list is empty so the user
has not chosen anything. The property CurrentRecord
allows the program to distinguish the two situations.
CurrentRecord gives the sequential number of the se-
lected record. It is zero if nothing is selected. If the list
is empty, we might claim it is an error to make a new
stay for a non-existing guest, but it turns out that users
find it natural to create a new guest in this case. So this
is what the program does; it opens the form with Open-
Args = 0. If a line is selected, it opens the form with
OpenArgs = guestID.
Notice the If-Then-Else-EndIf construct that makes the
program choose alternative paths, either executing the
Then-statements or the Else-statements. The line-
breaks must be as shown, with Else and EndIf on lines
of their own.
Notice also the comment we have put after Then to
explain to the reader what situation the program deals
with after Then.
cmdShowStay
Here we have three situations. (1) A stay is selected.
(2) A guest without stay is selected. (3) The list is
empty. In the last two cases, the program cannot open a
stay. The current value of stayID allows the program to
distinguish. StayID is >0 in the first case, and Null in
the last two cases. Notice that stayID exists even if the
list is empty and CurrentRecord is zero. The value of
stayID is just Null.
When stayID>0, we open the form with OpenArgs=-1.
We also have to set the filter so that only this stay is
visible. The WhereCondition-parameter to OpenForm
allows us to do this in a simple manner. If the user for
instance has selected a line with stayID=740, the
WhereCondition has to be the text
stayID=740
In the program we compute this text by concatenating
the text "stayID=" with the current stayID using the &-
operator. StayID is a number, but Visual Basic converts
it to a text before the concatenation.
When stayID is Null (or a number <=0) we have an
error situation. The program may show a message
using MsgBox, but in our case we just make the pro-
gram beep. The DoCmd-object can do this too. (Us-
ability tests show that users understand what is wrong
without any message explaining about it.)
FrmStay, Load event
Inside frmStay an event procedure must take care of
setting the right CRUD properties. Access calls the
Load-event procedure during open, and this is the place
we set the CRUD properties. Figure 5.5C shows the
procedure. Let us follow the path through the code for
each of the three cases.
OpenArgs is < 0: We open an existing stay. The
WhereCondition parameter has set the filter, so we
don't have to do much. The procedure just executes the
last line, which sets AllowAdditions to False.
OpenArgs = 0: We must create a new guest record and
a new stay record. First we allow additions and data
entry. Data entry means that existing records are not
visible, so the current record is a new "blank line".
FrmStay is bound to a join of tblGuest and tblStay, so
the current record will now consist of Null data for the
guest as well as the stay. In order to create the records,
we have to store something in them.
First we set the guest name to a single space character.
This creates a new guest record, and Access gives it an
AutoNumber. Notice how we address name with a
bang ( ! ) to distinguish it from the built-in Name prop-
erty.
Next we set the foreign key of the stay record to this
guest. This creates the stay record and also links it
properly to the guest. Then we set the name back to a
Null so that the user doesn't see a name starting with a
space when he enters the guest name. Notice the
strange use of square parentheses. We explain more
about them below.
90
5. Access through Visual Basic