-----Original Message-----
Sandra:
Thanks for the prompt reply.
Additional info:
- Yes, each BilletCode (essentially employee) works only
in 1 division and has only a single phone extension.
There can be multipe officers assigned to a
Correspondence case though
- In respect to updating the division and extension.
Unless a change in the subform would automatically update
the source table (tbl_BilletCodes), I believe it is
safest to not allow the user to update their division and
extension via the subform. This would ensure that I
maintain data integrity.
Thanks so much,
Tom
-----Original Message-----
Hi Tom,
Sometimes it is very difficult to find a problem without
seeing the same
thing that you are seeing. I don't mind taking a look when I have time
since I have been in the learners seat and I know firsthand that it
can be very frustrating to try to accomplish something that seems
rather doable, yet fails in spite of my best efforts!
The key to your immediate problem has to do with names.
The form object is
the standalone form and it's name is the name you see in
the database window
under the forms tab. However to refer to a control on a
subform you must
know the name of the subform control. Keep in mind that
the subform control
is just a window through which any form object could be
opened.
From the main form, click once on the subform control and then look at
the Name property which is found under the "Other" tab. In your case
the subform control is named:
"tblCorrespondenceActionOfficer Subform"
the form object which is opened in the subform (ie specified in the
SourceObject of the Subform control) is:
"frmCorrespondenceBilletCodes_Subform"
In your rowsource query for Division you were using the
name of the form
object instead of the name of the subform control. Instead, the query
should be as follows:
SELECT tbl_BilletCodes.BilletCode, tbl_BilletCodes.Division
FROM tbl_BilletCodes
WHERE
(((tbl_BilletCodes.BilletCode)=[Forms]!
[frmCorrespondence]![tblCorrespondenc
eActionOfficer Subform].[form]![BilletCode]))
ORDER BY tbl_BilletCodes.Division;
If you are using the Query Designer Grid you can simply
put the following
into the criteria under BilletCode:
[Forms]![frmCorrespondence]!
[tblCorrespondenceActionOfficer
Subform].[form]![BilletCode]
Now for the fun part, if I understand your design correctly for each
BilletCode there is only one Division and one Extension.
You really don't
need the second combo control. Instead you can pull the
related values for
Division and Extension directly from the combo for BilletCode by
making a few minor changes. But - before I go on I want to make sure
you understand what I've explained above.
Also, since there are a couple of different ways to do this I would
also need to know whether you want to allow the user to change
Division and/or Extension in this form and whether those changes should
be applied to
tbl_BilletCodes.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
Tom wrote:
Sandra:
This is so kind of you!!!
I sent the email w/ the attachment a moment ago.
Thanks so much!
Tom
-----Original Message-----
Hi Tom,
Don't give up! How big is your database? If you can compact it and
then zip it down to a reasonable size you can email it to
me.
I'll have a look then
let you know exactly how to fix it. To compact, go to
Tools->Database Utilities->Compact & Repair.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
Tom wrote:
Sandra:
I just don't seem to get it to work. I have read the
suggested website. There is too much specific info
there.
Based on the example given, I don't know which values
(i.e. "frmMain") should be replaced with my naming
convention or should not be modified.
I give up!
Thanks for you help,
Tom
-----Original Message-----
Hi Tom,
Lets back up to the beginning - you have a pair of
combo boxes, The
second ( "Division") is dependant on the first
("Billetcode").
I am assuming that
in the rowsource query for Division, you have a reference to the
BilletCode combo control on the same form (as described in the
following KB article)
ACC: How to Synchronize Two Combo Boxes on a Form (97624)
http://support.microsoft.com/default.aspx? scid=kb;
[LN];97624
Since your combos are on a subform, the reference has to be
modified in the SQL of the rowsource query for Division to refer
to Billetcode as a member of a subform - so the where clause of your
rowsource query
would look something like this:
Where
division=Forms!frmCorrespondence!
frmCorrespondenceBilletCodes_subform.form!b
illetCode
In the above, note that
frmCorrespondenceBilletCodes_subform should be the
name of the subform control. Billetcode is the combo control and
it is referenced as a member of the controls collection
of the subform's
form.
Then the AfterUpdate event of BilletCode should requery the
Division combo control.
The reason I questioned your other code is that the
following:
Set BilletCode =
[Forms]![frmCorrespondence]![BilletCode].[Form]! [BilletCode]
Set Division = [Forms]![frmCorrespondence]! [BilletCode].
[Form]![Division]
simply creates two VBA variables that refer to the
BilletCode and
Division code combo controls on a subform named
BilletCode. If
the subform is not
named Billetcode, then the references are invalid and will fail.
Even if they are valid, you are not doing anything with
the vba variables
so I do not see their purpose. They are not necessary for
synchronizing combo controls.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.
Tom wrote:
Sandra:
Again, thanks for your feedback... unfortunately,
I am
not smarter at this time.
Didn't I put the same statement into the function?
You
also mentioned that you didn't see the purpose of the 2
statements...
I truly would appreciate if you could provide me
additional pointers?
At this moment, I still get errors that the main form
cannot find the field.... and the subform won't allow me
to use the function as is either.
I am lost!!!
Thanks in advance,
Tom
-----Original Message-----
Hi Tom,
Try
Forms!frmCorrespondence!
frmCorrespondenceBilletCodes_subform.form! billetCode
I'm not sure I understand the purpose of these two statements:
Set BilletCode =
[Forms]![frmCorrespondence]![BilletCode]. [Form]!
[BilletCode]
Set Division = [Forms]![frmCorrespondence]! [BilletCode].
[Form]![Division]
Presumably they should be
Set BilletCode =
Forms!frmCorrespondence!
frmCorrespondenceBilletCodes_subform.form!
billetCode
Set Division =
Forms!frmCorrespondence!
frmCorrespondenceBilletCodes_subform.form! Division
but I don't see the purpose of having these Combo
control variables -
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies
to this newsgroup.
Tom wrote:
Hello Sandra:
Thanks for your prompt reply... this looks very
promising... unfortunately, I still haven't been
able to
get it to work.
I did not your comment about "sequencing" the control
name. Hopefully, I understood this correctly. This is
what I have done so far.
Name of Main Form: "frmCorrespondence"
Name of Sub
Form: "frmCorrespondenceBilletCodes_Subform"
Primary Combobox Name: "BilletCode"
Dependent Combobox Name: "Division"
Query Criteria:
[Forms]![frmCorrespondence]![BilletCode]. [Form]!
[BilletCode]
Note: This yields the correct answer, once a value is
entered at the parameter prompt
Function:
**************
Private Sub BilletCode_Change()
Dim BilletCode As ComboBox, Division As ComboBox
Set BilletCode = [Forms]! [frmCorrespondence]!
[BilletCode].[Form]![BilletCode]
Set Division = [Forms]![frmCorrespondence]!
[BilletCode].[Form]![Division]
Echo False
Division.Requery
Me.Division = Me.Division.ItemData(0)
Echo True
End Sub
**************
I appreciate if you could provide me any additional
pointers on this.
Thanks so much in advance,
Tom
-----Original Message-----
Hi Tom,
The reference must be through the subform control on the main
form. Since the subform is not open independantly it is not a
member of the forms collection.
Change your reference to something like:
forms!frmMain!sfrmCombo.form!BilletCode
Note that 'sfrmCombo' must be the name of the subform control
on the main form. This is not necessarily the same
as the name of the
form object that is referenced in the ControlSource of the
subform control. To be sure, open the main form and click once
on the subform then check the name property under the
Other tab. Whatever
you find there is what belongs in place
of 'sfrmCombo'.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.
Tom wrote:
I need some help with the function below. Although it is
working on a MAIN FORM, it does not work in conjunction
with a SUBFORM.
What am I trying to achieve:
- Have a combo "BilletCode"
- The value of combo "Extension" is updated when
the "BilletCode" changes
Problem:
- Again, this works fine when the combos reside on the
main form. However, once I use the
form "frmCombo" as a
subform on e.g. "frmMain" then changes the value in the
subform will throw an error (it can't field the field).
It won't work either if I change the following...
[Forms]![frmCombo]![BilletCode] to
[Forms]![frmMain]![BilletCode]
Does anyone know of a solution to this?
Thanks!
Tom
++++
Existing Query:
- Field: Extension
- Field: BilletCode
(criteria: [Forms]![frmCombo]![BilletCode]
++++
****
Private Sub BilletCode_Change()
Dim BilletCode As ComboBox, Extension As ListBox
Set BilletCode = Forms![frmCombo]. [BilletCode]
Set Extension = Forms![frmCombo]. [Extension]
Echo False
Extension.Requery
Me.Extension = Me.Extension.ItemData(0)
Echo True
End Sub
****
.
.
.
.
.
.
