I'm designing a timesheet database and I have a main form (frmTimeMain) with several subforms. There is a subform for each day of the week so each subform is linked to the same table and is writing to the same fields in that table. I have 3 values in the parent form that need to be passed to each subform. The way I have it setup now works - kind of. I can pass the data and save each record, but because the values are being passed as soon as the subforms load, blank records are being created. Is there a way to stop a record from being created until the subform has focus and also not pass the needed values from the parent form until at least one of three specific fields has data entered into them?
Subform - several questions (1 Viewer)
- Thread starter JasperQ
- Start date
- Local time
- Today, 02:49
- Joined
- Jul 9, 2003
- Messages
- 16,271
First of all set the RecordSource of your subforms to an empty string "" this is a good practice in most situations because it allows your forms to load faster.
As an aside:-
It's possible that you have 7 subforms, one for each day of the week. I would avoid this unnecessary duplication and complication! I would use a single form to represent "a day" ("sfrmDay") this form would be used 7 times in each of the "days" subform subreport controls.
Now on your main form I assume you have some way of selecting the person that the timesheet relates to, possibly with a combobox. Add code to the after update event of the combobox to update the RecordSources of the various subforms
You need code that looks something like the following:-
Me.subFrmWinForYourForm.Form.RecordSource = "SQL Statement"
Where "SQL Statement" is a string representing the set of data you want to display in each particular subform
Where "subFrmWinForYourForm" is the name of the subform/subreport control which houses your subform.
".Form." refers to the Form contained in the aforementioned Control.
".RecordSource =" refers to the Record Source of the aforementioned Form.
Regarding adding data from the main form to each subform. You are falling Into the trap of thinking about the problem from the point of view of forms and subforms.
Imagine the following scenario:- You are creating a Christmas list.
Some tasks you perform on your data you do manually, like checking off customers who will receive a bottle of plonk for Christmas. Over the years, as the number of your customers increases it can become a chore. You realise an update query based on some criteria, like total order value for the year would be a better option, so you write a suitable query. What have you done? You replace manual actions on the form with a query which operates directly on the underlying data.
So think about your problem from this point of view, how can you add the data from the main form directly into the table?
I recently created a playlist on my YouTube channel called "Subform" - Basically - I went through all my videos and just tagged ones with "subform" if they contained any mention of subforms at all! Hopefully there will be a bit more information about setting this up on subforms if you need it..
As an aside:-
It's possible that you have 7 subforms, one for each day of the week. I would avoid this unnecessary duplication and complication! I would use a single form to represent "a day" ("sfrmDay") this form would be used 7 times in each of the "days" subform subreport controls.
Now on your main form I assume you have some way of selecting the person that the timesheet relates to, possibly with a combobox. Add code to the after update event of the combobox to update the RecordSources of the various subforms
You need code that looks something like the following:-
Me.subFrmWinForYourForm.Form.RecordSource = "SQL Statement"
Where "SQL Statement" is a string representing the set of data you want to display in each particular subform
Where "subFrmWinForYourForm" is the name of the subform/subreport control which houses your subform.
".Form." refers to the Form contained in the aforementioned Control.
".RecordSource =" refers to the Record Source of the aforementioned Form.
Regarding adding data from the main form to each subform. You are falling Into the trap of thinking about the problem from the point of view of forms and subforms.
Imagine the following scenario:- You are creating a Christmas list.
Some tasks you perform on your data you do manually, like checking off customers who will receive a bottle of plonk for Christmas. Over the years, as the number of your customers increases it can become a chore. You realise an update query based on some criteria, like total order value for the year would be a better option, so you write a suitable query. What have you done? You replace manual actions on the form with a query which operates directly on the underlying data.
So think about your problem from this point of view, how can you add the data from the main form directly into the table?
I recently created a playlist on my YouTube channel called "Subform" - Basically - I went through all my videos and just tagged ones with "subform" if they contained any mention of subforms at all! Hopefully there will be a bit more information about setting this up on subforms if you need it..
Last edited:
Thanks Uncle Gizmo - I've watched several of your videos and they've been very helpful. I tried setting the RecordSource of the subforms to an empty string (after watching one of your videos explaining how), and it worked, but then other code I already had in place started causing errors. I played with it...and played with it...and eventually ended up with a big mess. So I decided to go back to my original design and work through each of the issues again as they arose, with the the goal of eventually being able to set the RecordSource to an empty string and have it work.
As for the multiple subforms, I see what you're saying and it's obviously a much cleaner approach that what I currently have. I've never done it that way, but I'll certainly play around with it.
As for the multiple subforms, I see what you're saying and it's obviously a much cleaner approach that what I currently have. I've never done it that way, but I'll certainly play around with it.
- Local time
- Today, 02:49
- Joined
- Jul 9, 2003
- Messages
- 16,271
Hi JasperQ, good to hear! I'm trying to get to 1000 YouTube subscribers on my YouTube channel. If I do, I get a level up! So it would be really great if you could subscribe, >>>HERE<<< (if you haven't already!) it would be very helpful.
__________________
Last edited:
- Local time
- Today, 02:49
- Joined
- Jul 9, 2003
- Messages
- 16,271
My apologies I got that wrong! In Design view, set the source object to nothing; nada; --- don't put any anything in there at all! Leave it blank. If you put double quotes in there you'll get an error message. I just tried it on a database I am setting up for a new set of videos on subforms. I was thinking in terms of VBA programming, if you were to set it to nothing with VBA then you would send it an empty string. Hence my mistake. Again, my apologies and sorry for causing you some error messages!
