append records
RH
Underground Strikes 26th April 2024
Hide
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Access Training and help » Append Records

Append Records

resolvedResolved · High Priority · Version 2010

Joanne has attended:
Access VBA course

Append Records

I cant find an notes in the booklets for a step by step instructions to Append Records. Is this possible for you to email these steps please?

RE: Append Records

Hi Joanne
In the course we first created a copy of a table called Orders.
When you paste select Structure Only to create a blank table.
We called the new table OrdersCompleted.
Then to create the Append query select Create, Query Design.
Add the Orders table and double click the title of the field list, select all fields.
Change the query type to !Append.
Add a criteria Yes in the Order Complete field
To run the query press Run!

Hope that helps. There is an OrderingdbComplete database as well.

Regards
Doug
Best STL


RE: Append Records

Hi Joanne
In the course we first created a copy of a table called Orders.
When you paste select Structure Only to create a blank table.
We called the new table OrdersCompleted.
Then to create the Append query select Create, Query Design.
Add the Orders table and double click the title of the field list, select all fields.
Change the query type to !Append.
Add a criteria Yes in the Order Complete field
To run the query press Run!

Hope that helps. There is an OrderingdbComplete database as well.

Regards
Doug
Best STL


RE: Append Records

Hi Doug thanks for your reply I remembered how to get this far after I requested help from you but now I realise in the lesson the record that got appeneded had a tick box and if that box was Yes/True then that got appended. My issue is that I dont want my appended records to be deleted and only the current record to be appended if a control box contains a message? Do you have any ideas on how I could get round this please.
Thanks
Jo

RE: Append Records

Hi again Joanne

An Append query won't delete the record, only copy it to a different table. As long as you don't run a Delete query as well as an Append Query.

If the criteria for appending is that a field contains a comment type IS NOT NULL into the criteria line for that field in the Append query.

Let me know if that makes sence or say a bit more about your example.

Regards
Doug
Best STL

RE: Append Records

Hi Doug I know to not have a delete query and all though I can say 'If Comment box = 3' then run the Append query but what happens it still looks for all comments that = 3. I think maybe I need a Me! line but I dont know how to write it correctly ? What do you think?
Jo

RE: Append Records

Hi Jo

Happy new year!
As you say adding 3 to the Criteria in the query will append all records that have that value.
Adding a second criteria in a field such as EntryDate would only append records entered on the current date with Control = 3.

I will reply again if I find a way of appending only the current record.

Regards
Doug

RE: Append Records

Hi Jo

I've found a way to append the current record rather than all records that match a criteria (eg a control = "3") in the current record.

Using a form based on my Order table here is the On Click event code for a button called cmdAppendRec

Sub cmdAppendRec_Click()

DoCmd.RunCommand acCmdSaveRecord
If OrderComplete = True Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.OpenTable "tblCompletedOrders", acViewNormal, acEdit
DoCmd.GoToRecord , "", acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
End If

End Sub


In your example you would put IF Control = "3" Then ...

I started with using macro commands then converted to VBA.
If you prefer macros here are the comands I used -

RunMenuCommand
SaveRecord
IF [OrderComplete] = True
RunMenuCommand
SelectRecord
RunMenuCommand
Copy
OpenTable
tblOrdersComplete
GotoRecord
New
RunMenuCommand
SelectRecord
RunMenuCommand
Paste
Endif

Hope that helps for now!
Doug

RE: Append Records

Hi Doug thanks for your help, erm the macro is not giving the option for copy or paste ?

RE: Append Records

Hi
For Copy and Paste
Use RunMenuCommand and click on Show All Actions button on the design ribbon.

Doug
Best STL

RE: Append Records

Sorry Doug show all actions is greyed out

RE: Append Records

Arr..
Not sure why that is. You may have to write the procedure in VBA for now until the Show all action issue is resolved. Take a look at my last but one reply. To select and copyv the current record you will be typing:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy

It will be good vba practice!

Doug

RE: Append Records

Hi Doug still no joy this is what I have typed and it stops on: DoCmd.RunCommand acCmdPaste with Runtime error 2046 the command Action or Paste isn't available now.



Private Sub cmdAppendRec_Click()
DoCmd.RunCommand acCmdSaveRecord
If Comment = 3 Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.OpenTable "AppendedTalyformData", acViewNormal, acEdit
DoCmd.GoToRecord , "", acNewRec
DoCmd.RunCommand acCmdPaste
End If
End Sub

RE: Append Records

Hi Jo

Sorry for taking a while to reply. I've been on site training.

Try adding the following line

DoCmd.RunCommand acCmdSelectRecord

after DoCmd.GoToRecord , "", acNewRec

The blank record has to be selected before data can be pasted.

Cheers
Doug

RE: Append Records

Hi Doug its nearly there but its not coping the values it just a blank record with zeros.
I noticed in the code that a copy command is not there so I tried that but that doesnt work either, so far i have this code:

Private Sub cmdAppendRec_Click()
DoCmd.RunCommand acCmdSaveRecord
If Comment = 3 Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.OpenTable "AppendedTalyformData", acViewNormal, acEdit
DoCmd.GoToRecord , "", acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
End If
End Sub

Then I removed "DoCmd.RunCommand acCmdSelectRecord" but that said Paste was not available?
So I dont know how to get the code to paste the values?

RE: Append Records

Hi Jo

I'm sending you my example called OrderingDatabseComplete. It seems to work with this example with or without the line for saving the record.

The button is on frmCustomer. As in your one the current record is copied if Comment = 3.

Hope this helps you find out why the same code doesn't work on your example.

Doug

Attached files...

OrderingdbComplete.zip

RE: Append Records

Hi Doug I got some help and this does the trick! Thanks for your help..

Dim strSQL As String

If Me.Comment = 3 Or 4 Then
strSQL = _
"INSERT INTO AppendedTalyformData " & _
"SELECT * " & _
"FROM [Enter Talyform Data] " & _
"WHERE CalculationID = " & Me.CalculationID

' ensure current record is saved
Me.Dirty = False
' execute SQL statement
CurrentDb.Execute strSQL, dbFailOnError
End If

RE: Append Records

Thanks Jo.
Doug
Best STL

Thu 30 Jan 2014: Automatically marked as resolved.

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Access articles

Please browse our web site to find out more about
access 2003 training and other training courses.

Access tip:

Change case

To change case from upper or lower case, highlight the text area, hold down the Shift key and pres F3

View all Access hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.05 secs.