Copy and paste a record in same table - Access/VBA

Solved/Closed

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018 - Aug 14, 2018 at 10:09 AM
PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018 - Aug 29, 2018 at 09:43 AM

Hello,

This might be more complex than the title suggests...

I have a task tracker that records; tasks, date recorded, frequency (d,ww,m,q,yyyy) and period (#). When I complete a task (all fields populated for a record, done using a complete task form and button), I would like a new record created with all the same details as the current record, with the exception of the date recorded, which should now be the date the record being copied was completed (which is in a date completed field).

Hope that made sense...Anyway would really appreciate any guidance on how to go about this.

I have seen one example online but was very complex and required various levels of nested tables (http://allenbrowne.com/AppRecur.html so something simpler would be great.

Thanks.

Access vba copy record in same table
Ms access download - Download - Databases
Access and downloading - Facebook Forum
How to download voice record in messenger - Guide
How to send voice record in messenger - Guide
How to gain access to my hacked facebook account - Facebook Forum

2 responses

Answer 1 / 2

Blocked Profile
Aug 14, 2018 at 05:03 PM

Preload the form with the previous info, all but the date. Query the default values (previously entered) and load them into the form, on FORM LOAD.

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Aug 15, 2018 at 03:49 AM

Thanks for the quick response.

I am still very new to access, please can you explain in a bit more detail what you are suggesting?

Cheers

Blocked Profile
Updated on Aug 15, 2018 at 04:16 PM

Find the Form, and select it in the project explorer. Open up VBA console. Select the form
Ok, there is an event called LOAD. Look at posted image:

Then after you select FOrm, in the second drop down, select LOAD.

As in:

After you press load, a new sub will appear called "form_load()". This will be one of the first things the form will do any time it loads. So, when it loads, you prepopulate the textbox with your predetermined values. As in:

Private Sub form_load()

Text67.Value = 0
Text71.Value = 0

End Sub

The above script will set the two textbox to 0. This is an example of preloading values. There is other ways(through cache and such, but this is the easiest least complex method of doing so)!

Start with displaying a message first, and closing and opeing the form, to see what VBA thinks is loading and closing. Just because something is not visible, does not mean it has been CLOSED. So, make certain your method of LOAD is correct. Look at the drop down, those are all of the different events that can be triggered on!

Keep up the work, before you know your relationships will look as clustered as this:

LOL Have FUN!

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Aug 23, 2018 at 06:24 AM

Hi,

Thanks for the response...I couldn't use form load because I'm using the subform (image below), but I did go with the idea to take the the existing fields and load them into variables, with the plan to create a new record with these variables. Unfortunately I am getting a syntax error on my INSERT coding and was wondering if you can think what is wrong with it? I have tested all of the variables and the values are as required.

Dim DateRecorded As Date
Dim RaisedBy As String
Dim Facility As String
Dim Machine As String
Dim Frequency As String
Dim Period As Integer
Dim Description As String

DateRecorded = Date
RaisedBy = Forms.FrmCompletePM.SubCompletePM.Form.[Raised By]
Facility = Forms.FrmCompletePM.SubCompletePM.Form.[Facility]
Machine = Forms.FrmCompletePM.SubCompletePM.Form.[Machine]
Frequency = Forms.FrmCompletePM.SubCompletePM.Form.[Frequency]
Period = Forms.FrmCompletePM.SubCompletePM.Form.[Period]
Description = Forms.FrmCompletePM.SubCompletePM.Form.[Description]

DoCmd.RunSQL "INSERT INTO PlannedMaintenance(DateRecorded, RaisedBy, Facility, Machine, Frequency, Period, Description)"

Blocked Profile
Updated on Aug 23, 2018 at 05:02 PM

Good deal!

A properly syntaxed SQL statement is in the following format:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN). SO in your case, you need (notice the quote structure):
"INSERT INTO PlannedMaintenance (column1, column2, column3,...columnN) " & _
"VALUES (" & DateRecorded & ", " & _
RaisedBy & ", " & _
Facility & ", " & _
Machine & ", " & _
Frequency & ", " & _
Period & ", " &_
Description & ");"

Or....

"INSERT INTO PlannedMaintenance (column1, column2, column3,...columnN) VALUES (" & DateRecorded & ", " & RaisedBy & ", " & Facility & ", " & Machine & ", " & Frequency & ", " & Period & ", " &Description & ");"

Of course, you have to treat each column as its data type. So in other words, text needs to have quotes around them, which can be tricky as you have to get the statement to print out the quotes, and not escape them. Format the SQL Statement first , then we will start looking at structure of the data types if you run into issues. Move forward.

Now the SQL script has the variables in place! Run a variable like this:


SQLStatement = "INSERT INTO PlannedMaintenance (column1, column2, column3,...columnN) VALUES (" & DateRecorded & ", " & RaisedBy & ", " & Facility & ", " & Machine & ", " & Frequency & ", " & Period & ", " &Description & ");"
msgbox(SQLStatement)
DoCmd.RunSQL SQLStatement

Now it will dislpay the statemtn so that you can troubleshoot the first couple of entries. After you are confident in what the query produces, REM out the MSGBOX!

I hope you are learning!

Have fun!

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Aug 24, 2018 at 06:01 AM

I am learning a lot for for sure, thanks to this new project and the advice you have given me, so thanks.

So, I have implemented the changes discussed above...it seems to be reacting well to a lot of the fields, however there are a couple things...

First, the output for Facility and Machine, which are drawn from related tables (facilityList and assetList) are only showing the IDs.

Second, the Description field is causing an error...Not sure if this is because the table field type is Long Text. I'm assuming I don't have to declare an insert for any fields I want to be blank?

Couple images attached to help...

Thanks

Answer 2 / 2

Blocked Profile
Aug 24, 2018 at 04:57 PM

OK, the IDS are because you really only want to use the least amount of storage space. You can set it to display what ever colum you wish, by setting the INDEX to the column you wish. I will try to find an example of this.

The query is failing because TEST 7 looks to be a text field, and you have not wrapped it in quotes. This is where it gets tough, because you have to use a combonation of single and double quotes. So, and example of the syntax is:

thecolor = "red"
testString = "None of the balloons popped, but the '" & thecolor & "' ones!"

Please notice the [']["]. YOu may also need to stack double quotes in order to get it to take, so that would be:

thecolor = "red"
testString = "None of the balloons popped, but the """ & thecolor & """ ones!"

That is ["]["]["]. The first two quotes tell VBA to use a double quote, and the third ESCAPES the string for the variable!

I hope this gets you over this one. I will look for index examples shortly!

Have FUN!

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Aug 29, 2018 at 05:51 AM

That worked a treat, thanks very much...needed some fiddling but works perfectly now. Date field was a bit of a pain! Final code posted below for reference.

SQLStatement = "INSERT INTO PlannedMaintenance ([Date Recorded], [Raised By], [Facility], [Machine], [Frequency], [Period], [Description]) VALUES ('" & Format(DateRecorded, "yyyy-mm-dd") & "', '" & RaisedBy & "', " & Facility & ", '" & Machine & "', '" & Frequency & "', '" & Period & "', '" & Description & "');"

'MsgBox (SQLStatement)
DoCmd.RunSQL SQLStatement

Are you able to walk me through the index field stuff now?

Thanks again

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Aug 29, 2018 at 09:43 AM

Actually never mind...this seems to have sorted itself out in this instance...I do have another question related to this issue but will open a new question on it.

Thanks.

Similar discussions

want to create a table in composing mail in gmail

Excel - To generate unique ID

Excel Macro to insert a new row

Excel: Copy active range to bottom of list

Short cut to merge cells

Similar discussions

Subscribe To Our Newsletter!