Hello boys and girls,
I've never really used Access before (although I have some SQL/DB experience) and therefore don't know my posterior from my elbow with regards to "the Access way of doing front ends" but I'd like to avoid doing everything in vba and DoCmd.RunSQL..
The DB's purpouse is to log information divided in four main categories; contacts, locations, events and organizations (each a table). Then there are log entries that have a many-to-many with all the categories. A log entry is just date, who entered and a rich text memo for the actual entry; one log entry might apply to two contacts, one location and three organizations for example. Furthermore there are a few other relations (such as a contact has a location, can be a member of one or several organizations, be reponsible for an event etc etc). The actual DB design is not an issue (just a few junction tables).
So, there are a number of form design issues that keep popping up.
MANAGING MANY TO MANY RELATIONS
What's the Access interface way of selecting to which records a log entry applies? I am thinking a popup form with 4 multi-select listviews; for each main category there's a listview and a "create new whatever" button linked to a small dialog (to create a new record in the category, the dialog will just take the bare minimum for the record, like first and last name for a contact). How do I set that up to avoid populating and updating everything with vba? Is there a way to use the "multi-value"-style combo box instead of the list views (without using multi-value fields)?
The actual db design is for this bit is 5 base tables:
contacts
locations
events
organizations
logs
And 4 junction tables:
contact-logs
location-logs
events-logs
organizations-logs
JUNCTION TABLE UPKEEP
What's the Access way of keeping the junction and base tables in sync? I.e. once there are no more relations to a logs record the record is deleted? Right now I'm looking at doing a clean up query in the main form OnClose, is there a "better way"?
CONTINUOUS VIEW SUBFORM DISPLAY
So, obviously I want to pull up the record for say a contact and see all the log entries which concerns him; so far I'm using a continous view subform for that. Ideally I would like each record to expand/shrink in size so that all info (specifically the memo) is displayed without scroll bars; i.e. each record has a different displayed height. Furthermore I'd like for the subform not to have any scrollbars but that the mainform is resized appropriately depending on the subform size (multi level scroll bars are a pet peeve of mine). What's the Access way of doing this? Right now I'm using the .../forums/showthread.php?t=174690 way, but I need to implement for resizing when a log entry is added and removed, and I can't seem to get the main form to shrink when displaying a record with less log entries (although the subform is appropriately displayed)..
FILLING CONTROLS
Another issue having a control display the result of a query based on the current record; for example, on the form displaying a contact I would like to show the organizations he's a member off, to keep the form compact I was thinking of a label and a "edit" button, the label is basically displaying "SELECT organizations.org_name FROM organizations INNER JOIN [contacts-organizations] ON organizations.org_id = [contacts-organizations].org_id WHERE [contacts-organizations].contact_id = XXXXXXX;" where XXXXXXX is the current record id, all concatenated with comas, and the edit button will pop up a dialog with a multi select list view. What's the Access of achieving this?
Thanks
j
I've never really used Access before (although I have some SQL/DB experience) and therefore don't know my posterior from my elbow with regards to "the Access way of doing front ends" but I'd like to avoid doing everything in vba and DoCmd.RunSQL..
The DB's purpouse is to log information divided in four main categories; contacts, locations, events and organizations (each a table). Then there are log entries that have a many-to-many with all the categories. A log entry is just date, who entered and a rich text memo for the actual entry; one log entry might apply to two contacts, one location and three organizations for example. Furthermore there are a few other relations (such as a contact has a location, can be a member of one or several organizations, be reponsible for an event etc etc). The actual DB design is not an issue (just a few junction tables).
So, there are a number of form design issues that keep popping up.
MANAGING MANY TO MANY RELATIONS
What's the Access interface way of selecting to which records a log entry applies? I am thinking a popup form with 4 multi-select listviews; for each main category there's a listview and a "create new whatever" button linked to a small dialog (to create a new record in the category, the dialog will just take the bare minimum for the record, like first and last name for a contact). How do I set that up to avoid populating and updating everything with vba? Is there a way to use the "multi-value"-style combo box instead of the list views (without using multi-value fields)?
The actual db design is for this bit is 5 base tables:
contacts
locations
events
organizations
logs
And 4 junction tables:
contact-logs
location-logs
events-logs
organizations-logs
JUNCTION TABLE UPKEEP
What's the Access way of keeping the junction and base tables in sync? I.e. once there are no more relations to a logs record the record is deleted? Right now I'm looking at doing a clean up query in the main form OnClose, is there a "better way"?
CONTINUOUS VIEW SUBFORM DISPLAY
So, obviously I want to pull up the record for say a contact and see all the log entries which concerns him; so far I'm using a continous view subform for that. Ideally I would like each record to expand/shrink in size so that all info (specifically the memo) is displayed without scroll bars; i.e. each record has a different displayed height. Furthermore I'd like for the subform not to have any scrollbars but that the mainform is resized appropriately depending on the subform size (multi level scroll bars are a pet peeve of mine). What's the Access way of doing this? Right now I'm using the .../forums/showthread.php?t=174690 way, but I need to implement for resizing when a log entry is added and removed, and I can't seem to get the main form to shrink when displaying a record with less log entries (although the subform is appropriately displayed)..
FILLING CONTROLS
Another issue having a control display the result of a query based on the current record; for example, on the form displaying a contact I would like to show the organizations he's a member off, to keep the form compact I was thinking of a label and a "edit" button, the label is basically displaying "SELECT organizations.org_name FROM organizations INNER JOIN [contacts-organizations] ON organizations.org_id = [contacts-organizations].org_id WHERE [contacts-organizations].contact_id = XXXXXXX;" where XXXXXXX is the current record id, all concatenated with comas, and the edit button will pop up a dialog with a multi select list view. What's the Access of achieving this?
Thanks
j
Last edited:
