Linking a record to records in another table

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have two tables one lists the names of committees and the other is a list
of documents they generate. I have a form based on the documents table which
gives details of the document including which committee generated it. On the
form I also want to indicate which other committees are interested in that
document, there could be 1 or say 8, it differs for each document. I had
thought of creating say 10 combo boxes based on the committee table called
comm1, comm2 comm3 etc and using them to store the other committees but this
seems a bit cumbersome and if say all the committees are interested, and
there are upto 30, then that approach would not be enough.
Anyone any ideas how I could work this? I am a relative newbie, particularly
at VBA!!!!
TIA

Tony Williams
 
YIKES!!! Put down the Keyboard and step away!!!!
Heeheeheehee .... Sorry ... just having some fun on this
wondeful Sunday before I get busy programming.

You need to create a listbox (multiselect as Extented) on
the form and allow the user to select one to ALL of the
commities. Create a JOIN table that stores just the
Committees ID and the DOC_ID they are interested in.
This table will then relate your information in three
fields (JOIN_ID, Committee_ID, Document_ID) in a much
smaller table. There may be tons of records in this
table, but it will not be the size of a table with 30
fields filled iwth little bits of data. Remember, a
table will allocate memory assuming there is data in the
field even if it is null.

IF you need help with the code on storing the
data "selected" in the listbox let me know.

The code would be something liek this:

Dim CommSel
Dim Jrst as New ADODB.Recordset

With Jrst
..open "tbl_Name", currentproject.connection,,
For each CommSel in Me.lstCommitteeAvailable.ItemsSelected
.addnew
!Committee_ID = Me.lstCommitteeAvailable.Column(0,
CommSel)
!Doc_ID = what ever txtbox you have the document
listed in ... or if in a combo box the bound column
(suggest you bound the box with the Doc ID and just show
the name... use Col Withs and Col Count in Props-Format)
.update
next
end with

Hope this helps.


Drew
"We are the programmers that dont do anything... ) For
those that know Veggie Tales.... substitue "pirates"

Have a great day!
 
Thanks Drew. Hope you enjoyed your Sunday!! I think (??) I follow you
although I'm a newbie with VBA but I'll try and work through it and come
back to you in a day or so. Do you relook at previous postings or would I be
better to repost?
Tony
 
repost.... I got back and check on previous postings that
I have answered to see if there are any additional issues.

Drew
 
Back
Top