How to insert multiple DIFFERENT recordsets into single form

[edisonl via AccessMonster.com]

Hi,

I had a form that need to look up records from multiple table and insert them
into my form controls (certain values but standard Field & Name for different
Table)

Anyone ever tried it ?

Edison

 

[Allen Browne]

I had a form that need to look up records from multiple table and insert
them
into my form controls (certain values but standard Field & Name for
different
Table)

You may be able to create a UNION query that draws records from multiple
tables, and assign that as the RecordSource for your form. Results will be
read-only, and the number of source tables is limited.

A better solution would be to create a relational design, where you have put
all the entities that are of the same type into one table, rather than have
multiple tables with essentially the same fields.

 

[edisonl via AccessMonster.com]

Hi Allen,

Thank you for replying..

Mine initial vision was to pull records not individual field from various
table and put it in ActiveX Control Calender9.0, but don't think be able to
do it so I decided to do it in subform.
Allow me to quote you an example:

I am doing a Leave application, before superior approve leave of staff,
he/she needed to see who is on leave (from the same department) on that
particular day.

Mr Allen Annual Leave : 4/12/08 - Approved
Mr Browne Child Care Leave : 2 to 4/12/08 -Approved
Ms Tina Exam Leave : 3 to 6/12/08 -Approved


Now
Mr Edison Annual Leave : 4/12/08 -Pending

So When Their Superior Login to approve Mr Edison leave, he should be able to
view 3 Leave respectively- Mr Allen(Annual Leave), Mr Browne(Child Care Leave)
, Ms Tina(Exam Leave)

If I should put a subform_name.recordsource = ExamLeave_Table, I can't
extract data from AnnualLeave_Table.

 

[Allen Browne]

Everything you say confirms my belief that these things would be better in
one table, with a field for the LeaveType.

You can then filter the different types as needed (which gives you all the
flexibility you currently have by using different tables.) But you can also
comine the values (since they are all in the one table) which you currently
can't do easily.

 

[edisonl via AccessMonster.com]

Hi Allen,

Thanks for that...

But How do I start going about doing it ?

Edison

Everything you say confirms my belief that these things would be better in
one table, with a field for the LeaveType.

You can then filter the different types as needed (which gives you all the
flexibility you currently have by using different tables.) But you can also
comine the values (since they are all in the one table) which you currently
can't do easily.

Hi Allen,

[quoted text clipped - 41 lines]

 

[Allen Browne]

Create a new table, with the fields the way you want.

Create a query selecting the first table.
Change it to an Append query (Append on query menu.)
Map the fields. Then run the query.

Repeat for the other tables.
You then have one table, that has all the values from the other tables in
it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for that...

But How do I start going about doing it ?

Edison

Everything you say confirms my belief that these things would be better in
one table, with a field for the LeaveType.

You can then filter the different types as needed (which gives you all the
flexibility you currently have by using different tables.) But you can
also
comine the values (since they are all in the one table) which you
currently
can't do easily.

Hi Allen,

[quoted text clipped - 41 lines]

have
multiple tables with essentially the same fields.

 

[edisonl via AccessMonster.com]

Hi MrAllen,

- Yup thanks for the advice though, come to think of it quite silly of me not
to think of it.

- Besides that any way that you come across allows me to insert values into
Calender ActiveX 9.0 ?

Edison

Create a new table, with the fields the way you want.

Create a query selecting the first table.
Change it to an Append query (Append on query menu.)
Map the fields. Then run the query.

Repeat for the other tables.
You then have one table, that has all the values from the other tables in
it.

Hi Allen,

[quoted text clipped - 19 lines]

 

[Allen Browne]

... any way that you come across allows me to insert values into
Calender ActiveX 9.0 ?

You should be able to assign a value to it.

I never use it though. I prefer to use a simple little Access form as a
popup calendar. Avoids reference problems, broken versions, etc.

Here's one you can download and use:
http://allenbrowne.com/ser-51.html

 

[edisonl via AccessMonster.com]

All right I gave a try & let you know again..

You should be able to assign a value to it.

I never use it though. I prefer to use a simple little Access form as a
popup calendar. Avoids reference problems, broken versions, etc.

Here's one you can download and use:
http://allenbrowne.com/ser-51.html

 

[edisonl via AccessMonster.com]

Wow..

That was quite awesome, Allen *Kudos

Edison