469,333 Members | 4,419 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,333 developers. It's quick & easy.

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

kcdoell
230 100+
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:

Expand|Select|Wrap|Line Numbers
  1. 'Checks to see if there are records for that given Division, Year, Month, Week Combo.
  2. 'If so tells the user to update
  3.  
  4. rstSQL = "SELECT tblStaticAllForecast.DivisionIDFK,  " & _
  5.             "FROM tblStaticAllForecast " & _
  6.             "WHERE ((tblStaticAllForecast.DivisionIDFK)='" & [Forms]![lock]![cboDivision] & "');"
  7.  
  8.     Dim rs As DAO.Recordset
  9.     Set rs = CurrentDb.OpenRecordset(rstSQL)
  10.     recordexists = rst.RecordCount
  11.  
  12.     If Nz(recordexists, 0) > 0 Then
  13.         MsgBox "The records that you are trying to lock already exists -- Please select another week or use the update feature."
  14.  
  15.         Else
  16.  
I am getting the following message when I execute:

Expand|Select|Wrap|Line Numbers
  1. The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. (Error 3141)
  2.  
The error focuses on the
Expand|Select|Wrap|Line Numbers
  1.  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.
Mar 26 '08 #1
4 1542
kcdoell
230 100+
Hello again:

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

Expand|Select|Wrap|Line Numbers
  1. Dim rstSQL As String
  2.  
  3. rstSQL = "SELECT tblStaticAllForecast.DivisionIDFK,  " & _
  4.             "FROM tblStaticAllForecast " & _
  5.             "WHERE ((tblStaticAllForecast.DivisionIDFK)='" & [Forms]![lock]![cboDivision] & "');"
  6.  
  7.     Dim rs As DAO.Recordset
  8.     Set rs = CurrentDb.OpenRecordset(rstSQL)
  9.     recordexists = rst.RecordCount
  10.  
  11.     If Nz(recordexists, 0) > 0 Then
  12.         MsgBox "The records that you are trying to lock already exists -- Please select another week or use the update feature."
  13.  
  14.         Else
  15.  
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.
Mar 26 '08 #2
kcdoell
230 100+
Hello:

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

Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2. Dim LockSQL As String
  3.  
  4.     LockSQL = "SELECT * FROM tblStaticAllForecast WHERE DivisionIDFK = '" & _
  5.     Val(Me.cboDivision.Value) & "'"
  6.  
  7.     Set rst = CurrentDb.OpenRecordset(LockSQL)
  8.     recordexists = rst.RecordCount
  9.  
  10.     If Nz(recordexists, 0) > 0 Then
  11.         MsgBox "The records that you are trying to lock already exists -- Please select another week or use the update feature."
  12.  
  13.         Else
  14.  
Now I am getting the following error message:

Expand|Select|Wrap|Line Numbers
  1. Data type mismatch in criteria expression. (Error 3464)
  2. 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.
  3.  
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.
Mar 26 '08 #3
kcdoell
230 100+
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.

Expand|Select|Wrap|Line Numbers
  1. 'Checks to see if there are records for that given Division, Year, Month, Week Combo.
  2. 'If so tells the user to update
  3.  
  4. Dim rst As DAO.Recordset
  5. Dim LockSQL As String
  6.  
  7.   LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
  8.         " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
  9.         " And YearID = '" & Val(Me.CboMonth.Value) & "'" & _
  10.         " And MonthID = '" & Val(Me.CboYear.Value) & "'" & _
  11.         " And FWeek = '" & Val(Me.cboWeek.Value) & "'"
  12.  
  13.     Set rst = CurrentDb.OpenRecordset(LockSQL)
  14.     recordexists = rst.RecordCount
  15.  
  16.     If Nz(recordexists, 0) > 0 Then
  17.                MsgBox "The records that you are trying to lock already exists. " & _
  18.                     "Please select another week or use the update feature."
  19.  
  20.         Else
  21.  
P.S. Three of my controls in my "where" statement were set to text on my table, hence the coding....

Best regards,

Keith.
Mar 26 '08 #4
kcdoell
230 100+
Correction in my code:

Expand|Select|Wrap|Line Numbers
  1. 'Checks to see if there are records for that given Division, Year, Month, Week Combo.
  2. 'If so tells the user to update
  3.  
  4. Dim rst As DAO.Recordset
  5. Dim LockSQL As String
  6.  
  7.   LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
  8.         " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
  9.         " And YearID = '" & Val(Me.CboYear.Value) & "'" & _
  10.         " And MonthID = '" & Val(Me.CboMonth.Value) & "'" & _
  11.         " And FWeek = '" & Val(Me.cboWeek.Value) & "'"
  12.  
  13.     Set rst = CurrentDb.OpenRecordset(LockSQL)
  14.     recordexists = rst.RecordCount
  15.  
  16.     If Nz(recordexists, 0) > 0 Then
  17.                MsgBox "The records that you are trying to lock already exists. " & _
  18.                     "Please select another week or use the update feature."
  19.  
  20.         Else
  21.  
Month and year were miss transposed.........
Mar 27 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Dima Protchenko | last post: by
2 posts views Thread by Chris via AccessMonster.com | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.