Sync Subforms

[HotelierDream]

Hello all,



I am making a DB for a hotel and I have everything pretty much done but I am having an issue, I created a navigation form with subforms in order to make it easier to use. It also has one report in the navigation form. The problem is that if I try to move to a new date (make a new record) on one form it does not change the record on the others. All the subforms are from one table as it was easier for reporting purposes. How can I make these items sync? See the ZIP with the BE and FE of the database.



Night Audit Manager.zip

[davegri]

Changed form_open event to avoid duplicating current date.
Added code to navigation button click events to add criteria to the recordsource of the forms.
You'll need additional code for the additional buttons. Not all recordsources are same objects, so be aware of that.
After changing the input date at the top, clicking the button will automatically requery the form/report.

Option Compare Database
Option Explicit


Private Sub Form_Open(Cancel As Integer)
    DoCmd.Maximize
    If DCount("*", "[Daily Input Report]", "[Input Date]=" & Date) = 0 Then
        DoCmd.GoToRecord , , acNewRec
    End If
End Sub


Private Sub NavigationButton21_Click()
    If IsNull(Me.[Input Date]) Then
    Else
        Me.NavigationSubform.Form.RecordSource = "Select * from [Daily Input Report] Where [Input Date] = #" & Me.[Input Date] & "#"
    End If
End Sub


Private Sub NavigationButton25_Click()
    If IsNull(Me.[Input Date]) Then
    Else
        Me.NavigationSubform.Form.RecordSource = "Select * from [Daily Input Report] Where [Input Date] = #" & Me.[Input Date] & "#"
    End If
End Sub

Another way of handling the recordsources without using VBA is like this:

[HotelierDream]

Changed form_open event to avoid duplicating current date.
Added code to navigation button click events to add criteria to the recordsource of the forms.
You'll need additional code for the additional buttons. Not all recordsources are same objects, so be aware of that.
After changing the input date at the top, clicking the button will automatically requery the form/report.

Option Compare Database
Option Explicit


Private Sub Form_Open(Cancel As Integer)
    DoCmd.Maximize
    If DCount("*", "[Daily Input Report]", "[Input Date]=" & Date) = 0 Then
        DoCmd.GoToRecord , , acNewRec
    End If
End Sub


Private Sub NavigationButton21_Click()
    If IsNull(Me.[Input Date]) Then
    Else
        Me.NavigationSubform.Form.RecordSource = "Select * from [Daily Input Report] Where [Input Date] = #" & Me.[Input Date] & "#"
    End If
End Sub


Private Sub NavigationButton25_Click()
    If IsNull(Me.[Input Date]) Then
    Else
        Me.NavigationSubform.Form.RecordSource = "Select * from [Daily Input Report] Where [Input Date] = #" & Me.[Input Date] & "#"
    End If
End Sub

Another way of handling the recordsources without using VBA is like this:

First of all, YOU ARE AMAZING!!!! This was just what I needed for the forms!

Now I have a final issue, how do I get the Report to update when I click on it? Everything else updates except for the Report (M3 Input) that should show the report generated for the date on the main form.

[HotelierDream]

Also, How would I go about refreshing the Total Revenue / Rooms form when I change the date on the main form?

[davegri]

Night Audit Input Report-davegri-v01.zip

Have a look at this. It should work OK with your current BE.

[HotelierDream]

Thank you so much, you have got to be the nicest person! Also, I looked at the code and it makes a bit more sense now!

[HotelierDream]

I spoke a tad bit too soon, so it does update the date on the forms but it is displaying information from previous entries. It is like its just changing one record instead of making a new one except it when I look at the Night Audit input table it does in fact make a new record. Sorry to bother you!