Adding to a combo box list that is based on a table

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I would like to be able to have my combo box have an item at the top and be
the default selection. Something like "[Add New Item]". The row source is a
query to a few tables. I can't see a way without have a record in the source
table.

And if it makes any difference, the source is a query that using a concat of
several fields to display, but the bound column is column 2, the record ID.

Here is the query I am using for the source.

SELECT [a_caliber] & " " & [dbtbullet_lst].[b_weight] & "g " &
[dbtbullet_lst].[b_mfg] & " " & [dbtbullet_lst].[b_type] & ", " & [a_charge]
& "g " & [dbtpowder_lst].[mfg] & " " & [dbtpowder_lst].[desc] AS expr1,
dbtReloadRecipe.recipe_id, dbtReloadRecipe.a_caliber FROM dbtPowder_lst INNER
JOIN (dbtBullet_lst INNER JOIN dbtReloadRecipe ON dbtBullet_lst.bulletID =
dbtReloadRecipe.a_bulletID) ON dbtPowder_lst.powderID =
dbtReloadRecipe.a_powderID WHERE
(((dbtReloadRecipe.a_caliber)=[Forms]![frmMaintainReloads]![r_caliber]));

Any ideas?
 
Pat

Have you looked into using the Limit To List property and the Not In List
event? You could get your combobox to react to a value not in the list...
(see Access HELP)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
You can do this with an UNION query. You just have to create dummy values.
With a Union Query, there has to be an equal number of fields and the field
data types have to match. You also need to have an ORDER BY that will cause
your [Add New Item] field to be the first item in the list. Here is an
example that may help. Assume you have an Autonumber field as the first
field you will want sort on, and a description field to display in the combo,
then a text field and another Long Integer field in the table. You can mock
up the Union side:

SELECT [KeyFld], [DescriptionField], [BarrellType], [BarrellLength] FROM
tblGuns UNION SELECT 0 as Dummy, "[Add New Item]" As [DescriptionField], ""
As [BarrellType], 0 As [BarrellLength] FROM tblGuns ORDER BY [KeyFld];

Then to make [Add New Item] the default, use the form Current Event to
select then item. This assumes the Bound Column property of your combo is 1:

Me.MyCombo = 0
 
I thought about it, but it would not help me in this case. If I have no
other option I could force it on them.

Jeff Boyce said:
Pat

Have you looked into using the Limit To List property and the Not In List
event? You could get your combobox to react to a value not in the list...
(see Access HELP)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Pat said:
I would like to be able to have my combo box have an item at the top and be
the default selection. Something like "[Add New Item]". The row source
is a
query to a few tables. I can't see a way without have a record in the
source
table.

And if it makes any difference, the source is a query that using a concat
of
several fields to display, but the bound column is column 2, the record
ID.

Here is the query I am using for the source.

SELECT [a_caliber] & " " & [dbtbullet_lst].[b_weight] & "g " &
[dbtbullet_lst].[b_mfg] & " " & [dbtbullet_lst].[b_type] & ", " &
[a_charge]
& "g " & [dbtpowder_lst].[mfg] & " " & [dbtpowder_lst].[desc] AS expr1,
dbtReloadRecipe.recipe_id, dbtReloadRecipe.a_caliber FROM dbtPowder_lst
INNER
JOIN (dbtBullet_lst INNER JOIN dbtReloadRecipe ON dbtBullet_lst.bulletID =
dbtReloadRecipe.a_bulletID) ON dbtPowder_lst.powderID =
dbtReloadRecipe.a_powderID WHERE
(((dbtReloadRecipe.a_caliber)=[Forms]![frmMaintainReloads]![r_caliber]));

Any ideas?
Click to expand...
Click to expand...
 
This seems to be what I need. I have not used the UNION eyt. I'll give it
a try.

Thanks!

Klatuu said:
You can do this with an UNION query. You just have to create dummy values.
With a Union Query, there has to be an equal number of fields and the field
data types have to match. You also need to have an ORDER BY that will cause
your [Add New Item] field to be the first item in the list. Here is an
example that may help. Assume you have an Autonumber field as the first
field you will want sort on, and a description field to display in the combo,
then a text field and another Long Integer field in the table. You can mock
up the Union side:

