Can't figure this out...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Morning everyone.

I am trying to use a subform(in datasheet view) to change the parent form's
record using RecordSetClone.

When you click on the record in the subform it changes the parent form
perfectly but since the subform is linked to the main form the selected
record on the subform always goes back to the 1st record.

I am trying to get the subform to stay on the selected record and not go
back to the 1st record. I have got this to work on my other forms but this
one is giving me trouble for some reason and I can't figure out what the
problem is.

Here is the Function I am using for the OnClick on the subform:

Public Function ChangeRecordSet()
strFind = "[StructureID] = " & Forms!StructureInput![Structures
subform]![StructureID] & " And [AreaID] =" & Forms!StructureInput![Structures
subform]![AreaID]
If Not strFind = "[StructureID] = And [AreaID] =" Then
With Forms!StructureInput.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput.Bookmark = .Bookmark
Forms!StructureInput![Structures subform].Form.Bookmark =
..Bookmark
Else
Beep
End If
End With
Else
Beep
End If
End Function

This line gives the error #3159 - Not a valid Bookmark.

" Forms!StructureInput![Structures subform].Form.Bookmark = .Bookmark "

I have another function that is almost identical that works great.

Public Function ChangeInspRecordSet()
strFind = " [InspID] = " & Forms!StructureInput![Inspections
subform].Form![Inspections subform1]![InspID]
If Not strFind = " [InspID] = " Then
With Forms!StructureInput![Inspections subform].Form.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput![Inspections subform].Form.Bookmark =
..Bookmark
Forms!StructureInput![Inspections
subform]![Inspectionssubform1].Form.Bookmark = .Bookmark
Else
Beep
End If
End With
Else
Beep
End If
End Function

Any suggestions are greatly appreciated!

Thank You
Joel C.
 
