LOOP

[Nobleadi]

VERY new to VBA and have spent hrs trying to work out why the Loop doesn't stop at the 'Loop Until rs.EOF' line.
All records update fine but when it gets to the last record, the DoCMD line shown in red tries to get to the next record when there isn't one.
I subsequently get a frozen form which I have to escape out of.

If anyone can help, i'd greatly appreciate it.

 

[bastanu]

Use Rs.MoveNext instead of Docmd

 

[Gasman]

Not sure what you are trying to do there, but I would be using rs.MoveNext ?

I cannot see how the records update successfully as there is no rs.Edit or rs.Update command ?

 

[Nobleadi]

Hi. I tried rs.MoveNext but that only updates the first record.

 

[Uncle Gizmo]

I use a loop for creating multiple PDF files and explain how to use it on my website here:-

Generate Multiple Reports​

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

Also this YouTube video from this time index shows the basic code you need:-

Multiple Reports - Nifty Access​

 

[Nobleadi]

Thanks ill have a look.

 

[Uncle Gizmo]

BTW,

The Sample code linked to on my website is available for free, contact me for the details of how to obtain a free copy..

 

[bastanu]

As Gasman said you need to use rs.Edit and rs.Update for each record. Is that the recordsource of a form? Why not use an update query?

Cheers,

 

[Gasman]

Hi. I tried rs.MoveNext but that only updates the first record.

Well it does depend on where you place it, but it should be in place of your DoCmd line ?

 

[Solo712]

Hi. I tried rs.MoveNext but that only updates the first record.

You should always test for an empty recordset and move to first record before moving to next, e.g.

If Not rs.EOF Then rs.MoveFirst

Do While Not rs.EOF
    :
    rs.MoveNext
Loop

Best,
Jiri

 

[Pat Hartman]

Please explain the business logic of what you are doing. Aside from the coding issues, the logic doesn't feel right.

 

[Nobleadi]

Please explain the business logic of what you are doing. Aside from the coding issues, the logic doesn't feel right.

Hi Pat,

This is what I'm attempting to do. As I say I'm very new to VBA and to be honest relatively new to access in general. The design of the database may also be in question hence the reason I'm having difficulty doing something that access cant do..... easily.

 

[Gasman]

Just bind the controls to the relevant fields?

 

[bastanu]

Do not duplicate the fields coming from SQL server, create a subform based on that table and link it to the main form by RouteID (or use dLookups to populate them in the existing form).

Cheers,
Vlad

 

[Pat Hartman]

Telling us your pre-determined technical solution isn't helpful. You are a novice and just chose something you thought you could implement. That isn't necessarily the best option.

As the others have already pointed out, storing the same data in multiple places is poor practice and will lead to data anomalies Therefore, you need a different technical solution.

Forms can be bound to queries that join several tables and that may be your "solution". HOWEVER doing this comes with some danger. Typically, you don't want users to update the "lookup" data on this form so you MUST set the locked property for all the lookup fields to yes to prevent accidental updating. Remember, updating the lookup data on this form would update the lookup data in the table and that means that it would change everywhere which is not likely to be what you want to happen.