Access VBA Error?

[Alan S]

Hi All,



I'm fairly new to access and VBA and I'm getting an error that I have no idea what it means.

Basically I have a tabbed form with a subform on each tab. That tabbed form is then a subform on the main page. On the main page I have a placeholder button called Box60, the code for Box60_Click is as follows ( most of it is commented out as I was trying to get just 1 line to work)

Public Sub Box60_Click()  Dim dbVideoCollection As DAO.Database
  Dim rs As DAO.Recordset
  Dim rsE As DAO.Recordset
  Dim rsM As DAO.Recordset
  


  Set dbVideoCollection = CurrentDb
  Set rs = dbVideoCollection.OpenRecordset("Child")


  rs.AddNew
  


  
  rs("Child Name").Value = ChildMGMT_EnrollChild_ChildDetails.enrollchild_childname.Value
'  rs("Child DOB").Value = ChildMGMT_EnrollChild_ChildDetails.enrollchild_childdob.Value
'  rs("Child Address").Value = ChildMGMT_EnrollChild_ChildDetails.enrollchild_childaddress.Value
'  rs("Child Gender").Value = ChildMGMT_EnrollChild_ChildDetails.enrollchild_childgender.Value
'  rs("Child Ethnic Group").Value = ChildMGMT_EnrollChild_ChildDetails.enrollchild_childethnicgroup.Value
'  rs("Child Group").Value = ChildMGMT_EnrollChild_ChildDetails.enrollchild_childgroup.Value
'
'  rs("Parent Name 1").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_1pname.Value
'  rs("Parent Home Address 1").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_1paddress.Value
'  rs("Parent Contact Number 1").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_1pcontactnumber.Value
'  rs("Parent Email 1").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_1pemail.Value
'  rs("Parent Employment/School 1").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_1pschool.Value
'  rs("Parent Employment/School Address 1").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_1pschooladdress.Value
'  rs("Parent Employment/School Phone Number 1").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_1pschoolphone.Value
'  rs("Parent Name 2").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_2pname.Value
'  rs("Parent Home Address 2").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_2paddress.Value
'  rs("Parent Contact Number 2").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_2pcontactnumber.Value
'  rs("Parent Email 2").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_2pemail.Value
'  rs("Parent Employment/School 2").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_2pschool.Value
'  rs("Parent Employment/School Address 2").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_2pschooladdress.Value
'  rs("Parent Employment/School Phone Number 2").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_2pschoolphone.Value
'  rs("Parent Course").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_parentcourse.Value
'  rs("Info Checked by Parent").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_infochecked.Value
'  rs("Admission Date").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_admissiondate.Value
'  rs("Leaving Date").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_leavingdate.Value
'  rs("Parent Signiture Received").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_parentsig.Value
'  rs("Staff Signiture Received").Value = ChildMGMT_EnrollChild_ParentDetails.enrollchild_staffsig.Value
'
'  Set rsE = dbVideoCollection.OpenRecordset("EmergCont")
'  rsE.AddNew
'  rsE("1stContact Name").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_1contactname.Value
'  rsE("1stContact Address").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_1contactaddress.Value
'  rsE("1stContact PhNum").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_1contactphone.Value
'  rsE("1stRelation title").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_1contactrelationtitle.Value
'  rsE("1stPlace employed").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_1placeofemployment.Value
'  rsE("1stEmplyment Address").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_1employmentaddress.Value
'  rsE("1stEmplotment PhNum").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_1employmentphone.Value
'  rsE("2ndContact Name").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_2contactname.Value
'  rsE("2ndContact Address").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_2contactaddress.Value
'  rsE("2ndContact PhNum").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_2contactphone.Value
'  rsE("2ndRelation title").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_2contactrelationtitle.Value
'  rsE("2ndPlace employed").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_2placeofemployment.Value
'  rsE("2ndEmplyment Address").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_2employmentaddress.Value
'  rsE("2ndEmplotment PhNum").Value = ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_2employmentphone.Value
'
'  Set rsM = dbVideoCollection.OpenRecordset("MedicalInfo")
'  rsM.AddNew
'  rsM("DocName").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_docname.Value
'  rsM("DocAddress").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_docaddress.Value
'  rsM("DocPhone").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_docphone.Value
'  rsM("BCG").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_bcg.Value
'  rsM("BCGDatesRecived").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_BCGDatesRecived.Value
'  rsM("Diphteria").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_diphteria.Value
'  rsM("DiphteriaDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_diphteriaDatesRec.Value
'  rsM("Tetanus").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_tetanus.Value
'  rsM("TetanusDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_TetanusDatesRec.Value
'  rsM("Pertusis").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_pertusis.Value
'  rsM("PertusisDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_PertusisDatesRec.Value
'  rsM("HIB").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_hib.Value
'  rsM("HIBDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_HIBDatesRec.Value
'  rsM("Polio").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_polio.Value
'  rsM("PoloDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_PolioDatesRec.Value
'  rsM("HepB").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_hepb.Value
'  rsM("HepBDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_HepBDatesRec.Value
'  rsM("PCV").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_pcv.Value
'  rsM("PCVDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_PCVDatesRec.Value
'  rsM("Meningitis").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_meningitis.Value
'  rsM("MeningitisDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_MeningitisDatesRec.Value
'  rsM("MMR").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_mmr.Value
'  rsM("MMRDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_MMRDatesRec.Value
'  rsM("BoosterShot").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_boostershot.Value
'  rsM("BoosterShotDatesRec").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_BoosterDatesRec.Value
'  rsM("Illness").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_illness.Value
'  rsM("Disability").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_disability.Value
'  rsM("Medication Allergy").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_medicationallergy.Value
'  rsM("SpecialNeeds").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_specialneeds.Value
'  rsM("ParaLinkConsent").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_paralink.Value
'  rsM("Allergies").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_allergies.Value
'  rsM("FirstAidConsent").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_firstaid.Value
'  rsM("EmerTreatmentConsent").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_emergencytreatment.Value
'  rsM("SuncreamConsent").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_suncream.Value
'  rsM("OnCampus").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_oncampus.Value
'  rsM("OffCampus").Value = ChildMGMT_EnrollChild_MedicalInfo.enrollchild_offcampus.Value


 
  
  rs.Update
