form and subform upadates...sometimes

[ppoindexter]

i have a form with a bound subform
on the main form i have combo_one that when selected
displays all records relevant to that record on the subform

the main form and subform are linked via the id that is selected in the combo_one

the form is bound to tblalpha
the subform is bound to tblalpha and tblalphalist

the problem is sometimes the subform updates as records are selected in combo_one and sometimes it will not update until after i add a record to the chosen record in combo_one and sometimes it will not update at all...

(when the subform will not update...the previous records linked records stays visible in the subform even though a new record in combo_one has been chosen)

i thought since the form and subform are linked (master/child) that was all that was needed....

thanks in advance

 

[Jack Cowley]

The subform should only be bound to the tblAlphaList. Adding data in the Mainform should not add records in the subform unless you have code to append data to the subforms table. An example is Customers and Orders. The Orders table is joined to the Customer table through CustomerID and linked via Master/Child links. To add an Order you select the Customer in the Mainform then add the Order data in the subform. This is how your setup should work too...

hth,
Jack

 

[ppoindexter]

hi jack
how ya been? you probably dont remember me, but you helped me with my first db a couple of years ago..through this forum...and i learned so much from you...just wanted to say thanks...and thanks for responding to this post...

about the post...i think i didnt do a very good job of explaining
so i will try again...sorry for the confusion..

i have a form that is bound to tbl_lesson_skill

tbl_lesson_skill
fld_lesson_skill_id (pkey)
fld_lesson_id (fkey from tbl_lesson_list)
fld_skill_id (fkey from tbl_skill_list)


the form has several cascading combos and a list box
combo_lesson (record source tbl_lesson_list, bound to fld_lesson_id)
list_skill (record source tbl_skill_list, bound to fld_skill_id)

user selects a lesson from combo_lesson then
selects a skill(s) from list_skill, clicks cmd_add
user selects as many skills as they want for each lesson

what i am trying to do is set up a subform that when a lesson is selected in combo_lesson, the subform then shows all skills selected, and then updates as other skills are added to that particular record...

that is why i based it on tbl_lesson_skill, and tbl_skill_list,
as the main form as i am just wanting to view the entire list of skills for each lesson as they are added to that table.....i may be going about this totally wrong....hope that explains better

 

[Jack Cowley]

Hiya Pat!!!

I wasn't sure if that was you or not, but now I am sure that it IS you! I surely do remember helping you in the past with a database that ended up with 12 million tables, forms and other bits of flotsam....or there about... I hope this finds you well and I can see that your are obviously working like mad on a new database...

My suggestion would be to have a list box that lists only the available skills for the selected lesson. (I.e. in your subform for the lesson it may show skills A, B and C so the list to select from would only show skills D, E, F, etc. as available to add to the current lesson.) Then the user can check the skills in the list box that they want to add and then code can append the checked records to the appropriate table. I may have lessons and skills mixed up but I am sure you can sort out my mistakes...

Here is some pseudo code to loop through selected items in a list box and add them to a table:

For Each varItem In ctlList.ItemsSelected

mysql = "INSERT INTO tblSkills (SkillID) VALUES (" & tlList.ItemData(varItem) & ");"

DoCmd.SetWarnings False
DoCmd.RunSQL mysql
DoCmd.SetWarnings True

Next varItem

I hope this helps and if you need more details I think you still have my email address so email me directly. If not, my email address should be available here at Access World forums.

Jack

 

[Jack Cowley]

Pat -

I have your db and will be looking at it shortly. The reason for this post is to let you know that your email box at Hotmail is full and I cannot send you an email...

Jack