Combo Box Records

  • Thread starter Thread starter Karan
  • Start date Start date
K

Karan

Hi all,

I have a Table called Product Sizes. one field is product name, another
field is product size, i have used the data type: text and the lookup
wizard... and typed the mostly used sizes for example 6 mm, 7 mm, & 8 mm.
Later when a new size is been purchased or available, i can enter the size
of the product for example 9 mm, 10 mm 12 mm. Now i want this new sizes to
be saved to the Combo Box list. which i am not able to do. If i go to next
record i will only have 6 mm, 7 mm & 8 mm.

I want the newly add records in the list to the combo box, like
6 mm
7 mm
8 mm
9 mm
10 mm
12 mm
and the Row Source Type is set to Value List.

can any of you help me to get this done.

Karan.
 
Karan,

I take it your combo property Limit To List is set to No...
The easiest way to get your combo to "learn" new sizes as you add them, is
to change its RouwSource Type to Table/Query, then click on the little
button with the three dots that appears on the right of the property, and
when taken to the query design view, add the Product Sizes table, take just
the size field down to the grid, click on Totals (button with capital T on
the toolbar), leave the default Totals function (Group By) and close the
design view window to return to your form design, confiming to save the SQL
query.
This way your combo source will be all the unique values entered in the
table, but new additions will only be picked up the next time you open the
form. In order to overcome this and make new additions available in your
combo right away, use the form's On Current event (Properties, Events tab)
to run a macro with a single action Requery on the combo box.

HTH,
Nikos
 
Mr. Nikos,

Thanks a lot for your tips & help.

It works, but the default Listed Product Sizes Disappear (Ex: 6 mm, 7 mm, &
8 mm) Which was done by Lookup wizard. I want this always available and
want to add the New Sizes as it comes. I think it is becasue i have set the
Row Source Type to Table/Query, In the previous case it was set to Value
List.

Once again thanks a lot for your valuable help.

Karan
 
Karan,

"the default Listed Product Sizes Disappear"...! Are there any entries in
your table with those sizes? The method I described will show all existing
sizes in the table after the first time they are entered, but not ones that
don't exist in the table.

If you want to add those values to the combo list even though they do not
exist in the table yet, you neet to go to the SQL statement in the row
source property of the combo box, and paste this in the end:
UNION SELECT "6 mm" FROM tblName UNION SELECT "7 mm" FROM tblName UNION
SELECT "8 mm" FROM tblName
leaving a space so that the first UNION word is separated from the last
existing one, and changing the word tblName in my example to the table name.

HTH,
Nikos
 
Mr. Nikos,

It Works Excellent. Thanks a lot for your Help.

Now, when i try the same procedure to another table to build the SQL
property it says "Not a Valid Alias Name"

The Code is as follows: SELECT tblJobOrders.[StrapColourGents] FROM
tblJobOrders; UNION SELECT "Black-Croco" FROM tblJobOrders UNION SELECT
"Black-Retro" FROM tblJobOrders UNION SELECT "Black-Plain" FROM tblJobOrders
UNION SELECT "Black-Kriss Cross" FROM tblJobOrders UNION SELECT "Black-Long
Drain" FROM tblJobOrders UNION SELECT "Black-Croco Duke" FROM tblJobOrders
UNION SELECT "Black-Bombe Stitch Flat" FROM tblJobOrders UNION SELECT
"Black-Bombe Stitch Duke" FROM tblJobOrders UNION SELECT "Black-Dots" FROM
tblJobOrders UNION SELECT "Black-Checks" FROM tblJobOrders UNION SELECT
"Black-Import" FROM tblJobOrders UNION SELECT "Black-CNS" FROM tblJobOrders
UNION SELECT "Brown-Plain" FROM tblJobOrders UNION SELECT "Brown-Retro" FROM
tblJobOrders UNION SELECT "Brown-Croco" FROM tblJobOrders UNION SELECT
"Brown-Bombe Stitch Flat" FROM tblJobOrders UNION SELECT "Brown-Bombe Stitch
Duke" FROM tblJobOrders UNION SELECT "Brown-Croco Duke" FROM tblJobOrders
UNION SELECT "Grey-Bombe Stitch Duke" FROM tblJobOrders UNION SELECT "Blue-"
FROM tblJobOrders UNION SELECT "Blue-Retro" FROM tblJobOrders UNION SELECT
"Blue-Plain" FROM tblJobOrders UNION SELECT "Blue-Croco Duke" FROM
tblJobOrders UNION SELECT "Blue-Nylon" FROM tblJobOrders UNION SELECT
"Blue-CNS" FROM tblJobOrders UNION SELECT "Green-Plain" FROM tblJobOrders
UNION SELECT "Green-Import" FROM tblJobOrders UNION SELECT "Green-Hirsch"
FROM tblJobOrders UNION SELECT "Green-Croco Duke" FROM tblJobOrders UNION
SELECT "Silver Chain" FROM tblJobOrders;

I know it is very long, compared to the previous one. But, in some other
fields it was well. Only in this "StrapColourGents" doesn't work, i really
don't know what is alias name? Can you help me out of this also. Thanks in
Advance.

Karan.
 
Back
Top