'  rsE.Update
'  rsM.Update
End Sub

What the code is supposed to do is take the values that are in textboxes on the tabbed forms and put them into the relevent table and columns however I keep getting an error saying object RunTime Error 424 - Object Required for the line

rs("Child Name").Value = ChildMGMT_EnrollChild_ChildDetails.enrollchild_childname.Value

In this line of code rs is the recordset variable for the "Child" table, "Child Name" is the name of the column, "ChildMGMT_EnrollChild_ChildDetails" is the name of the form and enrollchild_childname is the name of the textbox?

If anyone could help out it would be greatly appreciated!

[pbaldy]

Two thoughts come to mind. Because of the inadvisable space, you may need to bracket your field names:

[Child Name]

I'd also use the appropriate syntax to refer to the textbox:

http://access.mvps.org/access/forms/frm0031.htm

[Alan S]

I'm after overcoming that problem somewhat.


Basically I discovered I was naming the form wrong, In the VBA code editor in Access the form was named "Form_ChildMGMT_EnrollChild_ChildDetails" I tried that and now it works to input the details from the child form and the parent form.


Just to clarify I have 6 forms, a ChildDetails, ParentDetails, EmergencyContacts and Medicial Info form, all are which are combined in a tabbed form in EnrollChild_Tabbed Form, that tabbed form is then on a form called EnrollChild , all the VBA code is on this EnrollChild form in a Sub for Box60_Click.


The problem is the code stops working when its time to process the information from the EmergencyContacts form.


The code breaks exactly on these lines of code

 Form_ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_1contactname.Focus
rsE("1stContact Name").Value = Form_ChildMGMT_EnrollChild_EmergencyContacts.enrollchild_1contactname.Text

Which is supposed to focus the on the textbox "enrollchild_1contactname" which is on the Form_ChildMGMT_EnrollChild_EmergencyContacts" form , however I get the error "Compile Error - Method or Data Member not found"


Any ideas?

[pbaldy]

Well, I would use the syntax from that link, and it sounds like the main form is EnrollChild. Note on the link that if the name of a subform is different from the name of the subform control, you need the name of the control.

[Alan S]

I realised the problem was actually that I was using the command .Focus instead of .SetFocus!

Thanks for the help!

[pbaldy]

Duh, I didn't notice that. Glad you sorted it out.