[RESOLVED] Error Msg "Query must have at least one destination field"

[bubica_au]

Hi All,

Got stuck again with VB 6 and Access 2003 report.

I am getting the error message "Query must have at least one destnation field".

Ok, this is the scenario:

There are 2 txtboxes (txtDateFrom and txtDateTo) on VB 6 form the user enters the dates from and to and clicks on command button to view report.

The SELECT query grabs data from 3 tables based on date the user enters into the 2 text boxes.

I received the above error. Than I added the INSERT statement. But still getting the same error msg.

Not sure whats wrong.

Hope someone might give me a hand.

Also, how do you clear an access query?

The normal "DELETE * FROM [Ng_Report] " is not working.
Ng_Report is a access query not an access table.


Is there a better way of doing all this?


Thanks in advance.

Svet

Sub DeleteFromNgReport()

'Delete all records in Ng_Report Query
Dim strSQL As String
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
   
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FOBL.mdb"

strSQL = "DELETE * FROM Ng_Report "
conn.Open
conn.Execute strSQL
conn.Close
End Sub

Sub GetNgaireReport()

'Call DeleteFromNgReport

Dim strSQL As String
Dim pstFromDate As String
Dim pstToDate As String
Dim lngRows As Long
Dim SurveyId As String
Dim SESType As String
Dim QuestionGroupCode As String
Dim EvalDate As String
Dim StaffId As String
Dim StaffName As String
Dim SbjId As String
Dim SbjFullName As String
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection

pstFromDate = txtDateFrom.Text
pstToDate = txtDateTo.Text
   
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FOBL.mdb"
  
   strSQL = "SELECT DISTINCT HE_Order.SurveyId, HE_Order.SESType, HE_Order.QuestionGroupCode, " & _
            "HE_Order.EvalDate, HE_Staff.StaffId, HE_Staff.StaffName, HE_Subject.SbjId, " & _
            "HE_Subject.SbjFullName " & _
            "FROM HE_Order, HE_Staff, HE_Subject " & _
            "WHERE HE_Order.SbjId = HE_Subject.SbjId " & _
            "AND HE_Order.StaffId = HE_Staff.StaffId " & _
            "AND HE_Order.ForwardDate = " & pstFromDate & " AND HE_Order.ForwardDate = " & pstToDate & " "
  conn.Open
  conn.Execute strSQL, lngRows, adExecuteNoRecords
  
  conn.Execute ("INSERT INTO Ng_Report (SurveyId, SESType, QuestionGroupCode, EvalDate, StaffId, StaffName, SbjId, SbjFullName) VALUES ('" & SurveyId & "', '" & SESType & "', '" & QuestionGroupCode & "', '" & EvalDate & "', '" & StaffId & "', '" & StaffName & "', '" & SbjId & "', '" & SbjFullName & "')")

  conn.Close

  MsgBox lngRows & " Records Imported into your Report Ngaire."

End Sub

Private Sub cmdViewNgRpt_Click()

Call GetNgaireReport

Dim objAccess As New Access.Application
'If MsgBox("Are you sure you want to print the 'Create Questionair' report?", vbYesNo + vbQuestion + vbDefaultButton2, "Print Report") = vbYes Then
        With objAccess
            .OpenCurrentDatabase App.Path & "\FOBL.mdb", True
            '.DoCmd.Refresh
            .DoCmd.OpenReport "rptNg_Report", View:=2
            .Visible = True
        End With
        Set objAccess = Nothing
        'MsgBox "Your Report Ngaire has been printed.", vbInformation, "Print"
'End If
End Sub

[bubica_au]

The SOLUTION:

 Sub DeleteFromNgReport()

'Delete all records in Ng_Report Query
Dim strSQL As String
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
   
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FOBL.mdb"

strSQL = "DELETE * FROM HE_NgReport_Temp "
conn.Open
conn.Execute strSQL
conn.Close
End Sub


Sub GetNgaireReport()

Call DeleteFromNgReport

Dim strSQL As String
Dim lngRows As Long
Dim SurveyId As String
Dim SESType As String
Dim QuestionGroupCode As String
Dim EvalDate As String
Dim StaffId As String
Dim StaffName As String
Dim SbjId As String
Dim SbjFullName As String
Dim GetRecords As New ADODB.Recordset
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FOBL.mdb"
conn.Open
Set GetRecords = conn.Execute("SELECT DISTINCT HE_Order.SurveyId, HE_Order.SESType, HE_Order.QuestionGroupCode, " & _
            "HE_Order.EvalDate, HE_Staff.StaffId, HE_Staff.StaffName, HE_Subject.SbjId, " & _
            "HE_Subject.SbjFullName " & _
            "FROM HE_Order, HE_Staff, HE_Subject " & _
            "WHERE HE_Order.SbjId = HE_Subject.SbjId " & _
            "AND HE_Order.StaffId = HE_Staff.StaffId " & _
            "AND HE_Order.ForwardDate BETWEEN '" & txtDateFrom.Text & "' AND '" & txtDateTo.Text & "'")
            
Do Until GetRecords.EOF
    SurveyId = GetRecords!SurveyId
    SESType = GetRecords!SESType
    QuestionGroupCode = GetRecords!QuestionGroupCode
    EvalDate = GetRecords!EvalDate
    StaffId = GetRecords!StaffId
    StaffName = GetRecords!StaffName
    SbjId = GetRecords!SbjId
    SbjFullName = GetRecords!SbjFullName
  
  conn.Execute ("INSERT INTO HE_NgReport_Temp (SurveyId, SESType, QuestionGroupCode, EvalDate, StaffId, StaffName, SbjId, SbjFullName) VALUES ('" & SurveyId & "', '" & SESType & "', '" & QuestionGroupCode & "', '" & EvalDate & "', '" & StaffId & "', '" & StaffName & "', '" & SbjId & "', '" & SbjFullName & "')")
    GetRecords.MoveNext
    Loop
  conn.Close

  'MsgBox lngRows & " Records Imported into your Report Ngaire."

End Sub

Private Sub cmdViewNgRpt_Click()

Call GetNgaireReport

Dim objAccess As New Access.Application
'If MsgBox("Are you sure you want to print the 'Create Questionair' report?", vbYesNo + vbQuestion + vbDefaultButton2, "Print Report") = vbYes Then
        With objAccess
            .OpenCurrentDatabase App.Path & "\FOBL.mdb", True
            '.DoCmd.Refresh
            .DoCmd.OpenReport "HE_NgReport", View:=2
            .Visible = True
        End With
        Set objAccess = Nothing
        'MsgBox "Your Report Ngaire has been printed.", vbInformation, "Print"
'End If
End Sub

Not Happy Jane!!!