Prev: video or animations on ms accses 2003
Next: How do you execute an Access Pgm?
From: Craig on 15 Mar 2010 16:18
I am currently trying to get to grips with Access 2003, and i seem to have a
handle on some of the basics.

I have managed to write a couple of queries, which feed from the one before
ie a list of results is displayed, the user then clicks an entry, and this
then goes into a second query to populate a second list box.

The thing i am trying to do is now have a query on a second form [Form2]
reference a click from [Form1]

I thought the criteria would read as this

[Forms]![Form1]![List7] [List7] being the field on the first form, but
it does not seem to be the case.

Hopefully it is a naming convention i am failing to get correct
From: Jeff Boyce on 15 Mar 2010 17:01
Craig

What is the content(s) of [List7]? Is that a listbox? If so, which item is
seleted (or can multiple items be selected)? When you pass the value of
that listbox (?) to your query, what does Access tell you?

Is the form in which this control exists actually open when you try to run
the query?

Please post the SQL statement of the query.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Craig" <Craig(a)discussions.microsoft.com> wrote in message
news:EC8CCC7E-A99F-4B99-B975-85101C2A22E4(a)microsoft.com...
>I am currently trying to get to grips with Access 2003, and i seem to have
>a
> handle on some of the basics.
>
> I have managed to write a couple of queries, which feed from the one
> before
> ie a list of results is displayed, the user then clicks an entry, and this
> then goes into a second query to populate a second list box.
>
> The thing i am trying to do is now have a query on a second form [Form2]
> reference a click from [Form1]
>
> I thought the criteria would read as this
>
> [Forms]![Form1]![List7] [List7] being the field on the first form,
> but
> it does not seem to be the case.
>
> Hopefully it is a naming convention i am failing to get correct


From: KARL DEWEY on 15 Mar 2010 17:20
Post the SQL of both queries by open in design view, clicking on VIEW - SQL
View, highlight all, copy, and paste in a post.

--
Build a little, test a little.


"Craig" wrote:

> I am currently trying to get to grips with Access 2003, and i seem to have a
> handle on some of the basics.
>
> I have managed to write a couple of queries, which feed from the one before
> ie a list of results is displayed, the user then clicks an entry, and this
> then goes into a second query to populate a second list box.
>
> The thing i am trying to do is now have a query on a second form [Form2]
> reference a click from [Form1]
>
> I thought the criteria would read as this
>
> [Forms]![Form1]![List7] [List7] being the field on the first form, but
> it does not seem to be the case.
>
> Hopefully it is a naming convention i am failing to get correct
From: Craig on 15 Mar 2010 17:29
As requested, here is the sql for the query that appears on Form1

SELECT LIVEDB_MBB010.PARTNO, LIVEDB_MBB010.DESCRIPTION2
FROM LIVEDB_MBB010
WHERE (((LIVEDB_MBB010.PARTNO) Like "FG*") AND
((LIVEDB_MBB010.DESCRIPTION2)=[Forms]![Form1]![List5]))
ORDER BY LIVEDB_MBB010.PARTNO;


And then, here is the sql for the query that is done for Form2

