SQL on the fly...?

[Dave Birley]

I've got some code that works (yeah, really!) that I want to use in my After
Change of a ComboBox. It defines a Query that looks at the result of the
changed Combobox and of an unbound List box (containing only three items),
and then filters the main data based on those two factors.

I have my code set up as

dim SQL as string

SQL = "SELECT Company.CompanyID, Payload.PayloadID, Item.Item,
Item.Reference_Address"
SQL = SQL + " FROM Company INNER JOIN (([Group] INNER JOIN Item ON
Group.GroupID = Item.GroupID) INNER JOIN Payload ON Group.PayloadID =
Payload.PayloadID) ON Company.CompanyID = Group.CompanyID "
SQL = SQL + " WHERE Group.CompanyID = Me.Payload.Form.cboCOmpany.CompanyID,
Group.PayloadID = Me.[Payload Subform].PayloadID "
SQL = SQL + " ORDER BY Item.Item;"

Me![Group Subform].Item.ControlSource = SQL

...where Item is the textbox on the Group Subform. Candidly I'd prefer to
make the Query I'm generating be the ControlSource for the whole Subform, but
I'll take what I can get. The code as I present it does not work --
obviously, or I wouldn't be here asking. One thought that occurs to me is to
make the SQL in the form of..

SELECT <blah> from <Blahblah> as <MyQuery>

...but I just don't know enough about SQL as Access uses it.

Your assistance is solicited.

 

[Brendan Reynolds]

Unlike VBA code, the query engine won't be able to evaluate references to
form controls using the 'Me' syntax. You have to evaluate them in the code
and concatenate the result into the SQL statement like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + ", Group.PayloadID = " & Me.[Payload Subform].PayloadID

This still isn't valid SQL, though, because multiple conditions in a WHERE
clause are not separated by commas like multiple columns in a SELECT clause,
they are combined using 'AND' and/or 'OR' like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + " AND Group.PayloadID = " & Me.[Payload Subform].PayloadID

Another issue is that a SQL statement can not be used as a control source.
You could use it as the row source of a list box, or as the record source of
a subform.

I haven't tested this, but you may run into complications using Item and
Group as table names, as many objects have an 'Item' property, and 'GROUP'
is a JET SQL reserved word. See the following URL for a list of JET reserved
words ...

http://office.microsoft.com/en-us/assistance/HP010322491033.aspx

It's generally safer to use the '&' operator rather than '+' for string
concatenation.

 

[Dave Birley]

Thanks a million for your thorough and comprehensive answer. That is exactly
what I was hoping for. Unfortunately I am about to bail out for the day, and
tomorrow I shall be spending the day as an extra in a movie (you just can't
get the ol' sawdust out of the veins once it's in there) -- so I won't get to
play with your code until Friday. However I have no doubt that I now have all
the information I need to m ake this li'l pup behave for me.
--
Dave
Temping with Staffmark
in Rock Hill, SC

Unlike VBA code, the query engine won't be able to evaluate references to
form controls using the 'Me' syntax. You have to evaluate them in the code
and concatenate the result into the SQL statement like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + ", Group.PayloadID = " & Me.[Payload Subform].PayloadID

This still isn't valid SQL, though, because multiple conditions in a WHERE
clause are not separated by commas like multiple columns in a SELECT clause,
they are combined using 'AND' and/or 'OR' like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + " AND Group.PayloadID = " & Me.[Payload Subform].PayloadID

Another issue is that a SQL statement can not be used as a control source.
You could use it as the row source of a list box, or as the record source of
a subform.

I haven't tested this, but you may run into complications using Item and
Group as table names, as many objects have an 'Item' property, and 'GROUP'
is a JET SQL reserved word. See the following URL for a list of JET reserved
words ...

http://office.microsoft.com/en-us/assistance/HP010322491033.aspx

It's generally safer to use the '&' operator rather than '+' for string
concatenation.

--
Brendan Reynolds
Access MVP

I've got some code that works (yeah, really!) that I want to use in my
After
Change of a ComboBox. It defines a Query that looks at the result of the
changed Combobox and of an unbound List box (containing only three items),
and then filters the main data based on those two factors.

