Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-23-2018, 04:34 AM   #1
Badvoc
Newly Registered User
 
Join Date: Apr 2009
Posts: 69
Thanks: 2
Thanked 0 Times in 0 Posts
Badvoc is on a distinguished road
SQL Backend previous/next button not working

Hi we have a database frontend/backend and am moving the backend to an SQL server.
This seems to have stopped the next and previous button working on the form although the add new button still works. The form is opened to a specified record via and another form using this code
Code:
 
    Dim stDocName As String
    Dim stLinkCriteria As String        
        stDocName = "frmForm"        
        stLinkCriteria = "[ID]=" & "'" & [Text0] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
This works nicely, the form opens at the required record, on the opened form are a previous and next button, clicking the previous button gives an error “you can’t go to the specified record” clicking the next button creates a new blank record, this is most likely due to Allow Additions = yes.
I have an idea that when the form is opened the stLinkCriteria from the opening form is creating a recordset of 1 record but I need to have a next&prev buttons on the main form that work.

The old database opened the form with the “Find and Replace” menu item open and then the user searched for their given record but this doesn’t work with the SQL backend for some reason, it just hangs.
Now Im stumped.

Badvoc is offline   Reply With Quote
Old 11-23-2018, 04:51 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,325
Thanks: 431
Thanked 787 Times in 764 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: SQL Backend previous/next button not working

Is that EXACTLY what is written?

I only ask, as if one comma is missing then the criteria becomes a filter and not a where condition, which sounds like what is happening.?

Also I'd expect if you are using ID then that is the autonumber key, and only one record with that value would exist.?, though why you are checking a text value is confusing.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

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.
Gasman is online now   Reply With Quote
Old 11-23-2018, 05:31 AM   #3
Badvoc
Newly Registered User
 
Join Date: Apr 2009
Posts: 69
Thanks: 2
Thanked 0 Times in 0 Posts
Badvoc is on a distinguished road
Re: SQL Backend previous/next button not working

Code:
 
 Dim stDocName As String
    Dim stLinkCriteria As String        
        stDocName = "frmForm"        
        stLinkCriteria = "[DrawingNo]=" & "'" & [Text0] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
Sorry shoun't of changed it.

Badvoc is offline   Reply With Quote
Old 11-23-2018, 05:46 AM   #4
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,325
Thanks: 431
Thanked 787 Times in 764 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: SQL Backend previous/next button not working

So how many records exist for each DrawingNo?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

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.
Gasman is online now   Reply With Quote
Old 11-23-2018, 05:50 AM   #5
Badvoc
Newly Registered User
 
Join Date: Apr 2009
Posts: 69
Thanks: 2
Thanked 0 Times in 0 Posts
Badvoc is on a distinguished road
Re: SQL Backend previous/next button not working

15945 total records in table, the form gets its data from a query of the SQL table
Badvoc is offline   Reply With Quote
Old 11-23-2018, 06:08 AM   #6
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,325
Thanks: 431
Thanked 787 Times in 764 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: SQL Backend previous/next button not working

Quote:
Originally Posted by Badvoc View Post
15945 total records in table, the form gets its data from a query of the SQL table
You did not answer the question.?

So if the DrawingNo is unique and you open the form for a particular DrawingNo how many records do you expect to see?

If I create a form on a table and issue
docmd.OpenForm "Emails1",,,"ID=913"

I get one record as the ID is unique.? If I did it on ClientNo I would get 28 records.?

You are in effect setting a filter for a subset of records.

Indeed with my ID option, if I toggle the filter I get all the records again.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

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.
Gasman is online now   Reply With Quote
Old 11-23-2018, 06:45 AM   #7
Badvoc
Newly Registered User
 
Join Date: Apr 2009
Posts: 69
Thanks: 2
Thanked 0 Times in 0 Posts
Badvoc is on a distinguished road
Re: SQL Backend previous/next button not working

Sorry I miss-understood your question,

but Iv solved it now, I found a post by Baldy with a link on how to "Open a second form to the record selected on the first form, but still include all records"

a bit of code changes and it works for me

Code:
Dim rs As Object 
'open the new form
 DoCmd.OpenForm "frmGAForm"
'take it to the selected record
 Set rs = Forms!frmGAForm.RecordsetClone
 rs.FindFirst "[GA Drawing]=" & "'" & [Text0] & "'"
 Forms!frmGAForm.Bookmark = rs.Bookmark
 Set rs = Nothing
thanks for your help.

Badvoc is offline   Reply With Quote
Old 11-23-2018, 07:03 AM   #8
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,325
Thanks: 431
Thanked 787 Times in 764 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: SQL Backend previous/next button not working

Well that was going to be my suggestion once I found out exactly what you were doing and data setup.

Good to know I was along the right lines, although I was thinking of passing the key in as an OpenArgs and that method of PBaldy is a neater way.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

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.
Gasman is online now   Reply With Quote
Reply

Tags
access , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Cmd button msgBox dependent on previous cmd Button Choice SimoneRene Forms 11 03-31-2017 02:24 AM
Calculated text box not working with slow connection to backend tomusn83 Forms 4 12-21-2015 05:17 AM
Changing backend from a button jasons Modules & VBA 3 07-21-2011 03:50 AM
Cant Get Previous Working TurboDieselOne Modules & VBA 4 06-21-2004 02:42 AM
[SOLVED] Previous Working Day cpw6 Forms 2 05-12-2003 11:58 PM




All times are GMT -8. The time now is 01:30 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