edc
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
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.
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.
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
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
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.
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.
ASKER
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(acSysCmdGetObjectSt ate, acForm, FormName) Then
DoCmd.OpenForm FormName
End If
Set frm = Forms(FormName)
Set rst = frm.RecordsetClone
SyncCriteria = BuildCriteria("Q3000QuoteN umber", 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
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(acSysCmdGetObjectSt
DoCmd.OpenForm FormName
End If
Set frm = Forms(FormName)
Set rst = frm.RecordsetClone
SyncCriteria = BuildCriteria("Q3000QuoteN
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
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.
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.
ASKER
Thanks for all of your help!
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.