98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Append Records
Append Records
Resolved · High Priority · Version 2010
Fri 6 Dec 2013, 10:17Delegate Joanne said...
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?
Sat 7 Dec 2013, 13:04Trainer Doug said...
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
Sat 7 Dec 2013, 13:04Trainer Doug said...
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
Mon 9 Dec 2013, 06:45Delegate Joanne said...
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
Mon 9 Dec 2013, 13:26Trainer Doug said...
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
Tue 10 Dec 2013, 11:45Delegate Joanne said...
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
Tue 7 Jan 2014, 11:31Trainer Doug said...
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
Thu 9 Jan 2014, 12:23Trainer Doug said...
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
Thu 9 Jan 2014, 16:10Delegate Joanne said...
RE: Append Records
Hi Doug thanks for your help, erm the macro is not giving the option for copy or paste ?
Thu 9 Jan 2014, 16:21Trainer Doug said...
RE: Append Records
Hi
For Copy and Paste
Use RunMenuCommand and click on Show All Actions button on the design ribbon.
Doug
Best STL
Thu 9 Jan 2014, 16:29Delegate Joanne said...
RE: Append Records
Sorry Doug show all actions is greyed out
Thu 9 Jan 2014, 16:54Trainer Doug said...
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
Fri 10 Jan 2014, 06:58Delegate Joanne said...
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
Fri 17 Jan 2014, 09:58Trainer Doug said...
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
Fri 17 Jan 2014, 12:30Delegate Joanne said...
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?
Fri 17 Jan 2014, 14:46Trainer Doug said...
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.zipMon 20 Jan 2014, 18:08Delegate Joanne said...
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
Thu 23 Jan 2014, 10:40Trainer Doug said...
RE: Append Records
Thanks Jo.
Doug
Best STL
Thu 30 Jan 2014: Automatically marked as resolved.
Training information:
See also:
|
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Change caseTo change case from upper or lower case, highlight the text area, hold down the Shift key and pres F3 |
