If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Criteria for opening form from a different subform
Hi Scott,
I see you got rid of the default values for numeric foreign keys smile In my opinion, it is best not to use Captions in the table design -- the only people who should open tables directly are administrators and they need to see the real fieldnames, not captions Do be sure to fill out the field Descriptions in your table designs -- these are used for StatusBarText when you created forms. you still have forms based on multiple tables -- if you plan to edit data, your should not do this in frmViewDonor, do not pull Position information into the RecordSource -- this can be shown using a combobox. Because qryViewDonor gets all fields from both Contributors and PositionList, you are only going to see records where PositionID is filled out. Here is what you see for your current sql: SELECT tblContributors.* , ([DonorFirstName] & " " & [DonorLastName]) AS DonorName , ([City] & ", " & [StateorProvince] & " " & [PostalCode]) AS CSZ , tblContributors.DonorLastName , tblPositionList.* FROM tblPositionList INNER JOIN tblContributors ON tblPositionList.Position_ID = tblContributors.PositionID ORDER BY tblContributors.DonorLastName; Donor First Name Diane Scott Karen Mary Luci Claire June Amy Linda by changing the Join Type to show all records from Contributors even if a matching record is not found in PositionList, the FROM clause of the sql now is: FROM tblPositionList RIGHT JOIN tblContributors ON tblPositionList.Position_ID = tblContributors.PositionID and we now see 20 records: Donor First Name Diane Scott Jackson Karen William Mary James Joyce Ann Mary Ellen Gaston Willy Luci Claire June Bill Sara James Sam Amy Linda .... but, as I said, you should not be pulling the tblPositionList table into the RecordSource for this form anyway It doesn't even look like you are using any fields from PositionList on your form. The combobox for PositionID can use PositionList as a RowSource withOUT it being in the form RecordSource Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/strive4peace free 100-page book that covers essentials in Access * (: have an awesome day * Scott_Brasted via AccessMonster.com wrote: Good morning Crystal, Did you get this to work in your copy of the db? I followed your instructions to the letter and I still get a blank form except for first record. Here is what I did. First changed all occurances of Name and Position to DonorName and Positn, then: 1. moved idContributorID to subform header 2. renamed idContributorID control to ContributorID 3. changed background to black and text to white 4. selected ContributorID from Control Source dropdown list in property sheet for text box control that is now in header of subform 5. saved subform 6 decided needed fresh start & deleted cbo & replaced w/Campaign Name field temporarily in main form. will revisit soon. 7. saved main form. 8. double checked doubleclick event vb code on field "Donor_Name" for names, etc. & changed idContributorID to ContributorID. Here is current code: Private Sub Donor_Name_DblClick(Cancel As Integer) DoCmd.OpenForm "frmViewDonor", , , "ContributorID=" & Me.ContributorID End Sub 9. saved code 10. restarted Access No joy. Still blank records. Scott Crystal (strive4peace) wrote: Hi Scott, "Wow is the only word that comes to mind." gee thanks! you're welcome "And it,of course is the one thing I really need to understand. " I am referring to the Property sheet for the control. Read the Properties and Methods section in Access Basics. ~~~ go to the design view of frmCamPledgeListSub Select the idContributorID control ... but since it is under [Name], you cannot see it To select it you can drop down the combobox at the top of the Property sheet and choose -- idContributorID (I don't know why you did not name the control the same as the Controlsource -- but at least it is specific) Once you do get the control selected, move it to the header section where you can SEE it in Design View and set the following properties: Visible -- No (Format tab of Property sheet) ForeColor -- White (Font Color icon) BackColor -- Black (Paint Bucket icon) On the DATA tab, you will see a property called -- ControlSource click in it when you do this, a drop-down arrow will appear to the right -- this is the list of fields in your RecordSource. As you can see, what you have it set to is not in the list... these link controls are very important. It is one thing to hide them from users when they view the form, but you need to be able to see them right away when you look at the design -- that is why I had you set it to be white on black. "but that screws up the combo box and the vb for the cbo gives me a run time error that says you cannot assign a value to this object. " if you still get an error, try to explain this better ... "you did yoeman's work" don't quite understand what you mean, but I will take it as a compliment Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/strive4peace free 100-page book that covers essentials in Access * (: have an awesome day * Crystal, [quoted text clipped - 23 lines] Best, Scott |
#22
|
|||
|
|||
Criteria for opening form from a different subform
Evening Crystal,
One last thing hopefully. I took tblPostions out of the query and everything works fine. I created a cbo for the position field and and it has the right data in it, but the cbo is empty when I change records and stays empty. How do I go about getting it to display the related data for the record. The data list is there when I click the down arrow. Do I need code? Best, Scott Crystal (strive4peace) wrote: Hi Scott, I see you got rid of the default values for numeric foreign keys smile In my opinion, it is best not to use Captions in the table design -- the only people who should open tables directly are administrators and they need to see the real fieldnames, not captions Do be sure to fill out the field Descriptions in your table designs -- these are used for StatusBarText when you created forms. you still have forms based on multiple tables -- if you plan to edit data, your should not do this in frmViewDonor, do not pull Position information into the RecordSource -- this can be shown using a combobox. Because qryViewDonor gets all fields from both Contributors and PositionList, you are only going to see records where PositionID is filled out. Here is what you see for your current sql: SELECT tblContributors.* , ([DonorFirstName] & " " & [DonorLastName]) AS DonorName , ([City] & ", " & [StateorProvince] & " " & [PostalCode]) AS CSZ , tblContributors.DonorLastName , tblPositionList.* FROM tblPositionList INNER JOIN tblContributors ON tblPositionList.Position_ID = tblContributors.PositionID ORDER BY tblContributors.DonorLastName; Donor First Name Diane Scott Karen Mary Luci Claire June Amy Linda by changing the Join Type to show all records from Contributors even if a matching record is not found in PositionList, the FROM clause of the sql now is: FROM tblPositionList RIGHT JOIN tblContributors ON tblPositionList.Position_ID = tblContributors.PositionID and we now see 20 records: Donor First Name Diane Scott Jackson Karen William Mary James Joyce Ann Mary Ellen Gaston Willy Luci Claire June Bill Sara James Sam Amy Linda ... but, as I said, you should not be pulling the tblPositionList table into the RecordSource for this form anyway It doesn't even look like you are using any fields from PositionList on your form. The combobox for PositionID can use PositionList as a RowSource withOUT it being in the form RecordSource Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/strive4peace free 100-page book that covers essentials in Access * (: have an awesome day * Good morning Crystal, [quoted text clipped - 97 lines] Best, Scott -- Message posted via http://www.accessmonster.com |
#23
|
|||
|
|||
Criteria for opening form from a different subform
Hi Scott,
"everything works fine" great! "Do I need code?" I don't think so. "but the cbo is empty when I change records and stays empty." .... it sounds like perhaps you don't have ControlSource specified... Please tell me the following properties for your combobox: ControlSource RowSource ColumnCount ColumnWidths Listwidth Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/strive4peace free 100-page book that covers essentials in Access * (: have an awesome day * Scott_Brasted via AccessMonster.com wrote: Evening Crystal, One last thing hopefully. I took tblPostions out of the query and everything works fine. I created a cbo for the position field and and it has the right data in it, but the cbo is empty when I change records and stays empty. How do I go about getting it to display the related data for the record. The data list is there when I click the down arrow. Best, Scott |
#24
|
|||
|
|||
Criteria for opening form from a different subform
Never mind, I figured it out. Thank you VERY much for teaching all this. It
has been a real eye opener. I am now reading your site. Learn , learn , learn. All my best, Scott p.s. Took ALL captions out! Working on multi-table forms now. Lots of cbos Crystal (strive4peace) wrote: Hi Scott, I see you got rid of the default values for numeric foreign keys smile In my opinion, it is best not to use Captions in the table design -- the only people who should open tables directly are administrators and they need to see the real fieldnames, not captions Do be sure to fill out the field Descriptions in your table designs -- these are used for StatusBarText when you created forms. you still have forms based on multiple tables -- if you plan to edit data, your should not do this in frmViewDonor, do not pull Position information into the RecordSource -- this can be shown using a combobox. Because qryViewDonor gets all fields from both Contributors and PositionList, you are only going to see records where PositionID is filled out. Here is what you see for your current sql: SELECT tblContributors.* , ([DonorFirstName] & " " & [DonorLastName]) AS DonorName , ([City] & ", " & [StateorProvince] & " " & [PostalCode]) AS CSZ , tblContributors.DonorLastName , tblPositionList.* FROM tblPositionList INNER JOIN tblContributors ON tblPositionList.Position_ID = tblContributors.PositionID ORDER BY tblContributors.DonorLastName; Donor First Name Diane Scott Karen Mary Luci Claire June Amy Linda by changing the Join Type to show all records from Contributors even if a matching record is not found in PositionList, the FROM clause of the sql now is: FROM tblPositionList RIGHT JOIN tblContributors ON tblPositionList.Position_ID = tblContributors.PositionID and we now see 20 records: Donor First Name Diane Scott Jackson Karen William Mary James Joyce Ann Mary Ellen Gaston Willy Luci Claire June Bill Sara James Sam Amy Linda ... but, as I said, you should not be pulling the tblPositionList table into the RecordSource for this form anyway It doesn't even look like you are using any fields from PositionList on your form. The combobox for PositionID can use PositionList as a RowSource withOUT it being in the form RecordSource Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/strive4peace free 100-page book that covers essentials in Access * (: have an awesome day * Good morning Crystal, [quoted text clipped - 97 lines] Best, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 |
#25
|
|||
|
|||
Criteria for opening form from a different subform
you're welcome, Scott happy to help
"I am now reading your site. Learn , learn , learn ... Took ALL captions out! Working on multi-table forms now. Lots of cbos" great and great! I am currently adding a Whistles and Bells section to my site... so keep checking back till items listed are done. The index lists stuff I plan to add pages for. Whistles and Bells http://www.accessmvp.com/Strive4Peace/Whistle I have an example finished with code called Sort123, which enables the user to easily sort by any column in a Continuous form by clicking on the column header. Now I am working on a page called BoldMe to demonstrate how you can boldface a label if the checkbox is true, or boldface the label of the selected option in a frame. Once the items listed are done, I will probably add more ... or maybe I'll finish another video -- who knows. Depends which way the wind blows Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/strive4peace free 100-page book that covers essentials in Access * (: have an awesome day * Scott_Brasted via AccessMonster.com wrote: Never mind, I figured it out. Thank you VERY much for teaching all this. It has been a real eye opener. I am now reading your site. Learn , learn , learn. All my best, Scott p.s. Took ALL captions out! Working on multi-table forms now. Lots of cbos |
Thread Tools | |
Display Modes | |
|
|