SQL Refresh for a Report in VBA

shamas21

Registered User.
Local time
Today, 15:35
Joined
May 27, 2008
Messages
162
Hi All

I desperately need to resolve this issue which came out of the blue.

I have a form where the users click a button that produces SQL. The SQL is passed to the report I have and the data is presented to the user in the report based on the SQL: This works on certain computers and returns the data on the report but it doesnt work on some of the other computers. The database is on a shared network drive and everyone uses the same code on the Form to generate the report. Please help.

Code:
Dim sFinalSQL as String
sFinalSQL = "Select * From tblCustomers" 
DoCmd.OpenReport "rptInvoiceIn", acViewReport
Reports![rptInvoiceIn].RecordSource = sFinalSQL

Thanks
 
The database is on a shared network drive and everyone uses the same code on the Form to generate the report.
If the database is open by multiple people at the same time, what I have found out is that only ONE person at a time can run the report.

This is a common issue when a database is opened by multiple users at the same time (shared).

If you want more that one person at a time to be able to run the report at the same time, then you will need to set up your database properly for multiple users. This means splitting the database and every user must have their own (not shared) copy of the front end.

See:
Split your Access database into data and application
 
Is the actual code more complicated than that? There would be no reason to code the SQL unless it was dynamic based on user selections (and by dynamic I mean different fields selected type of thing; different criteria I would handle with a wherecondition).

In any case, I would do this type of thing from within the report rather than from outside. Depending on version, you can pass the SQL in OpenArgs or use a public variable, and set the recordsource in the report's open event.
 
Is the actual code more complicated than that? There would be no reason to code the SQL unless it was dynamic based on user selections (and by dynamic I mean different fields selected type of thing; different criteria I would handle with a wherecondition).

In any case, I would do this type of thing from within the report rather than from outside. Depending on version, you can pass the SQL in OpenArgs or use a public variable, and set the recordsource in the report's open event.

Hi

Your right that I do use dynamic fields. I have a form where users can use a combobox or text boxes to specify their date... the code is more complicated it is as followows: p.s. the one thing i dont understand is why it works on some computers but not on others...hmmm..

Code:
Private Sub btnGenerate_Click()

Dim sSQL1 As String
Dim sSQL2 As String
Dim sSQL3 As String
Dim sSQL4 As String
Dim sFinalSQL As String
'==============
 
If Me.frmPaymentType = 1 Then
    sSQL1 = "SELECT tblCases.CaseID, StrConv([title] & ' ' & [forename] & ' ' & [surename],3) AS Client, tblPayments.Amount, tblCases.Consultant, tblPayments.Date FROM tblCustomers INNER JOIN (tblCases INNER JOIN tblPayments ON tblCases.CaseID = tblPayments.CaseID) ON tblCustomers.CustomerID = tblCases.CustomerID"
ElseIf Me.frmPaymentType = 2 Then
    
    sSQL1 = "SELECT tblCases.CaseID, StrConv([title] & ' ' & [forename] & ' ' & [surename],3) AS Client, tblInvoicedCharges.Invoice AS [Invoice No], tblInvoicedCharges.Net, tblInvoicedCharges.Date, tblCases.Consultant FROM tblCustomers INNER JOIN (tblInvoicedCharges INNER JOIN tblCases ON tblInvoicedCharges.CaseID = tblCases.CaseID) ON tblCustomers.CustomerID = tblCases.CustomerID"