SELECT LIVEDB_MBB680.SKILLGRADE_PMR01, LIVEDB_MBB680.ACCOUNT15_PMR,
LIVEDB_MBB680.PROCNO_PMR, LIVEDB_MBB680.PROCVER_PMR,
LIVEDB_MBB680.PARTNO_PMR, LIVEDB_MBB680.PROCSTAGE_PMR,
LIVEDB_MBB680.RESOURCENO_PMR, LIVEDB_MBB680.SKILLGRADE_PMR02,
LIVEDB_MBB680.SKILLGRADE_PMR03, LIVEDB_MBB680.SKILLGRADE_PMR04,
LIVEDB_MBB680.SKILLGRADE_PMR05, LIVEDB_MBB680.SKILLGRADE_PMR06,
LIVEDB_MBB680.SKILLGRADE_PMR07, LIVEDB_MBB680.SKILLGRADE_PMR08,
LIVEDB_MBB680.SKILLGRADE_PMR09, LIVEDB_MBB680.SKILLGRADE_PMR10,
LIVEDB_MBB680.RSMANUSAGE_PMR01, LIVEDB_MBB680.RSMANUSAGE_PMR02,
LIVEDB_MBB680.RSMANUSAGE_PMR03, LIVEDB_MBB680.RSMANUSAGE_PMR04,
LIVEDB_MBB680.RSMANUSAGE_PMR05, LIVEDB_MBB680.RSMANUSAGE_PMR06,
LIVEDB_MBB680.RSMANUSAGE_PMR07, LIVEDB_MBB680.RSMANUSAGE_PMR08,
LIVEDB_MBB680.RSMANUSAGE_PMR09, LIVEDB_MBB680.RSMANUSAGE_PMR10,
LIVEDB_MBB680.RSMANLEVEL_PMR01, LIVEDB_MBB680.RSMANLEVEL_PMR02,
LIVEDB_MBB680.RSMANLEVEL_PMR03, LIVEDB_MBB680.RSMANLEVEL_PMR04,
LIVEDB_MBB680.RSMANLEVEL_PMR05, LIVEDB_MBB680.RSMANLEVEL_PMR06,
LIVEDB_MBB680.RSMANLEVEL_PMR07, LIVEDB_MBB680.RSMANLEVEL_PMR08,
LIVEDB_MBB680.RSMANLEVEL_PMR09, LIVEDB_MBB680.RSMANLEVEL_PMR10
FROM LIVEDB_MBI070 INNER JOIN (LIVEDB_MBB160 INNER JOIN LIVEDB_MBB680 ON
LIVEDB_MBB160.ACCOUNT15_SG = LIVEDB_MBB680.ACCOUNT15_PMR) ON
(LIVEDB_MBI070.ACCOUNT15_LAB = LIVEDB_MBB680.ACCOUNT15_PMR) AND
(LIVEDB_MBI070.SKILLGRADE = LIVEDB_MBB160.SKILLGRADE_SG)
GROUP BY LIVEDB_MBB680.SKILLGRADE_PMR01, LIVEDB_MBB680.ACCOUNT15_PMR,
LIVEDB_MBB680.PROCNO_PMR, LIVEDB_MBB680.PROCVER_PMR,
LIVEDB_MBB680.PARTNO_PMR, LIVEDB_MBB680.PROCSTAGE_PMR,
LIVEDB_MBB680.RESOURCENO_PMR, LIVEDB_MBB680.SKILLGRADE_PMR02,
LIVEDB_MBB680.SKILLGRADE_PMR03, LIVEDB_MBB680.SKILLGRADE_PMR04,
LIVEDB_MBB680.SKILLGRADE_PMR05, LIVEDB_MBB680.SKILLGRADE_PMR06,
LIVEDB_MBB680.SKILLGRADE_PMR07, LIVEDB_MBB680.SKILLGRADE_PMR08,
LIVEDB_MBB680.SKILLGRADE_PMR09, LIVEDB_MBB680.SKILLGRADE_PMR10,
LIVEDB_MBB680.RSMANUSAGE_PMR01, LIVEDB_MBB680.RSMANUSAGE_PMR02,
LIVEDB_MBB680.RSMANUSAGE_PMR03, LIVEDB_MBB680.RSMANUSAGE_PMR04,
LIVEDB_MBB680.RSMANUSAGE_PMR05, LIVEDB_MBB680.RSMANUSAGE_PMR06,
LIVEDB_MBB680.RSMANUSAGE_PMR07, LIVEDB_MBB680.RSMANUSAGE_PMR08,
LIVEDB_MBB680.RSMANUSAGE_PMR09, LIVEDB_MBB680.RSMANUSAGE_PMR10,
LIVEDB_MBB680.RSMANLEVEL_PMR01, LIVEDB_MBB680.RSMANLEVEL_PMR02,
LIVEDB_MBB680.RSMANLEVEL_PMR03, LIVEDB_MBB680.RSMANLEVEL_PMR04,
LIVEDB_MBB680.RSMANLEVEL_PMR05, LIVEDB_MBB680.RSMANLEVEL_PMR06,
LIVEDB_MBB680.RSMANLEVEL_PMR07, LIVEDB_MBB680.RSMANLEVEL_PMR08,
LIVEDB_MBB680.RSMANLEVEL_PMR09, LIVEDB_MBB680.RSMANLEVEL_PMR10
HAVING (((LIVEDB_MBB680.PROCNO_PMR)=[Forms]![Form1]![List7]));

Not sure if it helps at all, but Form1 also has the following VBA to help
populate the list boxes

List7.value = Null
List7.Requery





"KARL DEWEY" wrote:

> Post the SQL of both queries by open in design view, clicking on VIEW - SQL
> View, highlight all, copy, and paste in a post.
>
> --
> Build a little, test a little.
>
>
> "Craig" wrote:
>
> > I am currently trying to get to grips with Access 2003, and i seem to have a
> > handle on some of the basics.
> >
> > I have managed to write a couple of queries, which feed from the one before
> > ie a list of results is displayed, the user then clicks an entry, and this
> > then goes into a second query to populate a second list box.
> >
> > The thing i am trying to do is now have a query on a second form [Form2]
> > reference a click from [Form1]
> >
> > I thought the criteria would read as this
> >
> > [Forms]![Form1]![List7] [List7] being the field on the first form, but
> > it does not seem to be the case.
> >
> > Hopefully it is a naming convention i am failing to get correct
From: Jeff Boyce on 15 Mar 2010 18:34
For what it's worth, forms don't hold data in Access. That what tables do.
So your queries (SQL) really need to be against underlying tables, although
using a form for selection criteria is fine.

I couldn't help but notice that you have a lot of what appear to be tables
and fields with very similar names. This may be what is called "repeating
fields", a prime example of which would be fields named [January],
[February], [March], etc.

While this approach (essentially identical table structures for multiple
tables, all holding the same kind of information) is quite common ... in
spreadsheets ... it will put a serious crimp in your use of Access. Access
is optimized for well-normalized relational data, not 'sheet data.

