Referring to a form in a query - Syntax

[Guest]

The following reference works when it is present on the active loaded form:
"=Forms!frmMainTab!cboVendor"; however, it will not work on any other form,
or on a report, or in a query. I think it is my understanding that I should
use "=Forms!Me!cboVendor" when referencing the active loaded form, but that
does not work for me either.

How do I properly reference a combo box on a form when running a query that
is embedded in a report?

Here's what I currently have. (You should probably be able to ignore all
but the last couple of lines, and I wish you would because I did not write
this query, nor am I eager to take credit for it.):

SELECT ManInfo.L1, IIf([L1]="E","Electronic Manuals","") AS L2Electronic,
IIf([L1]="RC","Recalled Manuals","") AS L2Recalledt, IIf([L1]="BC","Boeing
Cartridges","") AS L2BoeingCart, IIf([L1]="BP","Boeing Paper Manuals","") AS
L2BoeingPaper, IIf([L1]="MC","Miscellaneous Cartridges","") AS L2MiscCart,
IIf([L1]="PC","Pratt & Whitney Cartridges","") AS L2PrattCart,
IIf([L1]="PP","Pratt & Whitney Paper Manuals","") AS L2PrattPaper, IIf([L1]
Is Null,"Unknown Manuals - L1 Type Not Completed","") AS L2UnknownManual,
IIf([L1]="SP","Spirit Paper Manuals","") AS L1SpiritPaper,
[L1]+LTrim(Str([L2])) AS L1Show, ManInfo.L2,
IIf(ManInfo!MediaType="Electronic","File Name is: " & ManInfo!FileName,"") AS
FileNameYes, [MediaType]+" / "+Trim(Str([MediaNum])) AS MediaShow,
ManInfo.LibID, ManInfo.ManID, ManInfo.ManTitle, ManInfo.Vendor,
ManInfo.ManTyp, ManInfo.FileName, ManInfo.ATA, ManInfo.MediaType,
ManInfo.MediaNum, ManInfo.CurRevNum, ManInfo.CurRevDate, ManInfo.CurTRNum,
ManInfo.CurTRDate, ManInfo.LastAud, ManInfo.AnnCost, ManInfo.AnnDupCost,
ManInfo.[FN-Dir]
FROM ManInfo
WHERE (((ManInfo.ManID)<>0) AND
((ManInfo.Vendor)=[Forms]![frmMainTab]![cboVendor]))
ORDER BY ManInfo.ManTitle;

(BTW, I did not write this original query, nor did I design the original
database, and I do hope to someday replace the entire mess; however, if I can
not even get the syntax right to make this one simple update, I will never be
able to take on the entire project.)

 

[John Vinson]

The following reference works when it is present on the active loaded form:
"=Forms!frmMainTab!cboVendor"; however, it will not work on any other form,
or on a report, or in a query. I think it is my understanding that I should
use "=Forms!Me!cboVendor" when referencing the active loaded form, but that
does not work for me either.

That's not quite how the Me! keyword works. If you have code running
as part of Forms!frmMainTab, then you can use either

Forms!frmMainTab!cboVendor

or

Me!cboVendor

in the VBA code for that form. If the code is in some other form, or
report, or module, then Me! refers to THAT form or report, and you
must use the full reference in order to see a control on frmMainTab.

How do I properly reference a combo box on a form when running a query that
is embedded in a report?

The query isn't "embedded" in the report I presume. The Report is
based on the Query (i.e. uses the query as its Recordsource).

Here's what I currently have. (You should probably be able to ignore all
but the last couple of lines, and I wish you would because I did not write
this query, nor am I eager to take credit for it.):

