Do until but have it go by the records in the query - VBA

[Ruegen]

I have a Do Until Me.ID = 24855

however I would like it to match the ID that is in the current query of the form record source



I have it run next until it finishes at 24855 but if I shorten the amount of records in the query then the until number won't be 24855

is there code to just go until the last record of that record source?

[ItsMe]

Dcount will give you a number of records. You can assign it to an intenger and use the Do statement. Someting like Do while intSomething <= intCount or Do Until intCount = intCount +1

After your Do statement you want to Loop


You can grab a copy of your form's recordset.
Dim rcd As DAO.Recordset
Set rcd = Me.RecordsetClone

Then with that you can

Do while Not rcd.EOF

"Doing Something"

If "Found a Problem" Then
Exit Do
Exit Sub
End if
'Then you can call your loop
Loop

It's been a while (I think that is the way) so make sure you test any loop comands on a copy of a DB in case it loops into a fireball.

[Ruegen]

I guess if when you execute the dcount first and then get the intenger for the do then you get the value. I am assuming dcount will only count the number of records in that query and nothing exceeding? Wouldn't I just use Count(me.ID)?

Then have the value an intenger and make the d statement that - makes sense to me. So when the records increase, the count increases and the do gets the new value to step until to.

Is that possible?

(thinking about it, I am only asking it to count the first me.id and not all the records...)

[ItsMe]

Think of your integer as a container. When you first declare its data type it doesn't have a value.

Then on a line of code you place

intCount = mycountofrecords

Imagine a line number to the left of your integer. Maybe, 20.

Something has to happen at line 15 or 16 or 17... before firing line 20. An event needs to take place to get to line 20

So, as you are adding records, editing fields, doing whatever, there may be a need to GoTo that line of code to fill you integer container with the correct value.

So however you use Count(me.ID) to get the value to assign to your integer variable (container) you need to make sure it is not old, stale info before you run your loop.

[Ruegen]

I have done
Dim IngID As Long
IngID = Me.BookingsID
Dim IngCount As Long


IngCount = DCount([BookingsID], "tblBookings")


MsgBox IngCount


but instead of tblBookings I could ask it to look up a query say "qryBookings" or a record set of the same form (which will be the set query).

I did the above to get an accurate number to show. So far just by going off the table that is correct. If I point it to a record set on the same form is that possible?

Update: pointing to a Query works fine, not sure how to make it work looking up the same record set.

[ItsMe]

dcount is looking at a recordset.

The reason I gave you this
Dim IngID As Long
IngID = Me.BookingsID

was so you could insure you were making a comparison to something WITHIN your current recordset.

If your form is on a specific record when you get your dcount and you reference the lngID within your dcount you are counting something relevant to that unique Booking ID. You can place "Me.BookingsID" within your dcount statement. Same difference.

It just gives you a way to debug line by line

[Ruegen]

dcount is looking at a recordset.

The reason I gave you this
Dim IngID As Long
IngID = Me.BookingsID

was so you could insure you were making a comparison to something WITHIN your current recordset.

If your form is on a specific record when you get your dcount and you reference the lngID within your dcount you are counting something relevant to that unique Booking ID. You can place "Me.BookingsID" within your dcount statement. Same difference.

It just gives you a way to debug line by line

oh wait I see

ignore the above lines, I'm only interested in counting the recordset of the current open form then having that as a dim so that I can use it for an until in vba

I am guessing that is what

Dim rcd As DAO.Recordset
Set rcd = Me.RecordsetClone

is for?

Unless I don't need to count it and have "rcd.EOF" as the until?

[Ruegen]

I am guessing you are using rcd.EOF just so that you don't encounter an error at the end of the until avoiding having to count in the first place?

[ItsMe]

rcd is declared as a recordset and it is set as a clone of the forms current recordset, filtered or otherwise. It uses more resources than SQL and dlookup but has some good functions too.

