Transfer data between sub forms

[Guest]

I'm trying to avoid any programming and happy to have a two step process, but
I'm seeking to transfer data between a subform and a subform within a subform
sited on the same main form!

Main Form = "Main Form"
a) Personal Details sub form = "View Personal Details"
b) "Attendees" subform sits within "Workshops" subform, which in turn sits
on Main Form

I want to transfer Personal Details from the "View Perosnal Details"
subform, and create a new record on the "Attendees" subform

I had thought about a two stage process 1) that copied active record from
"Personal Details" onto main form, and 2) that copied details from Main Form
into selected record on Attendees subform.

Not exactly sure how to force data into new record, or maybe there is an
easier way?

Any pointers / guidance would be much appreciated.

Many thanks

Winger



On the "Personal Details" Forms

 

[Douglas J. Steele]

Data doesn't exist on forms: it exists in tables. You shouldn't be copying
data from form to form: you should be putting the data in the appropriate
table, and ensure each subform is using the correct query to get at the
relevant data.

In other words, telling us about the forms doesn't give us much information:
we need to know about the underlying tables, and how they relate to each of
the forms/subforms.

 

[Guest]

Doug,

Thanks for your response.

In this case the forms are direct mirrors of the tables:
"Personal Details" form shows the data in the Personal Details Table and
"Attendees" show data from the Attendees table, likewise with Workshops.

Attendees subform is linked to Workshops via "Event", i.e. each workshop is
an event, and the Attendees is a list of Attendees and the events they have
attended.

There are currently no queries between the table and the forms, and the
forms allow data entry, so, maybe mistakenly, on a simple!! application like
this, I suppose I see the forms as interchangeable with the tables.

In brief, I have a large list of students, who attend a range of workshops,
and I need to track which workshops they make an enquiry to attend, which
they actually book on, and when they fail to turn up or pay. The list of
students, and workshops is constanly growing.

So at the moment, I'm manulay transfering data on one side of the page to a
selected workshop on the other . It works, but its not elagant and too labour
intensive.

Any pointers on perhaps how I could restructure the whole thing, or some
tips on transfering data, especially puting data into a new record into a
table I don't currently have open(which I've never realy grapsed), without
using prograamming would be gratefully received.

thanks again

Winger

 

[Douglas J. Steele]

Sounds as though you should have 3 tables: People (one row per person),
Workshops (one row per workshop) and Attendees (the resolution of the
many-to-many between People and Workshops, which would have the combination
of PersonId and WorkshopId as its primary key).

Create a form that lets you manage the information in People. Put a subform
on that bound to Attendees. You'd pick up PersonId from the parent form, and
have a combo box with a query based on Workshops as its RowSource bound to
WorkshopId so that you'd pick the Workshop from the combo box and it would
automatically store the Id in Attendees. This is along the lines of the
Orders and Orders Subform in Northwind: the Orders Subform has a combo box
that pulls information from the Product table.

 

[Guest]

Doug,

Thanks.

I do have three tables, but in your example the focus is on the peolpe, and
mine I think is more on the workshops.

There is quite a bit of information for each workshop, the application
prints the attendees list, logs that they have shown an interest, logs if
they've paid etc, so I would prefer to transfer the person details to the
workshop, and then use the workshop form/table to mangae attendance / payment
etc.
I don't see how I can achive this through passing data via combo's.
As the list of students can be in the 100's+, I would fear the combo list
would be unwieldy, and there are several fields that need to be passed.
At present, the View Personal Details subform is a continuos form, which
allows the user to check them against a long list (also pops up with
additional data that can be checked).

Is it possible to copy data from a sub form, to the main form (in a holding
field) and then onto a nested subform? The advantage this would give me is
that each person can be booked onto several workshops very quickly as the
list of workshops is scrolled through and the perosn added to each
appropriate one. Their attendance can then be logged against each
individual workshops.

I would dabble with a bit of VBA,(only if there was no alternative) if I
knew how to a) reference the selected data in the Perosnal Details subform
recored, and b) force the same details into a new recored for each workshop
list of attendees.

Not sure if this make things any clearer?

Regards

Winger

 

[Guest]

Doug,

I think I've grasped what you were saying in your last repsonse. Excpet I
would focus on the Workshops form with a subform for attendees, which picks
up details from a combo. How to I transfer several value from a combo into
several fields?

And how does the Attendees table get (resolved?) made. Do I have to run a
make table query between Peolpe and Workshops each time I want to extract
from it?

thanks

Peter

 

[Douglas J. Steele]

Assuming you're only transferring the values for display purposes, put code
in the combo box's AfterUpdate event to copy values into the text boxes.

Private Sub MyComboBox_AfterUpdate

Me.Text0 = Me.MyComboBox.Column(1)
Me.Text1 = Me.MyComboBox.Column(2)

End Sub

That will copy the value from the 2nd column of the selected row into Text0,
and the value from the 3rd column of the selected row into Text1. (The
Column collection starts at 0). The reason I say that this should only be
for display purposes is that you shouldn't store the data redundantly.

No Make Table should ever be required. Every time you add a new workshop,
you'll select those people who are going to be attending it, and that will
create the entries in the Attendees table. Note that you can set the
LimitToList of the combo box to True, and use the NotInList event to add
entries to the People table.

 

[Guest]

Doug,

Sucess - I use the code in a two stage process, which may not be perfect,
but the end result is that we've saved my admin assistant a lot of time.

Many thanks for your time.

Much appreciated.

Winger