When you move the main form to a different record, the subform (assuming
that it's linked to the main form) will be requeried, and the Bookmark
values that the subform had before that move are now invalid for the subform
after the move.

You'll need to store the primary key of the subform's record prior to moving
the main form to a different record, then do a FindFirst on the subform's
RecordsetClone to refind that subform record.
 
Thanks a bunch Ken.

Here is what I changed to make it work:

Public Function ChangeRecordSet()
strFind = "[StructureID] = " & Forms!StructureInput![Structures
subform]![StructureID] & " And [AreaID] =" & Forms!StructureInput![Structures
subform]![AreaID]
If Not strFind = "[StructureID] = And [AreaID] =" Then
With Forms!StructureInput.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput.Bookmark = .Bookmark
With Forms!StructureInput![Structures
subform].Form.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput![Structures
subform].Form.Bookmark = .Bookmark
End If
End With
Else
Beep
End If
End With
Else
Beep
End If
End Function

Joel C.

Ken Snell (MVP) said:
When you move the main form to a different record, the subform (assuming
that it's linked to the main form) will be requeried, and the Bookmark
values that the subform had before that move are now invalid for the subform
after the move.

You'll need to store the primary key of the subform's record prior to moving
the main form to a different record, then do a FindFirst on the subform's
RecordsetClone to refind that subform record.
--

Ken Snell
<MS ACCESS MVP>



Joel said:
Morning everyone.

I am trying to use a subform(in datasheet view) to change the parent
form's
record using RecordSetClone.

When you click on the record in the subform it changes the parent form
perfectly but since the subform is linked to the main form the selected
record on the subform always goes back to the 1st record.

I am trying to get the subform to stay on the selected record and not go
back to the 1st record. I have got this to work on my other forms but this
one is giving me trouble for some reason and I can't figure out what the
problem is.

Here is the Function I am using for the OnClick on the subform:

Public Function ChangeRecordSet()
strFind = "[StructureID] = " & Forms!StructureInput![Structures
subform]![StructureID] & " And [AreaID] =" &
Forms!StructureInput![Structures
subform]![AreaID]
If Not strFind = "[StructureID] = And [AreaID] =" Then
With Forms!StructureInput.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput.Bookmark = .Bookmark
Forms!StructureInput![Structures subform].Form.Bookmark =
.Bookmark
Else
Beep
End If
End With
Else
Beep
End If
End Function

This line gives the error #3159 - Not a valid Bookmark.

" Forms!StructureInput![Structures subform].Form.Bookmark = .Bookmark "

I have another function that is almost identical that works great.

Public Function ChangeInspRecordSet()
strFind = " [InspID] = " & Forms!StructureInput![Inspections
subform].Form![Inspections subform1]![InspID]
If Not strFind = " [InspID] = " Then
With Forms!StructureInput![Inspections subform].Form.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput![Inspections subform].Form.Bookmark =
.Bookmark
Forms!StructureInput![Inspections
subform]![Inspectionssubform1].Form.Bookmark = .Bookmark
Else
Beep
End If
End With
Else
Beep
End If
End Function

Any suggestions are greatly appreciated!

Thank You
Joel C.
Click to expand...
Click to expand...
 
Looks good. May I suggest a slightly different approach for determining
whether to do the find action:


Public Function ChangeRecordSet()
If Len(Forms!StructureInput![Structures subform]![StructureID] & "") > 0
And Len(Forms!StructureInput![Structures subform]![AreaID] & "") > 0 Then
strFind = "[StructureID] = " & Forms!StructureInput![Structures
subform]![StructureID] & " And [AreaID] =" &
Forms!StructureInput![Structures
subform]![AreaID]
With Forms!StructureInput.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput.Bookmark = .Bookmark
With Forms!StructureInput![Structures
subform].Form.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput![Structures
subform].Form.Bookmark = .Bookmark
End If
End With
Else
Beep
End If
End With
Else
Beep
End If
End Function

What I suggest is that you test the content of the controls directly, and if
both are not empty, then do the Find action. (I have used And logic in the
If statement even though your code suggests Or logic -- the reason is that
you'll get an error if one control is empty and the other is not -- the
FindFirst will fail because of an improperly constructed string in the
strFind variable). This avoids using a "string construction" result as a
match -- I don't like to do this where the spaces might be different than
I'd expect, etc. But this is my personal preference....

If you stay with your code, you may wish to change the test so that you
handle the case where one control has a value and the other does not.
--

Ken Snell
<MS ACCESS MVP>



Joel said:
Thanks a bunch Ken.

Here is what I changed to make it work:

Public Function ChangeRecordSet()
strFind = "[StructureID] = " & Forms!StructureInput![Structures
subform]![StructureID] & " And [AreaID] =" &
Forms!StructureInput![Structures
subform]![AreaID]
If Not strFind = "[StructureID] = And [AreaID] =" Then
With Forms!StructureInput.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput.Bookmark = .Bookmark
With Forms!StructureInput![Structures
subform].Form.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput![Structures
subform].Form.Bookmark = .Bookmark
End If
End With
Else
Beep
End If
End With
Else
Beep
End If
End Function

Joel C.

Ken Snell (MVP) said:
When you move the main form to a different record, the subform (assuming
that it's linked to the main form) will be requeried, and the Bookmark
values that the subform had before that move are now invalid for the
subform
after the move.

You'll need to store the primary key of the subform's record prior to
moving
the main form to a different record, then do a FindFirst on the subform's
RecordsetClone to refind that subform record.
--

Ken Snell
<MS ACCESS MVP>



Joel said:
Morning everyone.

I am trying to use a subform(in datasheet view) to change the parent
form's
record using RecordSetClone.

When you click on the record in the subform it changes the parent form
perfectly but since the subform is linked to the main form the selected
record on the subform always goes back to the 1st record.

I am trying to get the subform to stay on the selected record and not
go
back to the 1st record. I have got this to work on my other forms but
this
one is giving me trouble for some reason and I can't figure out what
the
problem is.

Here is the Function I am using for the OnClick on the subform:

Public Function ChangeRecordSet()
strFind = "[StructureID] = " & Forms!StructureInput![Structures
subform]![StructureID] & " And [AreaID] =" &
Forms!StructureInput![Structures
subform]![AreaID]
If Not strFind = "[StructureID] = And [AreaID] =" Then
With Forms!StructureInput.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput.Bookmark = .Bookmark
Forms!StructureInput![Structures subform].Form.Bookmark =
.Bookmark
Else
Beep
End If
End With
Else
Beep
End If
End Function

This line gives the error #3159 - Not a valid Bookmark.

" Forms!StructureInput![Structures subform].Form.Bookmark = .Bookmark "

I have another function that is almost identical that works great.

Public Function ChangeInspRecordSet()
strFind = " [InspID] = " & Forms!StructureInput![Inspections
subform].Form![Inspections subform1]![InspID]
If Not strFind = " [InspID] = " Then
With Forms!StructureInput![Inspections
subform].Form.RecordsetClone
.FindFirst strFind
If .NoMatch = False Then
Forms!StructureInput![Inspections subform].Form.Bookmark
=
.Bookmark
Forms!StructureInput![Inspections
subform]![Inspectionssubform1].Form.Bookmark = .Bookmark
Else
Beep
End If
End With
Else
Beep
End If
End Function

Any suggestions are greatly appreciated!

Thank You
Joel C.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top