Paste append to form dialogue work around

[pubdude2003]

Hey all....

I am getting a dialogue that says

"None of the field names you pasted onto the Clipboard match the field names
on the form."

When I paste records from the clipboard to a form I've created. The paste
works fine, I just want to kill the dialogue so that my less than bright
clientele don't call me to ask me whether they should click Yes or No. The
err.number (error... well it's not technically is it?) is 8512 but this code
isn't killing the dialogue.... (and now on further testing the six sites that
said this error was 8512 were fibbing a bit, the err.number isn't 8512 which
may well be why the code is not trapping... sigh.. anywaaaaay)

Err_Command7_Click:
If Err.Number = 8512 Then
Resume Next
End If
MsgBox Err.Description & Err.Number
Resume Exit_Command7_Click

Thoughts anyone?

 

[Mark A. Sam]

Try this:

Err_Command7_Click:
If Err.Number = 8512 Then
Resume Next
Else
MsgBox Err.Description & Err.Number
Resume Exit_Command7_Click
End If

The way you wrote it, the message box will still display, even though you
said Resume next.

God Bless,

Mark A. Sam

 

[Douglas J. Steele]

I'd agree that your approach is better, Mark, but the Resume Next should
send program execution back to the line after that line that caused the
error.

Take a look at this test routine:

Sub Test()
On Error GoTo Err_Test

Dim sngQuotient As Single

Debug.Print "This is before the error."
sngQuotient = 1 / 0
Debug.Print "This is after the error."

End_Test:
Exit Sub

Err_Test:
If Err.Number = 11 Then
Debug.Print "Error number 11"
Resume Next
End If
Debug.Print "This is in the error handler."
Resume End_Test

End Sub

Here's what I see when I run it:

This is before the error.
Error number 11
This is after the error.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this:

Err_Command7_Click:
If Err.Number = 8512 Then
Resume Next
Else
MsgBox Err.Description & Err.Number
Resume Exit_Command7_Click
End If

The way you wrote it, the message box will still display, even though you
said Resume next.

God Bless,

Mark A. Sam

 

[Mark A. Sam]

Doug,

If you rem out the End_Test section, it will go to the

Debug.Print "This is in the error handler."

on the next pass. I didn't see where pubdude has an exit sub statement. I
tried it with my modification and the second debug print printed. I think
his problem is the lack of an exit sub. I have done that myself a few
times.

Sub Test()

On Error GoTo Err_Test

Dim sngQuotient As Single

Debug.Print "This is before the error."
sngQuotient = 1 / 0
Debug.Print "This is after the error."

'End_Test:
' Exit Sub

Err_Test:
If Err.Number = 11 Then
Debug.Print "Error number 11"
Resume Next
End If
Debug.Print "This is in the error handler."
' Resume End_Test

End Sub

I'd agree that your approach is better, Mark, but the Resume Next should
send program execution back to the line after that line that caused the
error.

Take a look at this test routine:

Sub Test()
On Error GoTo Err_Test

Dim sngQuotient As Single

Debug.Print "This is before the error."
sngQuotient = 1 / 0
Debug.Print "This is after the error."

End_Test:
Exit Sub

Err_Test:
If Err.Number = 11 Then
Debug.Print "Error number 11"
Resume Next
End If
Debug.Print "This is in the error handler."
Resume End_Test

End Sub

Here's what I see when I run it:

This is before the error.
Error number 11
This is after the error.

 

[Douglas J. Steele]

I don't see where you have an Exit Sub in what you proposed!

You're absolutely right that an Exit Sub is required before the error
handler. VBA has no way of knowing that it's not supposed to execute that
code if there isn't an error.

I suppose you could use the following instead, but there's no point:

Err_Command7_Click:
Select Case Err.Number
Case 0, 8512
Resume Next
Case Else
MsgBox Err.Description & Err.Number
Resume Exit_Command7_Click
End Select

 

[pubdude2003 via AccessMonster.com]

Hi guys, sorry my enable alerts seemed to be on holiday... I feel a little
left out of this dialogue.

Sorry my snippet was too short... here's the code... unfortunately it stills
doesn't trap the dialogue box. As my initial post mentioned, technically not
an error so it doesn't trap?

The whole thing:

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

MsgBox "A dialogue box will appear, please click yes", vbOKOnly, "xxxx"
DoCmd.RunCommand acCmdPasteAppend

Me.Refresh

Exit_Command7_Click:
Exit Sub


Err_Command7_Click:
If Err.Number = 8512 Then
Resume Next
Else
MsgBox Err.Description & Err.Number
Resume Exit_Command7_Click
End If

End Sub

 

[Mark A. Sam]

Dude (I always wanted to call someone that)

What version of Access are you using? I created a table in Access 2007 with
fields and associated form textboxes, [CopyFrom] and [Pasteto]. I placed
buttons on the form, one to copy the record and one to paste onto a new
record. I didn't get an error. Here is the code. Maybe this will help.

On the copy button (Command2) this code in the Click Event:

Private Sub Command2_Click()

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy

End Sub

On the paste button (Command3) this code in the Click Event:

Private Sub Command3_Click()

DoCmd.RunCommand acCmdPasteAppend

End Sub

I hope that helps.

God Bless,

Mark A. Sam

 

[pubdude2003 via AccessMonster.com]

Thanks Mark... and God bless ya right back!

Perhaps you've hit on something. I am trying to build something that pastes
directly into a form and therefor that goofie message but perhaps I should do
this the proper way. Append directly to the table and then refresh the form
to show the new records...

let me give that a try... I'm always trying to short cut stuff and I am
always in a quandry as to why these 'short cuts' don't work!!!!

 

[Mark A. Sam]

Dude,

I pasted to the form. Maybe the problem is that you didn't select the
record before you copied it.

God Bless,

Mark

 

[pubdude2003 via AccessMonster.com]

Actually you won't get the dialogue if you're only pasting a single record...
it only appears if there are multiple records on the clipboard

 

[Mark A. Sam]

I've only used this command once I think to copy and paste from and to
textboxes, not to transfer records. I used queries or DAO. I don't know if
it is designed for multiple records. Information about the runcommand
object seems to be one of Microsofts best kept secrets...lol.

 

[pubdude2003 via AccessMonster.com]

I hear ya!!!

But users want something that they're used to and the copy and paste should
be a bit more 'seamless'

The current solution should work... they get a warning and an instruction...
so ... oh, well!