Client List

[andreainpanama]

The backpacker hostel in Panama again.

What is the best way to organize my unique client numbers when more than one
guest stays in the same room? For example:

If Lisa and Jim both stay in room D for 2 days in April, and then in 2 months
Lisa comes back without Jim. but now is travelling with Melissa and stays in
Room B for 3 nights. The next day Jim comes back alone and stays in a
different room.

Right now what I have is:

The client info table is the main form
The stay table is a sub form in data sheet view
The friend table is a form that pops up when I push its command button.

Lisa has her own client number (autonumber in the client info table) with
all her personal data and a stay number (autonumber in the stay table) with
all the pertinent stay information. Jim's info I add in another "friend"
table, and he gets assigned his own unique client number (autonumber with the
prefix F to show he is a friend, or secondary person in the room) The same
scenario will happen when Lisa comes back with Melissa. And when Jim comes
back on his own, he gets entered again as a new "Main" guest and will finally
get his "own" client number, and maybe one day he will have some friends that
will get assigned the F friend number.

So, I have successfully figured out a way to make sure that if one guest
comes back to my hostel with a completely different guest, my info reflects a
new stay number with the new friend who has their own friend client number.
It's all fine and I am amazed that I actually set this up!!!!

The problem is theoretical. The truth is that in both scenarios, Jim and
Melissa are just as much as a "Main" guest as Lisa. They shouldn't HAVE to
be listed in the separate friend table just because I haven't figured out how
to give them their own unique number in the main client form!

It all works OK from a data entry point of view, but I am concerned,
because looking at the future, if I ever want a listing of all my clients, I
would have to run the query or report by stay number (the only number they
have in common), as opposed to dates of stay or name order.

I was so interested in normalizing my data, that I separated the
individuals, it might have just been simpler to have one long guest table
with room for entering 3 different individuals and manually assigning them
incremental guest ids.

Am I creating problems where there aren't any. Is this stuff solvable once
I learn how to set up reports and queries?

 

[tina]

by separating the clients into different tables based on whether they're a
"main client" or a "friend" on a given stay, you're de-normalizing your
table design rather than normalizing it. if you have a tblFriends, and a
tblClients, then you're putting data into tablenames ("friend", and
"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals, however you
want to name it). enter each guest as a record, whether the guest is the
person paying for the room, or just a friend tagging along for free. since
one guest can have many stays, and one stay can have many guests, you have a
many-to-many relationship between those two entities. you'll need a junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a trivial
project you've undertaken. to do it correctly, and therefore efficiently,
you need to really understand the principles of relational data modeling. i
didn't see your previous thread; but if nobody recommended that you study
relational data modeling already, then i strongly recommend that you do so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at
http://www.databaseanswers.org/data_models/hotels/index.htm.

<<note that the data model does not show a many-to-many relationship between
guests and bookings. this is because the only "person" that's stored in that
table is the person booking (paying for) the room. in your case, you're
storing data about everyone who stays in a room, not just the booker/payer -
hence the many-to-many relationship.>>

note that using somebody else's data model can be a great timesaver - but it
*does not* replace the necessity of learning the principles of relational
data modeling for yourself. you are in a much better position to analyze
your business process and determine the best tables/relationships design,
than anyone in these newsgroups - once you've learned how to do it."

if you've been studying relational design, then you need to continue -
you're not quite there yet. don't be discouraged; relational design
principles, or normalization, is the most difficult concept to learn when
building a relational database, and most people struggle with it. but it's
well worth the time you have to invest in learning to "do it right"; in the
long run, the time you spend on tables/relationships will be more than saved
on the time spent building the rest of the database.

hth

 

[andreainpanama]

You are absolutely right with what you are saying about normalization. I
can't believe that I lost track of it!

So there is no question that I will convert my set up (thank goodness only
have to change about 40 records) to having the one sole guest table set up
and I will keep it that each new entry will receive a unique autonumber
client number without F prefixes. I will connect individual guests who
happen to be staying together at the same time with a unique stay autonumber.

So now the question is...how can I automatically assign more than one guest
the same stay autonumber, or will just the first entry have it assigned
automatically and the second and third guests have a field where I manually
fill in the same number?

Thanks so much !

 

[David Cox]

If it helps you to visualise what Tinas geek speak is trying to tell you,
you do not want to be searching for John Smith in two different places. Main
Guest or friend they are people that stayed at your hotel. Whether they were
a main guest or a friend depends on which stay (transaction) you are talking
about, so that info belongs in a separate table linking the guest with a
stay. Few programmers try to run hostels ....

 

[tina]

i'm not sure if we're having a two-way communication problem, or if you're
not understanding the table setup i described elsewhere in this post.

