Suggestions please on how to create this form

  • Thread starter Thread starter Tamara
  • Start date Start date
T

Tamara

Hi
I have a database with 2 tables:

1)600+ contacts

Name Company Address
A Bloggs Smith & Co 1 London Wall
B Clark Jennings plc 2 Barbican
....

2)table with 2 columns: dates of conferences and contacts
attending

Conference Date Contact
Nov 2003 A Bloggs
Nov 2003 B Clark
Dec 2003 B Clark
....

When a new conference date is organized, most of the
contacts will attend.

I want an easy way add a conference date and all the
people attending ie not adding 600+ records.

I thought I could via a form. The form should contain
the names of everyone and a check box next to each one.
There should be a select all option and then one could
deselect all those not attending.

Please could you let me know how to do this or suggest a
more efficient way of approaching this.

Thanks
Tamara
 
The easiest way I could think of to do it is by using a
list box. That would allow you to use the shift keys and
control keys to select, deselect all contacts or just an
individual contact. The shift keys allow you to select a
first and last contact with everybody in between and the
control key allows you to select an individual contact.
 
Tamara,

I would add a third table. Table structure would be as
follows:

tableContacts:

contact_id, surName, lastName, Address, etc...

contact_id would be your primary key and should be
autonumber.

tableConference:

conference_id, conf_date, conf_location, conf_description
etc...
again conference_id is the primary key and would be an
autonumber field.
tableConferenceAttendees:

contact_id, conference_id

The primary key in this table would be the contact_id,
conference_id combination.

You would have to add the conference to the conference
table first, then open an attendee form. On this form you
would have one field for the conference that is a combobox
based on the conference table. You would also have a
listbox based on the contacts table. You can manually
select attendees from the list box and there is probably a
way to press a command button or something like that, but
I am not sure off hand how to do that. To add records to
the table tableCOnferenceAttendees you would use code
similar to the following:

***************************
dim db as database
dim qdf1 as querydef
dim sqlStr1 as string
dim itemSelected as integer

dim conferenceID as Long
....

set db=currentdb

with me!comboboxName

for itemSelected = 0 to .Listcount-1 'combobox listcount
is 0 based
if(.selected(itemSelected))then
sqlStr1 = sqlstring to append conferenceID and
contactID to tableConferenceAttendees
set qdf1 = db.createquerydef ("",sqlStr1)
qdf1.execute

if(qdf1.recordsAffected=0)then
msgBox "There has been an error, contact the
database administrator for assistance",vbOKOnly
endif
...
end if
next itemSelected

end with

************************

Using the suggested table structure will be more efficient
and will allow you to retain conference history.

If you need to delete someone from the list, you would do
something similar but substitute a delete query for the
append query.

Hope that helps.

Kevin
 
Back
Top