I have my code set up as

dim SQL as string

SQL = "SELECT Company.CompanyID, Payload.PayloadID, Item.Item,
Item.Reference_Address"
SQL = SQL + " FROM Company INNER JOIN (([Group] INNER JOIN Item ON
Group.GroupID = Item.GroupID) INNER JOIN Payload ON Group.PayloadID =
Payload.PayloadID) ON Company.CompanyID = Group.CompanyID "
SQL = SQL + " WHERE Group.CompanyID =
Me.Payload.Form.cboCOmpany.CompanyID,
Group.PayloadID = Me.[Payload Subform].PayloadID "
SQL = SQL + " ORDER BY Item.Item;"

Me![Group Subform].Item.ControlSource = SQL

..where Item is the textbox on the Group Subform. Candidly I'd prefer to
make the Query I'm generating be the ControlSource for the whole Subform,
but
I'll take what I can get. The code as I present it does not work --
obviously, or I wouldn't be here asking. One thought that occurs to me is
to
make the SQL in the form of..

SELECT <blah> from <Blahblah> as <MyQuery>

..but I just don't know enough about SQL as Access uses it.

Your assistance is solicited.

 

[Dave Birley]

Well, after having the sawdust kicked, rather than shaken out of my bones
yesterday (here in South Carolina they pay minimum hourly wage for screen
extras rather than S.E.G. scale earned under license as in other locales) I
am back doing what I should have been doing all along <g>!

I have taken another look at this inherently simple Form and de-complicated
it a bit more. My tblItems table (renamed following your caveat) already
contains a field, GroupID, so the big overarching Query "Reference" is
overkill in the extreme. Remember that this whole database involves six
Companies, three Payloads, and 84 Items, and is never likely to grow by more
than perhaps 1% -- ever! This isn't rocket science.

With the record source of the SubForm that will display the Items changed to
tblItems, all I need to do is to tell it to filter under three sets of
circumstance:

1.) On initiation of the form, when the Company Combobox will always be
assumed to be 1 and the Payload subform will also be assumed to be 1
2.) When the Company Combobox changes
3.) When the Payload subform selection changes.

Items 2 and 3 do meet in a table, tblGroup from which the GroupID value is
derived. Therefore it is the changes in items 2 and 3 that will tweak the 18
items in tblGroup, and the resulting pointer from there that will find the
set in tblItems to display.

(Sorry, dude, but nobody else around here to whom I can say confession, and
talking it out helps me to clarify it in my own mind).

So now I believe I understand what I should really be doing, instead of what
I asked for before. Of course, I still don't know how -- FWIW, however, I now
have a copy of Access 2002 Inside and Out (soft) plugged into a USB port, so
if there is a section you would rather point me to in that, I'm ready to
learn!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Unlike VBA code, the query engine won't be able to evaluate references to
form controls using the 'Me' syntax. You have to evaluate them in the code
and concatenate the result into the SQL statement like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + ", Group.PayloadID = " & Me.[Payload Subform].PayloadID

This still isn't valid SQL, though, because multiple conditions in a WHERE
clause are not separated by commas like multiple columns in a SELECT clause,
they are combined using 'AND' and/or 'OR' like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + " AND Group.PayloadID = " & Me.[Payload Subform].PayloadID

Another issue is that a SQL statement can not be used as a control source.
You could use it as the row source of a list box, or as the record source of
a subform.

I haven't tested this, but you may run into complications using Item and
Group as table names, as many objects have an 'Item' property, and 'GROUP'
is a JET SQL reserved word. See the following URL for a list of JET reserved
words ...

http://office.microsoft.com/en-us/assistance/HP010322491033.aspx

It's generally safer to use the '&' operator rather than '+' for string
concatenation.

--
Brendan Reynolds
Access MVP

I've got some code that works (yeah, really!) that I want to use in my
After
Change of a ComboBox. It defines a Query that looks at the result of the
changed Combobox and of an unbound List box (containing only three items),
and then filters the main data based on those two factors.

I have my code set up as

dim SQL as string

