Full Version: Problem With Subform
UtterAccess Forums > Microsoft® Access > Access Forms
shep
I am modifying the MS ACCESS template "Contact Management" for a charitable organization. I created a Main Form for contacts and am putting some of the Template's forms on it as subforms in tabs. After putting the form Contacts on it, the Command Button for Calls does not work. I get a request to enter parameter value "Forms!Contacts!ContactID'.
When I open the form Contacts, not the one on the Main Form, and press the Call button it opens the Calls form.
What might be the reason it is not working in the Main Form?
Thanks
fkegley
The message you are getting indicates it cannot find Forms!Contacts!ContactID. I would begin by checking the spelling of the names of the controls on the Contacts form.
shep
That may be, but it finds it from the form Contacts and I have not changed any structure. I only added the Contacts form to the Main Contacts form as a subform on a tab. Therefore it seems that in doing so, it upset something in the code.
cannot open the form Calls except via the command button on the Contacts form, so I think the code is confused when the Contacts form is changed to a subform.
If you have time to open that Template and assess, I would be most grateful.
Thanks
StarsFan
Is ContactID on the newly created subform?
Is the command button on the main form?
You may not be referencing it correctly.
It would need to be Forms![MainFormName]![subFormName].Form![ContactID]
HTH,
Shane
shep
I created a query from table Contacts that contacts ContactID and a new field ContactName that contains LastName and FirstName
created frmMainContactsV2 from the query that has the ContactID and ContactName fields. I then added a Tab page and created a subfrom on in created from existing form Contacts, which was created from table Contacts.
The form Contacts has the Command button Calls on it to open the form Calls. Here is code for the button:
Private Sub Calls_Click()
On Error GoTo Err_Calls_Click
If IsNull(Me![ContactID]) Then
MsgBox "Enter contact information before making a call."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Calls"
End If
Exit_Calls_Click:
Exit Sub
Err_Calls_Click:
MsgBox Err.Description
Resume Exit_Calls_Click
End Sub
Does the code you suggested need to be in this code?
Thanks
StarsFan
Is, 'Forms!Contacts!ContactID', in the grid of Record Source for the Calls form? If it is it would be under ContactsID in the criteria section.
If it is there then that is where you would need to change your reference. If you moved Contacts from a MainForm to a subForm then the line 'Forms!Contacts!ContactID' is no longer recognized by the Calls form.
HTH,
Shane
shep
I suspect you have identified the problem.
Record source for Calls:
SELECT DISTINCTROW Contacts.* FROM Contacts WHERE (((Contacts.ContactID)=forms!Contacts!ContactID));
riteria: [forms]![Contacts]![ContactID]
Otried entering code you suggested, Forms![frmMainContactsV2]![Contacts].Form![ContactID]
in the criteria to no avail. I also tried it in the record source.
I probably am not entering it correctly, so I ask for your patience and help a little further. Have I misread your code or not enterd it in the right place.
Thanks
StarsFan
Well at least we've gotten some where. If ContactsID is in a subForm called Contacts and Contacts is in a MainForm called frmMainContactsV2, then the reference I gave you should be right so from here I would guess you either have something misspelled or we're missing something in the reference. Why don't you post back with exactly where ContactsID is.
hane
shep
Thanks
ContactID is in the table Contacts, autoNumber and PK
It is in the table Calls, row source is: Select [ContactID],[LastName] & "," & [FirstName] From [Contacts] ORDER BY [LastName] & "," & [FirstName];
It is in the query qryContactNameV1, SELECT Contacts.ContactID, ([LastName]) & ", " & ([FirstName]) & " " & ([MI]) AS ContactName
FROM Contacts;
It is in the form frmMainContactsV2, record source qryContactNameV1
It is in the form Contacts, record source table Contacts. The command Button to open form Calls is on this form
Private Sub Calls_Click()
On Error GoTo Err_Calls_Click
If IsNull(Me![ContactID]) Then
MsgBox "Enter contact information before making a call."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Calls"
End If
Exit_Calls_Click:
Exit Sub
Err_Calls_Click:
MsgBox Err.Description
Resume Exit_Calls_Click
End Sub
The form Contacts is also on frmMainContactsV2 as a subform
If I try to open the Calls form, I get the same request for parameter value as I do when I click the Command Button on the subform Contacts which I created on the frmMainContactsV2 from existing form Contacts. When I click the Command Button on the form Contacts, it works.
Ocopied the code you provided and pasted it in the criteria for Calls, clicked the Command Button on subform Contacts (on frmMainContactsV2) and received message to Open Calls Form Using Buttion on Contacts Form.
THere is SQL for Calls criteria: SELECT DISTINCTROW Contacts.*
FROM Contacts
WHERE (((Contacts.ContactID)=[Forms]![frmMainContactsV2]![Contacts].[Form]![ContactID]
));
I then clicked Command Button on form Contacts and got request for parameter value [Forms]![frmMainContactsV2]![Contacts].[Form]![ContactID]
I apologize if I have included too much data, but I don't want to be guilty of not fully responding to you.
Thanks for your patience and effort.
StarsFan
Hey Shep,
How big is your DB? Maybe it would be easier just to attach it and let me or someone from UA, take a look at it.
Shane
shep
Thanks
It is 98KB zipped
would love to send it. I would almost drive from San Antonio to Rendon!
For the record, I am a retired fellow and IT is not my forte. But I have a little ability, but not programming. I do volunteer work for charitable organizations. In this case, I am trying to help one convert an unmanageable Excell workbook comprising 15 really messed up worksheets to a dBase.
May I send the file as a winzip attachment?
StarsFan
Hey Shep,
You should be able to attach here at UA so that not only I can look at it but anyone else that wants to jump in and help.
Shane
shep
Great. I have just begun working on this so most of structure is original MS ACCESS template Contact Management. I have not added frmMainContactV2 to the switchboard.
Thanks for your help
StarsFan
Shep,
See if this is what your were looking for.
Shane
shep
It is so close! It does open the Calls form, but it does not appear that I can post. Did you try to make an entry? We are almost to China Grove!!
StarsFan
Hey Shep,
So, I didn't try to do any data entry, but I just now tried. Looking around behind the scenes, doing a fast scan, you have a little bit of a cob web to untangle. Due to moving the forms around and making them subforms, their references are off. In some of the code too. I wish I had time to help you sort it all out but unfortunately, I don't. I did get the Calls form to open for ya so maybe that can help get ya on the right track.
Good Luck,
Shane
shep
Hey, you are a winner!! Thanks for all your help. Given what you have provided and what is to come, we gonna win!
o all of you professionals that share your expertise and time to help us novices, Thanks!!
StarsFan
Your welcome Shep. If you get stuck again, just post. This is BY FAR the best forum, with great folks, eager to help and if you get real stuck, then let me know, you may have to make that drive up I35. frown.gif
hane
shep
To my friends at UA
thought you might be interested in my progress with this project. I don't have much test data in it, but I think enough that you can get a feel for it. I know that y'all are very busy and I don't mean to impose on your time, but if you are inclined take it for a little test drive. I would not be this far along without the valuable help y'all have given me.
Thanks
Jack Cowley
On your contact form and the Volunteer button I can select Fundraiser in the left box but it does not appear in the list to the right until I close the form. You may want to requery the subform to the right so the addition shows right away. I can also select Fundraiser again, close the from and reopen and now Fundraiser has replaced one of the other 3 records so now there are 2 Fundraiser records, which I am sure is not what you want...
hings are looking good....
Jack
shep
Thank you very much for that feed back, both issues I want to resolve! However, I do not know how to resolve those issues now. I will research, but would also appreciate help.
Thanks again
Jack Cowley
I would do the Volunteer part of your form quite differently. You are, in my humble opinion, making it more difficult that it needs to be. I would have only the Datasheet view subform and it would be based on the table "tblVolunteerTypes2V1" or a query based on that table. the VolunteerTypeID control would be a Combo box based on table "tblVolunteerTypeV1". This would be so much easier and it eliminates the form to the left. If you need to add a volunteer type you can use the Not In List event of the combo box to let the user add a new volunteer.
oes what I am saying make sense? If you need more details just let me know.
Jack
shep
Yes it does make sense to me, a novice. I created the subform on the left to select and delete the types of Contacts and Volunteer a Contact opts for and the one on the right to show at once the types of Contacts and Volunteer a Contact has. It occured to me that one subform should be able to accomplish both, but I have not had time to explore how to do that.
Is you probably noticed, the subform on the right is created from a query, which is created from "tblVolunteerTypes2V1" , just as you recommend. The " Not In List" event is the part I'll have to learn to implement. I assume that with the method you propose, the user will still be able to pick "Types" from a dropdown list, that is important.
You have suggested three significant improvements and I appreciate that much. I will work to solve those three this weekend, and I will appreciate greatly any specific examples or code you or others can proved.
Kind Regards
Jack Cowley
Shep -
That you need to do is really quite simple. Just open your current datasheet subform in design view, right click on the volunteerTypeID control and select Change To. Change it to a combo box. Set the Row Source to the Volunteer table. Set Column count to 2. Set Column Widths to 0";1". That should do the trick.
HAs for the Not In List event - Let's suppose the user wants to add another volunteer type. He/She would type in the name in the combo box and would be given a message that that type does not exist and do you want to add it. Answering Yes allows a form to be opened for the addition of the type or, in your case, you can use code to add it without opening a form. You will find the answer on how to do this in Method 1 of this article.
Good luck. I will be around tomorrow (probably) but not Sunday...
Jack
shep
I think I have improved the dBase according to some of your recommendations. Please look at the Contacts, Volunteer, Donor, and Membership subforms.
still need to fix those subforms so that a "Type" cannot be added more than once. Will that require coding?
I did not try to include the "Not In List" event since I do not expect that to be an issue for the user. I expect the Administrator will control the options. I will do it on a work copy for the experience.
Does it appear that I revised as you envisioned?
Thanks again for your help.
Jack Cowley
Things are looking much better and I see you are using the proper naming conventions for some of your objects. At this point my only suggest regarding your tables is to get rid of the Lookups (ie. table 'Calls', field 'ContractID') as you want to see the ID when you look at the table, not what it represents. You want to do all 'lookups' (combo boxes) at form level, not table level.
et up all of your relationships in the Relationship window and only use the first radio button for the Join Type ("Only include rows where the joined fields from both tables are equal"). Enforce Referential Integrity for all joins.
HAs for no duplicates when making a selection form a combo box - You will have to use code to do this. You can use DLookup() to see if the item selected is a duplicate or you can base the Row Source of the combo box on a query that is filtered by the record you are working on. This means that the combo box only shows items that have NOT been added to the proper table so the user cannot enter a duplicate because none are shown. This may be a bit more difficult as it will require a subquery within a query to work properly.
In table 'Contacts' MaritalStatus should be an ID to a related table so that all users use the same status for each contact.
Table "tblMembershipsV1" you might want to conside a related table for the two dates so you have a history.
I have only taken a cursory look at the entire database as there is a lot here, but I think you are on the right track.
hth,
Jack
shep
Many thanks for all your help. I shall work on all your recommendations. I'll post again on this project only if I cannot resolve any of the issues you raised.
ind Regards
Jack Cowley
Shep -
You are welcome and good luck!!!
Jack
shep
I believe I am about done. I would like to add the table you suggested for membership history. Would please give some directions on how to do that.
Thanks
Jack Cowley
Shep -
You can use your existing "tblMembershipV1" as long as it is in Datasheet view as a subform. In the main form you should be able to select the Member and MembershipType and then add the new EffDate and ExpDate to a new record in the subform. This will give you the history that you need of each Member and their MembershipType.
Let me know if this will work for you, but I think it will.
Jack
shep
OK Thanks. I have it that way now albeit in form view vice Datasheet view. I have a command button for ease of going to a new record. I have entered test data and it appears to work properly.
Is there an advantage to using Datasheet view?
Thanks
Jack Cowley
The only advantage to Datasheet view for your subform is that you can see the past few records. Other than that there is no advantage. Use whatever view works best for your users!
ack
shep
Again, many thanks sir! However you celebrate this season, I pray it is joyous for you and your family and that you have a happy and successful 2006.
Jack Cowley
Shep -
Thank you very much and I am glad I was able to help. I, too, which you a joyous season and my the New Year be your best one ever!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.