Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-12-2019, 01:01 AM   #1
Drand
Newly Registered User
 
Join Date: Jun 2019
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Drand is on a distinguished road
Problem with Looping

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

Drand is offline   Reply With Quote
Old 10-12-2019, 01:07 AM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,411
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Problem with Looping

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-12-2019 at 01:15 AM.
June7 is offline   Reply With Quote
Old 10-12-2019, 01:08 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Problem with Looping

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-12-2019, 01:33 AM   #4
Drand
Newly Registered User
 
Join Date: Jun 2019
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Drand is on a distinguished road
Re: Problem with Looping

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.
Drand is offline   Reply With Quote
Old 10-12-2019, 07:37 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,285
Thanks: 13
Thanked 4,111 Times in 4,043 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Problem with Looping

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

http://www.baldyweb.com/wherecondition.htm
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-12-2019, 07:48 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,815
Thanks: 57
Thanked 1,273 Times in 1,254 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Problem with Looping

Quote:
Originally Posted by Drand View Post
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.
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 10-12-2019, 11:36 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,562
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Problem with Looping

Quote:
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 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/off...docmd.openform

Code:
Set db = CurrentDb
Set qdf = db.QueryDefs("qryEventFollowUp")


'could have saved ONE step with 
'Set qdf = CurrentDb.QueryDefs("qryEventFollowUp")


Set rst = qdf.OpenRecordset()
'...but could have saved TWO steps if you directly used 
'Set rst = CurrentDB.OpenRecordset( "qryEventFollowUp" )

'If there is no implied order in the query (i.e. no ORDER BY) you should use
'rst.MoveFirst
'because without an ORDER BY, record order is not 100% predictable
'(but do it outside the loop)

With rst
    Do Until .EOF

        DoCmd.OpenForm FormName:="frmMessageBoxEventFollowUpEdit", WindowMode:=acDialog , , "[fieldname]=" & rst![somefield]

'Here is the mistake that you made:  You opened the form but didn't tell it where, and 
'you have a recordset available to tell you what you need to specify "where".
'look after the code segment for the explanation

        .MoveNext 'Move to the next Record

    Loop
End With
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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-13-2019, 03:06 PM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,037
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Problem with Looping

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.
Mark_ is offline   Reply With Quote
Old 10-13-2019, 04:48 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Problem with Looping

Quote:
I was trying to learn about looping
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-13-2019, 07:52 PM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,562
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Problem with Looping

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-14-2019, 10:30 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Problem with Looping

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

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Looping Problem ddrew Forms 8 12-03-2012 05:02 AM
Question Looping through txtbox problem Jia General 12 02-25-2012 05:02 AM
Problem with looping through records ds_8805 Forms 4 02-25-2010 06:27 PM
looping Problem amit82 Modules & VBA 2 02-08-2009 10:34 PM
If...Then... looping problem CEH Forms 6 12-18-2006 02:29 PM




All times are GMT -8. The time now is 10:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World