Subform reference

Using Access 2003.  I have a main form [Customers and Job Details]. I have a subform [frmServiceAppts].  I have a calendar popup that sets the value of a control to the date I select. I used the control on the main form using the following code:

DoCmd.OpenForm "John'sPopUp", acNormal,,,,acDialog, Me.Name & ".{controlName]"

This worked fine.  I have the popup on the subform.  How do I refer to the control?

Thanks
jtruslerAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Forms("John'sPopUp").ControlName

mx
0
 
jtruslerAuthor Commented:
The main form  is [Customers and Job Details]
The subform  is [frmServiceAppts]
I have a 3rd form that I call via a command button next to a control on the subform.  The command button opens the form [JohnsPopUp] where I select a date that populates the control that I refer to.  In this case it is [ApptDate].

I had this control on the main form before, but now it is on the subform.  I used to put the following code with the OnClick event:

DoCmd.OpenForm "John'sPopUp", acNormal,,,,acDialog, Me.Name & ".[controlName]"

How do I change it to refer to [frmServiceAppts]![ApptDate] which is the subform?

Thanks
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ... not exactly sure what you need, but ... first, you open the form, then ... you refer to a control on that form ... you don't do that in one line of code.

I have the popup on the subform."

Not sure what you mean by that?  Maybe you could break this down as to exactly what is on what form ... want the steps the you need to do?

mx
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
jtruslerAuthor Commented:
When I open the database, I open the form [Customers and Job Details]. Let's call it Customers for short.  It has CustID,Fname, Lname, address etc. On the form, I have a tabbed subform.  The subform is called frmServiceAppts.  Lets call it Appts. There is a one to many relationship between Customers and Appts.  The fields are ApptID CustID ( to link with ID on the Customer form. ) as well as ApptType ApptDate ApptTime etc. On the Appts form, I have a small command button beside the ApptDate field.

Rather than type in a date, I want my customers to select it. When I click on the button, the code is:

Private Sub Command74_Click()
DoCmd.OpenForm "JohnsPopUp", acNormal,,,,acDialog, Me.Name & ".[ApptDate]"
End Sub

I used this on my main customers form for the initial contact date and it put the right value in the control.

When used on the subform, I get error 2450 - Can't find 'Customers and Job Details' referred to in a macro expression or VBA code. There is some code with the pop up calendar that craps out.  I bought a developer version of it, but I don't think I can ship the code over a service like this.  

I assume that I have to give focus to the subform for the calendar control to work.

Thanks.  Hope this helps you. I bumped the points.


0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You refer to a Subform like this:

Me.NameOfYourSubFormCONTROL.Form.ControlName

Where "NameOfYourSubformCONTROL" is the name of the Subform object on the main form ... this may or may not be the name of the form being used as a Subform, and is often something like Child6 ...

0
 
jtruslerAuthor Commented:
Thanks for your notes.  I've tried something similar to that before.  Let me give it another try.
0
 
jtruslerAuthor Commented:
I'm getting an error "Can't find the field "|" referred to you in your expression"

I used the following:
DoCmd.OpenForm "JohnPopUpCalendar", acNormal,,,,acDialog,Me.[frmJobInfoforSub1].Form.[HOA Approval Date]
frmJobIndoforSub1 is the subform control
[HOA Approval Date] is the control that gets updated by the calendar
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Ok ... you are trying to use OpenArgs, right ... to send a parameter to the other fomr?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Possibly a spelling error in this part?

Me.[frmJobInfoforSub1].Form.[HOA]

Is HOA an actual field in the RecordSource of your main form?

mx
0
 
jtruslerAuthor Commented:
I checked spelling several times. HOA is a field on the subform that is linked to the main form.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This line of code:

DoCmd.OpenForm "JohnPopUpCalendar", acNormal,,,,acDialog,Me.[frmJobInfoforSub1].Form.[HOA Approval Date]

Which form is this in ... the subform?
If so, try this:

DoCmd.OpenForm "JohnPopUpCalendar", acNormal,,,,acDialog,Me.[HOA Approval Date]

In other words, you are on the subform, you click a button to open the Calendar form ... and you wsnt to pass [HOA Approval Date]

If that doesn't work, try:


DoCmd.OpenForm "JohnPopUpCalendar", acNormal,,,,acDialog,Me("HOA Approval Date")

mx
0
 