SQL = "SELECT Company.CompanyID, Payload.PayloadID, Item.Item,
Item.Reference_Address"
SQL = SQL + " FROM Company INNER JOIN (([Group] INNER JOIN Item ON
Group.GroupID = Item.GroupID) INNER JOIN Payload ON Group.PayloadID =
Payload.PayloadID) ON Company.CompanyID = Group.CompanyID "
SQL = SQL + " WHERE Group.CompanyID =
Me.Payload.Form.cboCOmpany.CompanyID,
Group.PayloadID = Me.[Payload Subform].PayloadID "
SQL = SQL + " ORDER BY Item.Item;"

Me![Group Subform].Item.ControlSource = SQL

..where Item is the textbox on the Group Subform. Candidly I'd prefer to
make the Query I'm generating be the ControlSource for the whole Subform,
but
I'll take what I can get. The code as I present it does not work --
obviously, or I wouldn't be here asking. One thought that occurs to me is
to
make the SQL in the form of..

SELECT <blah> from <Blahblah> as <MyQuery>

..but I just don't know enough about SQL as Access uses it.

Your assistance is solicited.

 

[Dave Birley]

So, further clarifying [my own thoughts] a change to the Company Combobox or
a change to the selection in the Payload listbox will cause the pointer to
move to a different row in the tblGroups table. The unique GroupsID will then
need to become the basis for filtering tblItems.

Man-oh-man I could do this stuff in my sleep in VFP, but I'm just baffled in
Access <g>! That's the problem -- bringing a different data management
mindset into this territory. Please be gentle with me <g>!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Well, after having the sawdust kicked, rather than shaken out of my bones
yesterday (here in South Carolina they pay minimum hourly wage for screen
extras rather than S.E.G. scale earned under license as in other locales) I
am back doing what I should have been doing all along <g>!

I have taken another look at this inherently simple Form and de-complicated
it a bit more. My tblItems table (renamed following your caveat) already
contains a field, GroupID, so the big overarching Query "Reference" is
overkill in the extreme. Remember that this whole database involves six
Companies, three Payloads, and 84 Items, and is never likely to grow by more
than perhaps 1% -- ever! This isn't rocket science.

With the record source of the SubForm that will display the Items changed to
tblItems, all I need to do is to tell it to filter under three sets of
circumstance:

1.) On initiation of the form, when the Company Combobox will always be
assumed to be 1 and the Payload subform will also be assumed to be 1
2.) When the Company Combobox changes
3.) When the Payload subform selection changes.

Items 2 and 3 do meet in a table, tblGroup from which the GroupID value is
derived. Therefore it is the changes in items 2 and 3 that will tweak the 18
items in tblGroup, and the resulting pointer from there that will find the
set in tblItems to display.

(Sorry, dude, but nobody else around here to whom I can say confession, and
talking it out helps me to clarify it in my own mind).

So now I believe I understand what I should really be doing, instead of what
I asked for before. Of course, I still don't know how -- FWIW, however, I now
have a copy of Access 2002 Inside and Out (soft) plugged into a USB port, so
if there is a section you would rather point me to in that, I'm ready to
learn!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Unlike VBA code, the query engine won't be able to evaluate references to
form controls using the 'Me' syntax. You have to evaluate them in the code
and concatenate the result into the SQL statement like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + ", Group.PayloadID = " & Me.[Payload Subform].PayloadID

This still isn't valid SQL, though, because multiple conditions in a WHERE
clause are not separated by commas like multiple columns in a SELECT clause,
they are combined using 'AND' and/or 'OR' like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + " AND Group.PayloadID = " & Me.[Payload Subform].PayloadID

Another issue is that a SQL statement can not be used as a control source.
You could use it as the row source of a list box, or as the record source of
a subform.

I haven't tested this, but you may run into complications using Item and
Group as table names, as many objects have an 'Item' property, and 'GROUP'
is a JET SQL reserved word. See the following URL for a list of JET reserved
words ...

http://office.microsoft.com/en-us/assistance/HP010322491033.aspx

It's generally safer to use the '&' operator rather than '+' for string
concatenation.

--
Brendan Reynolds
Access MVP

I've got some code that works (yeah, really!) that I want to use in my
After
Change of a ComboBox. It defines a Query that looks at the result of the
changed Combobox and of an unbound List box (containing only three items),
and then filters the main data based on those two factors.

