Solved

Controlling the current record on a form

Posted on 1997-08-16
10
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 4

Accepted Solution

by:
ozphil earned 100 total points
ID: 1955684
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
ID: 1955685
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
ID: 1955686
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:edc
ID: 1955687
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
ID: 1955688
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
 
LVL 4

Expert Comment

by:ozphil
ID: 1955689
  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
ID: 1955690
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
ID: 1955691
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
ID: 1955692
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
ID: 1955693
Thanks for all of your help!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
unknown computers in my LAN 12 100
Combo box question 6 55
Outlook mail to Access 8 29
Unidentified Function 2 31
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

751 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