SELECT [KeyFld], [DescriptionField], [BarrellType], [BarrellLength] FROM
tblGuns UNION SELECT 0 as Dummy, "[Add New Item]" As [DescriptionField], ""
As [BarrellType], 0 As [BarrellLength] FROM tblGuns ORDER BY [KeyFld];

Then to make [Add New Item] the default, use the form Current Event to
select then item. This assumes the Bound Column property of your combo is 1:

Me.MyCombo = 0

--
Dave Hargis, Microsoft Access MVP


Pat said:
I would like to be able to have my combo box have an item at the top and be
the default selection. Something like "[Add New Item]". The row source is a
query to a few tables. I can't see a way without have a record in the source
table.

And if it makes any difference, the source is a query that using a concat of
several fields to display, but the bound column is column 2, the record ID.

Here is the query I am using for the source.

SELECT [a_caliber] & " " & [dbtbullet_lst].[b_weight] & "g " &
[dbtbullet_lst].[b_mfg] & " " & [dbtbullet_lst].[b_type] & ", " & [a_charge]
& "g " & [dbtpowder_lst].[mfg] & " " & [dbtpowder_lst].[desc] AS expr1,
dbtReloadRecipe.recipe_id, dbtReloadRecipe.a_caliber FROM dbtPowder_lst INNER
JOIN (dbtBullet_lst INNER JOIN dbtReloadRecipe ON dbtBullet_lst.bulletID =
dbtReloadRecipe.a_bulletID) ON dbtPowder_lst.powderID =
dbtReloadRecipe.a_powderID WHERE
(((dbtReloadRecipe.a_caliber)=[Forms]![frmMaintainReloads]![r_caliber]));

Any ideas?
Click to expand...
Click to expand...
 
Again thanks for your reply. I did a little research and the UNION appears
to require as you have said like tables. Since my query is referencing 3
seperate tables, how would I include the UNION statement? I am a little
green in this area, your help is very much appreciated.

Klatuu said:
You can do this with an UNION query. You just have to create dummy values.
With a Union Query, there has to be an equal number of fields and the field
data types have to match. You also need to have an ORDER BY that will cause
your [Add New Item] field to be the first item in the list. Here is an
example that may help. Assume you have an Autonumber field as the first
field you will want sort on, and a description field to display in the combo,
then a text field and another Long Integer field in the table. You can mock
up the Union side:

SELECT [KeyFld], [DescriptionField], [BarrellType], [BarrellLength] FROM
tblGuns UNION SELECT 0 as Dummy, "[Add New Item]" As [DescriptionField], ""
As [BarrellType], 0 As [BarrellLength] FROM tblGuns ORDER BY [KeyFld];

Then to make [Add New Item] the default, use the form Current Event to
select then item. This assumes the Bound Column property of your combo is 1:

Me.MyCombo = 0

--
Dave Hargis, Microsoft Access MVP


Pat said:
I would like to be able to have my combo box have an item at the top and be
the default selection. Something like "[Add New Item]". The row source is a
query to a few tables. I can't see a way without have a record in the source
table.

And if it makes any difference, the source is a query that using a concat of
several fields to display, but the bound column is column 2, the record ID.

Here is the query I am using for the source.

SELECT [a_caliber] & " " & [dbtbullet_lst].[b_weight] & "g " &
[dbtbullet_lst].[b_mfg] & " " & [dbtbullet_lst].[b_type] & ", " & [a_charge]
& "g " & [dbtpowder_lst].[mfg] & " " & [dbtpowder_lst].[desc] AS expr1,
dbtReloadRecipe.recipe_id, dbtReloadRecipe.a_caliber FROM dbtPowder_lst INNER
JOIN (dbtBullet_lst INNER JOIN dbtReloadRecipe ON dbtBullet_lst.bulletID =
dbtReloadRecipe.a_bulletID) ON dbtPowder_lst.powderID =
dbtReloadRecipe.a_powderID WHERE
(((dbtReloadRecipe.a_caliber)=[Forms]![frmMaintainReloads]![r_caliber]));

Any ideas?
Click to expand...
Click to expand...
 
Even though you are using mutliple tables for your query, it still return
rows made up of the fields in the query. The UNION part just has to match
the list of fields returned by the query.

