Can't read values while looping through a table

[uarana]

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

[uarana]

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

[nico5038]

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)

[uarana]

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

[NeoPa]

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.

[nico5038]

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)

[MMcCarthy]

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.

 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i, recCount As Integer
Dim tabID, strSQL_update As String
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tbl_Tabs")
 
    ' to get an accurate record count of a recordset you have to 
    ' movelast then move first (just a small bug in engine)
    rs.MoveLast
    rs.MoveFirst
 
    ' always check for no records returned as it will give an error
    If rs.RecordCount <> 0 Then recCount = rs.RecordCount
 
    ' moved this out of loop as you're resetting it unnecessarily everytime you loop
     DoCmd.SetWarnings False
 
    For i = 1 To recCount
        If rs!Delete = -1 Then ' use -1 as it's easier to control
             tabID = rs!tabID
            strSQL_update = "UPDATE [tbl_Chapters] SET [Delete] = -1 WHERE [tabID] =" & tabID
            DoCmd.RunSQL strSQL_update
        End If
        rs.MoveNext ' move to the next record in the recordset
    Next i
 
    ' turn warnings back on
    DoCmd.SetWarnings True
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
End Sub
 
 

[PEB]

To be based on uour code however:

Dim strSQL1 As String
    strSQL1 = "UPDATE [tbl_Manuals] SET [Delete] = Yes WHERE [ManualID] =" & Str(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] =" & str(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] =" & Str(tabID)+";"
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL_update
        End If
    Next i
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
 

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