SELECT ManInfo.L1, IIf([L1]="E","Electronic Manuals","") AS L2Electronic,
IIf([L1]="RC","Recalled Manuals","") AS L2Recalledt, IIf([L1]="BC","Boeing
Cartridges","") AS L2BoeingCart, IIf([L1]="BP","Boeing Paper Manuals","") AS
L2BoeingPaper, IIf([L1]="MC","Miscellaneous Cartridges","") AS L2MiscCart,
IIf([L1]="PC","Pratt & Whitney Cartridges","") AS L2PrattCart,
IIf([L1]="PP","Pratt & Whitney Paper Manuals","") AS L2PrattPaper, IIf([L1]
Is Null,"Unknown Manuals - L1 Type Not Completed","") AS L2UnknownManual,
IIf([L1]="SP","Spirit Paper Manuals","") AS L1SpiritPaper,
[L1]+LTrim(Str([L2])) AS L1Show, ManInfo.L2,
IIf(ManInfo!MediaType="Electronic","File Name is: " & ManInfo!FileName,"") AS
FileNameYes, [MediaType]+" / "+Trim(Str([MediaNum])) AS MediaShow,
ManInfo.LibID, ManInfo.ManID, ManInfo.ManTitle, ManInfo.Vendor,
ManInfo.ManTyp, ManInfo.FileName, ManInfo.ATA, ManInfo.MediaType,
ManInfo.MediaNum, ManInfo.CurRevNum, ManInfo.CurRevDate, ManInfo.CurTRNum,
ManInfo.CurTRDate, ManInfo.LastAud, ManInfo.AnnCost, ManInfo.AnnDupCost,
ManInfo.[FN-Dir]
FROM ManInfo
WHERE (((ManInfo.ManID)<>0) AND
((ManInfo.Vendor)=[Forms]![frmMainTab]![cboVendor]))
ORDER BY ManInfo.ManTitle;

(BTW, I did not write this original query, nor did I design the original
database, and I do hope to someday replace the entire mess; however, if I can
not even get the syntax right to make this one simple update, I will never be
able to take on the entire project.)

This will work if and only if frmMainTab is open at the time the
report is executed. You can see to it that this is true in a couple of
ways. One is to put a command button on frmMainTab to open the Report,
and encourage - nay, insist - that the users open the report only from
this command button, not from the Reports window.

Another is to open frmMainTab in the Open event of the report; open it
in Dialog mode, and provide a button on the form which sets the Form's
Visible property to False. A Dialog form stops code execution while
it's open *and visible*; you don't want to close the form (because you
need that combo box!) but making it invisible lets the report continue
to open. If you do this, be sure to close the form in the Report's
Close event.

John W. Vinson[MVP]

 

[Guest]

John, Thank you very much for your quick response to my posting.

I did not properly explain my problem. Apparently, I don't communicate in
English any better than VBA.

I have a button on my form that opens a report which is based on a query
(not an actual query that is saved with a query name but an SQL statement
that is part of the properties of the report). When the report opens, it
does not close the form, so to my thinking, it should read the combo box on
the form just as easily as a text box which is on the form itself.

The following syntax works just fine when it is in a text box on the same
form:
"Forms!frmMainTab!cboVendor"

The same exact words do not work when I put them in the underlying SQL
statement for the report, even when the form itself is open.

I failed to mention before that the form has multible tabs, but it is not in
a subform so I don't see why that should matter.

There has to be something simple that I'm overlooking. Or maybe the syntax
should be different for the statement if it is part of an SQL statement that
is part of a report?

Thnx!

The following reference works when it is present on the active loaded form:
"=Forms!frmMainTab!cboVendor"; however, it will not work on any other form,
or on a report, or in a query. I think it is my understanding that I should
use "=Forms!Me!cboVendor" when referencing the active loaded form, but that
does not work for me either.

That's not quite how the Me! keyword works. If you have code running
as part of Forms!frmMainTab, then you can use either

Forms!frmMainTab!cboVendor

or

Me!cboVendor

in the VBA code for that form. If the code is in some other form, or
report, or module, then Me! refers to THAT form or report, and you
must use the full reference in order to see a control on frmMainTab.

How do I properly reference a combo box on a form when running a query that
is embedded in a report?

The query isn't "embedded" in the report I presume. The Report is
based on the Query (i.e. uses the query as its Recordsource).

Here's what I currently have. (You should probably be able to ignore all
but the last couple of lines, and I wish you would because I did not write
this query, nor am I eager to take credit for it.):

