?
Solved

Subform reference

Posted on 2007-08-10
26
Medium Priority
?
972 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:jtrusler
  • 11
  • 10
  • 2
  • +1
24 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 19673727
Forms("John'sPopUp").ControlName

mx
0
 

Author Comment

by:jtrusler
ID: 19676716
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
 
LVL 75
ID: 19676785
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jtrusler
ID: 19677038
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
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 19677506
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
 

Author Comment

by:jtrusler
ID: 19677521
Thanks for your notes.  I've tried something similar to that before.  Let me give it another try.
0
 

Author Comment

by:jtrusler
ID: 19679641
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
 
LVL 75
ID: 19679732
Ok ... you are trying to use OpenArgs, right ... to send a parameter to the other fomr?

mx
0
 
LVL 75
ID: 19679734
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
 

Author Comment

by:jtrusler
ID: 19679828
I checked spelling several times. HOA is a field on the subform that is linked to the main form.
0
 
LVL 75
ID: 19679848
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
 

Author Comment

by:jtrusler
ID: 19679882
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
 
LVL 75
ID: 19679886
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
 

Author Comment

by:jtrusler
ID: 19679900
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
 
LVL 85
ID: 19680157
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
 
LVL 75
ID: 19680221
Yes ... OpenArgs cannot be Null ...

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

Convert Null to an empty String ...

mx
0
 

Author Comment

by:jtrusler
ID: 19680290
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
 
LVL 75
ID: 19680307
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
 
LVL 75
ID: 19680318
"    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
 

Author Comment

by:jtrusler
ID: 19680429
MX,

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

John
0
 
LVL 75
ID: 19680494
Hummm ...

ok try just t his much:

DoCmd.OpenForm "JohnPopUpCalendar"
0
 

Author Comment

by:jtrusler
ID: 19680503
This opens the pop up calendar.  If I select a date and press OK, nothing happens.
0
 
LVL 75
ID: 19680515
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
 
LVL 61

Expert Comment

by:mbizup
ID: 19940245
Sorry, guys! That was a big mistake...
Revised recommendation:

 Split: DatabaseMX {http:#19673727} & LSMConsulting {http:#LSMConsulting}
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

864 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