Solved Refresh a query thru VBA (1 Viewer)

[mloucel]

Hello Gurus..

After a few minutes I decided to post this here, since the issue will be handled at the end by VBA..

Issue:
I am working in an application, where a specific number of records will be displayed in the form footer of my Main Menu on a Sub-Form:

This SubForm is created Using 2 queries [I have to], then I create the form and add to the menu.

My SubQuery removes certain Data:

And the Main query, then sets everything to 30 days but eventually I will set this to 90 days maximum [this is JUST for the opening]

I need to be able to create a range on the ReferDate so that I can go to any Max date found in the data base, and any other date within the Min range of the database.
The FIRST DISPLAY [or at least I hope it will be] (when the menu is open) only the first 90 days from today's date will be displayed, but I want to be able to manipulate those dates, so the End User can see any range withing those MIN and MAX of the database.
Just like this:

I have tried with the button set to:

Me.Recordset.Requery

but surely doesn't work, I know I am wrong but if someone can help me with any idea on how to accomplish this..

Thanks

 

[theDBguy]

You might consider posting a sample db with test data. It might help get to a solution quicker.

 

[arnelgp]

you may also try this on the Refresh button click"

Private Sub btnRefresh_Click()
Dim sql As String
dim i As Integer
Dim db As DAO.Database
If IsDate(Me.ReferDateFrom) And IsDate(Me.ReferDateTo) Then
    ' change the record source of your form
    Set db = Currentdb
    sql = db.QueryDefs("qryAuthorizationFullView").SQL
    sql= Replace$(sql, ";", "")
    i = Instr(1, sql, "WHERE")
    If i <> 0 Then
        sql = Left$(sql, i - 1)
   End If
   
    sql = sql & " WHERE ReferDate Between #" & Format$(Me.ReferDateFrom, "mm/dd/yyyy") & _
                       "# And #" & Format$(Me.ReferDateTo, "mm/dd/yyyy") & "#"
    me.RecordSource = sql
End If
Set db = Nothing
End Sub

 

[LarryE]

Hello Gurus..

After a few minutes I decided to post this here, since the issue will be handled at the end by VBA..

Issue:
I am working in an application, where a specific number of records will be displayed in the form footer of my Main Menu on a Sub-Form:
View attachment 115955

This SubForm is created Using 2 queries [I have to], then I create the form and add to the menu.

My SubQuery removes certain Data:

View attachment 115956

And the Main query, then sets everything to 30 days but eventually I will set this to 90 days maximum [this is JUST for the opening]
View attachment 115957

I need to be able to create a range on the ReferDate so that I can go to any Max date found in the data base, and any other date within the Min range of the database.
The FIRST DISPLAY [or at least I hope it will be] (when the menu is open) only the first 90 days from today's date will be displayed, but I want to be able to manipulate those dates, so the End User can see any range withing those MIN and MAX of the database.
Just like this:
View attachment 115958

I have tried with the button set to:

Me.Recordset.Requery

but surely doesn't work, I know I am wrong but if someone can help me with any idea on how to accomplish this..

Thanks

Me.Sub-FormName.Requery or Forms![Parent Form Name]![Sub-Form Name].Requery

 

[mloucel]

You might consider posting a sample db with test data. It might help get to a solution quicker.

Thanks, I'm going to try, @arnelgp 's solution and will go from there.

 

[mloucel]

you may also try this on the Refresh button click"

Private Sub btnRefresh_Click()
Dim sql As String
dim i As Integer
Dim db As DAO.Database
If IsDate(Me.ReferDateFrom) And IsDate(Me.ReferDateTo) Then
    ' change the record source of your form
    Set db = Currentdb
    sql = db.QueryDefs("qryAuthorizationFullView").SQL
    sql= Replace$(sql, ";", "")
    i = Instr(1, sql, "WHERE")
    If i <> 0 Then
        sql = Left$(sql, i - 1)
   End If
  
    sql = sql & " WHERE ReferDate Between #" & Format$(Me.ReferDateFrom, "mm/dd/yyyy") & _
                       "# And #" & Format$(Me.ReferDateTo, "mm/dd/yyyy") & "#"
    me.RecordSource = sql
End If
Set db = Nothing
End Sub

Thanks @arnelgp , I am going to try your solution now.

 

[mloucel]

Me.Sub-FormName.Requery or Forms![Parent Form Name]![Sub-Form Name].Requery

Thanks @LarryE I tried already that and it didn't work, I'm going to try the solution from @arnelgp and will go from there.

 

[mloucel]

you may also try this on the Refresh button click"

Private Sub btnRefresh_Click()
Dim sql As String
dim i As Integer
Dim db As DAO.Database
If IsDate(Me.ReferDateFrom) And IsDate(Me.ReferDateTo) Then
    ' change the record source of your form
    Set db = Currentdb
    sql = db.QueryDefs("qryAuthorizationFullView").SQL
    sql= Replace$(sql, ";", "")
    i = Instr(1, sql, "WHERE")
    If i <> 0 Then
        sql = Left$(sql, i - 1)
   End If
  
    sql = sql & " WHERE ReferDate Between #" & Format$(Me.ReferDateFrom, "mm/dd/yyyy") & _
                       "# And #" & Format$(Me.ReferDateTo, "mm/dd/yyyy") & "#"
    me.RecordSource = sql
End If
Set db = Nothing
End Sub

Thanks it worked, I just made the adjustments as suggested and added order by referdate at the end so that it is sorted as well, it works pretty well, exactly as I imagined.

Thank you @arnelgp

I have attached the test Database, just in case anyone wants to see the final result, and might even help them in the future.