SELECT ManInfo.L1, IIf([L1]="E","Electronic Manuals","") AS L2Electronic,
IIf([L1]="RC","Recalled Manuals","") AS L2Recalledt, IIf([L1]="BC","Boeing
Cartridges","") AS L2BoeingCart, IIf([L1]="BP","Boeing Paper Manuals","") AS
L2BoeingPaper, IIf([L1]="MC","Miscellaneous Cartridges","") AS L2MiscCart,
IIf([L1]="PC","Pratt & Whitney Cartridges","") AS L2PrattCart,
IIf([L1]="PP","Pratt & Whitney Paper Manuals","") AS L2PrattPaper, IIf([L1]
Is Null,"Unknown Manuals - L1 Type Not Completed","") AS L2UnknownManual,
IIf([L1]="SP","Spirit Paper Manuals","") AS L1SpiritPaper,
[L1]+LTrim(Str([L2])) AS L1Show, ManInfo.L2,
IIf(ManInfo!MediaType="Electronic","File Name is: " & ManInfo!FileName,"") AS
FileNameYes, [MediaType]+" / "+Trim(Str([MediaNum])) AS MediaShow,
ManInfo.LibID, ManInfo.ManID, ManInfo.ManTitle, ManInfo.Vendor,
ManInfo.ManTyp, ManInfo.FileName, ManInfo.ATA, ManInfo.MediaType,
ManInfo.MediaNum, ManInfo.CurRevNum, ManInfo.CurRevDate, ManInfo.CurTRNum,
ManInfo.CurTRDate, ManInfo.LastAud, ManInfo.AnnCost, ManInfo.AnnDupCost,
ManInfo.[FN-Dir]
FROM ManInfo
WHERE (((ManInfo.ManID)<>0) AND
((ManInfo.Vendor)=[Forms]![frmMainTab]![cboVendor]))
ORDER BY ManInfo.ManTitle;

(BTW, I did not write this original query, nor did I design the original
database, and I do hope to someday replace the entire mess; however, if I can
not even get the syntax right to make this one simple update, I will never be
able to take on the entire project.)

This will work if and only if frmMainTab is open at the time the
report is executed. You can see to it that this is true in a couple of
ways. One is to put a command button on frmMainTab to open the Report,
and encourage - nay, insist - that the users open the report only from
this command button, not from the Reports window.

Another is to open frmMainTab in the Open event of the report; open it
in Dialog mode, and provide a button on the form which sets the Form's
Visible property to False. A Dialog form stops code execution while
it's open *and visible*; you don't want to close the form (because you
need that combo box!) but making it invisible lets the report continue
to open. If you do this, be sure to close the form in the Report's
Close event.

John W. Vinson[MVP]

 

[Douglas J. Steele]

"Doesn't work" really doesn't give us much to go on.

Do you get a prompt when you run the query, asking you to enter a value for
Forms!frmMainTab!cboVendor, do you get an error message, or does the query
run, but not return the expected results?

If you're getting a prompt (and the form is open), are you positive you
haven't mistyped either the form name or the control name?

If you're getting an error, what's the error?

If the query runs but you're not getting the expected results, are you sure
that the bound field in the combobox is what you think it is? In other
words, you may be thinking that the reference to the combo box is going to
give you the Vendor Id, but in actual fact it may be giving you the Vendor
Name (or vice versa)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John, Thank you very much for your quick response to my posting.

I did not properly explain my problem. Apparently, I don't communicate in
English any better than VBA.

I have a button on my form that opens a report which is based on a query
(not an actual query that is saved with a query name but an SQL statement
that is part of the properties of the report). When the report opens, it
does not close the form, so to my thinking, it should read the combo box
on
the form just as easily as a text box which is on the form itself.

The following syntax works just fine when it is in a text box on the same
form:
"Forms!frmMainTab!cboVendor"

The same exact words do not work when I put them in the underlying SQL
statement for the report, even when the form itself is open.

I failed to mention before that the form has multible tabs, but it is not
in
a subform so I don't see why that should matter.

There has to be something simple that I'm overlooking. Or maybe the
syntax
should be different for the statement if it is part of an SQL statement
that
is part of a report?

Thnx!

The following reference works when it is present on the active loaded
form:
"=Forms!frmMainTab!cboVendor"; however, it will not work on any other
form,
or on a report, or in a query. I think it is my understanding that I
should
use "=Forms!Me!cboVendor" when referencing the active loaded form, but
that
does not work for me either.

That's not quite how the Me! keyword works. If you have code running
as part of Forms!frmMainTab, then you can use either

Forms!frmMainTab!cboVendor

or

Me!cboVendor

in the VBA code for that form. If the code is in some other form, or
report, or module, then Me! refers to THAT form or report, and you
must use the full reference in order to see a control on frmMainTab.

How do I properly reference a combo box on a form when running a query
that
is embedded in a report?

The query isn't "embedded" in the report I presume. The Report is
based on the Query (i.e. uses the query as its Recordsource).

Here's what I currently have. (You should probably be able to ignore
all
but the last couple of lines, and I wish you would because I did not
write
this query, nor am I eager to take credit for it.):

