 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / February 2008SubForm Problem
My SubForm has a Default View of DataSheet. I am entering a Directory Name, and a Department Name. The Department Name has a criteria as follows: SELECT zDepartments.DeptID, zDepartments.DeptName, zDepartments.DirectoryID FROM zDepartments WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneDirectory]! [PDSubForm]![cboDirectory])) ORDER BY zDepartments.DeptName; and I also perform the following: Private Sub cboDirectory_AfterUpdate() Me!cboDeptName.Requery End Sub This works fine until I enter a Directory in a different Row of the DataSheet, then the Department Name Displays as blank for all previous Rows. The correct data is correctly stored in the table, it just doesn't display on the form. Can someone explain how to fix this? This happens because Access only maintains a single set of properties for the form even though you are viewing multiple instances in the DataSheet or Continuous Form views. You cannot solve this problem in DataSheet view. You will need to switch the form to Continuous view. 1. Change the subform' default view from datasheet to continuous 2. Replace the subform's RecordSource with a query that joins to the lookup table. You need to select the text value from the lookup table as well as the numeric value from the main table. 3. Add a textbox control to the form and align it exactly over the combo. 4. On the format menu, send the new control to the back. 5. Change the combo's BackStyle to Transparent. 6. In the GotFocus event of the text box, move the focus to the combo. You don't want the text box to ever get the focus. In summary what we are doing is placing a control on the form that always shows the text value that was selected from the combo. We are putting that behind the combo control so that when the combo's RowSource is changed, there is still a value to show even though the desired value is no longer in the current RowSource. > My SubForm has a Default View of DataSheet. > I am entering a Directory Name, and a Department Name. [quoted text clipped - 19 lines] > > Can someone explain how to fix this? > This happens because Access only maintains a single set of properties for > the form even though you are viewing multiple instances in the DataSheet or [quoted text clipped - 39 lines] > > > Can someone explain how to fix this? Pat, thanks for the fine explanation and instructions. I don't have time to fully understand why a DataSheet view will not work in this case, but I do think I understand your solution for making a Continuous Form work. I'll give it a try later, and then respond again with my results. Again, thanks for your help. Bernie The solution doesn't work for DataSheet view because you don't control how the form looks, Access does and it won't overlay one control with another which is the heart of this solution. >> This happens because Access only maintains a single set of properties for >> the form even though you are viewing multiple instances in the DataSheet [quoted text clipped - 55 lines] > Again, thanks for your help. > Bernie > The solution doesn't work for DataSheet view because you don't control how > the form looks, Access does and it won't overlay one control with another [quoted text clipped - 63 lines] > > Again, thanks for your help. > > Bernie Okay, I have modified everything as you suggested, and everything seems to be configured as you instructed. However, EXACTLY the same thing happens with the Continuous Form as was happening with the DataSheet Form. As soon as I select a "Directory" value in a new row, the Department value goes blank (not visible). By the way, an additional thing I did was to make the DeptName Text Field tab stop = No. I don't know what else to try at this point. I'm sure this is not a problem with the form, but something else regarding the Directory and Department Tables. So in case that is the problem, I'm showing you the table design of each. zDepartments: Table DeptID.........AutoNumber DeptName...Text DirectoryID...Number...Row Source for Lookup: SELECT zDirectories.DirectoryID, zDirectories.Directory FROM zDirectories ORDER BY zDirectories.Directory; zDirectories: Table DirectoryID...AutoNumber Directory.....Text I hope this helps you, or someone else to find the problem. Thanks again... Bernie Try changing the z order of the two controls. I may have that backwards. I don't have a database where I used this method handy. To prove to yourself that it will work, drag the new control down to a separate line. The new control should show the valid value regardless of the state of the combo's RowSource. >> The solution doesn't work for DataSheet view because you don't control >> how [quoted text clipped - 101 lines] > Thanks again... > Bernie > Try changing the z order of the two controls. I may have that backwards. I > don't have a database where I used this method handy. To prove to yourself [quoted text clipped - 111 lines] > > Thanks again... > > Bernie I found the problem! Hooray! It was just a simple matter of setting the BackStyle to Transparent. I promise, I did this several times but apparently did not save the form properly. In any case, it now works. I appreciate your help Pat. Thanks for staying with me on this. Bernie Glad to help. I'm hoping that one day, Access will handle this natively:) >> Try changing the z order of the two controls. I may have that backwards. >> I [quoted text clipped - 128 lines] > I appreciate your help Pat. Thanks for staying with me on this. > Bernie Hello Pat and Others. I am having the exact same problem, but I feel I am a little denser then iamnu. Could you please break down the solution a little further? I get all of the points about the form controls. I just do not understand where the text box will get the data from. Any aditional help would be very appreciated! Joe > > Try changing the z order of the two controls. I may have that backwards. I > > don't have a database where I used this method handy. To prove to yourself [quoted text clipped - 119 lines] > I appreciate your help Pat. Thanks for staying with me on this. > Bernie |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.