Recordset not updating

[Guest]

With the following code (stripped of unnecessary? detail), I am looking to
update the PrintLabel field.

The message proves that the code is getting to rs1.Edit 4 times - but the
field value in the table remains as false.

I've spent hours on this, just as well it's volunteer work.

Private Sub btnGo_Click()
Dim rs1 As DAO.Recordset
Dim sSQL As String

sSQL = "SELECT tAddress.AddressID, tAddress.Active, tAddress.PrintLabel,
tCategory.* " _
& "FROM tAddress INNER JOIN tCategory ON tAddress.AddressID =
tCategory.AddressID "

If Me.Controls("chkActive").Value = -1 _
Then
sSQL = sSQL & "WHERE (((tAddress.Active)=True));"
Else
sSQL = sSQL & "WHERE (((tAddress.Active)=False));"
End If

Set rs1 = CurrentDb.OpenRecordset(sSQL)

With rs1
Do Until .EOF
If <value found in tCategory> = True _
Then
MsgBox "found"
rs1.Edit
!PrintLabel = True
End If
.MoveNext
Loop
.Close
End With
Set rs1 = Nothing
'fnCreateLabels
End Sub

 

[John Spencer]

where is ".Update" in your code. You need that to save the change.

With rs1
Do Until .EOF
If <value found in tCategory> = True Then
MsgBox "found"
.Edit
!PrintLabel = True
.Update '<<<<<<<<<<<<<<<<<<<<<<<
End If
MoveNext
Loop
.Close
End With

Although I would think you could construct a query that would do this
easier and faster. Hard to say without knowing a bit more about the
code and what the test "<value found in tCategory>" is.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thanks John

That solved the problem - so easy.

It is difficult doing these things without a complete understanding. Is
there some way that I can get a better understanding of DAO?

I take you point about using a query, but the exercise is not simple.

tCategory has 21 boolean fields labelled CatInclude1 ... CatInclude21, and
the values in these fields are assigned based on an alphabettical listing of
records in tMstrCategory.

I am conscious of the fact that this is not normalised but I cannot think of
another way of performing a flexible and dynamic allocation.