Unanswered: Need subform->datasheet to show all records (zip attached)
I cannot seem to find anyone to understand what I am trying to do (which means it might not be a good idea!), so I am attaching my problem in a zip.
I need two subform->datasheets on a form that are one-many (Subjects to Books). The left subform needs to show ALL Subjects and the right subform needs to show only related Books, if any. My relation works okay, but I simply cannot figure out how to show ALL Subjects, instead of one at a time.
I have made this work with lists, but I need to be able to go to the last row and enter a new record on each subform, which a datasheet makes very intuitive. Also, these will each hold several thousands of records, which seems more feasible on a datasheet than a list.
Is this along the lines of what you are trying to do?
It's not fully functional but does demonstrate synchronizing the two subforms. Note that I don't set a recordsource to the parent form, frmMain since that would create a direct relationship to the first subform, subfrmSubjects and only show subject at a time.
Thanks much. That looks great and is exactly what I was looking for. Now, can you give me a couple of clues as to how you did this? I noticed code with OnOpen and OnLoad, but I am a little unsure as to what they accomplish.
I knew that the current event of any form fires whenever that record takes the focus. For example, when I click on the row labeled music, the current event for the music subject row fires off.
So whatever code you put in the current event procedure will fire each time you move from one subject to another.
In the current event of the subject subform, I added a piece of code that sets the recordsource of the resource subform to whatever the subject id is from the subject subform:
In subfrmSubjects I put:
Private Sub Form_Current()
In Module1 I put:
Public Sub SetSubFormRecordSource()
On Error GoTo ErrorHandler
Dim lSubjectId As Long
lSubjectId = Nz(Forms!frmMain.child0.Form.SubjectID, 0)
If lSubjectId > 0 Then
Forms!frmMain.child1.Form.RecordSource = "SELECT tblResources.* FROM tblResources WHERE tblResources.SubjectID=" + CStr(lSubjectId)
If Err.Number = 2455 Then
I threw in a very sloppy error handler just to catch the error 2455, which occurs if I try setting a recordsource before all the forms (frmMain, and the two subforms) have completely opened.
The error code probably should have been:
If Err.Number <> 2455 Then
But I wasn't done yet. When I opened frmMain the first time, I noticed that the first subject - the music row, did not set the recordsource of resources (probably because of the above mentioned error).
Events fire in a special order so it is important that you examine the help section in Access on event handling since some events work better than others, and the order in which they fire may mean the difference between your code working or failing.
Now, I wanted to ensure that when frmMain opened for the first time (before a user would change rows, thus setting the proper subject id) that I forced the resource subform to the correct subject.
That is why I added an additional call to SetSubFormRecordSource in frmMain's load event:
Private Sub Form_Load()
Note there was a duplicate call to SetSubFormRecordSource in the form open event (due to my earlier testing offline). You could probably remove this duplicate call.
Okay, well that's how we got the two subforms sync'd up. Now the bad news...
You are not done coding this solution yet...
For example, what happens if you add a new row to the subject subform? What happens if you delete a subject?
You don't have the benefit of the built-in Access parent-child relationship to auto-enter your subject id into the resource subform.
So you may need to add code to the subject form's insert and form delete events to ensure the resource subform's rows are still pointing to the correct subject.
The current resources that you had previously entered in your example already had the subject id in them. However, with this new subform arrangement, if you add a resource, you will have to manually fill in the subject id for that resource.
Otherwise, once you left that subject and scrolled to a new subject the newly added resource entries (missing their subject ids) would disappear forever from view (become widowed records).
To solve this dilemna you could do 2 things.
In the current event, you could automatically set the default value property of the subject id in the resource subform to the subject id of the subject subform.
That way, as soon as you add a new resource (let's say for the music subject) the subject id for music would automatically be entered in the subject id field behind the scenes in the resource subform.
Or, you could write a piece of code to make the value of the subject id field in the resource subform equal to the subject id in the subject subform.
As you can see you will need to do a bit of testing of all possible situations to make this user-foolproof - but it's not impossible.
One last comment: I played around a little bit with creating a nested subform (a subform on a subform) so that I could use Access' built in sychronizing of parent-child forms. I found however, that the appearance was not as nice as your sample. Also, there were limitations (for example, I couldn't use continuous forms for the subject and a datasheet for the resource form).
Personally, I would work out the remainder of your example and then you could re-use this arrangement in the future for your other projects.
I was going to do my research before I asked some other questions, but you answered them anyway! (...concerning adding records on both sides)
I appreciate folks like you sharing your expertise. 15 minutes of your brain probably saved me 10 hours or more, given how long I have already been trying to solve this!
I used to program extensively in FoxPro where you coded everything, but then you knew how to do everything, too. I know Access is more powerful, but several concepts are very different. I am getting there!
I also started in FoxPro and worked my way into Access. Actually I first started in DBase 3+ way back in the early 80's during the DOS days and then bought FoxPro when it came out for $99 where I learned to work in Windows apps for the first time.
I was intrigued by Access and one day I told myself I was going to spend the next two weekends and just bury my head in an Access database until I figured it all out. Seven years later I'm still figuring it out, LOL.
Two differences between Access and FoxPro which I had to get used to were that Access kept everything in one project file while FoxPro used separate files for the objects, and also that Access treated indexing a little differently than in FoxPro insofar as Rushmore technology was concerned.
But I never regretted learning Access. A lot of people don't realize how huge a product it is for Microsoft.
Not only that but you can do almost anything database-wise in Access that you can in the bigger enterprise databases at a fraction of the cost.
Finally, if you do upsize to a larger solution, Access still makes a great front end for custom reporting.
I have a client, for example, that just went to SQL Server but they were not able to justify dumping the Access database they had grown so fond of because it was so easy to customize it.
Anyhow Wayne, glad to help you. Let me know if you have any other questions.