SQL Backend previous/next button not working (1 Viewer)

Badvoc

Registered User.
Local time
Today, 17:04
Joined
Apr 22, 2009
Messages
69
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.:banghead:
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:04
Joined
Sep 21, 2011
Messages
14,044
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.?
 

Badvoc

Registered User.
Local time
Today, 17:04
Joined
Apr 22, 2009
Messages
69
Code:
 Dim stDocName As String
    Dim stLinkCriteria As String        
        stDocName = "frmForm"        
        stLinkCriteria = "[DrawingNo]=" & "'" & [Text0] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria

Sorry shoun't of changed it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:04
Joined
Sep 21, 2011
Messages
14,044
So how many records exist for each DrawingNo?
 

Badvoc

Registered User.
Local time
Today, 17:04
Joined
Apr 22, 2009
Messages
69
15945 total records in table, the form gets its data from a query of the SQL table
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:04
Joined
Sep 21, 2011
Messages
14,044
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.?
 

Badvoc

Registered User.
Local time
Today, 17:04
Joined
Apr 22, 2009
Messages
69
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:04
Joined
Sep 21, 2011
Messages
14,044
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.
 

Users who are viewing this thread

Top Bottom