Assistance Needed

[Guest]

I am trying to make a simple loop that will check a database field, if it is
not null then change a yes/no box to checked.

Ex. Fields Code1, Code2, Code3
Yes/No Boxes List1,List2,List3

VB Code =
While x < 612

If [Code1] = 1 Then [List1] = True
If [Code2] = 2 Then [List2] = True
If [Code3] = 3 Then [List3] = True
x = x + 1

----
How do I get VB to switch to the next record in the db and then run through
the If statements again. It does the first record just fine, then dies. The
x < 612 is in there as there are only 611 records in the table.

Any ideas?

thanks

Ken L.
I.T. Support Lackey

 

[Duane Hookom]

I would use and update query with SQL like:

UPDATE tblYourTable
SET [List1] = IIf([Code1] = 1, -1,[Code1]),
SET [List2] = IIf([Code2] = 2, -1,[Code2]),
SET [List3] = IIf([Code3] = 3, -1,[Code3]);

Always perform mass updates on a copy prior to running on actual data.

 

[Guest]

Hi Ken,

You would probably want to open a recordset to loop through the records. A
recordset can be opened various ways, but the most common is by using a table
or query name, or an SQL statement. Following is a brief example of how a
DAO recordset would be used (you can also use ADO).

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String 'only needed if using an SQL statement

Set db = CurrentDB
Set rst = db.OpenRecordset("YourTableOrQueryName")

'or alternatively
Set rst = db.OpenRecordset(strSQL)

'Loop through the records if not End Of Field (.EOF)
'Note that when EOF occurs, you have moved past the last record, not on it.

'Use the with statement to imply rst in front
'of all .'s and !'s
With rst
'Do while not end of field
Do While Not .EOF
'put current record in edit mode
.Edit
If !Code1 = 1 Then
!Lst1 = True
End If
'etc - do the rest of your loop stuff
'save the changes to the current record
.Update
'move to the next record
.MoveNext
'check the Do condition for the next record
Loop

'close the recordset
.Close

End With

'free the rst variable
Set rst = Nothing

'free the db variable
Set db = Nothing

You can find a lot more help on using recordsets in VBA help. There are
many options that you can set when opening them, and obviously many other
good things to know. You may want to start by looking at the OpenRecordset
method. You should be able to go straignt to it if you highlight it on the
db.OpenRecordset part of the code above and press F1.

Hope that helps, Ted Allen

 

[Guest]

I agree, Duane's method would be much better for doing the update that you
mentioned. When I read you post, the question about looping through records
caught my attention more than what you were actually trying to achieve. Use
the update query and save the info on recordsets for future reference.

-Ted

I would use and update query with SQL like:

UPDATE tblYourTable
SET [List1] = IIf([Code1] = 1, -1,[Code1]),
SET [List2] = IIf([Code2] = 2, -1,[Code2]),
SET [List3] = IIf([Code3] = 3, -1,[Code3]);

Always perform mass updates on a copy prior to running on actual data.

--
Duane Hookom
MS Access MVP

I am trying to make a simple loop that will check a database field, if it
is
not null then change a yes/no box to checked.

Ex. Fields Code1, Code2, Code3
Yes/No Boxes List1,List2,List3

VB Code =
While x < 612

If [Code1] = 1 Then [List1] = True
If [Code2] = 2 Then [List2] = True
If [Code3] = 3 Then [List3] = True
x = x + 1

----
How do I get VB to switch to the next record in the db and then run
through
the If statements again. It does the first record just fine, then dies.
The
x < 612 is in there as there are only 611 records in the table.

Any ideas?

thanks

Ken L.
I.T. Support Lackey

 

[Guest]

Much obliged. It worked perfectly!

I would use and update query with SQL like:

UPDATE tblYourTable
SET [List1] = IIf([Code1] = 1, -1,[Code1]),
SET [List2] = IIf([Code2] = 2, -1,[Code2]),
SET [List3] = IIf([Code3] = 3, -1,[Code3]);

Always perform mass updates on a copy prior to running on actual data.

--
Duane Hookom
MS Access MVP

I am trying to make a simple loop that will check a database field, if it
is
not null then change a yes/no box to checked.

Ex. Fields Code1, Code2, Code3
Yes/No Boxes List1,List2,List3

VB Code =
While x < 612

If [Code1] = 1 Then [List1] = True
If [Code2] = 2 Then [List2] = True
If [Code3] = 3 Then [List3] = True
x = x + 1

----
How do I get VB to switch to the next record in the db and then run
through
the If statements again. It does the first record just fine, then dies.
The
x < 612 is in there as there are only 611 records in the table.

Any ideas?

thanks

Ken L.
I.T. Support Lackey