Help on how to make a form with these characteristics

[seanst8579]

Hello guys and gals,



I'm pretty new to Access and am having problems creating the sort of form I need. Here's the background:

I've got three tables, tblContacts, which contains names and contact data, tblTitles, a list of books and book-specific data, and tblContactTitlesMM, a many-many junction table linking contacts to titles. Drawing from the book-specific data on tblTitles, I also created a query, qryPredNet, which calculates a monetary value for each title.

I need a form that will show users individual contacts from tblContacts and the total PredNet for all titles associated with that contact through the junction table such that records in tblContacts are editable through the form and the form can be sorted by total PredNet.

My initial thought was to create a new query, sum the values from qryPredNet and group by contact, but this ends up giving me everything I need -- the PredNet sum, PredNet sortability and unduplicated contacts -- except data entry. Creating a Make Table query was another idea I tried, but I want edits going to the tables above and any new title information entered via a subform would seem to require requerying the Make Table query every time something is changed.

Any suggestions about how this form should be set up would be greatly appreciated!

[ssanfu]

I would create:
a form (actually it will be a sub form) for tblContactTitlesMM.
a form for tblContacts and
a form for tblTitles.

All three form have the record source as a query based on the table.
If you opened the form frmContacts, you could add titles in the sub form.
If you opened the form frmTitles, you would be able to add contacts.

"qryPredNet" could be the record source for another form (un-editable).

You might want to work through these tutorials:
http://www.rogersaccesslibrary.com/forum/forum46.html
I think it will help you.

[seanst8579]

I would create:
a form (actually it will be a sub form) for tblContactTitlesMM.
a form for tblContacts and
a form for tblTitles.

All three form have the record source as a query based on the table.
If you opened the form frmContacts, you could add titles in the sub form.
If you opened the form frmTitles, you would be able to add contacts.

"qryPredNet" could be the record source for another form (un-editable).

You might want to work through these tutorials:
http://www.rogersaccesslibrary.com/forum/forum46.html
I think it will help you.

Thanks for the reply.

I'm not sure this will give me what I need; without the qryPredNet sums attached to the main form with the contacts, how can I sort the contacts according to those values? I thought about putting the sums in a subform and displaying them on the main form via an unbound text box, but it just gives an error when you look at the main form in datasheet view.

[ssanfu]

Well, I don't use datasheet view. I use continuous forms view and set the controls touching.

I would put the sub form in the form footer.
Since the query "qryPredNet" is not editable, the form bound to "qryPredNet" would be for viewing - you can set the sort order however you want.

Do you have a demo dB of what you are trying to do?

BTW, I would stay away from make table queries.

[seanst8579]

Do you have a demo dB of what you are trying to do?

I don't, no, but maybe it would be helpful if I just described the data a bit more.

So tblContacts looks something like:

ContactID	FirstName	LastName	Phone	Email
1	John	Smith	5555555	smith@smith.com
2	Robert	Jones	6666666	jones@jones.com

tblTitles like:

TitleID	Title	Data1	Data2	Data3
1	Book Title 1	3	8	87
2	Book Title 2	5	65	65
3	Book Title 3	7	34	43
4	Book Title 4	3	12	45
5	Book Title 5	2	89	67

tblContacTitlesMM:

ContactTitleID	TitleID	ContactID
1	1	1
2	2	1
3	3	2
4	4	2
5	5	2

and qryPredNet runs a calculation on all the data fields in tblTitles to determine a value for each title:

TitleID	PredNet
1	34
2	76
3	98
4	23
5	11

What I need -- what my colleague is asking for, really -- is for the form to look like this:

FirstName	LastName	Phone	Email	PredNetTotal
Robert	Jones	6666666	jones@jones.net	132
John	Smith	5555555	smith@smith.net	110

Sorted (or at least, sortable) in that order and all fields from tblContacts editable. The sort of things he would find unacceptable are, for example:

FirstName	LastName	Phone	Email	TotalPredNet
Robert	Jones	6666666	jones@jones.com	132
Robert	Jones	6666666	jones@jones.com	132
Robert	Jones	6666666	jones@jones.com	132
John	Smith	5555555	smith@smith.com	110
John	Smith	5555555	smith@smith.com	100

i.e. displaying a contact and a PredNet sum for each title associated with that contact or, even worse, displaying a contact and an unsummed PredNet for each title associated with that contact. I didn't mention it before because I didn't find anything about it particularly problematic, but just to be exhaustive, there are two additional junction tables, tblTitlesRHMM and tblTitlesAuthorsMM, that associate contacts with titles in their capacity as rights holders or authors. As the form is set up now -- and this is the sort of view my colleague wants to maintain -- the contacts and PredNet totals make up the main form, there's an editable subform displaying the titles and relevant title-related data associated with a particular contact, another subform showing all the authors of all those titles and a final subform showing all the rights holders.

[ssanfu]

Originally Posted by ssanfu
Do you have a demo dB of what you are trying to do?

I don't, no, but maybe it would be helpful if I just described the data a bit more.

I asked if you had a demo db so I wouldn't have to spend my time creating a dB. If you had provided the dB with a few records, I could have see what your table structure is and provided a better answer.

So here is my attempt.

but just to be exhaustive, there are two additional junction tables, tblTitlesRHMM and tblTitlesAuthorsMM,

I didn't try anything with the two additional junction tables.

If you double click on the first name, last name or the record selector, a form will pop up so the Contact info can be edited.

Good luck with your project....

[seanst8579]

Thanks, that was extremely helpful. I wonder if you might break down what you did with the code, or point me in the direction of some resource, so I can apply that sort of double-click-to-open-an-underlying-form approach elsewhere. For example, it would be nice to click somewhere on the titles subform (which is based on a query of tblTitles and tblTitlesContactsMM), have it open a form set to a new record, populate the ContactID field from the value in the main form and then enter a new title and title information.

I take it that this one:


Sub OpenContacts(pContactID As Long)
DoCmd.OpenForm "frmContacts", , , "ContactID_PK = " & pContactID
End Sub

creates "OpenContacts", and whenever OpenContacts is run (as on a double click), it opens frmContacts to a record that matches its ContactID value to whatever value of ContactID is on the mainform. What exactly is that "p" doing?

[ssanfu]

I wanted to open the form "frmContacts" from three different places: the form double click and the firstname and lastname controls double click events. Rather that write the same code three times (a valid option), I wrote a UDF (user defined function) and called it three times.
Now if I want to modify the code by opening a different form, I only have to change the code in one place, instead of three places.

I need to open the form "frmContacts" to a specific record, which is the PK field.
In the sub "OpenContacts", I could have used almost any word (but not reserved words) for the parameter. In my naming convention, I use the prefix "p" to denote a parameter variable. Easier for me to see where values come from...

For instance, I could have used "banana" and the code would have looked like

Sub OpenContacts(banana As Long)
    DoCmd.OpenForm "frmContacts", , , "ContactID_PK = " & banana
End Sub

whenever OpenContacts is run (as on a double click)

Not so much "where it is run" as "where the UDF "OpenContacts" is called from". But you are on the right track.
Look up the "Call" keyword.