Full Version: Problem Clearing A Nested Subform
UtterAccess Forums > Microsoftģ Access > Access Forms
Hello, Iím new to this forum and was hoping I could get some advice.
It work, I inherited a database that was corrupt, and have been working for the last month recreating what I could from the original (originally created in access 2000, modified in 2003, Iím now using 2007) The users of this will be a mix of both 2003 and 2007, so Iím trying to keep it as basic as possible so it will not become corrupt again.
The problem I am having at t his point is I can not get my nested subform to be clear on load so the user does not see anything but a blank form.
Right now it is showing blank only if the user has never entered information prior, and if they have previous entries, its shows the first entry the ever made...not the most recent, but the first...which Iím not sure why.
Iíve tried DoCmd.GoToRecord , , acNewRec on the form and subform load, but it either wiped out user name that connect the info back to the stable, or it doesnt work at all.
Iíve tried to get it to set Data Entry to yes, but again, it clears everything on the form and subform both, and there is not the option to do that on the subform alone that I can see.
Iíve tried me.controlname = null and it said it didnít recognize these fields on the subform or errors out all together.
Iíve been searching these forums and others for about 4 weeks trying everything they are suggesting and it either doesnít work, or it clears both the parent and the child form, which I can have as it needs the field on the master to connect the records.
Specifically, when you log in it takes you directly to frmUser Select. From there you select the unit and user name, which double clicking on the user names opens the next form, which is frm Action. Nested in that is sfrmAction. The problem is on sfrmAction where it brings in the first record ever recorded on the person, and I cant get that to clear on load. (I donít what code is telling g it to do this)
If I any controls to load fromAction, it wipes out both the user name field on frm Action AND the subform contents, which is not what I need. I need the name field at the top to connect the data back to that user.
This has been a difficult database to fix as it was created by a few different users, and they all had their own naming conventions so there are forms called "forms", and txtfields called "field" I really need to get this back and working for my employer ASAP and I have hit a brick wall with this, and this is the way they want the form set up. Ive been trying to go through and clean those up as well, but was hoping to get this major part fixed before I start messing with those smaller details. I'm wondering if something like that isn't whats blocking this from working....a naming convention that I changed that may be throwing something else of?
Iím attaching a mock copy of what I am working with to see if anyone can please offer some advice on how to get the subform to be blank on load. I got it to clear on enter, and clear on save, but not on load.
Have you tried clearing the subform's recordset then having a button on the master form to populate the subform when needed? The code for the button might look something like this:
e.SubFormName.Form.Recordset = "SELECT fieldname1, fieldname2, fieldname_andsoOn FROM YourTable WHERE fieldname2 = 'somevalue'"
Me.SubFormName.Form.Recordset = YourQueryName
Hope that helps
My fix was to add the line below to the Load event of the form. The user can't add new records to the control source table of the main form, but the code effectively brought up a clear subform until a main form record was seletecte.
oCmd.RunCommand acCmdRecordsGoToNew
Thanks for your suggestions! Im still sort of jsut an intermediate Access user ...nor great at writing code.
id you put that on the Parent form load, or the child subform load somehow? When I tried it on the parent, it cleared everything on my parent and child both...and I dont see a subform load option...only event options are "on enter" or "On exit"
Thanks for your suggestion!
It doesnt look to me like this is even using a recordset...the data for this subform comes directly from a select statement from the table itself, no query either...so Im not sure how I would apply this?
It looks to me that should go back to review your Table Structure first...
able [TblAction] does not have PK defined. Table [TblDesignUnit] refers to Division and Section but I don't see Tables representing these 2 Entities.
Some of the Fields are not correctly implemented, e.g. [FullName] and [Date] (which has a Text data-type besides using a reserved word as the Field name).
Have you read the first few chapters of your Access textbook(s) before trying to implement the Table design???
I didnt create this, I was told to fix it, so thats why Im here...somethings that may be obvious to you, that Im overlooking....those are excellent suggestions, and may be part of whyt his isnt working well. I go back over those (since I was mostly focusing on the form and reports) and clean those up. Thanks!
I didn't look at your file since policy prevents me from opening such files here at work. I made a lot of assumptions that may or may not be true with your individual setup. I use it on the parent form load. In my particular case the parent form didn't have any bound controls so the command had no effect on the parent record source, but did clear the subform. Sounds like you may have bound controls on the main and subform.
Maybe if you explain the workflow and purpose for the form we may be able to give you a better answer.
Basically, its a database for users to input their goals for the year.
The main form (frmUserSelect) Has a combo box to select the unit they work in, which populates a subform (sfrmNames) with all the users in that unit (textbox). When the user double clicks on that, it launches another form (frmAction) where that users name is in another txt box at the top linking it back to the unit/user. The subform on that page has a list box to select the desired goal, which when saved stores that information (along with the current date and their explanation of how they will achieve that goal) plus the user it is associated with into the table tblAction.
Im not the one who created this at all and I have been trying to fix broken code or design errors as Im plugging along. I have spent this morning correcting the table errors that were pointed out to me and cleaning up the reports associated with these changes, and so far it seems to be working fine still, except I still can not get the sfrmAction to be clear on load...but then again, Im not done with all the table/control name changes so maybe when I fix all those relationships and names it will help...but Im not that far yet. if it will help, if I can get this done either today or Monday, I can repost the changes in an updated file to share. If nothing else, at least Ill get this back to "normal stndards" as best as I can for the next person who has to modify this in the future (if it isnt me again). Its been extremely challanging with all the errors and incorrect naming conventions and such.
I do appreciate all the input...as I said, Ive sort of been a "learn as you go" user and its been a couple years since I took my access class, so Ive been trying to go back through my Access bible and ask for help here as best as possible to fix this, as well as try to figure out how/why they set this up like they did, or if there is a better structure that will give me the same results. This has definitely been testing my knowledge, as well as my limits of my knowledge.
OK, I think I have the tables cleaned up...hopefully they are correct this time. It's still having issues clearing that first saved record though, so if anyone can please take a look and guide me on what I can do to correct this, I would very much appreciate it.
What happens if you set the form to Data Entry in the Data tab in the form properties?
If I set it to the main form, it clears the control that the data is linked to as well, so it doesnt know who to save the record on. It wont let me set it to "yes" on just the subform. I change it to yes, save it, and when I go back, its back to "no".
Sometimes it is just easier to make new forms with the same function than try to fix broken forms that you inherited. It kind of sounds like this could all be accomplished with a single form as opposed to the mutiple forms that you described. This would clean it up and make the interaction better.
Ive tried but can not get the same basic layout as this with the textbox displaying the person chosen from the first page and a clear record. Any suggestions on a new layout that does the same thing? Ive spent so much time looking at this, Im having a hard time "thinking outside of the box"...so to speak.
All play around some more with it too and let you know if I come up with a fix.
Jasco13: Thanks for the suggestion. I went ahead and scraped the subform and jsut created a new one. Its not "pretty" like they had with the form layout, but I jsut did it in datasheet view and it works perfectly. Not exaclty what I was hoping to have as the end result, but it is functional!
Maybe I need to start a new thread to ask this question, but is it possible to make the previous entries on the datasheet not editable and just allow them to enter a new entry? Ive attached a screen shot to show you what it looks like now.
Thanks again for everyones input. I wish I could have figured out what was wrong with the previous form, just for knowledge sake, but at least I can move forward now and meet my deadline!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.