you have a table listing all guests, tblGuests.
you have a table listing all stays, tblStays.
both of the above are parent tables, in a parent-child relationship with
tblStay_Guests.

so if you have one stay record with a StayID of 247, and three guests
associated with that stay, and their GuestID's are 13, 37, and 182, then
there will be three records in tblStay_Guests:

StayGuestID StayID GuestID
1 247 13
2 247 37
3 247 182

in the above example, StayGuestID is the primary key field of
tblStay_Guests; StayID is a foreign key field that is linked to tblStays;
and GuestID is a foreign key field that is linked to tblGuests.

hth

 

[andreainpanama]

Thanks Tina. I am admittedly a bit slow on the uptake and I do suffer from
reading comprehension so bear with me. Sometimes I don't grasp concepts so
quickly.

I actually did have a junction table set up...with only two
fields....foreign keys from the two different tables, one is guestid, and the
other is stayid. They were set up as a composite primary key.

At your suggestion (not really sure why but I did it anyway...), I have
added the third field and called it stayid_guestid, and I have made IT the
primary key. I think I have everything set up.

Now the question is, what do I do with it? Do I fill in the data manually?
What is my best form set up for this new design?

Advise if now I should post in a different area.

And thanks David for extra support. My hostel is located in the city of
David, Panama! (go to page www.purplehousehostel.com to see!)

 

[tina]

comments inline.

Thanks Tina. I am admittedly a bit slow on the uptake and I do suffer from
reading comprehension so bear with me. Sometimes I don't grasp concepts so
quickly.

no problem.

I actually did have a junction table set up...with only two
fields....foreign keys from the two different tables, one is guestid, and the
other is stayid. They were set up as a composite primary key.

At your suggestion (not really sure why but I did it anyway...), I have
added the third field and called it stayid_guestid, and I have made IT the
primary key. I think I have everything set up.

okay. your initial two-field primary key was fine; you can either keep the
current table design or go back to the two-field design. i personally don't
like multi-field primary keys, at least in a table that is or may become a
"parent" table, linked to some child table - so i automatically think in
terms of single-field keys. but there is nothing wrong with using a
multi-field primary key, as long as all the fields used as the composite key
are required to make the key unique.

Now the question is, what do I do with it? Do I fill in the data manually?
What is my best form set up for this new design?

okay, now that we have the design/relationships of these three tables laid
out, it's (somewhat) safe to talk about data entry forms. usually you set up
a form to follow the flow of the business process it supports. again, you're
in a better position to determine that, than i am.

i'm guessing that you'll want to enter a "stay" record, and include details
of the guests who are associated with that stay - new and/or return guests.
bearing in mind the limits of your current form development/coding skill
levels, i'd suggest going with a standard setup for supporting the tables in
a many-to-many relationship: 1) create a form bound to tblGuests, where you
can add new guest records; i'll call it frmGuests. 2) create another form
bound to tblStay_Guests; i'll call it sfrmStay_Guests. on this form, create
a combo box control with the *RowSource* property set to tblGuests. (if
you're not familiar with creating combo box controls, read up on them in
Access Help; if you run into problems, post back with the actual field names
in tblGuests and i'll walk you thru the setup.) set the LimitToList property
to Yes. in the NotInList event procedure, add code to open frmGuests, add a
new guest record, update the combo box droplist, and enter the guest in the
combo box. 3) create a form bound to tblStays; i'll call it frmStays. on
this form, add a subform control from the Toolbox toolbar. i'll call the
subform control ChildGuests. set the control's properties as follows:

SourceObject: sfrmStay_Guests
LinkChildFields: StayID
LinkMasterFields: StayID

now, when you open frmStays to add a record, you can immediately assign the
guest(s) to that stay, in the subform. if you need to assign a guest who is
not in the combo box droplist on the subform, the NotInList event will open
frmGuests so you can add the new guest record to that subform "on the fly",
and then continue working in frmStays.

hth

 

[andreainpanama]

Hi Tina: I actually have had most of what you recommended already done in
terms of form setup. However for the last few days, (in another discussion
group,) I have been working on my NotInList event problem. Once I get that
going...then I can continue the final touches.

This is the code I am working with: (This was all given to me with the help
of another person, I know nothing about vb code so I am literally cutting and
pasting)

