How to get 2 codes to work together.

[IAMHINPS]

Ok, this is my first post on this forum. I have searched high and low but I am unable to get these 2 codes to play nice together. I have 2 access DBs. one VBA code on one that works really nice, and 1 VBA code on the other that works really nice. So on the first one, here is the VBA code

Private Sub Report_Load()
    'filter the records only on the first day
    Dim dtBeg As Variant, dtEnd As Variant
    dtEnd = DateValue(Now)
    If Day(dtEnd) = 1 Then
        dtBeg = DateSerial(Year(dtEnd), Month(dtEnd) - 1, 1)
        dtEnd = DateSerial(Year(dtBeg), Month(dtBeg) + 1, 0)
    Else
        dtBeg = DateSerial(Year(dtEnd), Month(dtEnd), 1)
    End If
        
    Me.Filter = "[date] Between " & Format$(dtBeg, "\#mm\/dd\/yyyy\#") & " And " & Format$(dtEnd, "\#mm\/dd\/yyyy\#")
    Me.FilterOn = True
End Sub

What this code does is allows me to view all the records on a report from the previous month. I only need it to be available on the 1st of every month, no other days during the month. So this works really well for that.

Then on my other DB I have this code that puts blank boxes on the report. Basically making the report look like the paper copy of the document. Well I was going to upload a basic copy of the DB with just the pertinent code, but I will just attach the code here.

Okay, I have a dummy table with just 1 field for ID. Then I have my data table with all of my fields on it. In this case it is 7. Nothing special about the tables at this point. Then I have a a form for data input, but it is irrelevant for what I need here. Then I have a report with this code in it

Option Compare Database
Option Explicit

Const MAX_LINES = 31
Private Sub Report_Open(Cancel As Integer)

    Dim iFill As Integer
    Dim total_records As Long
    Dim sql As String
    
    With CurrentDb.OpenRecordset(Me.RecordSource, dbOpenSnapshot)
        If Not (.BOF And .EOF) Then
            .MoveLast
            .MoveFirst
        End If
        total_records = .RecordCount
    End With
    If total_records < MAX_LINES Then
        iFill = MAX_LINES - total_records
        
    ElseIf total_records > MAX_LINES Then
        iFill = total_records Mod MAX_LINES
        
    End If
    sql = Trim$(Replace$(Me.RecordSource, ";", ""))
    If InStr(1, sql, "SELECT") <> 1 Then
        sql = "SELECT * FROM " & sql
    End If
    If iFill <> 0 Then
        sql = sql & " UNION " & fnCreateDummyLineQuery(iFill, "OrderNumber", "StopNumber", "CustomerName", "PONumber", "ItemNumber", "ModelNumber")
        Me.RecordSource = sql
    End If
End Sub

Finally I have a module with this code in it

Option Compare Database
Option Explicit

Public Function fnCreateDummyLineQuery( _
            ByVal iNumLines As Integer, _
            ParamArray extraFields() As Variant) As String
    Dim sql As String
    Dim i As Integer
    sql = "SELECT TOP " & iNumLines & " ID"
    For i = 0 To UBound(extraFields)
        sql = sql & ", Null As [" & extraFields(i) & "] "
    Next
    sql = sql & "FROM dummy;"
    fnCreateDummyLineQuery = sql
End Function

I have been going around and around for several days trying any and every solution I can find to make this work but no matter what I try to solve this issue, it all seems to simply fall apart. I hope someone here has an idea or a solution that will work.

 

[hermanlaksko]

Not sure what is in your "Me.Recordsource" but my guess is that it is a normal select statement.
And the sql = Trim(Replace(Me.RecordSource, ";", "")) removed the end ";" (No $ sign needed) super that is the way, but what is this doing:
If InStr(1, sql, "SELECT") <> 1 Then
sql = "SELECT * FROM " & sql
End If
As i see it it will give you a select statement before your first select statement.
Perhaps I am overlooking something, if so sry

Last I do not see any connection btw'n the 3 code snippets.

 

[dhookom]

I never filter a report after opening. I almost always use the where condition in the DoCmd.OpenReport method. This would remove the first part of your code.

 

[IAMHINPS]

I never filter a report after opening. I almost always use the where condition in the DoCmd.OpenReport method. This would remove the first part of your code.

I have never filtered something on the docmd part. I have always used a filter after the report opens, can you elaborate or give me an example of what you are talking about?

 

[IAMHINPS]

Not sure what is in your "Me.Recordsource" but my guess is that it is a normal select statement.
And the sql = Trim(Replace(Me.RecordSource, ";", "")) removed the end ";" (No $ sign needed) super that is the way, but what is this doing:
If InStr(1, sql, "SELECT") <> 1 Then
sql = "SELECT * FROM " & sql
End If
As i see it it will give you a select statement before your first select statement.
Perhaps I am overlooking something, if so sry

Last I do not see any connection btw'n the 3 code snippets.

That is the problem, I have been unable to establish a good connection to allow both cods to work on the same report(s)

 

[dhookom]

You got the same opinion in MS Answers about your unorthodox use of the filter in the on load of the report. Consider checking it out since between Scott and myself, there is over 50 years of developing in MS Access.