chowe18
asked on
Auto fill subform type mismatch error
This is the problem that I am having with a database in Access 2000. The main form (frmCensusMainEntry) has a subform (frmPatientPlacementSubfor m) and I want the subform to autofill a new record with information from its previous record. I used the following function from the Microsoft Knowledge Base Article - 210236:
Function AutoFillNewRecord(f As Form)
Dim rs As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer
On Error Resume Next
' Exit if not on the new record.
If Not f.NewRecord Then Exit Function
' Goto the last record of the form recordset (to autofill form).
Set rs = f.RecordsetClone
rs.MoveLast
' Exit if you cannot move to the last record (no records).
If Err <> 0 Then Exit Function
' Get the list of fields to autofill.
FillFields = ";" & f![AutoFillNewRecordFields ] & ";"
' If there is no criteria field, then set flag indicating ALL
' fields should be autofilled.
FillAllFields = Err <> 0
f.Painting = False
' Visit each field on the form.
For Each C In f
' Fill the field if ALL fields are to be filled OR if the
' ...ControlSource field can be found in the FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.NAME) & ";") > 0 Then
C = rs(C.ControlSource)
End If
Next
f.Painting = True
End Function
Then I put a non-visible text box in the subform with the defaultvalue: Chart;AdmitType;Program;Un it
The only field not included in the defaultvalue was Date, because I want the user to enter this as a control measure for data entry.
Finally, in the OnCurrent property I put the following: =AutoFillNewRecord([Forms] ![frmPatie ntPlacemen tSubform])
When I open the subform without opening the main form the function works properly. Yet, when I open the main form to access the subform the following error occurs:
The expression On Current you entered as the event property setting produced the following error: Type mismatch.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.
If anyone has any ideas for resolving this problem, I would greatly appreciate some help.
With Enthusiasm
Chowe18
Function AutoFillNewRecord(f As Form)
Dim rs As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer
On Error Resume Next
' Exit if not on the new record.
If Not f.NewRecord Then Exit Function
' Goto the last record of the form recordset (to autofill form).
Set rs = f.RecordsetClone
rs.MoveLast
' Exit if you cannot move to the last record (no records).
If Err <> 0 Then Exit Function
' Get the list of fields to autofill.
FillFields = ";" & f![AutoFillNewRecordFields
' If there is no criteria field, then set flag indicating ALL
' fields should be autofilled.
FillAllFields = Err <> 0
f.Painting = False
' Visit each field on the form.
For Each C In f
' Fill the field if ALL fields are to be filled OR if the
' ...ControlSource field can be found in the FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.NAME) & ";") > 0 Then
C = rs(C.ControlSource)
End If
Next
f.Painting = True
End Function
Then I put a non-visible text box in the subform with the defaultvalue: Chart;AdmitType;Program;Un
The only field not included in the defaultvalue was Date, because I want the user to enter this as a control measure for data entry.
Finally, in the OnCurrent property I put the following: =AutoFillNewRecord([Forms]
When I open the subform without opening the main form the function works properly. Yet, when I open the main form to access the subform the following error occurs:
The expression On Current you entered as the event property setting produced the following error: Type mismatch.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.
If anyone has any ideas for resolving this problem, I would greatly appreciate some help.
With Enthusiasm
Chowe18
ASKER
I think that I understand the concept, but I am not sure about writing the code. I am still pretty much an amateur with the syntax. Could you be more specific and is there more information that you need from me?
on the main form, open the properties window
select the event tab and locate the on current event
click the drop down arrow and select [Event Procedure]
Click the button to the right of the event property with the 3 dots on it. this will open the code window
Paste the code between the private sub and end sub.
Me!frmSubformContainerObje ct.Form.Au toFillNewR ecord(Me!f rmSubformC ontainerOb ject.Form)
you'll have to change the name 'frmSubformContainerObject ' to match your container object name.
Mike
select the event tab and locate the on current event
click the drop down arrow and select [Event Procedure]
Click the button to the right of the event property with the 3 dots on it. this will open the code window
Paste the code between the private sub and end sub.
Me!frmSubformContainerObje
you'll have to change the name 'frmSubformContainerObject
Mike
ASKER
Hi Mike,
I entered this event into the OnCurrent of the main form:
Me!frmPatientPlacementSubf orm.Form.A utoFillNew Record (Me!frmPatientPlacementSub form.Form)
When I open the main form I still get the original error message and a run-time error 2465, application-defined or object-defined error and the entire event is highlighted.
Craig
I entered this event into the OnCurrent of the main form:
Me!frmPatientPlacementSubf
When I open the main form I still get the original error message and a run-time error 2465, application-defined or object-defined error and the entire event is highlighted.
Craig
Craig,
Can you send me a copy of the zipped MDB? I will take a look and post the solution here.
I don't think I can truely see what it going on without seeing the db and the code executing.
My e-mail is in my profile.
Thanks,
Mike
Can you send me a copy of the zipped MDB? I will take a look and post the solution here.
I don't think I can truely see what it going on without seeing the db and the code executing.
My e-mail is in my profile.
Thanks,
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works just fine now. Thanks again for all of your help.
Craig
Craig
If you want the code to fire everytime the main form changes records, then do this
In the On Current event of the main form create an event procedure and then call the procedure in the subform
Me!frmSubformContainerObje
the frmSubformContainerObject is the name of the control that holds your subfrom not the name of the subform itself. Sometimes these may have the same name, but not usually.
Mike