By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,325 Members | 1,881 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,325 IT Pros & Developers. It's quick & easy.

Can't read values while looping through a table

P: 15
Hi All,

Can someone please help me with the following code. I've been working on this for the past 2 days and i can't seem to get past this obstacle.

Problem: The code opens up the Table "tbl_Tabs" and counts the number of records. Then it begins at record 1 and is suppose to read the value in each record for the field "Delete". If Delete(Yes/No field) is marked as "Yes", then it is suppose to open the Chapter's table "tbl_Chapters" and update the fields that have the same TabID as the one that is flagged. But the code doesn't seem to be generating this result. Instead it returns the value "False" for the field "Delete". I've tried everything i can think of, I have even played around with the quotes.

Please help, would really appreciate it.

Regards,
Uarana

----------------------------------------------------------------------------------------------

Private Sub Delete_Click()

If Me.lst_manuals.ItemsSelected.count = 0 Then
MsgBox "Please select a Manual to Delete.", vbInformation
End If

Dim mID As String
mID = Me.lst_manuals.Column(0)

Dim strSQL1 As String
'strSQL1 = "UPDATE [tbl_Manuals] SET [Delete] = Yes WHERE [ManualID] =" & mID
'DoCmd.SetWarnings False
'DoCmd.RunSQL strSQL1

Dim strSQL2 As String
'strSQL2 = "SELECT [ManualID], [ManualNumber], [ManualName] FROM [tbl_Manuals] WHERE [SignedOut] = NO AND [UnderRequest] = NO AND [Delete] = NO"
'Me.lst_manuals.RowSource = strSQL2
'Me.lst_manuals.Requery

Dim strSQL3 As String
'strSQL3 = "UPDATE [tbl_Tabs] SET [Delete] = Yes WHERE [ManualID] =" & mID
'DoCmd.SetWarnings False
'DoCmd.RunSQL strSQL3

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i, recordcount As Integer
Dim tabID, strSQL_update As String

Set db = Currentdb()
Set rs = db.OpenRecordset("tbl_Tabs")
rs.MoveFirst

recordcount = rs.recordcount

For i = 1 To recordcount
If rs.Fields("Delete").Value = "Yes" Then
tabID = rs.Fields("[TabID]").Value
strSQL_update = "UPDATE [tbl_Chapters] SET [Delete] = Yes WHERE [TabID] =" & tabID
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL_update
End If
Next i

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
Nov 17 '06 #1
Share this Question
Share on Google+
7 Replies


P: 15
Sorry, i forgot to mention. I commented the first three SQL statements in the code and i have uncommented them. I still receive the same error.

Regards,
Uarana
Nov 17 '06 #2

nico5038
Expert 2.5K+
P: 3,072
To start with you'll have to use:

SET [Delete] = True

in all SQL.

You can use one statement like:

currentdb.execute ("update tblX set [delete] = true where Mid=" & me.listboxname)

When the ID is a textfield you'll have to add surrounding single quotes like:

currentdb.execute ("update tblX set [delete] = true where Mid='" & me.listboxname & "'")

For getting the recordcount use:

rs.movelast
mycounter = rs.recordcount
rs.movefirst

Just a start...

Nic;o)
Nov 17 '06 #3

P: 15
Hi Nico,

Thanks for the suggestions. I have made the required changed but i still receive the same problem. Everytime the code runs, it doesn't catch the value of the field "Delete" from the database. It always returns the value "False" for every record even though some are set to "True".

If rs.Fields("[Delete]").Value = True Then ' Catches the field Delete as False
tabID = rs.Fields("[TabID]").Value
strSQL_update = "UPDATE [tbl_Chapters] SET [Delete] = True WHERE [TabID] =" & tabID
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL_update

Please advise.

Regards,
Uarana
Nov 17 '06 #4

NeoPa
Expert Mod 15k+
P: 31,661
When using CurrentDB.Execute, only have parameters in () if the response is assigned or used.
The exception to this is putting it in a Call statement.
The mID field should be surrounded in [] within Access to avoid confusion with the Mid() function.

uarana,

You say the field is not returning the value you expect (True).
What is it (and other fields of the same record) returning?
Try to be specific.
Is the record the one you think it is?
Remember that all non-zero values are treated as TRUE, but only all 1s (-1 numerically) is actually equal to TRUE.
Nov 17 '06 #5

nico5038
Expert 2.5K+
P: 3,072
Hmm, I would also start with renaming your [Delete] field into something else. Delete is also an SQL command and thus it's probably a reserved word.
Normally (like with Date) I qualify such a fieldname like StartDate, EndDate, etc.
this prevents problems because of the syntax.

Additionally you can first construct the query in the SQL mode of the query editor and check or it's working OK, then you can use this working query to change into a stringed one.

Nic;o)
Nov 17 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Firstly, I completely agree with other responders about the problems with using protected names like delete as a field name. Also looking at your code you are using function names like recordcount as a variable name also. If not only makes for problems when compiling code but also makes it very hard for the experts to follow what your are doing.

