Bookmark issues

G

Guest

I have a form with a subform in it. When a tool number is entered in the
subform, if it already exists in the table, I want that record to be returned
to the subform to populate the remaining fields in that subform. I am having
a problem with my code... I get an error on the Me.Bookmark = rsc.Bookmark
line. The tooltip says Me.Bookmark = <No Current Record.>

Anyone have any suggestions for me?

Thanks a bunch!
Seren

Private Sub ToolNum_BeforeUpdate(Cancel As Integer)
Dim TNum As String
Dim stDup As String
Dim rsc As dao.Recordset

Set rsc = Form_subTool.RecordsetClone

TNum = Form_subTool.ToolNum.Value
stDup = "ToolNum = " & "'" & TNum & "'"

' check if toolnum already exists in tblTool
If DCount("ToolNum", "tblTool", stDup) > 0 Then
' clear toolNum field
Me.Undo
' set form to existing matching record
rsc.FindFirst stDup
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
 
G

Guest

You should always check to see if you found a match before you move a bookmark.

rsc.FindFirst stDup
If Not rsc.NoMatch Then
Me.Bookmark = rsc.Bookmark
Else
'Whatever you want to do if a match was not found
End If
 
G

Guest

I see your point. One thing I do notice. Is Form_SubTool the sub form? If
so, it should be qualified with the parent form name. I can't debug it from
here, so I can't see what the real problem is; however, the DCount is
redundant and will slow your response. I suggest you try this modification
and see what happens:

Private Sub ToolNum_BeforeUpdate(Cancel As Integer)
Dim rsc As dao.Recordset

Set rsc = Forms!MainFormName!Form_subTool.RecordsetClone

' set form to existing matching record
rsc.FindFirst "[ToolNum] = '" & _
Forms!MainFormName!Form_subTool.ToolNum & "'"
If rsc.NoMatch Then
Cancel = 0
MsgBox "Tool Not Found"
Else
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
 
G

Guest

With that, it switches the error from the "me.bookmark" line to the " set rsc
= " and tooltip says "rsc = Nothing"
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


Klatuu said:
I see your point. One thing I do notice. Is Form_SubTool the sub form? If
so, it should be qualified with the parent form name. I can't debug it from
here, so I can't see what the real problem is; however, the DCount is
redundant and will slow your response. I suggest you try this modification
and see what happens:

Private Sub ToolNum_BeforeUpdate(Cancel As Integer)
Dim rsc As dao.Recordset

Set rsc = Forms!MainFormName!Form_subTool.RecordsetClone

' set form to existing matching record
rsc.FindFirst "[ToolNum] = '" & _
Forms!MainFormName!Form_subTool.ToolNum & "'"
If rsc.NoMatch Then
Cancel = 0
MsgBox "Tool Not Found"
Else
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub


Seren said:
But if there is not a match, it shouldn't be called anyway...
Click to expand...
Click to expand...
 
G

Guest

Are you trying to set it to the current form's recordsetclone. You are not
clear on which form you are working. If it is the current form, all you need
is Me.RecordsetClone.

Seren said:
With that, it switches the error from the "me.bookmark" line to the " set rsc
= " and tooltip says "rsc = Nothing"
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


Klatuu said:
I see your point. One thing I do notice. Is Form_SubTool the sub form? If
so, it should be qualified with the parent form name. I can't debug it from
here, so I can't see what the real problem is; however, the DCount is
redundant and will slow your response. I suggest you try this modification
and see what happens:

Private Sub ToolNum_BeforeUpdate(Cancel As Integer)
Dim rsc As dao.Recordset

Set rsc = Forms!MainFormName!Form_subTool.RecordsetClone

' set form to existing matching record
rsc.FindFirst "[ToolNum] = '" & _
Forms!MainFormName!Form_subTool.ToolNum & "'"
If rsc.NoMatch Then
Cancel = 0
MsgBox "Tool Not Found"
Else
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub


Seren said:
But if there is not a match, it shouldn't be called anyway...
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


:

You should always check to see if you found a match before you move a bookmark.

rsc.FindFirst stDup
If Not rsc.NoMatch Then
Me.Bookmark = rsc.Bookmark
Else
'Whatever you want to do if a match was not found
End If

