Solved

Controlling the current record on a form

Posted on 1997-08-16
10
229 Views
Last Modified: 2006-11-17
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
Comment
Question by:edc
  • 7
  • 3
10 Comments
 
LVL 4

Accepted Solution

by:
ozphil earned 100 total points
Comment Utility
By setting the dynaset bookmark of the second form and refreshing.

I shall elaborate as a comment to follow shortly.
0
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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
 
LVL 1

Author Comment

by:edc
Comment Utility
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
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 4

Expert Comment

by:ozphil
Comment Utility
  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
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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
 
LVL 1

Author Comment

by:edc
Comment Utility
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
 
LVL 4

Expert Comment

by:ozphil
Comment Utility
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
 
LVL 1

Author Comment

by:edc
Comment Utility
Thanks for all of your help!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now