.EOF stands for End Of Fields I believe. When you loop through or do a .MoveNext you may move past the last record. Just past the last record is .EOF

[Ruegen]

rcd is declared as a recordset and it is set as a clone of the forms current recordset, filtered or otherwise. It uses more resources than SQL and dlookup but has some good functions too.

.EOF stands for End Of Fields I believe. When you loop through or do a .MoveNext you may move past the last record. Just past the last record is .EOF

because I am make the recordset query etc show via a between date (already tested and working off another form, unbound text boxes, dates etc and query pulls their values) then .eof wouldn't be suggested?

[ItsMe]

EOF is a tool used when you call on the DAO recordset. You can do a lot with the recordset when you have a hold of it. Its like holding a form with out he GUI. You can edit delete update, retrieve data. All in any combination as you cycle, step, or loop through each record in the recordset.

You need to know when you reach the END. That is what .EOF is for.

[Ruegen]

EOF is a tool used when you call on the DAO recordset. You can do a lot with the recordset when you have a hold of it. Its like holding a form with out he GUI. You can edit delete update, retrieve data. All in any combination as you cycle, step, or loop through each record in the recordset.

You need to know when you reach the END. That is what .EOF is for.

If that's the case, can I use EOF to also run through all the records, do a command without having to see any these actions?

I ask this because I am, on a button click event, stepping through the records with an email command. This of course takes some time to do and doesn't exactly look pretty.

But if it can be done in the background and give a feedback when complete (message box probably) that would be better.

I could have a refresh form event after it has completed to update the current form to show which have been emailed.

[ItsMe]

.EOF by itself is not going to do anything for you. The first thing is that you can use .EOF when you are working with a recordset in a DAO object you created.

After you do this

Dim rcd As DAO.Recordset
Set rcd = Me.RecordsetClone

You can name your recordset whatever you want at the time you declare it. Maybe something meaningful so you can recognize it easily.

Dim rcdThisIsMyNewRecordset As DAO.Recordset
Set rcdThisIsMyNewRecordset = Me.RecordsetClone

Then you can do things with it.

rcdThisIsMyNewRecordset.MoveFirst

rcdThisIsMyNewRecordset.MoveNext


Do Until rcdThisIsMyNewRecordset.EOF
"I am doing something here"
Loop


Try declaring your recordset inside a bound test form. Once you have it named what you want you can use the VBE's "intelisense" by placing "." after your recordset name. Much like you would with Me + "."

[Ruegen]

If I do Do Until rcdThisIsMyNewRecordset.EOF
"I am doing something here"
Loop

how does it know when to stop if it is not counting by ID?

Normally I have to place in a ID to tell it to stop at that.

If it's a custom date range then I can't determine what the number of records will be to put into the code so I am assuming there is something to count the number so that it knows what to do it until?

Unless

Do Until rcdThisIsMyNewRecordset.EOF
"I am doing something here"
Loop

is saying only what is in this copied record set

then that would make sense.

Also executing something to the clone > how does that then affect the original if it is just affecting the clone? (unless you are only using the clone to count the number/boundaries and still executing an IF on the regular record set).

Am I on the right path to understanding this now?

[ItsMe]

Yes I believe you are tracking well.

So the EOF is the end. I think you are understanding this correctly. You do not always need the EOF. If you try to loop past the end with say a .moveNext it will error and launch the debugger. This is when you NEED to use .EOF

Alternatively, you could count the records and do until I = count or something like that. There is not any need to count the records using .EOF. But there are other tools to use also.

When you change a record via code while you have the form open I don't believe it will send a message to the user about updated records unless.....

You are not focused on the form while the code is executing. Don't quote me on that though. If I am opening a form to edit a recordset that another form is viewing, you will get a message saying the record has changed. So the best way to handle this is to save the record before losing focus.

You need to test it and see if the warning is coming up. If it is, call the form via VBA and save.

.