How to insert multiple DIFFERENT recordsets into single form "edisonl via AccessMonster.com" <u47544[ at ]uwe> 12/1/2008 7:37:28 AM 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1

Re: How to insert multiple DIFFERENT recordsets into single form "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 12/1/2008 7:51:22 AM "edisonl via AccessMonster.com" <u47544[ at ]uwe> wrote in message news:8e001daf77fe2[ at ]uwe... [Quoted Text] > > 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.

Re: How to insert multiple DIFFERENT recordsets into single form "edisonl via AccessMonster.com" <u47544[ at ]uwe> 12/4/2008 12:26:18 AM 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 wrote: [Quoted Text] >> 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. > -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1

Re: How to insert multiple DIFFERENT recordsets into single form "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 12/4/2008 8:32:18 AM 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "edisonl via AccessMonster.com" <u47544[ at ]uwe> wrote in message news:8e22124249998[ at ]uwe... [Quoted Text] > 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 wrote: >>> 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. >> > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1 >

Re: How to insert multiple DIFFERENT recordsets into single form "edisonl via AccessMonster.com" <u47544[ at ]uwe> 12/4/2008 8:47:00 AM Hi Allen, Thanks for that... But How do I start going about doing it ? Edison Allen Browne wrote: [Quoted Text] >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. -- Message posted via http://www.accessmonster.com

Re: How to insert multiple DIFFERENT recordsets into single form "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 12/4/2008 11:11:47 AM 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "edisonl via AccessMonster.com" <u47544[ at ]uwe> wrote in message news:8e267131ba5c1[ at ]uwe... [Quoted Text] > Hi Allen, > > Thanks for that... > > But How do I start going about doing it ? > > Edison > > Allen Browne wrote: >>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. > > -- > Message posted via http://www.accessmonster.com >

Re: How to insert multiple DIFFERENT recordsets into single form "edisonl via AccessMonster.com" <u47544[ at ]uwe> 12/4/2008 3:08:31 PM 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 Allen Browne wrote: [Quoted Text] >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] >>>>>have >>>>>multiple tables with essentially the same fields. -- Message posted via http://www.accessmonster.com

Re: How to insert multiple DIFFERENT recordsets into single form "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 12/5/2008 12:11:41 AM "edisonl via AccessMonster.com" <u47544[ at ]uwe> wrote in message news:8e29c5fcb9f7d[ at ]uwe... [Quoted Text] > > ... 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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.

Re: How to insert multiple DIFFERENT recordsets into single form "edisonl via AccessMonster.com" <u47544[ at ]uwe> 12/5/2008 12:17:57 AM All right I gave a try & let you know again.. :) Allen Browne wrote: [Quoted Text] >> ... 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 > -- Message posted via http://www.accessmonster.com

Re: How to insert multiple DIFFERENT recordsets into single form "edisonl via AccessMonster.com" <u47544[ at ]uwe> 12/5/2008 12:25:47 AM Wow.. That was quite awesome, Allen *Kudos Edison Allen Browne wrote: [Quoted Text] >> ... 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 > -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1