I have my code set up as

dim SQL as string

SQL = "SELECT Company.CompanyID, Payload.PayloadID, Item.Item,
Item.Reference_Address"
SQL = SQL + " FROM Company INNER JOIN (([Group] INNER JOIN Item ON
Group.GroupID = Item.GroupID) INNER JOIN Payload ON Group.PayloadID =
Payload.PayloadID) ON Company.CompanyID = Group.CompanyID "
SQL = SQL + " WHERE Group.CompanyID =
Me.Payload.Form.cboCOmpany.CompanyID,
Group.PayloadID = Me.[Payload Subform].PayloadID "
SQL = SQL + " ORDER BY Item.Item;"

Me![Group Subform].Item.ControlSource = SQL

..where Item is the textbox on the Group Subform. Candidly I'd prefer to
make the Query I'm generating be the ControlSource for the whole Subform,
but
I'll take what I can get. The code as I present it does not work --
obviously, or I wouldn't be here asking. One thought that occurs to me is
to
make the SQL in the form of..

SELECT <blah> from <Blahblah> as <MyQuery>

..but I just don't know enough about SQL as Access uses it.

Your assistance is solicited.

 

[Brendan Reynolds]

I'm sorry, Dave, but you've really lost me at this stage. I'm afraid I don't
know what the question is?

--
Brendan Reynolds
Access MVP

So, further clarifying [my own thoughts] a change to the Company Combobox
or
a change to the selection in the Payload listbox will cause the pointer to
move to a different row in the tblGroups table. The unique GroupsID will
then
need to become the basis for filtering tblItems.

Man-oh-man I could do this stuff in my sleep in VFP, but I'm just baffled
in
Access <g>! That's the problem -- bringing a different data management
mindset into this territory. Please be gentle with me <g>!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Well, after having the sawdust kicked, rather than shaken out of my bones
yesterday (here in South Carolina they pay minimum hourly wage for screen
extras rather than S.E.G. scale earned under license as in other locales)
I
am back doing what I should have been doing all along <g>!

I have taken another look at this inherently simple Form and
de-complicated
it a bit more. My tblItems table (renamed following your caveat) already
contains a field, GroupID, so the big overarching Query "Reference" is
overkill in the extreme. Remember that this whole database involves six
Companies, three Payloads, and 84 Items, and is never likely to grow by
more
than perhaps 1% -- ever! This isn't rocket science.

With the record source of the SubForm that will display the Items changed
to
tblItems, all I need to do is to tell it to filter under three sets of
circumstance:

1.) On initiation of the form, when the Company Combobox will always be
assumed to be 1 and the Payload subform will also be assumed to be 1
2.) When the Company Combobox changes
3.) When the Payload subform selection changes.

Items 2 and 3 do meet in a table, tblGroup from which the GroupID value
is
derived. Therefore it is the changes in items 2 and 3 that will tweak the
18
items in tblGroup, and the resulting pointer from there that will find
the
set in tblItems to display.

(Sorry, dude, but nobody else around here to whom I can say confession,
and
talking it out helps me to clarify it in my own mind).

So now I believe I understand what I should really be doing, instead of
what
I asked for before. Of course, I still don't know how -- FWIW, however, I
now
have a copy of Access 2002 Inside and Out (soft) plugged into a USB port,
so
if there is a section you would rather point me to in that, I'm ready to
learn!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Unlike VBA code, the query engine won't be able to evaluate references
to
form controls using the 'Me' syntax. You have to evaluate them in the
code
and concatenate the result into the SQL statement like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + ", Group.PayloadID = " & Me.[Payload Subform].PayloadID

This still isn't valid SQL, though, because multiple conditions in a
WHERE
clause are not separated by commas like multiple columns in a SELECT
clause,
they are combined using 'AND' and/or 'OR' like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + " AND Group.PayloadID = " & Me.[Payload
Subform].PayloadID

Another issue is that a SQL statement can not be used as a control
source.
You could use it as the row source of a list box, or as the record
source of
a subform.

I haven't tested this, but you may run into complications using Item
and
Group as table names, as many objects have an 'Item' property, and
'GROUP'
is a JET SQL reserved word. See the following URL for a list of JET
reserved
words ...