:

I have a form with a subform in it. When a tool number is entered in the
subform, if it already exists in the table, I want that record to be returned
to the subform to populate the remaining fields in that subform. I am having
a problem with my code... I get an error on the Me.Bookmark = rsc.Bookmark
line. The tooltip says Me.Bookmark = <No Current Record.>

Anyone have any suggestions for me?

Thanks a bunch!
Seren

Private Sub ToolNum_BeforeUpdate(Cancel As Integer)
Dim TNum As String
Dim stDup As String
Dim rsc As dao.Recordset

Set rsc = Form_subTool.RecordsetClone

TNum = Form_subTool.ToolNum.Value
stDup = "ToolNum = " & "'" & TNum & "'"

' check if toolnum already exists in tblTool
If DCount("ToolNum", "tblTool", stDup) > 0 Then
' clear toolNum field
Me.Undo
' set form to existing matching record
rsc.FindFirst stDup
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Actually, that was one question I have. When I'm working with a subform,
should I be setting those properties on the subform itself or the subform
property within the main form? I don't know if that even makes sense. I've
been thinking about it too long it's all starting to run together and not
make sense.

So, after speaking with someone here... i tried switching the form with the
subform. He said something about the way it was looking for multiple tool
numbers for each job number. (Job was main form, tool was subform). So, I
switched it, made tool main form, job subform. So it's now working... only--
backwards.

You can enter a tool number and get the first Job that goes with that...
with navigation buttons to seemingly allow you to flip through each
associated tool for that job. But when you try to go to the next job for
that tool, it all goes blank.

what I need to happen is for the user to type in the Job number and get the
*one* associated tool number. This is slowly but surely driving me crazy. :p
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


Klatuu said:
Are you trying to set it to the current form's recordsetclone. You are not
clear on which form you are working. If it is the current form, all you need
is Me.RecordsetClone.

Seren said:
With that, it switches the error from the "me.bookmark" line to the " set rsc
= " and tooltip says "rsc = Nothing"
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


Klatuu said:
I see your point. One thing I do notice. Is Form_SubTool the sub form? If
so, it should be qualified with the parent form name. I can't debug it from
here, so I can't see what the real problem is; however, the DCount is
redundant and will slow your response. I suggest you try this modification
and see what happens:

Private Sub ToolNum_BeforeUpdate(Cancel As Integer)
Dim rsc As dao.Recordset

Set rsc = Forms!MainFormName!Form_subTool.RecordsetClone

' set form to existing matching record
rsc.FindFirst "[ToolNum] = '" & _
Forms!MainFormName!Form_subTool.ToolNum & "'"
If rsc.NoMatch Then
Cancel = 0
MsgBox "Tool Not Found"
Else
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub


:

But if there is not a match, it shouldn't be called anyway...
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


:

You should always check to see if you found a match before you move a bookmark.

rsc.FindFirst stDup
If Not rsc.NoMatch Then
Me.Bookmark = rsc.Bookmark
Else
'Whatever you want to do if a match was not found
End If

:

I have a form with a subform in it. When a tool number is entered in the
subform, if it already exists in the table, I want that record to be returned
to the subform to populate the remaining fields in that subform. I am having
a problem with my code... I get an error on the Me.Bookmark = rsc.Bookmark
line. The tooltip says Me.Bookmark = <No Current Record.>

Anyone have any suggestions for me?

Thanks a bunch!
Seren

Private Sub ToolNum_BeforeUpdate(Cancel As Integer)
Dim TNum As String
Dim stDup As String
Dim rsc As dao.Recordset

Set rsc = Form_subTool.RecordsetClone

TNum = Form_subTool.ToolNum.Value
stDup = "ToolNum = " & "'" & TNum & "'"

' check if toolnum already exists in tblTool
If DCount("ToolNum", "tblTool", stDup) > 0 Then
' clear toolNum field
Me.Undo
' set form to existing matching record
rsc.FindFirst stDup
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error on first keystroke 4
search for existing record 1
duplicate fields 20
second form opens w/ no records 1
if duplicate goto record 3
Lookup record is not working 1
Create Table 2
Duplicate Values 3

Top