Counting records to do a loop? (1 Viewer)

[Elise_L]

I've got a form with several checkboxes on, ticked according to whether th evalue is tru or not. -Well sort of.

The code I have so far:
Private Sub Form_Current()
If Material <> "" Then
Do
If Material = "composites" Then
ChkComposites = True
End If

If Material = "metal" Then
ChkMetal = True
End If

etc....
If Material = "other" Then
ChkOthers = True
End If

DoCmd.GoToRecord acForm, "RelatedMaterials", acNext, 1

Loop
End If
End Sub

I get an error message at the end, because it doesn't recognise the end, that there is no records.

Can do any kind of modifications to the above, as this is the closest I've managed to get.

Or... I was thinking about doing a loop, so if there are 3 records gained from the query, then do the loop 3 times, but I haven't even got close...

I've been looking into .EOF and RecordCount, but no luck so far...

My access experience is 9 days!! So I need help!!!

 

[TiggR]

Have you tried

Do While EOF = False

Do your stuff here

nest record

loop

Doug

 

[Elise_L]

What would I do without you??!!

I get 'Argument not optional' for EOF, what am I doing wrong? Before when I did 'Do until .eof I got that it wasn't defined.

Thank you (again and again!)
Elise

 

[Jesseroh]

I've had this problem too, and the only luck I've had is to create a text box that will display the total number of records (you can set its properties to invisible if you don't want to see it). You do this by typing "=Count(*)" in the control source for your box. Then in your code you can access the box. This is kind of a cheap way to avoid the EOF problems.

 

[Ron Bell]

As a pointer to this problem:

EOF is a function and requires an argument ie EOF(indexnumber)

So if you use EOF without an argument you will get error = argument not optional

Look in Access or VB help on EOF function

.EOF (note the dot) is a property of of a recordset

if nothing is defined ie nothing is before the .EOF you will get error as described!

To try with .EOF is this brief start point:

variable = Forms![X].RecordsetClone.MoveLast.RecordCount
etc

then you could move to first record to do other stuff within the forms records

Forms![X].RecordsetClone.MoveFirst
Do Until Forms![X].RecordsetClone.EOF
etc:

Note: in VBA editor if you click your ( ie .EOF ), or anything else,
and then F1, you will get MS help on that item if relevant.

Hope this will point you in the correct direction

 

[Ron Bell]

Sorry Elise_L

Made a mistake in above
Line:variable = Forms![X].RecordsetClone.MoveLast.RecordCount , IS WRONG!!
Replace with: these-
Forms![X].RecordsetClone.MoveLast
variable = Forms![X].RecordsetClone.RecordCount

 

[Ron Bell]

Hope your not confused by my "on the cuff" replies - so just to make things clearer

Note a RecordsetClone is a vertual copy of your Forms recordsource - at that time

Your code at start should read:
_________________
Private Sub Form_Current()

If Material <> "" Then 'You may get problems with 'Material' ***** see below

Forms![X].RecordsetClone.MoveFirst
Do Until Forms![X].RecordsetClone.EOF
etc:

'Replace yr Docmd.GoToRecord...etc
with
Forms![X].RecordsetClone.MoveNext
Loop
End If
________________

***** What are your 'Material' , and "", values

If Access doesnt get precise instructions you could find code may not run.
So:
To be more precise use:
Me![mynameformaterial] <> ""

'Me' - is an identifier for current form, or report, which has the focus
and - '[mynameformaterial]' - is Name property of your control with stuff to be identified.
(which you are allowed to change, without effecting the control source)

To be even more precise use - Forms![X]![mynameformaterial]
' where 'X' = your database name of a Form

You may get problems with code <> ""
VBA has to identify precisely the meaning of a 'no entry' - which can be set to - Null, or ""
To be precise in your code - Set 'Default Value' property on yr Form for this 'Material' control - to "".
Then, if no entry is found on this field, "" is taken by yr VBA to be the entry.

Hope it all helps???