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

Posts
21
Registration date
Tuesday July 17, 2018
Last seen
September 5, 2018
- Aug 14, 2018 at 10:09 AM - Latest reply:
Posts
21
Registration date
Tuesday July 17, 2018
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.

See more 

Your reply

9 replies

Best answer
Posts
10871
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 20, 2018
Aug 14, 2018 at 05:03 PM
1
Thank you
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.

Thank you, ac3mark 1

Something to say? Add comment

CCM has helped 1695 users this month

Posts
21
Registration date
Tuesday July 17, 2018
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
Posts
10871
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 20, 2018
- Aug 15, 2018 at 04:05 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!
Posts
21
Registration date
Tuesday July 17, 2018
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)"
Posts
10871
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 20, 2018
- Aug 23, 2018 at 04:59 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!
Posts
21
Registration date
Tuesday July 17, 2018
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
Respond to ac3mark
Best answer
Posts
10871
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 20, 2018
Aug 24, 2018 at 04:57 PM
1
Thank you
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!

Thank you, ac3mark 1

Something to say? Add comment

CCM has helped 1695 users this month

Posts
21
Registration date
Tuesday July 17, 2018
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
Posts
21
Registration date
Tuesday July 17, 2018
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.
Respond to ac3mark