continuous from with subform

[shutzy]

i know all you guys out there are gonna tell me this cant be done for various reasons however i had this image in my head of what i wanted to do and what it would look like. however it cant be done via the subform route so i will need some suggestions.

what i have is a one to many table format.
in the continuous form in need to display on each record the 'many' records of the from the one table. in the past i have done this via lst boxes and subforms. these have of course been on single forms.

how would i do this. if there is no answer out there then i know of a work around not using the one to many table system but i wold like to atleast try this frist.

thank you

hopefully vba will answer as he is the one that introduced me to one to many

 

[vbaInet]

He might be around shutzy

You won't be able to have it in-line in a continuous form, but in datasheet view you can by using the Subdatasheet property of the table. This is not the preferred option.

What you can do however is house your continuous form in a subform and below this you will have another subform that displays the related records you wish to display. Sounds doable?

 

[shutzy]

sounds like it would do the trick but im not sure the data would display easily.

its for a waiting list. so we could do with quick viewing and the girls are beauty therapists. not exactly pc friendly and they can make alot of mistakes.

one possibility would be to have the continuous for within the subform. more like a lst box but displaying more details and on click of (somet) it would display the waiting list treatments in the main form.

the other option i thought of(my initial plans) was to have columns in the waiting list tbl. treatment1 - 10. i cant see there being anymore than 10 treatments in a waiting list for each individual case. then have a text box populated by this data ie. item1, item2, item3,. but that wouldnt leave room for changing the items in the waiting list.

it isnt really neccessary to do its more for reports purposes so we can see how much we are losing and if we need more staff. we can just write it as we are in a memo box. but i do like my gadjets!

 

[vbaInet]

Changing the table structure just to accommodate your form needs, shouldn't be an option. If your tables are already properly structured, it shouldn't be changed.

If it's going to be a read only list, just lock the subform and yes it should be quick (but then again I can't vouch for your db).

Create the two subforms as previously advised and we can take it from there.

 

[shutzy]

i have attempted what you suggested vba. i have created 2 subforms. both continuous. i have created a buttton on the waiting list record to show treatments within that record on the 2nd subform. just to see if it works really.

i have attached the relevant tables and forms and welcome any suggestions. any new tricks for me to learn.

thank you

 

[vbaInet]

Ok, follow the example in the link below to link both subforms:

http://www.access-diva.com/f2.html

 

[shutzy]

what would this do. or is it a big suprise?

 

[shutzy]

i have read it and i dont seem to be able to find the subform linker. is it because i created a form then draged it in instead of creating a subform within the main form

 

[shutzy]

sorry im braindead. try checking design view. ha ha

 

[vbaInet]

Click the subform once and look in the property sheet. A single click is the subform control itself (which is what you need), and a second consecutive click takes you to the form embedded in the subform control (which isn't what you want).

 

[shutzy]

in the treatment subform master field i have put

Forms!frmWaitingList!frmWaitingList-Subform!ID
in the child field i have put
tblWaitingListID

is this right. is it even within the correct form. this is very new ground for me.

i tried changing the source object but that changes the whole subform.

a possible way considering what i have already put together would be to have the treatment subform move virtically with the cursor on click of btnTreatments.

im only thinking out loud here so stop me if i am jumping ahead.

 

[vbaInet]

im only thinking out loud here so stop me if i am jumping ahead.

Stop... thinking out loud... you're hurting my eardrums

What are the names of the fields that should bind both subforms?

 

[shutzy]

tblWaitingList.ID & tblWaitingListTreatments.tblWaitingListID

the tblWaitingListID maybe should be WaitingListID

 

[vbaInet]

Indicate which subform is linking which and which field relates to which subform.

 

[shutzy]

have you not opened the attachment?

frmWaitingList-Subform Has ID and frmWaitingList-Treatmentsubform has tblWaitingListID.

sorry i wrote the last post incorrectly

 

[vbaInet]

Have a look at the attached.

 

[shutzy]

thank you for that. but what is it meant to do exactly. there are no records appearing in the -TreatmentSubform

 

[shutzy]

i have managed to make it show the records with the after change event on txtSubID to requery the subform.

 

[vbaInet]

There has to be corresponding records. Using the 17/05 it's the second record that has a corresponding record with the treatments subform.

 

[Sean O'Halloran]

Re: continuous form with subform

I want to thank both of you for showing me a new way to pull multiple levels of data together on one screen. I appreciate the time, effort, and the mdb examples.

Sincerely,
Smilin' Sean