Assigning Macros to Buttons using the Macro Builder

[AccessN00bee]

Hi,

I am completely new to Access. I have been asked to modify an existing form in a database. I have a form that we log hours into after a certain time and create a new record each time. I'm attempting to use macro builder (I have no VBA experience) to assign a macro to the button that will create a new record, take certain fields from the previous record and paste them into a previous hours field. I am able to use the button to create a new record, but that's as far as it goes. I attempted to use a sub-macro to reference the previous field and queries to select the previous, but that did not work. Any help is much appreciated. Thank you.

AccessN00bee

[June7]

The 'copy/paste' concept is not practical. What you want to do is carry forward the data into a new record. Best approach is to set the DefaultValue property of the controls then when moving to new record row and initiating data entry in any other field, those controls will automatically populate. I don't use macros, only VBA and don't know if macros can set properties. http://access.mvps.org/access/forms/frm0012.htm

[AccessN00bee]

I'm a little confused about that process. I will look into trying that a little later. I attempted to edit the Err_Duplicate_Projeck_Click by using Duplicate Record function when adding a button. No data is duplicated. This is the code for duplicating a record, but it does not work.

On Error GoTo Err_Duplicate_Project_Click


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste

Exit_Duplicate_Project_Click:
Exit Sub

Err_Duplicate_Project_Click:
MsgBox Err.Description
Resume Exit_Duplicate_Project_Click

End Sub

[AccessN00bee]

I found this code and I have attempted to modify it, but I haven't had any success so far. Here is the code:

'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!CustomerID = Me.CustomerID
!EmployeeID = Me.EmployeeID
!OrderDate = Date
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !OrderID

'Duplicate the related records: append query.
If Me.[Orders Subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Order Details] ( OrderID, ProductID, Quantity, UnitPrice, Discount ) " & _
"SELECT " & lngID & " As NewID, ProductID, Quantity, UnitPrice, Discount " & _
"FROM [Order Details] WHERE OrderID = " & Me.OrderID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub

[June7]

What does "haven't had any success" mean - error message, wrong results, nothing happens?

[AccessN00bee]

Nothing is duplicated. It just adds a new field with no entries in any fields.

[June7]

I am confused. You tried code that would just copy/paste record but then latest post says you want data to go into another field. Copy/paste record would not do that.

The latest attempt seems much more complicated than suggestion to set DefaultValue property.

I use:
CurrentDb.Execute

After the INSERT action, need to requery the form.

[AccessN00bee]

I may have not communicated what I want to do in the best way. I have attached a screenshot depicting the data (red column) that I need to move to fields represented in green column whenever a new record is created (which I am attempting to automate with a button).

[June7]

You want to save calculated values into the green record. Every value is a red value reduced by 10.6.

Saving calculated data is usually a bad idea, especially aggregate data. Ideally, enter raw data and do calculations when needed.

What I see is the need to calculate number of hours/miles since last inspection/maintenance to determine if equipment is due for another. This would probably require a table of odometer or date/time start and stop readings.

This has something to do with SCUBA?

[AccessN00bee]

Offshore surveying. The value in red column is calculated, I just want to use that value and migrate it to green column. I was trying to use Me.[Hours On Cathode] = The calculate value, but it won't accept the field name.

[June7]

As I said, you want to input data into the green box with calculated results based on values in the red box. Red box controls are unbound? How is this data generated?

What is the structure of this form - is it bound to table? What is structure of the table? If form is bound, is it on a new record row in the image?

My comments about saving calculated aggregate data still apply.

[AccessN00bee]

June7, I appreciate your help and responses. I am completely lost on how to achieve the final product I desire. I am going through a few access books and vba programming books to research the application further. Thanks again for your help.

[June7]

I just don't know enough about your data structure and processes. If you want to provide db for analysis, follow instructions at bottom of my post.

Populating the green box items should be simple but without better understanding of the data and the form arrangement, hard to give specific guidance.

[AccessN00bee]

June7, I really appreciate your efforts in helping me. The database contains approximately 10 forms and 40 or so reports; I would have to remove a significant amount of data to attach it due to confidential data. There are several forms and reports that are linked to the same table. I think the database structure started out poor and grew from there. It seems like a messy structure. I'm going to attempt to create a mock database with similar tables and forms, and post that. Thank you.

[AccessN00bee]

June7,

So I was able to get a little further in achieving what I was attempting to accomplish, but ran into another issue. Here is the sample code:

Private Sub CopyRecord_Click()

On Error Resume Next

Dim v1 As Variant

v1 = Me![E-Hours On Cathodes].Value

DoCmd.GoToRecord , , acNewRec

Me![S-Hours On Cathodes] = v1

End Sub

With this, I click on the record I want to copy, then click the button. It creates a new record with data I wanted to copy, but it doesn't allow for any new records after this one is created. I attached an image of what the record table looks like when I run this, and what it should look like. Any ideas? Thanks.