http://office.microsoft.com/en-us/assistance/HP010322491033.aspx

It's generally safer to use the '&' operator rather than '+' for string
concatenation.

--
Brendan Reynolds
Access MVP

I've got some code that works (yeah, really!) that I want to use in
my
After
Change of a ComboBox. It defines a Query that looks at the result of
the
changed Combobox and of an unbound List box (containing only three
items),
and then filters the main data based on those two factors.

I have my code set up as

dim SQL as string

SQL = "SELECT Company.CompanyID, Payload.PayloadID, Item.Item,
Item.Reference_Address"
SQL = SQL + " FROM Company INNER JOIN (([Group] INNER JOIN Item ON
Group.GroupID = Item.GroupID) INNER JOIN Payload ON Group.PayloadID =
Payload.PayloadID) ON Company.CompanyID = Group.CompanyID "
SQL = SQL + " WHERE Group.CompanyID =
Me.Payload.Form.cboCOmpany.CompanyID,
Group.PayloadID = Me.[Payload Subform].PayloadID "
SQL = SQL + " ORDER BY Item.Item;"

Me![Group Subform].Item.ControlSource = SQL

..where Item is the textbox on the Group Subform. Candidly I'd prefer
to
make the Query I'm generating be the ControlSource for the whole
Subform,
but
I'll take what I can get. The code as I present it does not work --
obviously, or I wouldn't be here asking. One thought that occurs to
me is
to
make the SQL in the form of..

SELECT <blah> from <Blahblah> as <MyQuery>

..but I just don't know enough about SQL as Access uses it.

Your assistance is solicited.

 

[Dave Birley]

Sorry, I got a bit into my novelist mode there, didn't I?

Combo box on the main form response produces an ID from a table.
List box in a subform produces an ID from a table.
These two values together, when fed to a table, (tblGroup), not visible on
the form, point to a unique ID value in tblGroup. This last ID value is the
filtering value that I apply to the tblItems table to display in the second
SubForm.

Is that any less murky?

I simply don't know how to tie these things together programmatically.
--
Dave
Temping with Staffmark
in Rock Hill, SC

I'm sorry, Dave, but you've really lost me at this stage. I'm afraid I don't
know what the question is?

--
Brendan Reynolds
Access MVP

So, further clarifying [my own thoughts] a change to the Company Combobox
or
a change to the selection in the Payload listbox will cause the pointer to
move to a different row in the tblGroups table. The unique GroupsID will
then
need to become the basis for filtering tblItems.

Man-oh-man I could do this stuff in my sleep in VFP, but I'm just baffled
in
Access <g>! That's the problem -- bringing a different data management
mindset into this territory. Please be gentle with me <g>!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Well, after having the sawdust kicked, rather than shaken out of my bones
yesterday (here in South Carolina they pay minimum hourly wage for screen
extras rather than S.E.G. scale earned under license as in other locales)
I
am back doing what I should have been doing all along <g>!

I have taken another look at this inherently simple Form and
de-complicated
it a bit more. My tblItems table (renamed following your caveat) already
contains a field, GroupID, so the big overarching Query "Reference" is
overkill in the extreme. Remember that this whole database involves six
Companies, three Payloads, and 84 Items, and is never likely to grow by
more
than perhaps 1% -- ever! This isn't rocket science.

With the record source of the SubForm that will display the Items changed
to
tblItems, all I need to do is to tell it to filter under three sets of
circumstance:

1.) On initiation of the form, when the Company Combobox will always be
assumed to be 1 and the Payload subform will also be assumed to be 1
2.) When the Company Combobox changes
3.) When the Payload subform selection changes.

Items 2 and 3 do meet in a table, tblGroup from which the GroupID value
is
derived. Therefore it is the changes in items 2 and 3 that will tweak the
18
items in tblGroup, and the resulting pointer from there that will find
the
set in tblItems to display.

(Sorry, dude, but nobody else around here to whom I can say confession,
and
talking it out helps me to clarify it in my own mind).

