Best way to make a very complicated form?!!

[jprochelle]

I have a list of clients. I have a list of events. I have a list of bankers. I want to creat a form by which the banker can scroll the events (possibly by record selector just going from one to the next) and for each event his list of clients populates (possibly in datasheet view) and he can click on a checkbox column labeled "INVITE". By doing this, he is invited these clients to that one particular event.

Afterwards, I plan to print a report that shows the ONE EVENT and all clients invited.

The problem I've had is getting a new list to populate each time for each event. Will it be easy to just add a command button that will populate a current client list for the banker?

Thanks!

 

[gol4]

Here is one of many ways to do it

1 form
add 1 combobox name it cbbankers
add 1 listbox call it lstevents
add another listbox call it lstclients
the banker will select their name from the combobox select the event and then select who they want to invite in the other listbox

set cdbankers rowsource to the bankers table or a query
select bankersid, bankername from bankerstable
bind its column to the bankerid and use column width so it only shows the bankers names

set lstevents row source to events table or a query make sure it is a single select listbox

then under the cbbankers after update event place code to
set lstclients row source to the bankers clients using something like this

dim strsql as string
stqsql = "select clientsid, clientname from clientsstable where bankerid = " & me!cbbanker
me!listclient.rowsource = strsql

Make sure lstclients is multi select.
Add a command button under the lstclients listbox and on the buttons on click event code to append invites to the invites table.

dim varitm as variant
'code to delete all previous entrys matching bakerid and eventid
for each varitm in lstclients.itemsselected
strsql = insert into invitestable eventid , clientid , bankerid) values(" & me!lstevent & "," & varitem & "," & me!bankerid)
docmd.runsql strsql
next varitm


then last thing would be in the lstevents after update event code to show who is already selected by using sql and going thru the listbox items set the selected to true. based on the banker and which event is selected on this form

reports would be based on invites table

This is real rough example on a way to do it and only for thought. Much better and working examples on how to accomplish each step can be found either in the FAQ section or via key word search.
If this is not clear enough let me know
Good Luck