Copy specific field value after clicking a button to duplicate

[theinviter]

hi guys:
i need help , i created a form linked to subform , and a button to duplicate the record in form and subform to new record, but i need help in modifying the code so that the field (user) will get the same value as present in the top of the form as there many user using the form.
more detail present in the attached picture

i user the code bellow:

'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngOldID As Long '***PRIMARY KEY VALUE OF THE ORIGINAL RECORD (YOU NEED THIS TO GET AT THE ORIGINAL SUB-RECORD)
Dim lngID As Long 'Primary key value of the new record.

' Dim lngID2 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

lngOldID = Me.ID '***YOU GRAB THE ORIGINAL ID BEFORE CREATING THE NEW RECORD SO YOU CAN GET THE RELATED SUBRECORD

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew


![Date__] = Me.[Date__]
![Date Mod] = Me.[Date Mod]
![Crash Cart Number] = Me.[Crash Cart Number]
!Location = Me.Location
![Replaced_Crash_cart_Number] = Me.[ Replaced_Crash_cart_Number]
![Replacement Date] = Me.[ Replaced_Crash_cart_Number]
!MRN = Me.MRN
![Patient Name] = Me.[ Patient Name]
!Bed = Me.Bed
![Reason for Opening] = Me.[ Reason for Opening]
![For Others] = Me.[ For Others]
![Pharmacist Name] = Me.[ Pharmacist Name]
![Date:] = Me.[Date:]
![Nurse Name] = Me.[ Nurse Name]
![Date::] = Me.[Date::]
!User = Forms![LIST_OF_FORMS]![txtnameid]

' !Field1 = Me.Field1
' !Field2 = Me.Field2



'etc for other fields.
.Update

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



'Duplicate the related records: append query.
If Me.[Dispensing 2 Subform].Form.RecordsetClone.RecordCount > 0 Then



'***HERE IS THE NEW CODE
strSql = "INSERT INTO [Dispensing 2] ( [Drug name], [File ID],[Quantity],[Expiry Date], [Batch Number],[Replaced Quantity],[Availability] ) " & _
"SELECT [Dispensing 2].[Drug name], " & lngID & " AS Expr1 , [Quantity], [Expiry Date], [Batch Number],[Replaced Quantity],[Availability] " & _
"FROM [dispensing 1] INNER JOIN [Dispensing 2] ON [dispensing 1].ID = [Dispensing 2].[File ID] " & _
"WHERE ((([dispensing 1].ID)=" & lngOldID & "));"

Debug.Print strSql


'***ON YOUR ORIGINAL CODE YOU NEVER ACTUALLY RAN THE SQL!
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True


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

ANY HELP PLEASE

 

[pbaldy]

This looks like it should be getting the user from the form. Is it not?

!User = Forms![LIST_OF_FORMS]![txtnameid]

If not, change the form reference as appropriate.

 

[theinviter]

This looks like it should be getting the user from the form. Is it not?

!User = Forms![LIST_OF_FORMS]![txtnameid]

If not, change the form reference as appropriate.

But i need to get the value in subform.

 

[pbaldy]

Then add that field to the insert SQL, and concatenate a form reference into the SELECT clause.

 

[theinviter]

Then add that field to the insert SQL, and concatenate a form reference into the SELECT clause.

iadded the field (user1) as described in the code bellow, but copy the same value, can please guide me

strSql = "INSERT INTO [Dispensing 2] ( [Drug name], [File ID],[Quantity],[Expiry Date], [Batch Number],[Replaced Quantity],[Availability], [User1] ) " & _
"SELECT [Dispensing 2].[Drug name], " & lngID & " AS Expr1 , [Quantity], [Expiry Date], [Batch Number],[Replaced Quantity],[Availability], [User1] " & _
"FROM [dispensing 1] INNER JOIN [Dispensing 2] ON [dispensing 1].ID = [Dispensing 2].[File ID] " & _
"WHERE ((([dispensing 1].ID)=" & lngOldID & "));"

Debug.Print strSql

 

[theinviter]

i modified this code