jtruslerAuthor Commented:
In both cases I get "Error 2498 -An expression you entered is the wrong data type for one of your arguments."

My calendar form doesn't open now. If I stop the code after "JohnPopUpCalendar" it does open.

It has to be something with the reference??
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok ... show me all the code now in that area.

Also ... try this - because OpenArgs needs to be a String:

DoCmd.OpenForm "JohnPopUpCalendar", acNormal,,,,acDialog, CStr(Me.[HOA Approval Date])

mx
0
 
jtruslerAuthor Commented:
Private Sub btnHOAApproval_Click()
DoCmd.OpenForm "JohnPopUpCalendar", acNormal,,,,acDialog, CStr(Me.[HOA Approval Date])
End Sub

I get Run Time 94.  Invalid use of Null
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That would indicate that there is no value in Me.[HOA Approval Date] ... you can use the Nz function if you want to pass a 0 or some other value:

DoCmd.OpenForm "JohnPopUpCalendar", acNormal,,,,acDialog, CStr(Nz(Me.[HOA Approval Date],0))
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yes ... OpenArgs cannot be Null ...

DoCmd.OpenForm "JohnPopUpCalendar", acNormal,,,,acDialog, CStr(Nz(Me.[HOA Approval Date],""))

Convert Null to an empty String ...

mx
0
 
jtruslerAuthor Commented:
The reason that we are passing null values is that this code does not open the calendar pop up and then passes the null.  I still think that it is a reference issue.

This works just fine when I am on a main form.

When i make the date selection using the calendar pop up it passes the vale to my control.  The issue arisies when I am on a subform passing a value to the same subform.  The pop up calendar uses the date I select and when I click OK it runs:

Private Sub OK_Click()
'if passed parameter then set date on control passed to selected date
    If IsNull(Me.OpenArgs) Then GoTo Done
    Dim xForm As String, xControl As String
    i = InStr(1, Me.OpenArgs, ".")
    xForm = Left(Me.OpenArgs, i - 1)
    xControl = Mid(Me.OpenArgs, i + 1)
    forms(xForm)(xControl) = Me.DispDate
Done:
    DoCmd.Close
End Sub

I need to use the code that we have been playing with to open the calendar pop up so I can select a value.  Does this help?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OpenArgs **cannot** be Null ... it **must** be a string value.

OpenArgs Property
See AlsoApplies ToExampleSpecificsDetermines the string expression specified by the OpenArgs argument of the OpenForm method that opened a form. Read/write Variant.

expression.OpenArgs
expression    Required. An expression that returns one of the objects in the Applies To list.

Remarks
This property is available only by using a macro or by using Visual Basic with the OpenForm method of the DoCmd object. This property setting is read-only in all views.

To use the OpenArgs property, open a form by using the OpenForm method of the DoCmd object and set the OpenArgs argument to the desired string expression. The OpenArgs property setting can then be used in code for the form, such as in an Open event procedure. You can also refer to the property setting in a macro, such as an Open macro, or an expression, such as an expression that sets the ControlSource property for a control on the form.

For example, suppose that the form you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"    If IsNull(Me.OpenArgs) Then GoTo Done"

Sure ... you can *test* for Null, but ... if ... you try to pass a Null ... you will get the error you are seeing.  Thus ... a test for Null is not necessary.  If anything ... you would do:

If Me.OpenArgs="" Then GoTo Done

mx
0
 
jtruslerAuthor Commented:
MX,

Not sure where you are pointing me. Even with the changes, the calendar pop up doesn't open.

John
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Hummm ...

ok try just t his much:

DoCmd.OpenForm "JohnPopUpCalendar"
0
 
jtruslerAuthor Commented:
This opens the pop up calendar.  If I select a date and press OK, nothing happens.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Did you fix the issue with the Null OpenArgs?  

Also, in the Calendar, I assume you are extracting the date to use?

I have to leave for LA ... I'll be back laster ...

If nothing else ....

Can you upload to http://www.ee-stuff.com/Expert/Upload/upload.php ... removing any sensitive data of course?  

Then, provide me the link EE-Stuff gives you to the file location ... back here in this thread.

Note: There is a  4MB upload limit.

mx
0
 
mbizupCommented:
Sorry, guys! That was a big mistake...
Revised recommendation:

 Split: DatabaseMX {http:#19673727} & LSMConsulting {http:#LSMConsulting}
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.