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:

[code=vb]
'Checks to see if there are records for that given Division, Year, Month, Week Combo.
'If so tells the user to update

rstSQL = "SELECT tblStaticAllFor ecast.DivisionI DFK, " & _
"FROM tblStaticAllFor ecast " & _
"WHERE ((tblStaticAllF orecast.Divisio nIDFK)='" & [Forms]![lock]![cboDivision] & "');"

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenR ecordset(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
[/code]

I am getting the following message when I execute:

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

The error focuses on the [code=vb] Set rs = CurrentDb.OpenR ecordset(rstSQL ) [/code]. 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:

[code=vb]
Dim rstSQL As String

rstSQL = "SELECT tblStaticAllFor ecast.DivisionI DFK, " & _
"FROM tblStaticAllFor ecast " & _
"WHERE ((tblStaticAllF orecast.Divisio nIDFK)='" & [Forms]![lock]![cboDivision] & "');"

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenR ecordset(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
[/code]

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:

[code=vb]
Dim rst As DAO.Recordset
Dim LockSQL As String

LockSQL = "SELECT * FROM tblStaticAllFor ecast WHERE DivisionIDFK = '" & _
Val(Me.cboDivis ion.Value) & "'"

Set rst = CurrentDb.OpenR ecordset(LockSQ L)
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
[/code]

Now I am getting the following error message:

[code=vb]
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.
[/code]

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.

[code=vb]
'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 tblStaticAllFor ecast WHERE" & _
" DivisionIDFK = " & Val(Me.cboDivis ion.Value) & _
" And YearID = '" & Val(Me.CboMonth .Value) & "'" & _
" And MonthID = '" & Val(Me.CboYear. Value) & "'" & _
" And FWeek = '" & Val(Me.cboWeek. Value) & "'"

Set rst = CurrentDb.OpenR ecordset(LockSQ L)
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
[/code]

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:

[code=vb]
'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 tblStaticAllFor ecast WHERE" & _
" DivisionIDFK = " & Val(Me.cboDivis ion.Value) & _
" And YearID = '" & Val(Me.CboYear. Value) & "'" & _
" And MonthID = '" & Val(Me.CboMonth .Value) & "'" & _
" And FWeek = '" & Val(Me.cboWeek. Value) & "'"

Set rst = CurrentDb.OpenR ecordset(LockSQ L)
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
[/code]

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