Displaying and modifying 1:N data in forms?

[stephan.vanbeerschoten]

Hi All,

I am relatively new to MS Access but am wel suited in general
relational databases. I seem to be unable to accomplish the displaying
of query results that return more then one line item.
I want to create a person record that can have multiple addresses, and
I want all these addresses displayed on a form for manipulation, but I
want to prevent saving the fields to the database as street1, street2,
zipcode1, zipcode2, state1, state2 etc etc for any numbers of addresses
(between 0 and 6 or so) that can be added to this person record.

I have the database tables setup with all the neccesary relations to
the other tables, that was the easy part for me. I have also already
come up with the neccesary queries to pull up the data. Now all I need
is to know how I can do this in the form world of ms access.

Help would be appreciated,
Stephan

[lesperancer]

create a single form for 'person'
create a continuous form for 'personAddress'
drag the continuous form onto the single person to create a subform
link the form and subform by the 'person' field

[stephan.vanbeerschoten]

Great! Thanks, this was the pointer I needed.
Is there a property I can set that will remove the line that divides
the individual records? I have changed the height of the continuous
subform so it is as close to 'one line' as possible, but access still
appends a line at the end of each record.

Stephan

[Albert D. Kallal]

The "many" side of your applications is going to be handled by what we call
a sub-form.

You can read about them here:
http://www.members.shaw.ca/AlbertKal...000000005.html

So, you "main" form (you can create it with a wizard) will have the persons
name, perhaps who entered the name, and general information that you want to
keep about this person.

Then, you need to build a sub-form based on the related "address" table.
(again, you can create this form with a wizard). (you crate one form, and
then save it).

Now, just bring back up the main from in design mode, and use the wizard to
insert the sub-form. You are done. This whole process takes no code, and
ms-access will manage the "one" to many for you...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[lesperancer]

not sure what you mean by a line ?
is it possible that there is a 'line' control on the form ?

[stephan.vanbeerschoten]

Don't worry about the line. It were form deviding lines, controlled by
a boolean that I found and set to false.

I have one more thing I need to figure out, perhaps someone can help me
with this too. I have a subform with several TextBoxes that hold dates.
I want a
calender to become visible when users dblclick on it, and be able to
pick a date etc etc. I have all of this working in a test form I
created, but my real form situation is a little different:

I don't have space on my subform to create the calender, so I placed
the calendar
on the main (parent) form. I was able to control the calender's
properties from
the sub form by using Parent.Calender.Visible=True etc, but how can I
do the reverse?
How can I modify the TextBoxes in my subform based on a date I select
on the Calendar (on the parent form) so I can do something like:
subForm.TextBox.Value = Calendar.Value

Thanks,
Stephan

Reply

[Bob Quintal]

st********************@gmail.com wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

Don't worry about the line. It were form deviding lines,
controlled by a boolean that I found and set to false.

I have one more thing I need to figure out, perhaps someone
can help me with this too. I have a subform with several
TextBoxes that hold dates. I want a
calender to become visible when users dblclick on it, and be
able to pick a date etc etc. I have all of this working in a
test form I created, but my real form situation is a little
different:

I don't have space on my subform to create the calender, so I
placed the calendar
on the main (parent) form. I was able to control the
calender's properties from
the sub form by using Parent.Calender.Visible=True etc, but
how can I do the reverse?
How can I modify the TextBoxes in my subform based on a date I
select on the Calendar (on the parent form) so I can do
something like: subForm.TextBox.Value = Calendar.Value

Thanks,
Stephan

Reply

see http://www.mvps.org/access/forms/frm0057.htm or just
download the application
databasehttp://www.mvps.org/access/downloads/datepicker.zip

It is a popup calendar. I suppose you could embed it into your
form, but It's just wonderful on continuous forms as it is.

--
Bob Quintal

PA is y I've altered my email address.