If you'll post back a description of your underlying table structure, folks
here may be able to offer ideas on ways to get more out of Access ...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Craig" <Craig(a)discussions.microsoft.com> wrote in message
news:5CD967D2-DD14-4474-B321-697186D74578(a)microsoft.com...
> As requested, here is the sql for the query that appears on Form1
>
> SELECT LIVEDB_MBB010.PARTNO, LIVEDB_MBB010.DESCRIPTION2
> FROM LIVEDB_MBB010
> WHERE (((LIVEDB_MBB010.PARTNO) Like "FG*") AND
> ((LIVEDB_MBB010.DESCRIPTION2)=[Forms]![Form1]![List5]))
> ORDER BY LIVEDB_MBB010.PARTNO;
>
>
> And then, here is the sql for the query that is done for Form2
>
> SELECT LIVEDB_MBB680.SKILLGRADE_PMR01, LIVEDB_MBB680.ACCOUNT15_PMR,
> LIVEDB_MBB680.PROCNO_PMR, LIVEDB_MBB680.PROCVER_PMR,
> LIVEDB_MBB680.PARTNO_PMR, LIVEDB_MBB680.PROCSTAGE_PMR,
> LIVEDB_MBB680.RESOURCENO_PMR, LIVEDB_MBB680.SKILLGRADE_PMR02,
> LIVEDB_MBB680.SKILLGRADE_PMR03, LIVEDB_MBB680.SKILLGRADE_PMR04,
> LIVEDB_MBB680.SKILLGRADE_PMR05, LIVEDB_MBB680.SKILLGRADE_PMR06,
> LIVEDB_MBB680.SKILLGRADE_PMR07, LIVEDB_MBB680.SKILLGRADE_PMR08,
> LIVEDB_MBB680.SKILLGRADE_PMR09, LIVEDB_MBB680.SKILLGRADE_PMR10,
> LIVEDB_MBB680.RSMANUSAGE_PMR01, LIVEDB_MBB680.RSMANUSAGE_PMR02,
> LIVEDB_MBB680.RSMANUSAGE_PMR03, LIVEDB_MBB680.RSMANUSAGE_PMR04,
> LIVEDB_MBB680.RSMANUSAGE_PMR05, LIVEDB_MBB680.RSMANUSAGE_PMR06,
> LIVEDB_MBB680.RSMANUSAGE_PMR07, LIVEDB_MBB680.RSMANUSAGE_PMR08,
> LIVEDB_MBB680.RSMANUSAGE_PMR09, LIVEDB_MBB680.RSMANUSAGE_PMR10,
> LIVEDB_MBB680.RSMANLEVEL_PMR01, LIVEDB_MBB680.RSMANLEVEL_PMR02,
> LIVEDB_MBB680.RSMANLEVEL_PMR03, LIVEDB_MBB680.RSMANLEVEL_PMR04,
> LIVEDB_MBB680.RSMANLEVEL_PMR05, LIVEDB_MBB680.RSMANLEVEL_PMR06,
> LIVEDB_MBB680.RSMANLEVEL_PMR07, LIVEDB_MBB680.RSMANLEVEL_PMR08,
> LIVEDB_MBB680.RSMANLEVEL_PMR09, LIVEDB_MBB680.RSMANLEVEL_PMR10
> FROM LIVEDB_MBI070 INNER JOIN (LIVEDB_MBB160 INNER JOIN LIVEDB_MBB680 ON
> LIVEDB_MBB160.ACCOUNT15_SG = LIVEDB_MBB680.ACCOUNT15_PMR) ON
> (LIVEDB_MBI070.ACCOUNT15_LAB = LIVEDB_MBB680.ACCOUNT15_PMR) AND
> (LIVEDB_MBI070.SKILLGRADE = LIVEDB_MBB160.SKILLGRADE_SG)
> GROUP BY LIVEDB_MBB680.SKILLGRADE_PMR01, LIVEDB_MBB680.ACCOUNT15_PMR,
> LIVEDB_MBB680.PROCNO_PMR, LIVEDB_MBB680.PROCVER_PMR,
> LIVEDB_MBB680.PARTNO_PMR, LIVEDB_MBB680.PROCSTAGE_PMR,
> LIVEDB_MBB680.RESOURCENO_PMR, LIVEDB_MBB680.SKILLGRADE_PMR02,
> LIVEDB_MBB680.SKILLGRADE_PMR03, LIVEDB_MBB680.SKILLGRADE_PMR04,
> LIVEDB_MBB680.SKILLGRADE_PMR05, LIVEDB_MBB680.SKILLGRADE_PMR06,
> LIVEDB_MBB680.SKILLGRADE_PMR07, LIVEDB_MBB680.SKILLGRADE_PMR08,
> LIVEDB_MBB680.SKILLGRADE_PMR09, LIVEDB_MBB680.SKILLGRADE_PMR10,
> LIVEDB_MBB680.RSMANUSAGE_PMR01, LIVEDB_MBB680.RSMANUSAGE_PMR02,
> LIVEDB_MBB680.RSMANUSAGE_PMR03, LIVEDB_MBB680.RSMANUSAGE_PMR04,
> LIVEDB_MBB680.RSMANUSAGE_PMR05, LIVEDB_MBB680.RSMANUSAGE_PMR06,
> LIVEDB_MBB680.RSMANUSAGE_PMR07, LIVEDB_MBB680.RSMANUSAGE_PMR08,
> LIVEDB_MBB680.RSMANUSAGE_PMR09, LIVEDB_MBB680.RSMANUSAGE_PMR10,
> LIVEDB_MBB680.RSMANLEVEL_PMR01, LIVEDB_MBB680.RSMANLEVEL_PMR02,
> LIVEDB_MBB680.RSMANLEVEL_PMR03, LIVEDB_MBB680.RSMANLEVEL_PMR04,
> LIVEDB_MBB680.RSMANLEVEL_PMR05, LIVEDB_MBB680.RSMANLEVEL_PMR06,
> LIVEDB_MBB680.RSMANLEVEL_PMR07, LIVEDB_MBB680.RSMANLEVEL_PMR08,
> LIVEDB_MBB680.RSMANLEVEL_PMR09, LIVEDB_MBB680.RSMANLEVEL_PMR10
> HAVING (((LIVEDB_MBB680.PROCNO_PMR)=[Forms]![Form1]![List7]));
>
> Not sure if it helps at all, but Form1 also has the following VBA to help
> populate the list boxes
>
> List7.value = Null
> List7.Requery
>
>
>
>
>
> "KARL DEWEY" wrote:
>
>> Post the SQL of both queries by open in design view, clicking on VIEW -
>> SQL
>> View, highlight all, copy, and paste in a post.
>>
>> --
>> Build a little, test a little.
>>
>>
>> "Craig" wrote:
>>
>> > I am currently trying to get to grips with Access 2003, and i seem to
>> > have a
>> > handle on some of the basics.
>> >
>> > I have managed to write a couple of queries, which feed from the one
>> > before
>> > ie a list of results is displayed, the user then clicks an entry, and
>> > this
>> > then goes into a second query to populate a second list box.
>> >
>> > The thing i am trying to do is now have a query on a second form
>> > [Form2]
>> > reference a click from [Form1]
>> >
>> > I thought the criteria would read as this
>> >
>> > [Forms]![Form1]![List7] [List7] being the field on the first form,
>> > but
>> > it does not seem to be the case.
>> >
>> > Hopefully it is a naming convention i am failing to get correct


 |  Next  |  Last
Pages: 1 2
Prev: video or animations on ms accses 2003
Next: How do you execute an Access Pgm?