Populate a subform date control based on main form date range controls

[noclueforid]

Hello!

On my main form I have two date controls, dtmTrainingStartDate and dtmTrainingEndDate. On a sub form, I have a date control dtmWeekEndingDate. What I would like to do is auto-populate the sub form control with all the Saturday dates between the main form date range controls.

If this is possible, how would you go about doing it?

Thanks!!!
Dave

[TheSmileyCoder]

Should there be 1 record for each date?

Or do you want a control like a combobox, where the combobox lists the available dates?

Is the subform meant for selection or for data entry?

[noclueforid]

One record for each date. The sub form is going to be for making payments every week. This way, auto-populating should prevent "missing" dates if the user has to manually type in all the dates.

[noclueforid]

Any suggestions on how to do this or is it not possible?

[TheSmileyCoder]

You could use VBA to loop through all dates in the range, and if they are a saturday add them to a table, with the correct foreign key.

You need to decide what the appropriate trigger should be (I.e. when should the code run) and what should happen if the start/end date gets changed after you have run the append code.

[NeoPa]

Dave, you need to appreciate that you don't enter data into forms (even though you may enter some via forms). Forms don't hold data. They are merely windows into the data.

With that understood, it seems you are really asking about populating the table the subform is showing you. This can be done - even within the code of a form or subform, but I suggest you stop a second and think very carefully about exactly what you want doing here before getting into deciding how to go about it. I don't hear the clarity as yet, but when we have that we can determine a way forward.

[noclueforid]

Okay, let me try to explain this a little better. I have two tables, tblStudents and tblPayments. The student table contains all the students personal and contract info. The payments table contains all the payment information. The student pays once a week, with the payments due by Saturday. What I am trying to do is insert every Saturday date into that table for each student. With the Saturday dates calculated and inserted, there is less chance staff will miss a payment or enter the wrong date. Also, staff can check a "Break" box to indicate the weeks students will not be making a payment.

Hope this helps!

[TheSmileyCoder]

Your attachment is 2007, so I can not open it.

I asked you about what the trigger should be, but you didn't answer. Lets just assume you use a button after filling out the 2 date controls.

Private Sub btn_DoDates_Click()
  'Rudimentary data check
    If Not IsDate(Me.dtm_StartDate) Or Not IsDate(Me.dtm_endDate) Then
        MsgBox "Valid dates must be entered"
        Exit Sub
    End If
    If Me.dtm_endDate < Me.dtm_StartDate Then
        MsgBox "Enddate must be later then start date"
        Exit Sub
    End If
 
    'Lets assume that your form has the student ID, of type long (autonumber) on the form, in textbox tb_StudentID
        Dim lngStudentID As Long
        lngStudentID = Me.tb_StudentID
 
    'Open a empty recordset for manipulation
        Dim rsPayments As DAO.Recordset
        Set rsPayments = CurrentDb.OpenRecordset("SELECT * FROM tbl_Payments WHERE ID_Student=-1", dbOpenDynaset)
 
    Dim dtRunningDate As Date
    dtRunningDate = Me.dtm_StartDate
    Do While dtRunningDate <= dtmEndDate
        If Weekday(dtRunningDate, vbMonday) = vbSaturday Then
            rsPayments.AddNew
                rsPayments!ID_Student = lngStudentID
                rsPayments!dt_Payment = dtRunningDate
            rsPayments.Update
        End If
        dtRunningDate = dtRunningDate + 1
    Loop
    'Cleanup
      Set rsPayments = Nothing
 
    'Requery subform
      Me.[subformControlName].Requery
End Sub

[noclueforid]

I'm sorry, yes, a button would initiate the code after the two dates were entered.

[NeoPa]

You're still not dealing with the points I raised Dave. If you are going to look at coding this up you will certainly need all the details as to which records need to be created; Will you decide to work on the basis of creating all possible records in one go or will you just add the first relevant record into the form as a template and let the user fill it in? What are the determining factors for which range of dates are to be added (Control Names are important here)?

This is why I suggested you think more clearly about your question. Not because I don't understand what you're after well enough, but because I don't get the impression you do (Obviously I don't have enough information either - but that's not the important point).

[TheSmileyCoder]

Did you look over the code I posted for you?

[noclueforid]

Yes, I have looked at it. I'm sorry for not getting back quickly, other projects took precedent for a few days.

I am probably what you call a casual user of Access, so its taking a little bit of time to understand the code, but I am working through it! I thank you for posting it!

What I don't understand is what NeoPa is asking me though. I thought i was pretty clear in what I was asking, and I fully understand what I need to do, so its a bit confusing there.

Again, thank you for your reply!

[NeoPa]

I'm sure you're fairly happy with the help Smiley is already providing, but as you had the sense to respond indicating your confusion as to my earlier posts, I feel you deserve the courtesy of clarification.

In paragraph #1 of my post #10 there are two questions that I felt needed answering. Now I've reread all your posts again carefully, I can see that there is indication in your post #7 of what your answer would likely be to the first question and in post #1 for the second. Sometimes it hard to work on a question though, when the relevant parts of the question are dotted around in different posts. This is understandable. It's often a building process after all, but it makes answering questions (or requests for information) clearly and precisely all the more important when they come up. In this case, I felt that your original terminology based around your form indicated there was at least a possibility that you might be after a form-based approach rather than a code based one (even after post #7). Unlikely, but an answer would have clarified the situation and helped me to realise that it was an issue you understood.

Sometimes a lack of response can tell us as much as a response can. Sometimes it's hard to know exactly what a member wants (after all we deal here with so many different types from professional to mildly amateur to unwilling participants) so it's very easy to give an answer that doesn't fit the question - even though it might seem to for any casual observer.

If you're keen on a form-based solution after all then the response would revolve around controls and their .DefaultValue properties. If, on the other hand, the batch idea is your preferred approach then I would submit a SQL based suggestion. Rightly or wrongly, from the little I saw in direct response to my posts, I was still unsure of your thinking.

I should add that I understand only too well how complicated it can be responding to multiple experts in the same thread. It's not a bad thing by any means, but some members can find it more complicated to maintain the flow fully.

Anyway, enough rabbiting on my part. I'm here if you want me, but I'm also happy if you decide that working with Smiley is an altogether easier proposition. Best wishes either way.

[noclueforid]

Thanks for your post, NeoPa. I do fully understand how hard it is trying to decipher the questions asked sometimes.

Since I am fairly new to Access (only used for about 2 years off and on), it takes me a bit to go through the code and completely understand what it is doing. I'm not one to just copy/paste code in, I like to understand what it is doing and why its there.

Once I get through it, and if I have more questions, I will reply back.

Thanks so much for all the help so far!

[noclueforid]

Sorry about the delay on this, but the code works perfectly!

Thanks Smiley!!!!

[NeoPa]

Glad to hear it all resolved in the end :-)