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

Posts
21
Registration date
Tuesday July 17, 2018
Last seen
September 5, 2018
- - Latest reply: PNyiam
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

2 replies

Best answer
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1893
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.

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 4504 users have said thank you to us this month

PNyiam
Posts
21
Registration date
Tuesday July 17, 2018
Last seen
September 5, 2018
-
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
ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1893 -
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
Last seen
September 5, 2018
-
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)"
ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1893 -
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
Last seen
September 5, 2018
-
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
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1893
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!

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 4504 users have said thank you to us this month

PNyiam
Posts
21
Registration date
Tuesday July 17, 2018
Last seen
September 5, 2018
-
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
Last seen
September 5, 2018
-
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