End If
        If Me.cbxDateType = "Quick Date Range" Then
        
                    Select Case Me.cbxDateTypeSelection
                    
                        Case "Today"
                            
                                sSQL2 = " WHERE Left([Date],10)=Left(Now(),10)"
                        
                        Case "Yesterday"
                        
                                sSQL2 = " WHERE Left([Date],10)=Left(Now()-1,10)"
                        
                        Case "Last 7 Days"
                        
                                sSQL2 = " WHERE (((CDate(Left([Date],10)))>CDate(Left(Now()-7,10))))"
                        
                        Case "This Month"
                        
                                sSQL2 = " WHERE (((CDate(Left([Date],10)))>=CDate('01/' & Month(Now()) & '/2009') And (CDate(Left([Date],10)))<CDate('01/' & Month(Now())+1 & '/2009')))"
                        
                        Case "Last Month"
                        
                                sSQL2 = " WHERE (((CDate(Left([Date],10)))>=CDate('01/' & Month(Now())-1 & '/2009') And (CDate(Left([Date],10)))<CDate('01/' & Month(Now()) & '/2009')))"
                        
                        Case "All Time"
                        
                                sSQL2 = " Where"
                        
                    End Select
                    
        ElseIf Me.cbxDateType = "Exact Date Range" Then
        
                If (Me.tbxFirstDate = "" Or IsNull(Me.tbxFirstDate)) Or (Me.tbxSecondDate = "" Or IsNull(Me.tbxSecondDate)) Then
                
                    MsgBox "Please supply both the From Date and the To Date."
                        Exit Sub
    
                End If
        
            sSQL2 = " WHERE (((CDate(Left([Date],10)))>=CDate([Forms]![frmReports]![tbxFirstDate]) And (CDate(Left([Date],10)))<=CDate([Forms]![frmReports]![tbxSecondDate])))"
                    
        End If
'==============
If Me.cbxDateTypeSelection = "All Time" And Me.cbxFor = "Capital Visas" Then
    sSQL3 = ""
ElseIf Me.cbxDateTypeSelection = "All Time" Then
    sSQL3 = " tblCases.Consultant =" & "'" & Me.cbxFor & "'"
ElseIf Me.cbxFor <> "Capital Visas" Then
    sSQL3 = " And tblCases.Consultant =" & "'" & Me.cbxFor & "'"
Else: sSQL3 = ""
End If
 
If Me.frmPaymentType = 1 Then
    sSQL4 = " ORDER BY tblPayments.Date"
ElseIf Me.frmPaymentType = 2 Then
    
    sSQL4 = " ORDER BY tblInvoicedCharges.Date"
End If
Select Case Me.frmPaymentType
    'Cash
    Case 1
    If Me.cbxDateType = "Quick Date Range" Then
        If Me.cbxFor = "Capital Visas" And Me.cbxDateTypeSelection = "All Time" Then
        sFinalSQL = sSQL1 & sSQL3 & sSQL4
        Else: sFinalSQL = sSQL1 & sSQL2 & sSQL3 & sSQL4
        End If
    ElseIf Me.cbxDateType = "Exact Date Range" Then
        sFinalSQL = sSQL1 & sSQL2 & sSQL4
    End If
    DoCmd.OpenReport "rptCashIn", acViewReport
    Reports![rptCashIn].RecordSource = sFinalSQL
    Forms!frmReports.Visible = False
Me.tbxMemo = sFinalSQL
    
    'Invoice
    Case 2
    If Me.cbxDateType = "Quick Date Range" Then
        If Me.cbxFor = "Capital Visas" And Me.cbxDateTypeSelection = "All Time" Then
        sFinalSQL = sSQL1 & sSQL3 & sSQL4
        Else: sFinalSQL = sSQL1 & sSQL2 & sSQL3 & sSQL4
        End If
    ElseIf Me.cbxDateType = "Exact Date Range" Then
        sFinalSQL = sSQL1 & sSQL2 & sSQL4
    End If
    DoCmd.OpenReport "rptInvoiceIn", acViewReport
    Reports![rptInvoiceIn].RecordSource = sFinalSQL
    Forms!frmReports.Visible = False
Me.tbxMemo = sFinalSQL
 
End Select
 
 
End Sub
 
Like I said, I would do it from within the report. You also haven't addressed Boyd's points about whether the db is multiuser/split.
 

Users who are viewing this thread

Back
Top Bottom