Can't get code to work

[Geoff Codd]

Too Few Parameters

Hi there,

I have the following code

Option Compare Database
Option Explicit

Private Sub Create_30_Min_Data()
   Dim dbs As DAO.Database
   Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset
   Dim i As Integer, timevar As Variant
   Dim strSQL As String
      
   strSQL = ("SELECT DataProfile.* " _
            & " FROM DataProfile " _
            & "WHERE (((DataProfile.Point_Id)=10567) " _
            & "AND ((DataProfile.Date) Between [Forms]![Report_Criteria_Selection]![Start_Date] And [Forms]![Report_Criteria_Selection]![End_Date]));")
   
   Set dbs = CurrentDb
   dbs.Execute "Delete * from tblDataProfile_2"

   Set rst1 = dbs.OpenRecordset(strSQL, dbOpenDynaset)
   Set rst2 = dbs.OpenRecordset("tblDataProfile_2")
  
   Do While rst1.EOF = False
     For i = 0 To 47
       With rst2
         .AddNew
         !DataSet_ID = rst1.Fields(1)
         !value1 = rst1.Fields(5 + i)
      
         If rst1.Fields(5 + i).Name = "24:00" Then
          ![Date] = rst1.Fields(3) + #11:30:00 PM#
         Else
           timevar = CDate(rst1.Fields(5 + i).Name) - #12:30:00 AM#
           ![Date] = rst1.Fields(3) + timevar
         End If
         .Update
       End With
     Next i
     rst1.MoveNext
   Loop
   Set rst1 = Nothing
   Set rst2 = Nothing
   Set dbs = Nothing

End Sub

I keep on getting the error - "Too few parameters expected 2"

Any ideas appreciated

Thanks
Geoff

 

[Mile-O]

Do a search - I've already posted a link to a thread answering this problem in the Forms forum this morning.

 

[Geoff Codd]

Thanks, I did see the other thread but it just went over my head. I am very new at this. I have adapted my code as follows

Option Compare Database
Option Explicit

Private Sub Create_30_Min_Data()
   Dim dbs As DAO.Database
   Dim QD1 As DAO.QueryDef
   Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset
   Dim i As Integer, timevar As Variant
   Dim strSQL As String
   
   QD1.Parameters![Forms]![Report_Criteria_Selection]![Start_Date] = [Forms]![Report_Criteria_Selection]![Start_Date]
   QD1.Parameters![Forms]![Report_Criteria_Selection]![End_Date] = [Forms]![Report_Criteria_Selection]![End_Date]
      
   strSQL = ("SELECT DataProfile.* " _
            & " FROM DataProfile " _
            & "WHERE (((DataProfile.Point_Id)=10567) " _
            & "AND ((DataProfile.Date) Between [Forms]![Report_Criteria_Selection]![Start_Date] " _
            & "And [Forms]![Report_Criteria_Selection]![End_Date]));")
   
   Set dbs = CurrentDb
   dbs.Execute "Delete * from tblDataProfile_2"

   Set rst1 = dbs.OpenRecordset(strSQL, dbOpenDynaset)
   Set rst2 = dbs.OpenRecordset("tblDataProfile_2")
  
   Do While rst1.EOF = False
     For i = 0 To 47
       With rst2
         .AddNew
         !DataSet_ID = rst1.Fields(1)
         !value1 = rst1.Fields(5 + i)
      
         If rst1.Fields(5 + i).Name = "24:00" Then
          ![Date] = rst1.Fields(3) + #11:30:00 PM#
         Else
           timevar = CDate(rst1.Fields(5 + i).Name) - #12:30:00 AM#
           ![Date] = rst1.Fields(3) + timevar
         End If
         .Update
       End With
     Next i
     rst1.MoveNext
   Loop
   Set rst1 = Nothing
   Set rst2 = Nothing
   Set dbs = Nothing

End Sub

But now I get the error "OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET"

What I was hoping for was for someone to walk me through this one, so I would know what I am doing in future.

Thanks
Geoff

 

[Mile-O]

But now I get the error "OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET

On what line? It's a lot of code and it's hard to get to grips with as it jumps from early to late binding and then back again (sometimes even on the same line i.e. ! to . to ! )

 

[Geoff Codd]

Thanks for helping, it is appreciated

I am getting the error at

QD1.Parameters![Forms]![Report_Criteria_Selection]![Start_Date] = [Forms]![Report_Criteria_Selection]![Start_Date]

I have attached a stripped down copy of my db, which I hope will help

Thanks
Geoff

 

[Mile-O]

First thing: the SQL statement you assign to the string variable strSQL is not dynamic in any way so you can dispense it from the code and save yourself time by making it a stored query.

The rest is done in the code, attached.

One thing, the table is extremely unnormalised - just a passing comment.

 

[Geoff Codd]

Thanks, I was trying to get away from having a stored query as this will soon be converted to VB6. There is nothing I can do about the table it is backend of a .exe

Is there a way I can do this with out having a stored query

Thanks
Geoff

 

[Rich]

Thanks, I was trying to get away from having a stored query as this will soon be converted to VB6. There is nothing I can do about the table it is backend of a .exe

Is there a way I can do this with out having a stored query

Thanks
Geoff

Isn't vb as flexible as Access then?

 

[Geoff Codd]

sorry don't get what you mean

 

[Mile-O]

Thanks, I was trying to get away from having a stored query as this will soon be converted to VB6. There is nothing I can do about the table it is backend of a .exe

But will the VB6 application not be using DAO to access the database anyway?

Is there a way I can do this with out having a stored query

Yes, it would involve deleting and recreating the query each time, though.

Basically:

Public Function MakeQuery() As Boolean

    On Error Goto Err_MakeQuery

    Dim db As DAO. Database
    Dim qdf As DAO.QueryDef

    Const qryName = "MyQuery"
    Const strSQL = ("SELECT DataProfile.* " _
            & " FROM DataProfile " _
            & "WHERE (((DataProfile.Point_Id)=10567) " _
            & "AND ((DataProfile.Date) Between [Forms]![Report_Criteria_Selection]![Start_Date] " _
            & "And [Forms]![Report_Criteria_Selection]![End_Date]));")

    Set db = CurrentDb

    On Error Resume Next
    Db.QueryDefs.Delete qryName
    On Error Goto Err_MakeQuery
    
    Set qdf = db.CreateQueryDef(qryName, strSQL)
    
    MakeQuery = True

Exit_MakeQuery:
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

Err_MakeQuery:
    MakeQuery = False
    Resume Exit_MakeQuery

Exit Function

In your function you can copy that to a module and then have:

If MakeQuery = True Then
    [i]' do stuff[/i]
Else  
    MsgBox "Unable to make query", vbExclamation
    Exit Sub
End If

 

[Mile-O]

Isn't vb as flexible as Access then?

It's a lot more flexible. Unless you are creating your own database, though, you are still going to use ADO or DAO to access a database anyway.