Creating a DAO.Recordset for the first time....Help....

[kcdoell]

Hello:

I am trying to create a DAO record set for the first time. I wanted to keep it simple, so I first wrote something to work for my Division field then I would expand on the code later. Below is what I have in my onClick event:

'Checks to see if there are records for that given Division, Year, Month, Week Combo.
'If so tells the user to update
 
rstSQL = "SELECT tblStaticAllForecast.DivisionIDFK,  " & _
            "FROM tblStaticAllForecast " & _
            "WHERE ((tblStaticAllForecast.DivisionIDFK)='" & [Forms]![lock]![cboDivision] & "');"
 
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(rstSQL)
    recordexists = rst.RecordCount
 
    If Nz(recordexists, 0) > 0 Then
        MsgBox "The records that you are trying to lock already exists -- Please select another week or use the update feature."
 
        Else
 

I am getting the following message when I execute:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. (Error 3141)
 

The error focuses on the

 Set rs = CurrentDb.OpenRecordset(rstSQL) 

. Since I am new at this can anybody see what I am missing? Any help or assistance would be greatly appreciated.

Thanks,

Keith.

[kcdoell]

Hello again:

I thought I needed to write in Dim rstSQL As String, so I included it below:

Dim rstSQL As String
 
rstSQL = "SELECT tblStaticAllForecast.DivisionIDFK,  " & _
            "FROM tblStaticAllForecast " & _
            "WHERE ((tblStaticAllForecast.DivisionIDFK)='" & [Forms]![lock]![cboDivision] & "');"
 
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(rstSQL)
    recordexists = rst.RecordCount
 
    If Nz(recordexists, 0) > 0 Then
        MsgBox "The records that you are trying to lock already exists -- Please select another week or use the update feature."
 
        Else
 

But that did not change the error message or where it is pointing to. Not sure if that was needed.

Any help would be great

Thanks,

Keith.

[kcdoell]

Hello:

I was thinking that my problem was in my SQL so I changed it to the following:

Dim rst As DAO.Recordset
Dim LockSQL As String
 
    LockSQL = "SELECT * FROM tblStaticAllForecast WHERE DivisionIDFK = '" & _
    Val(Me.cboDivision.Value) & "'"
 
    Set rst = CurrentDb.OpenRecordset(LockSQL)
    recordexists = rst.RecordCount
 
    If Nz(recordexists, 0) > 0 Then
        MsgBox "The records that you are trying to lock already exists -- Please select another week or use the update feature."
 
        Else
 

Now I am getting the following error message:

Data type mismatch in criteria expression. (Error 3464)
The criteria expression in a Find method is attempting to compare a field with a value whose data type does not match the field’s data type.
 

Am I going down the right road on this? I eventually want to expand on the where statement but at this point I am at a dead stop.

Any ideas would be a great help.

Thanks,

Keith.

[kcdoell]

I figured it out. The problem was in my SQL statement. In the end I wrote my other "where" statements. Below was my solution for those that stumble across this issue.

'Checks to see if there are records for that given Division, Year, Month, Week Combo.
'If so tells the user to update
 
Dim rst As DAO.Recordset
Dim LockSQL As String
 
  LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
        " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
        " And YearID = '" & Val(Me.CboMonth.Value) & "'" & _
        " And MonthID = '" & Val(Me.CboYear.Value) & "'" & _
        " And FWeek = '" & Val(Me.cboWeek.Value) & "'"
 
    Set rst = CurrentDb.OpenRecordset(LockSQL)
    recordexists = rst.RecordCount
 
    If Nz(recordexists, 0) > 0 Then
               MsgBox "The records that you are trying to lock already exists. " & _
                    "Please select another week or use the update feature."
 
        Else
 

P.S. Three of my controls in my "where" statement were set to text on my table, hence the coding....

Best regards,

Keith.

[kcdoell]

Correction in my code:

'Checks to see if there are records for that given Division, Year, Month, Week Combo.
'If so tells the user to update
 
Dim rst As DAO.Recordset
Dim LockSQL As String
 
  LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
        " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
        " And YearID = '" & Val(Me.CboYear.Value) & "'" & _
        " And MonthID = '" & Val(Me.CboMonth.Value) & "'" & _
        " And FWeek = '" & Val(Me.cboWeek.Value) & "'"
 
    Set rst = CurrentDb.OpenRecordset(LockSQL)
    recordexists = rst.RecordCount
 
    If Nz(recordexists, 0) > 0 Then
               MsgBox "The records that you are trying to lock already exists. " & _
                    "Please select another week or use the update feature."
 
        Else
 

Month and year were miss transposed.........