Error 2237

S

Stacey Crowhurst

Hi! I have a form that is having some coding issues. When I use the copy
record command I get run time error 2237. I deleted the lookup box on my
form and tried the copy record button and had no problems. When I reinserted
the lookup box, the error resumed. Here is all of the code on the form:

Option Compare Database

Private Sub Form_Current()
If Me.NewRecord Or IsNull(Me.prjParentProjectID) Then
Me.prjParentProjectID.Visible = False
Else
Me.prjParentProjectID.Visible = True
End If
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

Private Sub cmdCopyRecord_Click()
On Error GoTo Err_cmdCopyRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Exit_cmdCopyRecord_Click:
Exit Sub
Err_cmdCopyRecord_Click:
MsgBox Err.Description
Resume Exit_cmdCopyRecord_Click
End Sub

Private Sub lkpCCPID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[prjAutoNumberID] = " & Str(Nz(Me![lkpCCPID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The line of code that is highlighted as the problem is: If Not rs.EOF Then
Me.Bookmark = rs.Bookmark.

Any suggestions? Thank you very much!
Stacey
 
D

Daryl S

Stacey -

After a FindFirst, you should test for rs.NoMatch instead of rs.EOF:

If Not rs.NoMatch Then Me.Bookmark - rs.Bookmark

Also, bookmarks are set when the form opens. I don't know if adding new
records will cause problems - maybe you need a requery or refresh after
adding the new records.
 
S

Stacey Crowhurst

Thanks Daryl. I am not really familiar with code at all and have to use the
wizard to do my command buttons and such.

I added the line of code you recommended. Now I get RUN-TIME ERROR 13, TYPE
MISMATCH. This time it shows this part of the code as the problem:

rs.FindFirst "[prjAutoNumberID] = " & Str(Nz(Me![lkpCCPID], 0))

It seems that I am having a problem with my copy record button becuase the
lookup box is there. As you suggested, should I try to requery or refresh
after adding the new records? If so how do I do that?

Thank you very much.
Stacey

Daryl S said:
Stacey -

After a FindFirst, you should test for rs.NoMatch instead of rs.EOF:

If Not rs.NoMatch Then Me.Bookmark - rs.Bookmark

Also, bookmarks are set when the form opens. I don't know if adding new
records will cause problems - maybe you need a requery or refresh after
adding the new records.

--
Daryl S


Stacey Crowhurst said:
Hi! I have a form that is having some coding issues. When I use the copy
record command I get run time error 2237. I deleted the lookup box on my
form and tried the copy record button and had no problems. When I reinserted
the lookup box, the error resumed. Here is all of the code on the form:

Option Compare Database

Private Sub Form_Current()
If Me.NewRecord Or IsNull(Me.prjParentProjectID) Then
Me.prjParentProjectID.Visible = False
Else
Me.prjParentProjectID.Visible = True
End If
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

Private Sub cmdCopyRecord_Click()
On Error GoTo Err_cmdCopyRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Exit_cmdCopyRecord_Click:
Exit Sub
Err_cmdCopyRecord_Click:
MsgBox Err.Description
Resume Exit_cmdCopyRecord_Click
End Sub

Private Sub lkpCCPID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[prjAutoNumberID] = " & Str(Nz(Me![lkpCCPID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The line of code that is highlighted as the problem is: If Not rs.EOF Then
Me.Bookmark = rs.Bookmark.

Any suggestions? Thank you very much!
Stacey
Click to expand...
Click to expand...
 
D

Daryl S

Stacey -

If prjAutoNumberID is really an autonumber, then you should not convert the
lkpCCPID to string. Instead, use this:

rs.FindFirst "[prjAutoNumberID] = " & Nz(Me![lkpCCPID], 0)

The syntax for the requery and refresh for a current form is simple:

Me.Requery
Me.Refresh

--
Daryl S


Stacey Crowhurst said:
Thanks Daryl. I am not really familiar with code at all and have to use the
wizard to do my command buttons and such.

I added the line of code you recommended. Now I get RUN-TIME ERROR 13, TYPE
MISMATCH. This time it shows this part of the code as the problem:

rs.FindFirst "[prjAutoNumberID] = " & Str(Nz(Me![lkpCCPID], 0))

It seems that I am having a problem with my copy record button becuase the
lookup box is there. As you suggested, should I try to requery or refresh
after adding the new records? If so how do I do that?

Thank you very much.
Stacey

Daryl S said:
Stacey -

After a FindFirst, you should test for rs.NoMatch instead of rs.EOF:

If Not rs.NoMatch Then Me.Bookmark - rs.Bookmark

Also, bookmarks are set when the form opens. I don't know if adding new
records will cause problems - maybe you need a requery or refresh after
adding the new records.

--
Daryl S