But, I am curious as to why you think Jeff's suggestion will not work for
you. It really is the easier way to do this. If you can explain why you
think that, perhaps we can offer a suggestion on how to make it work.
--
Dave Hargis, Microsoft Access MVP


Pat said:
Again thanks for your reply. I did a little research and the UNION appears
to require as you have said like tables. Since my query is referencing 3
seperate tables, how would I include the UNION statement? I am a little
green in this area, your help is very much appreciated.

Klatuu said:
You can do this with an UNION query. You just have to create dummy values.
With a Union Query, there has to be an equal number of fields and the field
data types have to match. You also need to have an ORDER BY that will cause
your [Add New Item] field to be the first item in the list. Here is an
example that may help. Assume you have an Autonumber field as the first
field you will want sort on, and a description field to display in the combo,
then a text field and another Long Integer field in the table. You can mock
up the Union side:

SELECT [KeyFld], [DescriptionField], [BarrellType], [BarrellLength] FROM
tblGuns UNION SELECT 0 as Dummy, "[Add New Item]" As [DescriptionField], ""
As [BarrellType], 0 As [BarrellLength] FROM tblGuns ORDER BY [KeyFld];

Then to make [Add New Item] the default, use the form Current Event to
select then item. This assumes the Bound Column property of your combo is 1:

Me.MyCombo = 0

--
Dave Hargis, Microsoft Access MVP


Pat said:
I would like to be able to have my combo box have an item at the top and be
the default selection. Something like "[Add New Item]". The row source is a
query to a few tables. I can't see a way without have a record in the source
table.

And if it makes any difference, the source is a query that using a concat of
several fields to display, but the bound column is column 2, the record ID.

Here is the query I am using for the source.

SELECT [a_caliber] & " " & [dbtbullet_lst].[b_weight] & "g " &
[dbtbullet_lst].[b_mfg] & " " & [dbtbullet_lst].[b_type] & ", " & [a_charge]
& "g " & [dbtpowder_lst].[mfg] & " " & [dbtpowder_lst].[desc] AS expr1,
dbtReloadRecipe.recipe_id, dbtReloadRecipe.a_caliber FROM dbtPowder_lst INNER
JOIN (dbtBullet_lst INNER JOIN dbtReloadRecipe ON dbtBullet_lst.bulletID =
dbtReloadRecipe.a_bulletID) ON dbtPowder_lst.powderID =
dbtReloadRecipe.a_powderID WHERE
(((dbtReloadRecipe.a_caliber)=[Forms]![frmMaintainReloads]![r_caliber]));

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
I want the default selection to be the add new. From my testing (maybe my
fault) I am not able to get this on the combo box. It is blank. I set the
row source to a table, set the default value to "[Add New]". Am I missing
something?

Klatuu said:
Even though you are using mutliple tables for your query, it still return
rows made up of the fields in the query. The UNION part just has to match
the list of fields returned by the query.

But, I am curious as to why you think Jeff's suggestion will not work for
you. It really is the easier way to do this. If you can explain why you
think that, perhaps we can offer a suggestion on how to make it work.
--
Dave Hargis, Microsoft Access MVP


Pat said:
Again thanks for your reply. I did a little research and the UNION appears
to require as you have said like tables. Since my query is referencing 3
seperate tables, how would I include the UNION statement? I am a little
green in this area, your help is very much appreciated.

Klatuu said:
You can do this with an UNION query. You just have to create dummy values.
With a Union Query, there has to be an equal number of fields and the field
data types have to match. You also need to have an ORDER BY that will cause
your [Add New Item] field to be the first item in the list. Here is an
example that may help. Assume you have an Autonumber field as the first
field you will want sort on, and a description field to display in the combo,
then a text field and another Long Integer field in the table. You can mock
up the Union side:

SELECT [KeyFld], [DescriptionField], [BarrellType], [BarrellLength] FROM
tblGuns UNION SELECT 0 as Dummy, "[Add New Item]" As [DescriptionField], ""
As [BarrellType], 0 As [BarrellLength] FROM tblGuns ORDER BY [KeyFld];

Then to make [Add New Item] the default, use the form Current Event to
select then item. This assumes the Bound Column property of your combo is 1:

Me.MyCombo = 0

--
Dave Hargis, Microsoft Access MVP


:

I would like to be able to have my combo box have an item at the top and be
the default selection. Something like "[Add New Item]". The row source is a
query to a few tables. I can't see a way without have a record in the source
table.

And if it makes any difference, the source is a query that using a concat of
several fields to display, but the bound column is column 2, the record ID.

Here is the query I am using for the source.

SELECT [a_caliber] & " " & [dbtbullet_lst].[b_weight] & "g " &
[dbtbullet_lst].[b_mfg] & " " & [dbtbullet_lst].[b_type] & ", " & [a_charge]
& "g " & [dbtpowder_lst].[mfg] & " " & [dbtpowder_lst].[desc] AS expr1,
dbtReloadRecipe.recipe_id, dbtReloadRecipe.a_caliber FROM dbtPowder_lst INNER
JOIN (dbtBullet_lst INNER JOIN dbtReloadRecipe ON dbtBullet_lst.bulletID =
dbtReloadRecipe.a_bulletID) ON dbtPowder_lst.powderID =
dbtReloadRecipe.a_powderID WHERE
(((dbtReloadRecipe.a_caliber)=[Forms]![frmMaintainReloads]![r_caliber]));

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
I wouldn't do it that way. I would use the combo only to search for existing
records and use the Not On List event to add a new record. If that doesn't
suit you, I would suggest an Add New command button on the form.

Either of these methods is much easier that what you are trying to do.
--
Dave Hargis, Microsoft Access MVP


Pat said:
I want the default selection to be the add new. From my testing (maybe my
fault) I am not able to get this on the combo box. It is blank. I set the
row source to a table, set the default value to "[Add New]". Am I missing
something?

Klatuu said:
Even though you are using mutliple tables for your query, it still return
rows made up of the fields in the query. The UNION part just has to match
the list of fields returned by the query.

But, I am curious as to why you think Jeff's suggestion will not work for
you. It really is the easier way to do this. If you can explain why you
think that, perhaps we can offer a suggestion on how to make it work.
--
Dave Hargis, Microsoft Access MVP


Pat said:
Again thanks for your reply. I did a little research and the UNION appears
to require as you have said like tables. Since my query is referencing 3
seperate tables, how would I include the UNION statement? I am a little
green in this area, your help is very much appreciated.

:

You can do this with an UNION query. You just have to create dummy values.
With a Union Query, there has to be an equal number of fields and the field
data types have to match. You also need to have an ORDER BY that will cause
your [Add New Item] field to be the first item in the list. Here is an
example that may help. Assume you have an Autonumber field as the first
field you will want sort on, and a description field to display in the combo,
then a text field and another Long Integer field in the table. You can mock
up the Union side:

SELECT [KeyFld], [DescriptionField], [BarrellType], [BarrellLength] FROM
tblGuns UNION SELECT 0 as Dummy, "[Add New Item]" As [DescriptionField], ""
As [BarrellType], 0 As [BarrellLength] FROM tblGuns ORDER BY [KeyFld];

Then to make [Add New Item] the default, use the form Current Event to
select then item. This assumes the Bound Column property of your combo is 1:

Me.MyCombo = 0

--
Dave Hargis, Microsoft Access MVP


:

I would like to be able to have my combo box have an item at the top and be
the default selection. Something like "[Add New Item]". The row source is a
query to a few tables. I can't see a way without have a record in the source
table.

And if it makes any difference, the source is a query that using a concat of
several fields to display, but the bound column is column 2, the record ID.

Here is the query I am using for the source.

SELECT [a_caliber] & " " & [dbtbullet_lst].[b_weight] & "g " &
[dbtbullet_lst].[b_mfg] & " " & [dbtbullet_lst].[b_type] & ", " & [a_charge]
& "g " & [dbtpowder_lst].[mfg] & " " & [dbtpowder_lst].[desc] AS expr1,
dbtReloadRecipe.recipe_id, dbtReloadRecipe.a_caliber FROM dbtPowder_lst INNER
JOIN (dbtBullet_lst INNER JOIN dbtReloadRecipe ON dbtBullet_lst.bulletID =
dbtReloadRecipe.a_bulletID) ON dbtPowder_lst.powderID =
dbtReloadRecipe.a_powderID WHERE
(((dbtReloadRecipe.a_caliber)=[Forms]![frmMaintainReloads]![r_caliber]));

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top