VBA code cannot find fields on other tab pages

[grane236]

HI, Im using this code to copy fields from the previous record in my form and put the copies field values into a new record on the form. HOwever the code doesnt work when it tries to find the previous values for any fields on a tab page other than the first one. I tried to change the focus to the next tab pages but this didnt work. Any advice appreciated:

Private Sub New_Assessment_Click()
'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
![Contact Name] = Me.Contact_Name
![Position Title] = Me.Position_Title
![Contact Phone] = Me.Contact_Phone
![Contact Email] = Me.Contact_Email
Me.TabCtl0.Value = 1 <here is where new tab page begins>
![Liquids_on_the_Premises] = Me.Liquids_on_the_Premises
![Liquids_on_the_Premises] = Me.Liquids_on_the_Premises
![Large_Volume_Liquids] = Me.Large_Volume_Liquids
![Spill_Response_Materials_Available] = Me.Spill_Response_Materials_Available
![Photos_taken_of_Det_Deg_products_] = Me.Photos_taken_of_Det_Deg_products_
![General_comments_regarding_liquids] = Me.General_comments_regarding_liquids
![Det_Deg_Prodcut_Replacement_Advice_Provided] = Me.Det_Deg_Prodcut_Replacement_Advice_Provided
![Advice_provided_regarding_liquids] = Me.Advice_provided_regarding_liquids
![Details_of_liquid_advice_provided] = Me.Details_of_liquid_advice_provided
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !ID
'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

 

[pbaldy]

What happens if you delete the line in red? You don't need to do anything special to refer to controls on different tabs, as long as they're not in a subform.

 

[grane236]

Thanks pbaldy. I now believe its becuase they are not text fields. I need to refer to them differently. Most are combo boxes or list boxes. Any advice? Thankyou.

 

[pbaldy]

No difference if you want the bound column and list boxes are single select. If you need a different column than the bound column:

Me.ComboName.Column(x)

Where x is the zero based column number you want.

 

[grane236]

HI pbaldy,
I tried this and still get the data type conversion error or another error
With Me.RecordsetClone
.AddNew
![Contact Name] = Me.Contact_Name
Me.Contact_Name.ForeColor = vbRed
![Position Title] = Me.Position_Title
Me.Position_Title.ForeColor = vbRed
![Contact Phone] = Me.Contact_Phone
Me.Contact_Phone.ForeColor = vbRed
![Contact Email] = Me.Contact_Email
Me.Contact_Email.ForeColor = vbRed
![Copy Business Name] = Me.[Copy Business Name]
![Liquids on the Premises] = Me.[Liquids on the Premises].Column(1)
'etc for other fields.
.Update

 

[pbaldy]

Can you post the db here?

 

[grane236]

Hi pbaldy,

its 7mg so I dont think so unfortunatley.

stece