Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Controlling the current record on a form

Hello,
  I've got two forms attached to the same table.  The first form has a "Next>>" button on it which loads the second of the two forms.  When a user presses the next button I want the second form to open to the same record that the first form was using.  How can I do this programatically?
0
edc
Asked:
edc
  • 7
  • 3
1 Solution
 
ozphilCommented:
By setting the dynaset bookmark of the second form and refreshing.

I shall elaborate as a comment to follow shortly.
0
 
ozphilCommented:
in form1 put this code behind your next>> button

Sub Button1_Click ()
    Dim frm As Form

    DoCmd OpenForm "form2"
    Set frm = forms.form2
    frm.dynaset.FindFirst "yourtablefield = " & Me.yourformfield
    frm.bookmark = frm.dynaset.bookmark
End Sub

This works and is the simplest implementation of what you asked for. Both forms are synchronised when you press next>> button.

If you have any questions please provide a comment so that we may continue the exercise to its conclusion.
0
 
ozphilCommented:
This is a little more generic.

Sub Button1_Click ()
   Dim frm As Form

   DoCmd OpenForm "form2"
   Set frm = forms.form2
   Me.dynaset.bookmark = Me.bookmark
   frm.dynaset.FindFirst "indexTableField = " & Me.dynaset!indexTableField
   frm.bookmark = frm.dynaset.bookmark
End Sub
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
edcAuthor Commented:
It appears to have some problems.  First off, I'm working in Access '97.  Here is the 'code in progress':

Private Sub btnNextForm_Click()
On Error GoTo Err_btnNextForm_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim frm As Form
   
    Set frm = Forms("FAS")
    stDocName = "FAS"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    frm.Bookmark
    Form.Visible = False
   
Exit_btnNextForm_Click:
    Exit Sub

Err_btnNextForm_Click:
    MsgBox Err.Description
    Resume Exit_btnNextForm_Click
   
End Sub

On the set frm = forms.form2, VB only allows application, count, parent, and item.  I figured that the text that is in there now is basically what we are looking for.  Please correct me if I am wrong.  Second, on the line where you have frm.dynaset.findfirst...  there isn't a dynaset object under frm.  

Thanks for your help, and I look forward to further guidance.
0
 
ozphilCommented:
Thanks for your comment edc.

Im loading Access97 very shortly. There would be equivalents to Access 2.

With regard to the forms.form2 syntax, I think the rule now is that properties have the '.' sysntax, whereas user values have the '!' sysntax, so that forms!form2 may now be the correct way to specify a user property value.

With regard to dynaset, the term 'dynaset' is probably now been replaced with 'recordset'.

I'll be checking this in Access97 documentation right now and get back to you. If you wish, you may wish to determine the equivalent property in Access97.


0
 
ozphilCommented:
  I lifted this bit of code from Knowledgebase which applies to
   Access 7.0 & 97. It has nothing to do with your problem, but it   indicates that the syntax Recordset and RecordsetClone have   replaced dynaset. Until i get Access 97 running, could you try  substituting Recordset or RecordsetClone for dynaset.

   Dim RS As Recordset
   Set RS = Me.RecordsetClone
   RS.MoveLast
   If StrComp(Me.Bookmark, RS.Bookmark, 0) = 0 Then
      MsgBox "Form is displaying the last record."
   End If
0
 
ozphilCommented:
HOW ABOUT THAT!

I have just found a knowlegebase article which describes and answers your question.

the article is:
http://www.microsoft.com/kb/articles/q149/9/40.htm

The answer is very similar to mine with the appropriate changes in syntax. It uses recordset and recordset clone in lieu of dynaset.
Look at the kb article and the changes to my answer above will be apparent. Try just replacing dynaset with recordsetClone in the answer I have given you, or use the KB article code.


0
 
edcAuthor Commented:
Ozphil:  You're absolutely right, that article was what I was looking for.  There were a couple of minor problems that I was able to take care of, but there is one (I hope) final problem that is stumping me.  When the rst.FindFirst SyncCriteria line is executed, it's generating a "Data type mismatch in criteria experesion" error.  The SyncCriteria expression evaluates to "Q3000QuoteNumber="6"", (not always 6, but a number of somesort).  I don't know where to begin troubleshooting this one.  Any help would be GREATLY appreciated.  Thnaks.

Private Sub btnNextForm_Click()
On Error GoTo Err_btnNextForm_Click

    Dim FormName As String, SyncCriteria As String
    Dim frm As Form, rst As Recordset
    FormName = "FAS"
    If Not SysCmd(acSysCmdGetObjectState, acForm, FormName) Then
        DoCmd.OpenForm FormName
    End If
    Set frm = Forms(FormName)
    Set rst = frm.RecordsetClone
    SyncCriteria = BuildCriteria("Q3000QuoteNumber", dbText, Me!Q3000QuoteNumber)
    rst.FindFirst SyncCriteria
    If rst.NoMatch Then
        MsgBox "Fatal Error", vbOKOnly, FormName
    Else
        frm.Bookmark = rst.Bookmark
    End If
   
Exit_btnNextForm_Click:
    Exit Sub

Err_btnNextForm_Click:
    MsgBox Err.Description
    Resume Exit_btnNextForm_Click
   
End Sub
0
 
ozphilCommented:
The second argument in  BuildCriteria() needs to be changed to a different value.

Instead of the value dbText in the  BuildCriteria() function, this value should indicate the intended datatype of the third argument, viz Me!Q3000QuoteNumber is numeric in the table not text.

Look up BuildCriteria() Function in your Help file. Its not available in Access 2, although I have made an equivalent function of my own.
The dataType argument is probably something  dbNumeric or dbNumber, dbInteger.
Doing this will get rid of the quotes surround the 6 (for example).

If you need further help please comment.
0
 
edcAuthor Commented:
Thanks for all of your help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now