Stacey Crowhurst said:
Hi! I have a form that is having some coding issues. When I use the copy
record command I get run time error 2237. I deleted the lookup box on my
form and tried the copy record button and had no problems. When I reinserted
the lookup box, the error resumed. Here is all of the code on the form:

Option Compare Database

Private Sub Form_Current()
If Me.NewRecord Or IsNull(Me.prjParentProjectID) Then
Me.prjParentProjectID.Visible = False
Else
Me.prjParentProjectID.Visible = True
End If
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

Private Sub cmdCopyRecord_Click()
On Error GoTo Err_cmdCopyRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Exit_cmdCopyRecord_Click:
Exit Sub
Err_cmdCopyRecord_Click:
MsgBox Err.Description
Resume Exit_cmdCopyRecord_Click
End Sub

Private Sub lkpCCPID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[prjAutoNumberID] = " & Str(Nz(Me![lkpCCPID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The line of code that is highlighted as the problem is: If Not rs.EOF Then
Me.Bookmark = rs.Bookmark.

Any suggestions? Thank you very much!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
S

Stacey Crowhurst

Thanks again Daryl. I changed the code as you suggested.

However, now when I hit the copy record button if there is no text in the
lookup box, strange characters appear in the lookup box (ex: ꇀ౉㰤ᇭ剨థ), but I
don't get an error.

On the other hand, when I hit the copy record button if there is text in the
lookup box (leftover from when I used the lookup box to select a particular
record) then I get RUNTIME ERROR 3070, with this being the bad line of code:
rs.FindFirst "[prjAutoNumberID] = " & (Nz(Me![lkpCCPID], 0))
Also, my lookup box will have the text with a "w" on the end (ex: AMB07-01
turns into AMB07-01w). I am getting so confused! Let me know what you think.

Here is the SQL for the lookup box:
SELECT tblProjects.prjAutoNumberID, tblProjects.prjCCPID
FROM tblProjects INNER JOIN qryProjectMaxSequence ON (tblProjects.prjCCPID =
qryProjectMaxSequence.prjCCPID) AND (tblProjects.prjSequence =
qryProjectMaxSequence.MaxOfprjSequence)
ORDER BY tblProjects.prjCCPID;

Thanks again!!! Stacey



Daryl S said:
Stacey -

If prjAutoNumberID is really an autonumber, then you should not convert the
lkpCCPID to string. Instead, use this:

rs.FindFirst "[prjAutoNumberID] = " & Nz(Me![lkpCCPID], 0)

The syntax for the requery and refresh for a current form is simple:

Me.Requery
Me.Refresh

--
Daryl S


Stacey Crowhurst said:
Thanks Daryl. I am not really familiar with code at all and have to use the
wizard to do my command buttons and such.

I added the line of code you recommended. Now I get RUN-TIME ERROR 13, TYPE
MISMATCH. This time it shows this part of the code as the problem:

rs.FindFirst "[prjAutoNumberID] = " & Str(Nz(Me![lkpCCPID], 0))

It seems that I am having a problem with my copy record button becuase the
lookup box is there. As you suggested, should I try to requery or refresh
after adding the new records? If so how do I do that?

Thank you very much.
Stacey

Daryl S said:
Stacey -

After a FindFirst, you should test for rs.NoMatch instead of rs.EOF:

If Not rs.NoMatch Then Me.Bookmark - rs.Bookmark

Also, bookmarks are set when the form opens. I don't know if adding new
records will cause problems - maybe you need a requery or refresh after
adding the new records.

--
Daryl S


:

Hi! I have a form that is having some coding issues. When I use the copy
record command I get run time error 2237. I deleted the lookup box on my
form and tried the copy record button and had no problems. When I reinserted
the lookup box, the error resumed. Here is all of the code on the form:

Option Compare Database

Private Sub Form_Current()
If Me.NewRecord Or IsNull(Me.prjParentProjectID) Then
Me.prjParentProjectID.Visible = False
Else
Me.prjParentProjectID.Visible = True
End If
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

Private Sub cmdCopyRecord_Click()
On Error GoTo Err_cmdCopyRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Exit_cmdCopyRecord_Click:
Exit Sub
Err_cmdCopyRecord_Click:
MsgBox Err.Description
Resume Exit_cmdCopyRecord_Click
End Sub

Private Sub lkpCCPID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[prjAutoNumberID] = " & Str(Nz(Me![lkpCCPID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The line of code that is highlighted as the problem is: If Not rs.EOF Then
Me.Bookmark = rs.Bookmark.

Any suggestions? Thank you very much!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
D

Daryl S

Stacey -

Can you post the code behind your Copy Button? It appears there is some
connection between the lookup box and the copy button, though I don't think
that is what you want. I assume the lookup box is unbound (that is there is
nothing in the Control Source property). Please include the names of your
form, the lookup box, and the copy button.

