Access VBA (1 Viewer)

S1LV3RF0X87

Registered User.
Local time
Today, 10:31
Joined
Jul 7, 2017
Messages
26
Hi All

I hope you can help me with a problem that i have struggled with for the past few weeks. I have searched the web and all i can find is people talking about corruption and nothing that helps my situation.

I have an access database running in office 365 that has been split into two ends one end for my tables and the other end for my queries and forms. I have then created an accde file from the front end to allow multiple users into the database at once. This works fine with no issues.

My problem has recently cropped up when combining some coding that i have done with some new code i seem to get the following issue that crops up.

"The expression On Click you entered as the event property setting produced the following error: The command or action 'PasteAppend' isn't available now."

For the life of me i cannot figure out why i seem to be getting this or how to resolve it. I have however managed to drill down and find the cause of this but the answer to why it is doing it still eludes me.

I have a shutdown timer on my main form which has been working perfectly fine until i introduced this new code which is on a separate form. This separate form is where i experience the issue.

Here is the code i run which generates this issue;
Code:
Private Sub Command1849_Click()

If MsgBox("Are you sure you want to process a tariff change against this job order?", vbYesNo + vbQuestion) = vbYes Then

Me.Deactivate = True
Me.Deactivation_Date = ""
Me.Deactivation_Date = Date
Me.Deactivation_Reason = ""
Me.Deactivation_Reason = "Tariff Change"
Me.Deactivated_By = ""
Me.Deactivated_By = Me.Deactivated_By & Environ("username") & Me.Deactivated_By

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy

DoCmd.RunCommand acCmdPasteAppend



    
Me.Deactivate = False
Me.FulfilledDate = ""
Me.ContractTermMonths = 1
Me.OrderStatus = ""
Me.OrderStatus = 1
Me.ContractType = "Resign Business"

Else

End If

End Sub

Now when the error happens the debugger pops up and highlights the following section;

Code:
DoCmd.RunCommand acCmdPasteAppend

I cannot seem to figure out the reason for it.

However when i remove the On Timer function from my main form and then retry the above code it works perfectly.

Some how i cannot seem to have this code run in line with the shut down function of the database after an X amount of time has passed.

Any thoughts or ideas of what i need to do with this would be appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:31
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a thought, but maybe when the timer fires, the focus gets diverted/directed somewhere else and so the PasteAppend is out of context by then. Maybe?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:31
Joined
May 21, 2018
Messages
8,463
Use an append query instead of copy past.
 

S1LV3RF0X87

Registered User.
Local time
Today, 10:31
Joined
Jul 7, 2017
Messages
26
Hi. Just a thought, but maybe when the timer fires, the focus gets diverted/directed somewhere else and so the PasteAppend is out of context by then. Maybe?

I like your thinking and think you may be correct with why this is happening.

In that case if i set the focus of the form in question when running this code in theory it might work?

Do you know how i would set the focus to try this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:31
Joined
Oct 29, 2018
Messages
21,358
I like your thinking and think you may be correct with why this is happening.

In that case if i set the focus of the form in question when running this code in theory it might work?

Do you know how i would set the focus to try this?
You could try:


Forms!FormName.SetFocus


Hope it works. Let us know how it goes...


If it doesn't work, then I would also suggest you look into what MajP suggested about using an APPEND query. Cheers!
 

S1LV3RF0X87

Registered User.
Local time
Today, 10:31
Joined
Jul 7, 2017
Messages
26
Use an append query instead of copy past.

I have not really dabbled in append queries but the reason for the copy and paste function is i need to duplicate the record and then change some fields after the duplication.

This needs to be as easy as a 1 click function for ease of use as the people who will be using the system will not be tech savy and need to reduce the amount of user error as much as possible.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
BTW, if you are assigning new values to variables/controls, you do not need to clear them out first?

Code:
Me.Deactivation_Date = Date
Me.Deactivation_Reason = "Tariff Change"
Me.Deactivated_By = Me.Deactivated_By & Environ("username") & Me.Deactivated_By

would work just as well, and less typing to do. :)

HTH
 

S1LV3RF0X87

Registered User.
Local time
Today, 10:31
Joined
Jul 7, 2017
Messages
26
You could try:


Forms!FormName.SetFocus


Hope it works. Let us know how it goes...


If it doesn't work, then I would also suggest you look into what MajP suggested about using an APPEND query. Cheers!


Okay very interesting here when i set the focus i get a new problem.

I get the following;

Run-time error '2226':

The Clipboard isn't responding, so Access Database can't paste the Clipboard's contents.

Any ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:31
Joined
Oct 29, 2018
Messages
21,358
Okay very interesting here when i set the focus i get a new problem.

I get the following;

Run-time error '2226':

The Clipboard isn't responding, so Access Database can't paste the Clipboard's contents.

Any ideas?
Yeah, I would probably stop chasing this and work on a more stable approach like using an APPEND query. Does your table have a Primary Key field?
 

S1LV3RF0X87

Registered User.
Local time
Today, 10:31
Joined
Jul 7, 2017
Messages
26
Yes the table has a Primary Key field, I am just struggling to see how an Append Query will work in this situation that i am trying to do.

Is there some code i can use to run the APPEND Query from VBA?

Sorry but when it comes to VBA & APPEND Queries am learning as i go along.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:31
Joined
Oct 29, 2018
Messages
21,358
Yes the table has a Primary Key field, I am just struggling to see how an Append Query will work in this situation that i am trying to do.

Is there some code i can use to run the APPEND Query from VBA?

Sorry but when it comes to VBA & APPEND Queries am learning as i go along.
Yes, you can execute action queries using VBA. How many fields are you duplicating? The SQL syntax will probably look similar to this.
Code:
INSERT INTO TableName (Field1, Field2, Field3, etc.)
SELECT Field1, Field2, Field3, etc. FROM TableName WHERE ID=CurrentIDHere
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:31
Joined
May 21, 2018
Messages
8,463
To add a little more to DbGuy's post. Probably something like this:

Code:
dim strSql as string
strSql = "INSERT INTO TableName (Field1, Field2, Field3, etc.) SELECT Field1, Field2, Field3, etc. FROM TableName WHERE ID = " & me.SomeIDfield
debug.print strSql
currentDb.execute strSql
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:31
Joined
Feb 19, 2002
Messages
42,971
1. Why are you setting the value of fields multiple times? There is no reason to set a field to a ZLS and then set it to an actual value.
2. The error may be caused because you are dirtying the current record but not saving it before you try the copy and paste.

As the others have suggested, an append query is a better method in any event.
 

Users who are viewing this thread

Top Bottom