Access form Next record loop

[Godders199]

Hello, I am a beginner to Access, I have created a Database, Query and form which looks at the query results and updates the records. All this appears to work fine.

I have added a Button and used the wizard for it to be showing the next record when clicked. Which it does, however I just want it to loop through the records, currently it when it gets to the last row of the query it will just then show blank rows.

There is currently no VBA in the database.

The question is how do i get the next record button just to loop through the query results. Is there an option within the Design view or do I need to add code. If I need to add Code, could i have an example to follow.

Thanks for your help.

 

[alansidman]

As far as I know, there is no way to loop through a query as it is developed from table data. You would have to develop a loop to go through your table applying the same criteria that you used to develop the query.

Here is an example of how to do a "Do Loop" that does what you are looking for, but from a table. It will require some modification to meet your specific needs, but it will demonstrate the basic procedure.

http://www.datapigtechnologies.com/flashfiles/vbadoloop.html

 

[xenou]

You can avoid showing a blank record by making your form such that inserts are not allowed. This is a form property called Allow Additions- just set it to No on the property sheet for the form (under the Data tab).

For looping I'd advise against it. The simple way to do this is just to click the double arrows using the form record navigation buttons.
https://support.office.com/en-us/ar...database-705220B7-0255-4EF9-9349-6BD7442D1B7E

This is simple and works the same for all MSAccess forms, reports, and tables. It's better to just use inbuilt behavior and save yourself the headaches of having to add "tweaks" code to forms all the time - plus your users will have the benefit of knowing what to do when they use not only this form but any form in any access database.

 

[Godders199]

thanks for your help

 

[Godders199]

Thanks this all works, I think i am nearly there.

When I add data to the form, it updates the table, however i dont seem to be able to get the query to refresh, without saving and closing access and reopening.. what I am tryng to do is add a button to the form , which refreshes the query, so any records that no longer met the criteria do not show on the form view.

Is this possible.. Again happy just to be pointed in the right direction

 

[xenou]

Simply using an Me.Requery statement should work.

 

[Micron]

You say the form is linked to a query. To me, this says the form's record source is that query. Surprised you didn't say you have to close and reopen the table to see additions, so the table must be closed, you add data, you open the table and find the record there and wonder why it's not in the query. You don't need to open the query at all if the form is based on it, so why bother requerying the query? In fact, if you left the table open, added a record and went back to the table you'd experience the same thing. The Refresh button on the ribbon would take care of either issue if all you want to do is verify the form is working. Otherwise, the query and table should not be exposed to the user anyway.

 

[xenou]

Actually I hadn't realized that you actually wrote "saving and closing Access":

saving and closing access and reopening..

That's pretty extreme.
Is this all a local Access db or are you doing something with another database such as Oracle or SQL server?