--
Daryl S


Stacey Crowhurst said:
Thanks again Daryl. I changed the code as you suggested.

However, now when I hit the copy record button if there is no text in the
lookup box, strange characters appear in the lookup box (ex: ꇀ౉㰤ᇭ剨థ), but I
don't get an error.

On the other hand, when I hit the copy record button if there is text in the
lookup box (leftover from when I used the lookup box to select a particular
record) then I get RUNTIME ERROR 3070, with this being the bad line of code:
rs.FindFirst "[prjAutoNumberID] = " & (Nz(Me![lkpCCPID], 0))
Also, my lookup box will have the text with a "w" on the end (ex: AMB07-01
turns into AMB07-01w). I am getting so confused! Let me know what you think.

Here is the SQL for the lookup box:
SELECT tblProjects.prjAutoNumberID, tblProjects.prjCCPID
FROM tblProjects INNER JOIN qryProjectMaxSequence ON (tblProjects.prjCCPID =
qryProjectMaxSequence.prjCCPID) AND (tblProjects.prjSequence =
qryProjectMaxSequence.MaxOfprjSequence)
ORDER BY tblProjects.prjCCPID;

Thanks again!!! Stacey



Daryl S said:
Stacey -

If prjAutoNumberID is really an autonumber, then you should not convert the
lkpCCPID to string. Instead, use this:

rs.FindFirst "[prjAutoNumberID] = " & Nz(Me![lkpCCPID], 0)

The syntax for the requery and refresh for a current form is simple:

Me.Requery
Me.Refresh

--
Daryl S


Stacey Crowhurst said:
Thanks Daryl. I am not really familiar with code at all and have to use the
wizard to do my command buttons and such.

I added the line of code you recommended. Now I get RUN-TIME ERROR 13, TYPE
MISMATCH. This time it shows this part of the code as the problem:

rs.FindFirst "[prjAutoNumberID] = " & Str(Nz(Me![lkpCCPID], 0))

It seems that I am having a problem with my copy record button becuase the
lookup box is there. As you suggested, should I try to requery or refresh
after adding the new records? If so how do I do that?

Thank you very much.
Stacey

:

Stacey -

After a FindFirst, you should test for rs.NoMatch instead of rs.EOF:

If Not rs.NoMatch Then Me.Bookmark - rs.Bookmark

Also, bookmarks are set when the form opens. I don't know if adding new
records will cause problems - maybe you need a requery or refresh after
adding the new records.

--
Daryl S


:

Hi! I have a form that is having some coding issues. When I use the copy
record command I get run time error 2237. I deleted the lookup box on my
form and tried the copy record button and had no problems. When I reinserted
the lookup box, the error resumed. Here is all of the code on the form:

Option Compare Database

Private Sub Form_Current()
If Me.NewRecord Or IsNull(Me.prjParentProjectID) Then
Me.prjParentProjectID.Visible = False
Else
Me.prjParentProjectID.Visible = True
End If
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

Private Sub cmdCopyRecord_Click()
On Error GoTo Err_cmdCopyRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Exit_cmdCopyRecord_Click:
Exit Sub
Err_cmdCopyRecord_Click:
MsgBox Err.Description
Resume Exit_cmdCopyRecord_Click
End Sub

Private Sub lkpCCPID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[prjAutoNumberID] = " & Str(Nz(Me![lkpCCPID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The line of code that is highlighted as the problem is: If Not rs.EOF Then
Me.Bookmark = rs.Bookmark.

Any suggestions? Thank you very much!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
S

Stacey Crowhurst

Hi Daryl!

COPY BUTTON CODE (I used the wizard):

Private Sub cmdCopyRecord_Click()
On Error GoTo Err_cmdCopyRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Exit_cmdCopyRecord_Click:
Exit Sub
Err_cmdCopyRecord_Click:
MsgBox Err.Description
Resume Exit_cmdCopyRecord_Click
End Sub

Yes, the lookup box is unbound.

NAMES:
form = frmProjects
lookup box = lkpCCPID
copy button = cmdCopyRecord

I appreciate your continued help! Thank you very much :D
Stacey
 
D

Daryl S

Stacey -

You may need to re-populate the lookup box after copy/pasting the record.
After your DoCmd ... Paste Append, add this:
Me.lkpCCPID.requery

See if that helps.
 
S

Stacey Crowhurst

That helps Daryl. As long as I keep the lookup box empty I can copy without
errors or funny characters. Thank you very much!

Stacey
 

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

vba coding 4
"Delete Record" command button woes 4
Msgbox problem 4
Run time error '2001' You cancelled the previous operation 2
Show Access Print Dialog to Ask for a Printer 1
How do I show Microsoft Access Print Dialog 1
Duplicate Record when Required Fields are Present 8
Clear a form 1

Top