SELECT ManInfo.L1, IIf([L1]="E","Electronic Manuals","") AS
L2Electronic,
IIf([L1]="RC","Recalled Manuals","") AS L2Recalledt,
IIf([L1]="BC","Boeing
Cartridges","") AS L2BoeingCart, IIf([L1]="BP","Boeing Paper
Manuals","") AS
L2BoeingPaper, IIf([L1]="MC","Miscellaneous Cartridges","") AS
L2MiscCart,
IIf([L1]="PC","Pratt & Whitney Cartridges","") AS L2PrattCart,
IIf([L1]="PP","Pratt & Whitney Paper Manuals","") AS L2PrattPaper,
IIf([L1]
Is Null,"Unknown Manuals - L1 Type Not Completed","") AS
L2UnknownManual,
IIf([L1]="SP","Spirit Paper Manuals","") AS L1SpiritPaper,
[L1]+LTrim(Str([L2])) AS L1Show, ManInfo.L2,
IIf(ManInfo!MediaType="Electronic","File Name is: " &
ManInfo!FileName,"") AS
FileNameYes, [MediaType]+" / "+Trim(Str([MediaNum])) AS MediaShow,
ManInfo.LibID, ManInfo.ManID, ManInfo.ManTitle, ManInfo.Vendor,
ManInfo.ManTyp, ManInfo.FileName, ManInfo.ATA, ManInfo.MediaType,
ManInfo.MediaNum, ManInfo.CurRevNum, ManInfo.CurRevDate,
ManInfo.CurTRNum,
ManInfo.CurTRDate, ManInfo.LastAud, ManInfo.AnnCost, ManInfo.AnnDupCost,
ManInfo.[FN-Dir]
FROM ManInfo
WHERE (((ManInfo.ManID)<>0) AND
((ManInfo.Vendor)=[Forms]![frmMainTab]![cboVendor]))
ORDER BY ManInfo.ManTitle;

(BTW, I did not write this original query, nor did I design the original
database, and I do hope to someday replace the entire mess; however, if
I can
not even get the syntax right to make this one simple update, I will
never be
able to take on the entire project.)

This will work if and only if frmMainTab is open at the time the
report is executed. You can see to it that this is true in a couple of
ways. One is to put a command button on frmMainTab to open the Report,
and encourage - nay, insist - that the users open the report only from
this command button, not from the Reports window.

Another is to open frmMainTab in the Open event of the report; open it
in Dialog mode, and provide a button on the form which sets the Form's
Visible property to False. A Dialog form stops code execution while
it's open *and visible*; you don't want to close the form (because you
need that combo box!) but making it invisible lets the report continue
to open. If you do this, be sure to close the form in the Report's
Close event.

John W. Vinson[MVP]

 

[Speakeasy]

I have a similar problem and used the answers given with success. My
situation was a text box for input on a main form with a query attached to a
subform. Worked fine.

 

[Guest]

How will you ever believe me again?

On Friday, the expression was producing a prompt when I included it as the
criteria for a query or "Name?" when I pasted it on a report. I posted my
question, went home disgusted, forgot about the problem all weekend until you
responded yesterday, came in this morning, and VOILA! it works.

Honestly, I wouldn't even know how to score crack if I tried. Momma always
said experimenting in high school would come back to get me later...

I don't know how or why this did not work before, but I will thank the
Access gods for their mercy, their blessed messenger for your patience, and
go back to my trial and error method hoping not to make too much of a fool of
myself.

humbly,
dale

"Doesn't work" really doesn't give us much to go on.

Do you get a prompt when you run the query, asking you to enter a value for
Forms!frmMainTab!cboVendor, do you get an error message, or does the query
run, but not return the expected results?

If you're getting a prompt (and the form is open), are you positive you
haven't mistyped either the form name or the control name?

If you're getting an error, what's the error?

If the query runs but you're not getting the expected results, are you sure
that the bound field in the combobox is what you think it is? In other
words, you may be thinking that the reference to the combo box is going to
give you the Vendor Id, but in actual fact it may be giving you the Vendor
Name (or vice versa)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John, Thank you very much for your quick response to my posting.

I did not properly explain my problem. Apparently, I don't communicate in
English any better than VBA.

I have a button on my form that opens a report which is based on a query
(not an actual query that is saved with a query name but an SQL statement
that is part of the properties of the report). When the report opens, it
does not close the form, so to my thinking, it should read the combo box
on
the form just as easily as a text box which is on the form itself.

The following syntax works just fine when it is in a text box on the same
form:
"Forms!frmMainTab!cboVendor"

The same exact words do not work when I put them in the underlying SQL
statement for the report, even when the form itself is open.

I failed to mention before that the form has multible tabs, but it is not
in
a subform so I don't see why that should matter.

There has to be something simple that I'm overlooking. Or maybe the
syntax
should be different for the statement if it is part of an SQL statement
that
is part of a report?

Thnx!

The following reference works when it is present on the active loaded
form:
"=Forms!frmMainTab!cboVendor"; however, it will not work on any other
form,
or on a report, or in a query. I think it is my understanding that I
should
use "=Forms!Me!cboVendor" when referencing the active loaded form, but
that
does not work for me either.

That's not quite how the Me! keyword works. If you have code running
as part of Forms!frmMainTab, then you can use either

Forms!frmMainTab!cboVendor

or

Me!cboVendor

in the VBA code for that form. If the code is in some other form, or
report, or module, then Me! refers to THAT form or report, and you
must use the full reference in order to see a control on frmMainTab.

How do I properly reference a combo box on a form when running a query
that
is embedded in a report?

The query isn't "embedded" in the report I presume. The Report is
based on the Query (i.e. uses the query as its Recordsource).

Here's what I currently have. (You should probably be able to ignore
all
but the last couple of lines, and I wish you would because I did not
write
this query, nor am I eager to take credit for it.):