Private Sub cboMainGuestLastName_NotInList(NewData As String, Response As
Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In MainGuestInfo " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO MainGuestInfo (MainGuestLastName) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtMainGuestGender.SetFocus
Response = acDataErrAdded
Else
Me.cboMainGuestLastName.Undo
Response = acDataErrContinue
End If

End Sub

It isn't working for some reason. This is a rundown of exactly what I am
doing.

I go into the properties of MainGuestLastName.
I click the 3 dots next to the Not in List event.
It automatically opens a visual basic window that already has a lot of code
in it.
I delete all that code.
I go to the little list in the upper left window and choose my field,
MainGuestLastName. It automatically registers something in the little window
on the upper right that says, Before Update and it puts a line of code in the
box. I change the option from Before Update to Not in List and again, it
puts a line of code in, but this time what looks to be the first line of code
that you gave me. I then cut and paste the remaining portion of the code
that I have from you. This following line appears in red highlight.

rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null,

I save it all, and close all the vb stuff. Then when I go back to my form
entry mode, and I enter something in the field that is not in list...as soon
as I tab to the next field...it automatically opens up (from my data entry
form), a new visual basic code window. With an error message (rough
translation from Spanish (I am using the Spanish version of access, which
adds to my problems)....compilation error, syntax error.

And now this line appears in yellow highlight.
Private Sub MainGuestLastName_NotInList(NewData As String, Response As
Integer)

Any chance you might be able to help me with my notinlist problem?

Thanks!

 

[tina]

well, frankly, that seems like a lot of trouble to go to, appending a record
in code and then finding it within a recordset, and on, and on...

if you created a form bound to tblGuests, as i suggested, then look at the
following code, using the names we already established in previous posts in
this thread:

If MsgBox("Do you want to add this guest to the droplist?", _
vbYesNo + vbDefaultButton2, _
"NAME NOT IN LIST") = vbYes Then
DoCmd.OpenForm "frmGuests", , , , acFormAdd, _
acDialog
Response = acDataErrAdded
Else
Response = isNotInList
End If

except for the change in form name, and minor wording in the msgbox message,
this is the code i use to add a person to a table that feeds a combo box
RowSource "on the fly", in one of my own working applications (one that i
use every day at my job). it works very simply: if the user chooses the No
button, then my generic "not in list" code runs (i'll post that below). if
the user chooses the Yes button, then frmGuests opens to a new record, and
the code is suspended; after adding the new guest record, the user closes
that form, at which time the code resumes and automatically requeries the
combo box control so that the new record is available. recommend you read up
on the NotInList event in VBA Help, so you'll understand how it works.

the generic "not in list" handler code follows. you can paste it as-is into
the subform's module (only changing the word Public to Private), or paste it
as-is into a standard module. i'd recommend the latter; then you can use it
in the NotInList event procedure of a combo box control in any form in the
database, if you wish.

hth


Public Function isNotInList() As Integer

With Screen.ActiveControl
.Undo
.Dropdown
End With

isNotInList = acDataErrContinue

End Function

Hi Tina: I actually have had most of what you recommended already done in
terms of form setup. However for the last few days, (in another discussion
group,) I have been working on my NotInList event problem. Once I get that
going...then I can continue the final touches.

This is the code I am working with: (This was all given to me with the help
of another person, I know nothing about vb code so I am literally cutting and
pasting)

Private Sub cboMainGuestLastName_NotInList(NewData As String, Response As
Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In MainGuestInfo " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO MainGuestInfo (MainGuestLastName) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtMainGuestGender.SetFocus
Response = acDataErrAdded
Else
Me.cboMainGuestLastName.Undo
Response = acDataErrContinue
End If

End Sub

It isn't working for some reason. This is a rundown of exactly what I am
doing.

I go into the properties of MainGuestLastName.
I click the 3 dots next to the Not in List event.
It automatically opens a visual basic window that already has a lot of code
in it.
I delete all that code.
I go to the little list in the upper left window and choose my field,
MainGuestLastName. It automatically registers something in the little window
on the upper right that says, Before Update and it puts a line of code in the
box. I change the option from Before Update to Not in List and again, it
puts a line of code in, but this time what looks to be the first line of code
that you gave me. I then cut and paste the remaining portion of the code
that I have from you. This following line appears in red highlight.

rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null,

I save it all, and close all the vb stuff. Then when I go back to my form
entry mode, and I enter something in the field that is not in list...as soon
as I tab to the next field...it automatically opens up (from my data entry
form), a new visual basic code window. With an error message (rough
translation from Spanish (I am using the Spanish version of access, which
adds to my problems)....compilation error, syntax error.

And now this line appears in yellow highlight.
Private Sub MainGuestLastName_NotInList(NewData As String, Response As
Integer)

Any chance you might be able to help me with my notinlist problem?

Thanks!

comments inline.

concepts
so

no problem.
and
the

okay. your initial two-field primary key was fine; you can either keep the
current table design or go back to the two-field design. i personally don't
like multi-field primary keys, at least in a table that is or may become a
"parent" table, linked to some child table - so i automatically think in
terms of single-field keys. but there is nothing wrong with using a
multi-field primary key, as long as all the fields used as the composite key
are required to make the key unique.


okay, now that we have the design/relationships of these three tables laid
out, it's (somewhat) safe to talk about data entry forms. usually you set up
a form to follow the flow of the business process it supports. again, you're
in a better position to determine that, than i am.

i'm guessing that you'll want to enter a "stay" record, and include details
of the guests who are associated with that stay - new and/or return guests.
bearing in mind the limits of your current form development/coding skill
levels, i'd suggest going with a standard setup for supporting the tables in
a many-to-many relationship: 1) create a form bound to tblGuests, where you
can add new guest records; i'll call it frmGuests. 2) create another form
bound to tblStay_Guests; i'll call it sfrmStay_Guests. on this form, create
a combo box control with the *RowSource* property set to tblGuests. (if
you're not familiar with creating combo box controls, read up on them in
Access Help; if you run into problems, post back with the actual field names
in tblGuests and i'll walk you thru the setup.) set the LimitToList property
to Yes. in the NotInList event procedure, add code to open frmGuests, add a
new guest record, update the combo box droplist, and enter the guest in the
combo box. 3) create a form bound to tblStays; i'll call it frmStays. on
this form, add a subform control from the Toolbox toolbar. i'll call the
subform control ChildGuests. set the control's properties as follows:

SourceObject: sfrmStay_Guests
LinkChildFields: StayID
LinkMasterFields: StayID

now, when you open frmStays to add a record, you can immediately assign the
guest(s) to that stay, in the subform. if you need to assign a guest who is
not in the combo box droplist on the subform, the NotInList event will open
frmGuests so you can add the new guest record to that subform "on the fly",
and then continue working in frmStays.

hth
tell
you, places.
Main they
were and
a is
the free.
since you
have you
study you
do stored
in timesaver -
but learn
when but
it's right";
in more
than in
2 and
stays in
a table)
with (autonumber
with one
guest Jim
and shouldn't
HAVE figured
out guest
table

 

[andreainpanama]

Sorry for the delay in response. Yes, I am stuck. Can you help? This is
what you wrote: 2) create another form bound to tblStay_Guests; i'll call it
sfrmStay_Guests (OK, DONE). on this form, create a combo box control with
the *RowSource* property set to tblGuests. (OK, BUT WHAT OTHER FIELDS SHOULD
I HAVE ON THIS FORM????) (if you're not familiar with creating combo box
controls, read up on them in Access Help; if you run into problems, post back
with the actual field names
in tblGuests and i'll walk you thru the setup.) OK, I HAVE A BUNCH OF
FIELDS ABOUT GUEST DATA BUT THE MOST PERTINENT I GUESS ARE,

guestID
guestlastname
guestfirstname
guestnationality
guestpassport
guestgender
guesttouristactivity

You go on to say..."set the LimitToList property to Yes (IN WHICH FIELDS?).
in the NotInList event procedure, add code to open frmGuests, add a new guest
record, update the combo box droplist, and enter the guest in the combo box.
(WAY TOO FAST FOR ME, CAN YOU BE MORE SPECIFIC?)

I continue to plug away at this project and appreciate all the help!

well, frankly, that seems like a lot of trouble to go to, appending a record
in code and then finding it within a recordset, and on, and on...

if you created a form bound to tblGuests, as i suggested, then look at the
following code, using the names we already established in previous posts in
this thread:

If MsgBox("Do you want to add this guest to the droplist?", _
vbYesNo + vbDefaultButton2, _
"NAME NOT IN LIST") = vbYes Then
DoCmd.OpenForm "frmGuests", , , , acFormAdd, _
acDialog
Response = acDataErrAdded
Else
Response = isNotInList
End If

except for the change in form name, and minor wording in the msgbox message,
this is the code i use to add a person to a table that feeds a combo box
RowSource "on the fly", in one of my own working applications (one that i
use every day at my job). it works very simply: if the user chooses the No
button, then my generic "not in list" code runs (i'll post that below). if
the user chooses the Yes button, then frmGuests opens to a new record, and
the code is suspended; after adding the new guest record, the user closes
that form, at which time the code resumes and automatically requeries the
combo box control so that the new record is available. recommend you read up
on the NotInList event in VBA Help, so you'll understand how it works.

the generic "not in list" handler code follows. you can paste it as-is into
the subform's module (only changing the word Public to Private), or paste it
as-is into a standard module. i'd recommend the latter; then you can use it
in the NotInList event procedure of a combo box control in any form in the
database, if you wish.

hth


Public Function isNotInList() As Integer

With Screen.ActiveControl
.Undo
.Dropdown
End With

isNotInList = acDataErrContinue

End Function

Hi Tina: I actually have had most of what you recommended already done in
terms of form setup. However for the last few days, (in another discussion
group,) I have been working on my NotInList event problem. Once I get that
going...then I can continue the final touches.

This is the code I am working with: (This was all given to me with the help
of another person, I know nothing about vb code so I am literally cutting and
pasting)

Private Sub cboMainGuestLastName_NotInList(NewData As String, Response As
Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In MainGuestInfo " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO MainGuestInfo (MainGuestLastName) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtMainGuestGender.SetFocus
Response = acDataErrAdded
Else
Me.cboMainGuestLastName.Undo
Response = acDataErrContinue
End If

End Sub

It isn't working for some reason. This is a rundown of exactly what I am
doing.

I go into the properties of MainGuestLastName.
I click the 3 dots next to the Not in List event.
It automatically opens a visual basic window that already has a lot of code
in it.
I delete all that code.
I go to the little list in the upper left window and choose my field,
MainGuestLastName. It automatically registers something in the little window
on the upper right that says, Before Update and it puts a line of code in the
box. I change the option from Before Update to Not in List and again, it
puts a line of code in, but this time what looks to be the first line of code
that you gave me. I then cut and paste the remaining portion of the code
that I have from you. This following line appears in red highlight.

rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null,

I save it all, and close all the vb stuff. Then when I go back to my form
entry mode, and I enter something in the field that is not in list...as soon
as I tab to the next field...it automatically opens up (from my data entry
form), a new visual basic code window. With an error message (rough
translation from Spanish (I am using the Spanish version of access, which
adds to my problems)....compilation error, syntax error.

And now this line appears in yellow highlight.
Private Sub MainGuestLastName_NotInList(NewData As String, Response As
Integer)

Any chance you might be able to help me with my notinlist problem?

Thanks!

comments inline.

Thanks Tina. I am admittedly a bit slow on the uptake and I do suffer
from
reading comprehension so bear with me. Sometimes I don't grasp concepts
so
quickly.

no problem.


I actually did have a junction table set up...with only two
fields....foreign keys from the two different tables, one is guestid, and
the
other is stayid. They were set up as a composite primary key.

At your suggestion (not really sure why but I did it anyway...), I have
added the third field and called it stayid_guestid, and I have made IT the
primary key. I think I have everything set up.

okay. your initial two-field primary key was fine; you can either keep the
current table design or go back to the two-field design. i personally don't
like multi-field primary keys, at least in a table that is or may become a
"parent" table, linked to some child table - so i automatically think in
terms of single-field keys. but there is nothing wrong with using a
multi-field primary key, as long as all the fields used as the composite key
are required to make the key unique.


Now the question is, what do I do with it? Do I fill in the data
manually?
What is my best form set up for this new design?

okay, now that we have the design/relationships of these three tables laid
out, it's (somewhat) safe to talk about data entry forms. usually you set up
a form to follow the flow of the business process it supports. again, you're
in a better position to determine that, than i am.

i'm guessing that you'll want to enter a "stay" record, and include details
of the guests who are associated with that stay - new and/or return guests.
bearing in mind the limits of your current form development/coding skill
levels, i'd suggest going with a standard setup for supporting the tables in
a many-to-many relationship: 1) create a form bound to tblGuests, where you
can add new guest records; i'll call it frmGuests. 2) create another form
bound to tblStay_Guests; i'll call it sfrmStay_Guests. on this form, create
a combo box control with the *RowSource* property set to tblGuests. (if
you're not familiar with creating combo box controls, read up on them in
Access Help; if you run into problems, post back with the actual field names
in tblGuests and i'll walk you thru the setup.) set the LimitToList property
to Yes. in the NotInList event procedure, add code to open frmGuests, add a
new guest record, update the combo box droplist, and enter the guest in the
combo box. 3) create a form bound to tblStays; i'll call it frmStays. on
this form, add a subform control from the Toolbox toolbar. i'll call the
subform control ChildGuests. set the control's properties as follows:

SourceObject: sfrmStay_Guests
LinkChildFields: StayID
LinkMasterFields: StayID

now, when you open frmStays to add a record, you can immediately assign the
guest(s) to that stay, in the subform. if you need to assign a guest who is
not in the combo box droplist on the subform, the NotInList event will open
frmGuests so you can add the new guest record to that subform "on the fly",
and then continue working in frmStays.

hth


Advise if now I should post in a different area.

And thanks David for extra support. My hostel is located in the city of
David, Panama! (go to page www.purplehousehostel.com to see!)

:

If it helps you to visualise what Tinas geek speak is trying to tell
you,
you do not want to be searching for John Smith in two different places.
Main
Guest or friend they are people that stayed at your hotel. Whether they
were
a main guest or a friend depends on which stay (transaction) you are
talking
about, so that info belongs in a separate table linking the guest with a
stay. Few programmers try to run hostels ....


by separating the clients into different tables based on whether
they're a
"main client" or a "friend" on a given stay, you're de-normalizing
your
table design rather than normalizing it. if you have a tblFriends, and
a
tblClients, then you're putting data into tablenames ("friend", and
"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals,
however
you
want to name it). enter each guest as a record, whether the guest is
the
person paying for the room, or just a friend tagging along for free.
since
one guest can have many stays, and one stay can have many guests, you
have
a
many-to-many relationship between those two entities. you'll need a
junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a
trivial
project you've undertaken. to do it correctly, and therefore
efficiently,
you need to really understand the principles of relational data
modeling.
i
didn't see your previous thread; but if nobody recommended that you
study
relational data modeling already, then i strongly recommend that you
do
so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at

 

[tina]

comments inline.

Sorry for the delay in response. Yes, I am stuck. Can you help? This is
what you wrote: 2) create another form bound to tblStay_Guests; i'll call it
sfrmStay_Guests (OK, DONE). on this form, create a combo box control with
the *RowSource* property set to tblGuests. (OK, BUT WHAT OTHER FIELDS SHOULD
I HAVE ON THIS FORM????)

sfrmStay_Guests is bound to tblStay_Guests. that's your linking table that
has only two fields in it: StayID (the foreign key from tblStays) and
GuestID (the foreign key from tblGuests). you should NOT have *any* fields
in this table that describe either a stay or a guest - those fields should
be in tblStays and tblGuests, respectively. and so sfrmStay_Guests should
have only one *visible* control on it: the combo box control that is bound
to the GuestID field.

OK, I HAVE A BUNCH OF
FIELDS ABOUT GUEST DATA BUT THE MOST PERTINENT I GUESS ARE,

guestID
guestlastname
guestfirstname
guestnationality
guestpassport
guestgender
guesttouristactivity

as i said above, none of those fields should be in tblStay_Guests.

You go on to say..."set the LimitToList property to Yes (IN WHICH

FIELDS?).

in the combo box control.

in the NotInList event procedure, add code to open frmGuests, add a new guest
record, update the combo box droplist, and enter the guest in the combo box.
(WAY TOO FAST FOR ME, CAN YOU BE MORE SPECIFIC?)

the above quote is not taken from my previous post, hon, but from the post
before it. in my last post, i gave you the code you need for the NotInList
event procedure, and explained exactly how it will work. did you read my
last post?

hth

I continue to plug away at this project and appreciate all the help!

well, frankly, that seems like a lot of trouble to go to, appending a record
in code and then finding it within a recordset, and on, and on...

if you created a form bound to tblGuests, as i suggested, then look at the
following code, using the names we already established in previous posts in
this thread:

If MsgBox("Do you want to add this guest to the droplist?", _
vbYesNo + vbDefaultButton2, _
"NAME NOT IN LIST") = vbYes Then
DoCmd.OpenForm "frmGuests", , , , acFormAdd, _
acDialog
Response = acDataErrAdded
Else
Response = isNotInList
End If

except for the change in form name, and minor wording in the msgbox message,
this is the code i use to add a person to a table that feeds a combo box
RowSource "on the fly", in one of my own working applications (one that i
use every day at my job). it works very simply: if the user chooses the No
button, then my generic "not in list" code runs (i'll post that below). if
the user chooses the Yes button, then frmGuests opens to a new record, and
the code is suspended; after adding the new guest record, the user closes
that form, at which time the code resumes and automatically requeries the
combo box control so that the new record is available. recommend you read up
on the NotInList event in VBA Help, so you'll understand how it works.

the generic "not in list" handler code follows. you can paste it as-is into
the subform's module (only changing the word Public to Private), or paste it
as-is into a standard module. i'd recommend the latter; then you can use it
in the NotInList event procedure of a combo box control in any form in the
database, if you wish.

hth


Public Function isNotInList() As Integer

With Screen.ActiveControl
.Undo
.Dropdown
End With

isNotInList = acDataErrContinue

End Function

Hi Tina: I actually have had most of what you recommended already done in
terms of form setup. However for the last few days, (in another discussion
group,) I have been working on my NotInList event problem. Once I

get
that

going...then I can continue the final touches.

This is the code I am working with: (This was all given to me with

the
help

of another person, I know nothing about vb code so I am literally

cutting
and

pasting)

Private Sub cboMainGuestLastName_NotInList(NewData As String, Response As
Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In MainGuestInfo " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO MainGuestInfo (MainGuestLastName) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtMainGuestGender.SetFocus
Response = acDataErrAdded
Else
Me.cboMainGuestLastName.Undo
Response = acDataErrContinue
End If

End Sub

It isn't working for some reason. This is a rundown of exactly what I am
doing.

I go into the properties of MainGuestLastName.
I click the 3 dots next to the Not in List event.
It automatically opens a visual basic window that already has a lot of code
in it.
I delete all that code.
I go to the little list in the upper left window and choose my field,
MainGuestLastName. It automatically registers something in the little window
on the upper right that says, Before Update and it puts a line of code

in
the

box. I change the option from Before Update to Not in List and again, it
puts a line of code in, but this time what looks to be the first line

of
code

that you gave me. I then cut and paste the remaining portion of the code
that I have from you. This following line appears in red highlight.

rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null,

I save it all, and close all the vb stuff. Then when I go back to my form
entry mode, and I enter something in the field that is not in

list...as
soon

as I tab to the next field...it automatically opens up (from my data entry
form), a new visual basic code window. With an error message (rough
translation from Spanish (I am using the Spanish version of access, which
adds to my problems)....compilation error, syntax error.

And now this line appears in yellow highlight.
Private Sub MainGuestLastName_NotInList(NewData As String, Response As
Integer)

Any chance you might be able to help me with my notinlist problem?

Thanks!

:

comments inline.

Thanks Tina. I am admittedly a bit slow on the uptake and I do suffer
from
reading comprehension so bear with me. Sometimes I don't grasp concepts
so
quickly.

no problem.


I actually did have a junction table set up...with only two
fields....foreign keys from the two different tables, one is

guestid,
and

the
other is stayid. They were set up as a composite primary key.

At your suggestion (not really sure why but I did it anyway...), I have
added the third field and called it stayid_guestid, and I have

made IT
the

primary key. I think I have everything set up.

okay. your initial two-field primary key was fine; you can either

keep
the

current table design or go back to the two-field design. i

personally
don't

like multi-field primary keys, at least in a table that is or may

become
a

"parent" table, linked to some child table - so i automatically think in
terms of single-field keys. but there is nothing wrong with using a
multi-field primary key, as long as all the fields used as the

composite
key

are required to make the key unique.


Now the question is, what do I do with it? Do I fill in the data
manually?
What is my best form set up for this new design?

okay, now that we have the design/relationships of these three

tables
laid

out, it's (somewhat) safe to talk about data entry forms. usually

you
set up

a form to follow the flow of the business process it supports.

again,
you're

in a better position to determine that, than i am.

i'm guessing that you'll want to enter a "stay" record, and include details
of the guests who are associated with that stay - new and/or return guests.
bearing in mind the limits of your current form development/coding skill
levels, i'd suggest going with a standard setup for supporting the tables in
a many-to-many relationship: 1) create a form bound to tblGuests,

where
you

can add new guest records; i'll call it frmGuests. 2) create another form
bound to tblStay_Guests; i'll call it sfrmStay_Guests. on this form, create
a combo box control with the *RowSource* property set to tblGuests. (if
you're not familiar with creating combo box controls, read up on them in
Access Help; if you run into problems, post back with the actual

field
names

in tblGuests and i'll walk you thru the setup.) set the LimitToList property
to Yes. in the NotInList event procedure, add code to open

frmGuests,
add a

new guest record, update the combo box droplist, and enter the guest

in
the

combo box. 3) create a form bound to tblStays; i'll call it frmStays. on
this form, add a subform control from the Toolbox toolbar. i'll call the
subform control ChildGuests. set the control's properties as follows:

SourceObject: sfrmStay_Guests
LinkChildFields: StayID
LinkMasterFields: StayID

now, when you open frmStays to add a record, you can immediately

assign
the

guest(s) to that stay, in the subform. if you need to assign a guest

who
is

not in the combo box droplist on the subform, the NotInList event

will
open

frmGuests so you can add the new guest record to that subform "on

the
fly",

and then continue working in frmStays.

hth


Advise if now I should post in a different area.

And thanks David for extra support. My hostel is located in the

city
of

David, Panama! (go to page www.purplehousehostel.com to see!)

:

If it helps you to visualise what Tinas geek speak is trying to tell
you,
you do not want to be searching for John Smith in two different places.
Main
Guest or friend they are people that stayed at your hotel.

Whether
they

were
a main guest or a friend depends on which stay (transaction) you are
talking
about, so that info belongs in a separate table linking the

guest
with a

stay. Few programmers try to run hostels ....


by separating the clients into different tables based on whether
they're a
"main client" or a "friend" on a given stay, you're de-normalizing
your
table design rather than normalizing it. if you have a

tblFriends,
and

a
tblClients, then you're putting data into tablenames

("friend",
and

"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals,
however
you
want to name it). enter each guest as a record, whether the

guest
is

the
person paying for the room, or just a friend tagging along for free.
since
one guest can have many stays, and one stay can have many

guests,
you

have
a
many-to-many relationship between those two entities. you'll

need
a

junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a
trivial
project you've undertaken. to do it correctly, and therefore
efficiently,
you need to really understand the principles of relational data
modeling.
i
didn't see your previous thread; but if nobody recommended

that
you

study
relational data modeling already, then i strongly recommend

that
you

do
so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model

at

 

[andreainpanama]

Tina: Still plodding along. I have done as you advised and set up the
sformstay_guest bound to tblGuestInfo. I have the notinlist event working
with the code you gave me and when I add an ID number that is not in the
list, the msg box comes up and asks me if I want to add to the dropdown list.
I say yes and my guest info form automatically opens to allow me to enter
complete guest data. However when I type in the new name, it says that the
name is not in the list and i have to close out (the limit to list is set to
yes). Still not clear in how I am using the sformstay_guest.

Also not clear why I need the other code you gave me to use when the answer
to the question if I want to add to the dropdown is "no."

Can you clarify? (If you are tired of my questions, just advise and I will
start a new thread!)

Thanks!

Andrea

 

[tina]

comments inline.

Tina: Still plodding along. I have done as you advised and set up the
sformstay_guest bound to tblGuestInfo. I have the notinlist event working
with the code you gave me and when I add an ID number that is not in the
list, the msg box comes up and asks me if I want to add to the dropdown list.
I say yes and my guest info form automatically opens to allow me to enter
complete guest data. However when I type in the new name, it says that the
name is not in the list and i have to close out (the limit to list is set to
yes).

hmm, okay. open the subform in design view. select the combo box control,
and in the Properties box, look at the ColumnCount, ColumnWidths, and
BoundColumn properties and tell me what their settings are. also go to the
*RowSource* property and copy the entire SELECT statement. (if you're using
a query as the RowSource, then close the form, go to the Query tab in the
database window, and open the query in Design view. on the menu bar, click
View | SQL View, and copy the entire SELECT statement in the window.) paste
it in a post, along with values of the other properties i asked for above. i
need to get a picture of how your combo box is set up, to better figure out
how to make the NotInList code work for you.

Still not clear in how I am using the sformstay_guest.

all you're doing in the subform (if you don't have it's DefaultView property
set to Datasheet, suggest you change it to Datasheet) is listing all the
guests associated with a particular Stay record. if you need to list a guest
who's not already in the droplist, that's when you need the NotInList code
that opens frmGuests so you can add a new guest record to tblGuests.

Also not clear why I need the other code you gave me to use when the answer
to the question if I want to add to the dropdown is "no."

you don't *need* it. i use it in all my NotInList event procedures, because
i think it provides a cleaner, more seamless response to the user's actions
(and because it saves me time in writing code for all the individual combo
box controls i use in a database) - but that's just my opinion. if it
doesn't suit you, just go back to your original code, as

Me.cboMainGuestLastName.Undo
Response = acDataErrContinue

Can you clarify? (If you are tired of my questions, just advise and I will
start a new thread!)

a newsgroup format can be frustrating for both parties (or all parties) when
things don't fall into place easily - it just goes with the territory. as
long as you persevere, so will i, unless i get to a place where i don't
think i have anything helpful to offer you.

 

[andreainpanama]

OK, column count is 1, nothing appears in the column width, and the number 1
appears in bound column. The row source phrase is
SELECT tblGuestInfo.GuestID FROM tblGuestInfo;

Let me clarify something with you....are you envisioning me first going to a
guest info form and adding all pertinent info and then closing that form and
opening another form and adding all the stay information? Because right now
the stay information is a datasheet subform in my guestinfo form.

Thanks again for the help...

 

[tina]

okay, well, now you need to start a new thread. from about four posts ago,
back on 8/13, i outlined a standard form setup for you and we've been
discussing making that setup work since then. since you now tell me you're
using an entirely different form setup, i'd say we've wasted the last week.
if you begin a new thread, with a clear explanation of your table and form
setup, and ask a question, i imagine somebody will be able to help you out.
good luck with your project.