strSql = "INSERT INTO [Dispensing 2] ( [Drug name], [File ID],[Quantity],[Expiry Date], [Batch Number],[Replaced Quantity],[Availability], [User1] ) " & _
"SELECT [Dispensing 2].[Drug name], " & lngID & " AS Expr1 , [Quantity], [Expiry Date], [Batch Number],[Replaced Quantity],[Availability], [[User1]= Forms![form entery]![txtnameid1]] " & _
"FROM [dispensing 1] INNER JOIN [Dispensing 2] ON [dispensing 1].ID = [Dispensing 2].[File ID] " & _
"WHERE ((([dispensing 1].ID)=" & lngOldID & ")) ;"


but the value i got in the field as 0 and -1 .

please any advise .

 

[pbaldy]

Do you want to match the user in the old record (in your example, 2 for each)? Or should every new record have the user performing this task?

 

[Orthodox Dave]

Hi again,

In the SELECT section, you have to repeat the table name for each value, so:

strSql = "INSERT INTO [Dispensing 2] ( [Drug name], [File ID],[Quantity],[Expiry Date], [Batch Number],[Replaced Quantity],[Availability], [User1] ) " & _
"SELECT [Dispensing 2].[Drug name], " & lngID & " AS Expr1 , [B][I][Dispensing 2].[/I][/B][Quantity], [B][I][Dispensing 2].[/I][/B][Expiry Date], [B][I][Dispensing 2].[/I][/B][Batch Number],[B][I][Dispensing 2].[/I][/B][Replaced Quantity],[B][I][Dispensing 2].[/I][/B][Availability], [[User1]= Forms![form entery]![txtnameid1]] " & _
"FROM [dispensing 1] INNER JOIN [Dispensing 2] ON [dispensing 1].ID = [Dispensing 2].[File ID] " & _
"WHERE ((([dispensing 1].ID)=" & lngOldID & ")) ;"

 

[theinviter]

Do you want to match the user in the old record (in your example, 2 for each)? Or should every new record have the user performing this task?

the user performing this task.

 

[pbaldy]

Then something like:

strSql = "INSERT INTO [Dispensing 2] ( [Drug name], [File ID],[Quantity],[Expiry Date], [Batch Number],[Replaced Quantity],[Availability], [User1] ) " & _
"SELECT [Dispensing 2].[Drug name], " & lngID & " AS Expr1 , [Quantity], [Expiry Date], [Batch Number],[Replaced Quantity],[Availability], Forms![form entery]![txtnameid1] " & _
"FROM [dispensing 1] INNER JOIN [Dispensing 2] ON [dispensing 1].ID = [Dispensing 2].[File ID] " & _
"WHERE ((([dispensing 1].ID)=" & lngOldID & "));"

 

[theinviter]

Hi again,

In the SELECT section, you have to repeat the table name for each value, so:

strSql = "INSERT INTO [Dispensing 2] ( [Drug name], [File ID],[Quantity],[Expiry Date], [Batch Number],[Replaced Quantity],[Availability], [User1] ) " & _
"SELECT [Dispensing 2].[Drug name], " & lngID & " AS Expr1 , [B][I][Dispensing 2].[/I][/B][Quantity], [B][I][Dispensing 2].[/I][/B][Expiry Date], [B][I][Dispensing 2].[/I][/B][Batch Number],[B][I][Dispensing 2].[/I][/B][Replaced Quantity],[B][I][Dispensing 2].[/I][/B][Availability], [[User1]= Forms![form entery]![txtnameid1]] " & _
"FROM [dispensing 1] INNER JOIN [Dispensing 2] ON [dispensing 1].ID = [Dispensing 2].[File ID] " & _
"WHERE ((([dispensing 1].ID)=" & lngOldID & ")) ;"

i done this but its the same result i got as in the picture.

 

[theinviter]

Then something like:

strSql = "INSERT INTO [Dispensing 2] ( [Drug name], [File ID],[Quantity],[Expiry Date], [Batch Number],[Replaced Quantity],[Availability], [User1] ) " & _
"SELECT [Dispensing 2].[Drug name], " & lngID & " AS Expr1 , [Quantity], [Expiry Date], [Batch Number],[Replaced Quantity],[Availability], Forms![form entery]![txtnameid1] " & _
"FROM [dispensing 1] INNER JOIN [Dispensing 2] ON [dispensing 1].ID = [Dispensing 2].[File ID] " & _
"WHERE ((([dispensing 1].ID)=" & lngOldID & "));"

it work perfectly , thanks alot

 

[pbaldy]

No problemo!