Secondly there are also some other problems in the code which I will note as I make changes below: BTW I have removed the commented out code for the moment as it's confusing the issue. If you are having problems with this part of the code let me know and I'll look at it separately.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim i, recCount As Integer
  5. Dim tabID, strSQL_update As String
  6.  
  7.     Set db = CurrentDb()
  8.     Set rs = db.OpenRecordset("tbl_Tabs")
  9.  
  10.     ' to get an accurate record count of a recordset you have to 
  11.     ' movelast then move first (just a small bug in engine)
  12.     rs.MoveLast
  13.     rs.MoveFirst
  14.  
  15.     ' always check for no records returned as it will give an error
  16.     If rs.RecordCount <> 0 Then recCount = rs.RecordCount
  17.  
  18.     ' moved this out of loop as you're resetting it unnecessarily everytime you loop
  19.     DoCmd.SetWarnings False
  20.  
  21.     For i = 1 To recCount
  22.         If rs!Delete = -1 Then ' use -1 as it's easier to control
  23.             tabID = rs!tabID
  24.             strSQL_update = "UPDATE [tbl_Chapters] SET [Delete] = -1 WHERE [tabID] =" & tabID
  25.             DoCmd.RunSQL strSQL_update
  26.         End If
  27.         rs.MoveNext ' move to the next record in the recordset
  28.     Next i
  29.  
  30.     ' turn warnings back on
  31.     DoCmd.SetWarnings True
  32.  
  33.     rs.Close
  34.     Set rs = Nothing
  35.     Set db = Nothing
  36.  
  37. End Sub
  38.  
  39.  
Nov 17 '06 #7

PEB
Expert 100+
P: 1,418
PEB
To be based on uour code however:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL1 As String
  2.     strSQL1 = "UPDATE [tbl_Manuals] SET [Delete] = Yes WHERE [ManualID] =" & Str(mID) +";"
  3.     DoCmd.SetWarnings False
  4.     DoCmd.RunSQL strSQL1
  5.  
  6. Dim strSQL2 As String
  7.     strSQL2 = "SELECT [ManualID], [ManualNumber], [ManualName] FROM [tbl_Manuals] WHERE [SignedOut] = NO AND [UnderRequest] = NO AND [Delete] = NO"
  8.     'Me.lst_manuals.RowSource = strSQL2
  9.     'Me.lst_manuals.Requery
  10.  
  11. Dim strSQL3 As String
  12.     strSQL3 = "UPDATE [tbl_Tabs] SET [Delete] = Yes WHERE [ManualID] =" & str(mID) +";"
  13.    DoCmd.SetWarnings False
  14.    DoCmd.RunSQL strSQL3
  15.  
  16. Dim db As DAO.Database
  17. Dim rs As DAO.Recordset
  18. Dim i, recordcount As Integer
  19. Dim tabID, strSQL_update As String
  20.  
  21.     Set db = Currentdb()
  22.     Set rs = db.OpenRecordset("tbl_Tabs")
  23.     rs.MoveFirst
  24.  
  25.     recordcount = rs.recordcount
  26.  
  27.     For i = 1 To recordcount
  28.         If rs.Fields("Delete").Value = "Yes" Then
  29.             tabID = rs.Fields("[TabID]").Value
  30.                 strSQL_update = "UPDATE [tbl_Chapters] SET [Delete] = Yes WHERE [TabID] =" & Str(tabID)+";"
  31.                 DoCmd.SetWarnings False
  32.                 DoCmd.RunSQL strSQL_update
  33.         End If
  34.     Next i
  35.  
  36.     rs.Close
  37.     Set rs = Nothing
  38.     Set db = Nothing
  39.  
  40.  
Hi All,

Can someone please help me with the following code. I've been working on this for the past 2 days and i can't seem to get past this obstacle.

Problem: The code opens up the Table "tbl_Tabs" and counts the number of records. Then it begins at record 1 and is suppose to read the value in each record for the field "Delete". If Delete(Yes/No field) is marked as "Yes", then it is suppose to open the Chapter's table "tbl_Chapters" and update the fields that have the same TabID as the one that is flagged. But the code doesn't seem to be generating this result. Instead it returns the value "False" for the field "Delete". I've tried everything i can think of, I have even played around with the quotes.

Please help, would really appreciate it.

Regards,
Uarana

----------------------------------------------------------------------------------------------

Private Sub Delete_Click()

If Me.lst_manuals.ItemsSelected.count = 0 Then
MsgBox "Please select a Manual to Delete.", vbInformation
End If

Dim mID As String
mID = Me.lst_manuals.Column(0)

Dim strSQL1 As String
'strSQL1 = "UPDATE [tbl_Manuals] SET [Delete] = Yes WHERE [ManualID] =" & mID
'DoCmd.SetWarnings False
'DoCmd.RunSQL strSQL1

Dim strSQL2 As String
'strSQL2 = "SELECT [ManualID], [ManualNumber], [ManualName] FROM [tbl_Manuals] WHERE [SignedOut] = NO AND [UnderRequest] = NO AND [Delete] = NO"
'Me.lst_manuals.RowSource = strSQL2
'Me.lst_manuals.Requery

Dim strSQL3 As String
'strSQL3 = "UPDATE [tbl_Tabs] SET [Delete] = Yes WHERE [ManualID] =" & mID
'DoCmd.SetWarnings False
'DoCmd.RunSQL strSQL3

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i, recordcount As Integer
Dim tabID, strSQL_update As String

Set db = Currentdb()
Set rs = db.OpenRecordset("tbl_Tabs")
rs.MoveFirst

recordcount = rs.recordcount

For i = 1 To recordcount
If rs.Fields("Delete").Value = "Yes" Then
tabID = rs.Fields("[TabID]").Value
strSQL_update = "UPDATE [tbl_Chapters] SET [Delete] = Yes WHERE [TabID] =" & tabID
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL_update
End If
Next i

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
Nov 18 '06 #8

Post your reply

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