So now I believe I understand what I should really be doing, instead of
what
I asked for before. Of course, I still don't know how -- FWIW, however, I
now
have a copy of Access 2002 Inside and Out (soft) plugged into a USB port,
so
if there is a section you would rather point me to in that, I'm ready to
learn!
--
Dave
Temping with Staffmark
in Rock Hill, SC


:


Unlike VBA code, the query engine won't be able to evaluate references
to
form controls using the 'Me' syntax. You have to evaluate them in the
code
and concatenate the result into the SQL statement like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + ", Group.PayloadID = " & Me.[Payload Subform].PayloadID

This still isn't valid SQL, though, because multiple conditions in a
WHERE
clause are not separated by commas like multiple columns in a SELECT
clause,
they are combined using 'AND' and/or 'OR' like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + " AND Group.PayloadID = " & Me.[Payload
Subform].PayloadID

Another issue is that a SQL statement can not be used as a control
source.
You could use it as the row source of a list box, or as the record
source of
a subform.

I haven't tested this, but you may run into complications using Item
and
Group as table names, as many objects have an 'Item' property, and
'GROUP'
is a JET SQL reserved word. See the following URL for a list of JET
reserved
words ...

http://office.microsoft.com/en-us/assistance/HP010322491033.aspx

It's generally safer to use the '&' operator rather than '+' for string
concatenation.

--
Brendan Reynolds
Access MVP

I've got some code that works (yeah, really!) that I want to use in
my
After
Change of a ComboBox. It defines a Query that looks at the result of
the
changed Combobox and of an unbound List box (containing only three
items),
and then filters the main data based on those two factors.

I have my code set up as

dim SQL as string

SQL = "SELECT Company.CompanyID, Payload.PayloadID, Item.Item,
Item.Reference_Address"
SQL = SQL + " FROM Company INNER JOIN (([Group] INNER JOIN Item ON
Group.GroupID = Item.GroupID) INNER JOIN Payload ON Group.PayloadID =
Payload.PayloadID) ON Company.CompanyID = Group.CompanyID "
SQL = SQL + " WHERE Group.CompanyID =
Me.Payload.Form.cboCOmpany.CompanyID,
Group.PayloadID = Me.[Payload Subform].PayloadID "
SQL = SQL + " ORDER BY Item.Item;"

Me![Group Subform].Item.ControlSource = SQL

..where Item is the textbox on the Group Subform. Candidly I'd prefer
to
make the Query I'm generating be the ControlSource for the whole
Subform,
but
I'll take what I can get. The code as I present it does not work --
obviously, or I wouldn't be here asking. One thought that occurs to
me is
to
make the SQL in the form of..

SELECT <blah> from <Blahblah> as <MyQuery>

..but I just don't know enough about SQL as Access uses it.

Your assistance is solicited.

 

[Brendan Reynolds]

I'm afraid I'm still not seeing a question that I could attempt to answer.
If no one else steps in here, you might want to try posting a new question
with a new subject. Good luck.

--
Brendan Reynolds
Access MVP

Sorry, I got a bit into my novelist mode there, didn't I?

Combo box on the main form response produces an ID from a table.
List box in a subform produces an ID from a table.
These two values together, when fed to a table, (tblGroup), not visible on
the form, point to a unique ID value in tblGroup. This last ID value is
the
filtering value that I apply to the tblItems table to display in the
second
SubForm.

Is that any less murky?

I simply don't know how to tie these things together programmatically.
--
Dave
Temping with Staffmark
in Rock Hill, SC

I'm sorry, Dave, but you've really lost me at this stage. I'm afraid I
don't
know what the question is?

--
Brendan Reynolds
Access MVP

So, further clarifying [my own thoughts] a change to the Company
Combobox
or
a change to the selection in the Payload listbox will cause the pointer
to
move to a different row in the tblGroups table. The unique GroupsID
will
then
need to become the basis for filtering tblItems.

Man-oh-man I could do this stuff in my sleep in VFP, but I'm just
baffled
in
Access <g>! That's the problem -- bringing a different data management
mindset into this territory. Please be gentle with me <g>!
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

Well, after having the sawdust kicked, rather than shaken out of my
bones
yesterday (here in South Carolina they pay minimum hourly wage for
screen
extras rather than S.E.G. scale earned under license as in other
locales)
I
am back doing what I should have been doing all along <g>!

