querydef reding first rec only

[Mhebor]

Hi,
I'm new to posting so please bare w/ me. Currently using A2K. I'm
having a problem getting a querydef to work. I know for a fact that it
is only reading the first record. If I have 4 records in my query, it
runs thru the code 4 times, but it is only reading the first record.
Here's the code.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim stDocName
Dim rst As Recordset
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("qrycount1")

Me!won1 = 0
Me!won2 = 0
Me!won3 = 0
Me!won4 = 0
With rst
rst.MoveFirst
Do Until .EOF
If Me!ckchdgt1 = -1 Then
Me!won1 = Me!won1 + 1
End If
If Me!ckchdgt2 = -1 Then
Me!won2 = Me!won2 + 1
End If
If Me!ckchdgt3 = -1 Then
Me!won3 = Me!won3 + 1
End If
If Me!ckchdgt4 = -1 Then
Me!won4 = Me!won4 + 1
End If
rst.MoveNext
Loop
End With
Set rst = Nothing '

Long story short, I want to count the number of times a chkdgit has a
value of -1. If ckchdgt1 has a -1 value in rec #2 and 3, the Me!won1
value is 0 or if rec 1 and 4 has avalue -1, then Me!won1 results in a
value of 4.

Thanks in advance.

 

[Stuart McCall]

Comments inline:

Hi,
I'm new to posting so please bare w/ me. Currently using A2K. I'm
having a problem getting a querydef to work. I know for a fact that it
is only reading the first record. If I have 4 records in my query, it
runs thru the code 4 times, but it is only reading the first record.

Actually it's not reading even the first record, because your code doesn't
refer to the recordset at all.

Here's the code.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim stDocName
Dim rst As Recordset
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("qrycount1")

Me!won1 = 0
Me!won2 = 0
Me!won3 = 0
Me!won4 = 0
With rst
rst.MoveFirst
Do Until .EOF
If Me!ckchdgt1 = -1 Then
Me!won1 = Me!won1 + 1
End If
If Me!ckchdgt2 = -1 Then
Me!won2 = Me!won2 + 1
End If
If Me!ckchdgt3 = -1 Then
Me!won3 = Me!won3 + 1
End If
If Me!ckchdgt4 = -1 Then
Me!won4 = Me!won4 + 1
End If
rst.MoveNext
Loop
End With
Set rst = Nothing '

I suspect, given the description below, that you need code like:

If !ckchdgt1 = -1 Then

Because the code is inside a With rst block, you may leave off all rst
prefixes, so what that code is really saying is:

If rst!ckchdgt1 = -1 Then

(Also you can lose the prefix on the MoveFirst and MoveNext calls)

 

[Mhebor]

Comments inline:




Actually it's not reading even the first record, because your code doesn't
refer to the recordset at all.








I suspect, given the description below, that you need code like:

If !ckchdgt1 = -1 Then

Because the code is inside a With rst block, you may leave off all rst
prefixes, so what that code is really saying is:

If rst!ckchdgt1 = -1 Then

(Also you can lose the prefix on the MoveFirst and MoveNext calls)





- Show quoted text -- Hide quoted text -

- Show quoted text -

Ok - Thank You, I understand what you were saying, however, removing
the rst and "Me!" from the checkdigits, i am still getting the same
results. Here's the updated code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim stDocName
Dim rst As Recordset
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("qrycount1")

won1 = 0
won2 = 0
won3 = 0
won4 = 0
With rst
.MoveFirst
Do Until .EOF
If chcdigit1 = -1 Then
won1 = won1 + 1
End If
If chcdigit2 = -1 Then
won2 = won2 + 1
End If
If chcdigit3 = -1 Then
won3 = won3 + 1
End If
If chcdigit4 = -1 Then
won4 = won4 + 1
End If
.MoveNext
Loop
End With
Set rst = Nothing '


So still getting the same result where it appears to be reading the
first record. The won# value is either a 4 or 0 depending if the first
records ckdigit is 0 or -1. It is never 2 or 3.

 

[Stuart McCall]

Ok - Thank You, I understand what you were saying, however, removing
the rst and "Me!" from the checkdigits, i am still getting the same
results.

That's because the code is still incorrect, but now differently. Take a
closer look at this line I suggested:

If !ckchdgt1 = -1 Then

Notice the bang (!) ?

Also I never suggested you remove the Me! references.

You really ought to include this line at the top of your modules:

Option Explicit

(Right underneath the Option Compare line)
That will help in cases like this, because without it, 'ckchdgt1' (for
instance) will be interpreted as an undeclared variable (ie a compile
error), rather than as a member of the rst recordset.

 

[Mhebor]

That's because the code is still incorrect, but now differently. Take a
closer look at this line I suggested:


Notice the bang (!) ?

Also I never suggested you remove the Me! references.

You really ought to include this line at the top of your modules:

Option Explicit

(Right underneath the Option Compare line)
That will help in cases like this, because without it, 'ckchdgt1' (for
instance) will be interpreted as an undeclared variable (ie a compile
error), rather than as a member of the rst recordset.




- Show quoted text -

You're a genius It worked. Thank You!