Problem with Looping (1 Viewer)

Hi

I am new to coding.
I am trying to loop through a query that will show a form that reminds the user to follow up an event from last year.

My code is:

Public Function EventReminder()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryEventFollowUp")
Set rst = qdf.OpenRecordset()

With rst
Do Until .EOF
DoCmd.OpenForm FormName:="frmMessageBoxEventFollowUpEdit", WindowMode:=acDialog

.MoveNext 'Move to the next Record

Loop
End With


rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

End Function

There are 2 records in my test table/query. Unfortunately, this code produces the first record twice and does not show the second record when I close the form after the first record has displayed.

The form is set to dialogue.

It seems that the code is looping back to the first record at all times.

Sorry, but what am I doing wrong here?

Appreciate your assistance.

David

So form is bound to table? Your code opens form but does not apply filter so of course first record is always displayed. I am assuming form is set for Single view. Do you have navigation box active and can you navigate to other records?

Why don't you just open form filtered to display all desired records in Continuous or Datasheet view or a report?

For future, please post code between CODE tags to retain indentation and readability.

if qryEventFollowUp query is the recordsource of form frmMessageBoxEventFollowUpEdit, then you do not need to use recordset.
just open the form.

or you can use the Form's Open event and set the recordsource there:

private sub form_open(cancel as integer)
me.recordsource = "qryEventFollowUp"
end sub

Thanks
Appreciate the responses, and yes, I agree that I could just open a form.

What I was trying to achieve here is:

1. An autoexec option that popped up a diary style reminder everytime they opened the application. I do not want the user to have to remember to check the reminders. I guess opening the form you suggest will achieve this result anyway.

and

2. I was trying to learn about looping.

Anyway, thanks for the feedback.

Perhaps adding a where condition using the value from the recordset?

http://www.baldyweb.com/wherecondition.htm

Drand said:

Thanks
Appreciate the responses, and yes, I agree that I could just open a form.

What I was trying to achieve here is:

1. An autoexec option that popped up a diary style reminder everytime they opened the application. I do not want the user to have to remember to check the reminders. I guess opening the form you suggest will achieve this result anyway.

and

2. I was trying to learn about looping.

Anyway, thanks for the feedback.

Click to expand...

Hi. If there were say 10 reminders to show, would you like 10 separate forms to open at the same time or 1 form to open 10 different times after the user acknowledges each one?

Unfortunately, this code produces the first record twice and does not show the second record when I close the form after the first record has displayed.

Click to expand...

The others are making suggestions, and they are right, but you had a question that I will answer for you because you wanted to learn about coding. So maybe this is a little bit of a diversion from the other answers. I am going to annotate a couple things in alternate colors. I will refer to the arguments of the OpenForm method in my comments so here is a link if you need to review the call arguments.

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openform


When you want to open a form to a particular record, the fourth argument of DoCmd.OpenForm is a WHERE clause that you can use to select where you would open your form. If you have a primary key (PK) in the table underlying the form, you could unequivocally open the form to that record by supplying the name of the ID field and the desired value to select the specific record based on the ID field. Read the article and look at their sample code that opens the form based on the DepartmentID, which is the 2nd code snippet under the "Example" heading.

The reason you saw the same record twice in a row was because you closed and re-opened the form the same exact way twice. It didn't matter that you stepped the recordset because you didn't provide recordset data for the "WHERE" clause. And note that because we already know that the 4th argument is going to be a WHERE clause, we don't actually need to (and in fact should not) supply the word WHERE as part of that.

theDBGuy implicitly answered WHY June7 suggests not doing this the way you are doing it. From a users standpoint, having multiple "Reminders" pop up that are not relevant to what I'd doing at the moment becomes very problematic.

Most often when you see this type of coding it was done with the assumption that only one or two items EVER will be shown at a time. If an error (in design, use, or with the database or machine) occurs, you may have hundreds of windows open up with reminders. Forcing a user to respond to each means they may all be ignored, especially by a user who does not think they are responsible for them.

As June7 posted, you'd be much better off having a continuous form that shows all open items, preferably with ways to sort and limit it. If there are few items in the list this is as good as opening a form to remind the user. If there are many, it will be far easier to work with.

I was trying to learn about looping

Click to expand...

This is an admirable goal and one you should pursue but this isn't the place to use it. June gave you the solution in the first response but I'll repeat it,

create a query that selects the reminder records and use that query as the RecordSource for a form. Use that form as the opening form when the user opens the database.

A refinement is to somehow customize the list so that only the reminders for the person opening the database appear and not all the reminders for others. This is only possible if you have a way of identifying the logged in user and tying him to specific reminders.

Everyone else is telling you that in the case you have, a loop isn't the right answer, and I absolutely do not disagree with them. But next time you have a question about VBA and loops, this is the place to come. Because as Pat points out, learning how to properly construct and use loops must be considered a proper goal... albeit secondary to getting the job done. Therefore, don't stop being curious about how to do things.

Learning to program gets easier as you go. In the beginning, all you have is a hammer so everything looks like a nail