I have taken another look at this inherently simple Form and
de-complicated
it a bit more. My tblItems table (renamed following your caveat)
already
contains a field, GroupID, so the big overarching Query "Reference" is
overkill in the extreme. Remember that this whole database involves
six
Companies, three Payloads, and 84 Items, and is never likely to grow
by
more
than perhaps 1% -- ever! This isn't rocket science.

With the record source of the SubForm that will display the Items
changed
to
tblItems, all I need to do is to tell it to filter under three sets of
circumstance:

1.) On initiation of the form, when the Company Combobox will always
be
assumed to be 1 and the Payload subform will also be assumed to be 1
2.) When the Company Combobox changes
3.) When the Payload subform selection changes.

Items 2 and 3 do meet in a table, tblGroup from which the GroupID
value
is
derived. Therefore it is the changes in items 2 and 3 that will tweak
the
18
items in tblGroup, and the resulting pointer from there that will find
the
set in tblItems to display.

(Sorry, dude, but nobody else around here to whom I can say
confession,
and
talking it out helps me to clarify it in my own mind).

So now I believe I understand what I should really be doing, instead
of
what
I asked for before. Of course, I still don't know how -- FWIW,
however, I
now
have a copy of Access 2002 Inside and Out (soft) plugged into a USB
port,
so
if there is a section you would rather point me to in that, I'm ready
to
learn!
--
Dave
Temping with Staffmark
in Rock Hill, SC


:


Unlike VBA code, the query engine won't be able to evaluate
references
to
form controls using the 'Me' syntax. You have to evaluate them in
the
code
and concatenate the result into the SQL statement like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + ", Group.PayloadID = " & Me.[Payload
Subform].PayloadID

This still isn't valid SQL, though, because multiple conditions in a
WHERE
clause are not separated by commas like multiple columns in a SELECT
clause,
they are combined using 'AND' and/or 'OR' like so ...

SQL = SQL + " WHERE Group.CompanyID = " &
Me.Payload.Form.cboCOmpany.CompanyID
SQL = SQL + " AND Group.PayloadID = " & Me.[Payload
Subform].PayloadID

Another issue is that a SQL statement can not be used as a control
source.
You could use it as the row source of a list box, or as the record
source of
a subform.

I haven't tested this, but you may run into complications using Item
and
Group as table names, as many objects have an 'Item' property, and
'GROUP'
is a JET SQL reserved word. See the following URL for a list of JET
reserved
words ...

http://office.microsoft.com/en-us/assistance/HP010322491033.aspx

It's generally safer to use the '&' operator rather than '+' for
string
concatenation.

--
Brendan Reynolds
Access MVP

message
I've got some code that works (yeah, really!) that I want to use
in
my
After
Change of a ComboBox. It defines a Query that looks at the result
of
the
changed Combobox and of an unbound List box (containing only three
items),
and then filters the main data based on those two factors.

I have my code set up as

dim SQL as string

SQL = "SELECT Company.CompanyID, Payload.PayloadID, Item.Item,
Item.Reference_Address"
SQL = SQL + " FROM Company INNER JOIN (([Group] INNER JOIN Item ON
Group.GroupID = Item.GroupID) INNER JOIN Payload ON
Group.PayloadID =
Payload.PayloadID) ON Company.CompanyID = Group.CompanyID "
SQL = SQL + " WHERE Group.CompanyID =
Me.Payload.Form.cboCOmpany.CompanyID,
Group.PayloadID = Me.[Payload Subform].PayloadID "
SQL = SQL + " ORDER BY Item.Item;"

Me![Group Subform].Item.ControlSource = SQL

..where Item is the textbox on the Group Subform. Candidly I'd
prefer
to
make the Query I'm generating be the ControlSource for the whole
Subform,
but
I'll take what I can get. The code as I present it does not
work --
obviously, or I wouldn't be here asking. One thought that occurs
to
me is
to
make the SQL in the form of..

SELECT <blah> from <Blahblah> as <MyQuery>

..but I just don't know enough about SQL as Access uses it.

Your assistance is solicited.