<g> No comment... well, I do have comments but I'll restraing
myself...

SELECT ManInfo.L1, IIf([L1]="E","Electronic Manuals","") AS
L2Electronic,
IIf([L1]="RC","Recalled Manuals","") AS L2Recalledt,
IIf([L1]="BC","Boeing
Cartridges","") AS L2BoeingCart, IIf([L1]="BP","Boeing Paper
Manuals","") AS
L2BoeingPaper, IIf([L1]="MC","Miscellaneous Cartridges","") AS
L2MiscCart,
IIf([L1]="PC","Pratt & Whitney Cartridges","") AS L2PrattCart,
IIf([L1]="PP","Pratt & Whitney Paper Manuals","") AS L2PrattPaper,
IIf([L1]
Is Null,"Unknown Manuals - L1 Type Not Completed","") AS
L2UnknownManual,
IIf([L1]="SP","Spirit Paper Manuals","") AS L1SpiritPaper,
[L1]+LTrim(Str([L2])) AS L1Show, ManInfo.L2,
IIf(ManInfo!MediaType="Electronic","File Name is: " &
ManInfo!FileName,"") AS
FileNameYes, [MediaType]+" / "+Trim(Str([MediaNum])) AS MediaShow,
ManInfo.LibID, ManInfo.ManID, ManInfo.ManTitle, ManInfo.Vendor,
ManInfo.ManTyp, ManInfo.FileName, ManInfo.ATA, ManInfo.MediaType,
ManInfo.MediaNum, ManInfo.CurRevNum, ManInfo.CurRevDate,
ManInfo.CurTRNum,
ManInfo.CurTRDate, ManInfo.LastAud, ManInfo.AnnCost, ManInfo.AnnDupCost,
ManInfo.[FN-Dir]
FROM ManInfo
WHERE (((ManInfo.ManID)<>0) AND
((ManInfo.Vendor)=[Forms]![frmMainTab]![cboVendor]))
ORDER BY ManInfo.ManTitle;

(BTW, I did not write this original query, nor did I design the original
database, and I do hope to someday replace the entire mess; however, if
I can
not even get the syntax right to make this one simple update, I will
never be
able to take on the entire project.)

This will work if and only if frmMainTab is open at the time the
report is executed. You can see to it that this is true in a couple of
ways. One is to put a command button on frmMainTab to open the Report,
and encourage - nay, insist - that the users open the report only from
this command button, not from the Reports window.

Another is to open frmMainTab in the Open event of the report; open it
in Dialog mode, and provide a button on the form which sets the Form's
Visible property to False. A Dialog form stops code execution while
it's open *and visible*; you don't want to close the form (because you
need that combo box!) but making it invisible lets the report continue
to open. If you do this, be sure to close the form in the Report's
Close event.

John W. Vinson[MVP]