Formatting Error with VBA Array?

[M_S_Jones]

Hi gang,

I've been asked to modify a form of a database so that at the click of a button, the data entry form will be refreshed and ready for a new record with the values of the previous record (just entered prior to pressing the button), as defaults for the fields. This functionality isn't often required, so having it at the press of a button is desirble. I've attempted to do this as follows:

I've added a new button which On Click opens a new form. The new form is very basic and contains no objects. The On Open event for the form reads: (I've named the forms frm1 and frm2 to keep it simple, frm1 is the data entry form and frm2 is the new form):

Private Sub Form_Open(Cancel As Integer)
'Hide this form:
Me.Visible = False
'Create array & store values from frm1
Dim myArray As Variant
myArray = Array(Forms!frm1.Text1, Forms!frm1.Text2)

'Preview the first value to test that the data is being retrieved:
MsgBox myArray(0)
'Open & close frm1 to ensure that the data is saved and a new ID generated:
DoCmd.Close acForm, "frm1"
DoCmd.OpenForm "frm1"
'Assign the values from the array on frm2 over to the newly refreshed frm1:
Forms!frm1.Text1 = myArray(0)
Forms!frm1.Text2 = myArray(1)
'Close this form
DoCmd.Close
End Sub

This code displays the msgbox with the correct value in, but then rather than a form containing the assigned default values, I'm confronted with an error message, which reads:

Run-time error '-2147352567 (8020009)':

The value you entered isn't valid for this field​

Can anyone lend a hand here? I know there are other ways to do what I am hoping to achieve, but now I've started this way, I'd like to get it up and running. I've never used arrays in VBA before, so I thought that it would be good as practice, as well as hopefully accomplishing the functionality that is required. If there isn't a way of resolving this issue, then I'm open to suggestions using other methods.

Thanks,

Matt

 

[ByteMyzer]

Try the following code modifications for frm2 (see highlighted text):

Private Sub Form_Open(Cancel As Integer)

'Hide this form:
Me.Visible = False
'Create array & store values from frm1
Dim myArray As Variant
myArray = Array(Forms!frm1.Text1[b][color=red].Value[/color][/b], Forms!frm1.Text2[b][color=red].Value[/color][/b])

'Preview the first value to test that the data is being retrieved:
MsgBox myArray(0)
'Open & close frm1 to ensure that the data is saved and a new ID generated:
DoCmd.Close acForm, "frm1"
DoCmd.OpenForm "frm1"
'Assign the values from the array on frm2 over to the newly refreshed frm1:
Forms!frm1.Text1 = myArray(0)
Forms!frm1.Text2 = myArray(1)
'Close this form
DoCmd.Close [b][color=red]acForm, Me.Name[/color][/b]

End Sub

 

[M_S_Jones]

Works perfectly first time, thank you very much!

Matt

 

[CyberLynx]

Well...another way you could do this is utilizing the DoCmd.RunCommand method:

Private Sub Command1_Click()
   If Me.Dirty = True Then
      DoCmd.RunCommand acCmdSelectRecord
      DoCmd.RunCommand acCmdCopy
      DoCmd.RunCommand acCmdRecordsGoToNew
      DoCmd.RunCommand acCmdPasteAppend
      [COLOR="DarkGreen"]'Oh...I want a new entry in this one...[/COLOR]
      Me.OneOfMyFormTextBoxes = Null
      [COLOR="DarkGreen"]'Oh gee Ya...I don't want anything in this one either...[/COLOR]
      Me.AnotherOneOfMyFormTextBoxes = Null
   End If
End Sub

Only one Form...the Data Entry Form with a Command Button on it.
.