Solved

Auto fill subform type mismatch error

Posted on 2004-08-05
7
405 Views
Last Modified: 2011-09-20
This is the problem that I am having with a database in Access 2000. The main form (frmCensusMainEntry) has a subform (frmPatientPlacementSubform) 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;Unit
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]![frmPatientPlacementSubform])

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
0
Comment
Question by:chowe18
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 11732522
When is the procedure supposed to fire?  Think of it in terms of the main form point of view since the child records will be displayed when ever the main form changes.

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!frmSubformContainerObject.Form.AutoFillNewRecord(Me!frmSubformContainerObject.Form)

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

0
 

Author Comment

by:chowe18
ID: 11741328
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?
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 11741366
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!frmSubformContainerObject.Form.AutoFillNewRecord(Me!frmSubformContainerObject.Form)

you'll have to change the name 'frmSubformContainerObject' to match your container object name.

Mike

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:chowe18
ID: 11754509
Hi Mike,

I entered this event into the OnCurrent of the main form:
Me!frmPatientPlacementSubform.Form.AutoFillNewRecord (Me!frmPatientPlacementSubform.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
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 11754868
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
0
 
LVL 18

Accepted Solution

by:
Data-Man earned 500 total points
ID: 11768132
the problem was in the location of the function autofillnewrecord.

It was a public function in a module and not on the subform.

I changed the on current code of the main form to

Call AutoFillNewRecord(Me.frmPatientPlacementSubform.Form)

and removed the code from the sub form's on current event.

Thanks,

Mike
0
 

Author Comment

by:chowe18
ID: 11768435
It works just fine now. Thanks again for all of your help.

Craig
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question