ListBox & ComboBox

[edisonl via AccessMonster.com]

Hi,

1. Whats the difference between Combo & ListBox ?

2. I had a table behind call order_table, it uses a field call model that
retrieve data from model_table.
2.1 Order_table consist of customer id, model, quantity
2.2 Model_table consist of Model Name, Dimension, Costs

3. So I had this Order_Subform allows user to mass copy order from excel file
into my subform, however instead of hard code to check the validity of model
and quantity, I like to set the Model field which is listbox to limit to list
strictly.

4. How can I do that ?

Regards, Edison

 

[John W. Vinson]

Hi,

1. Whats the difference between Combo & ListBox ?

The Combo shows only one row until you "drop it down". The listbox shows all
the rows that it has room for at all times.

They also differ in how they handle auto-completion; if you select a combo box
and type "b" it will show the first row where the first displayed column
starts with the letter b ("Bacall" perhaps); if you then type the letter l it
will go down to "Blaine", and so on. A listbox will jump first to the first
row starting with B, and then to the first row starting with L.

In addition, a listbox can be multiselect, a combo cannot.

2. I had a table behind call order_table, it uses a field call model that
retrieve data from model_table.

What is a "field call model"? That is not a standard Access term and I have no
idea what you mean.

2.1 Order_table consist of customer id, model, quantity
2.2 Model_table consist of Model Name, Dimension, Costs

3. So I had this Order_Subform allows user to mass copy order from excel file
into my subform, however instead of hard code to check the validity of model
and quantity, I like to set the Model field which is listbox to limit to list
strictly.

4. How can I do that ?

The Model field IS NOT A LISTBOX.

Tables do not contain listboxes. They do not contain combo boxes. They contain
*data*! Microsoft's misdesigned, misleading, irksome "Lookup Field" is
probably deceiving you. I suspect that your table actually contains a
(concealed) numeric ModelID, and only APPEARS to contain a model name. If so,
your Excel import would need to contain that model ID.

I cannot see your database so I can't be sure, but that would certainly appear
to fit the problem.

 

[PieterLinden via AccessMonster.com]

I cannot see your database so I can't be sure, but that would certainly appear
to fit the problem.

You mean you don't get special MVP glasses that can do that?!! Gee, I'm
disappointed!!

 

[edisonl via AccessMonster.com]

Hi,

Maybe I din't quite explain clearly.
Model_Table>> ModelName, MinimumQty, Price, ModelCode
Order_Table>> CustomerID, ModelName, OrderQty, TotalCosts

1. So My Order_Table.ModelName include ModelName (A combobox) that derive its
value from Model_Table.ModelName

2. Access Interface only expose Order_Table_Subform to user.

3. User input through pasting of data from excel file in the same number of
field, same data type.

4. But because is mass data pasting, I would like to set it in such that no
other Model could be input into Order_Table_Subform!ModelName (I set limit to
values but it doesn't work for mass data input)

Regards, Edison

 

[John W. Vinson]

Hi,

Maybe I din't quite explain clearly.
Model_Table>> ModelName, MinimumQty, Price, ModelCode
Order_Table>> CustomerID, ModelName, OrderQty, TotalCosts

1. So My Order_Table.ModelName include ModelName (A combobox) that derive its
value from Model_Table.ModelName

Again:

Your table DOES NOT CONTAIN A COMBO BOX.

Your table contains a *numeric model ID*.

The irksome, obnoxious Lookup Wizard conceals that very basic fact from your
view, and makes you *think* that the table contains a combo box. It doesn't!
The combo box is nothing but a display tool.

See http://www.mvps.org/access/lookupfields.htm for a critique.

2. Access Interface only expose Order_Table_Subform to user.

3. User input through pasting of data from excel file in the same number of
field, same data type.

Are you pasting the model name or the modelID from Excel? If the ID, how does
Excel get the ID?

4. But because is mass data pasting, I would like to set it in such that no
other Model could be input into Order_Table_Subform!ModelName (I set limit to
values but it doesn't work for mass data input)

That's because you cannot import data into a Form, because a form *does not
store data*. You can only import data into a Table.

Perhaps I'm still missing the point. What is the datatype of the ModelName
field in Order_Table? What is on the Lookup tab in the field properties? What
is actually stored in the Excel spreadsheet - the name, or the model ID? Have
you considered using TransferSpreadsheet or File... Get External Data...
Import to import data directly into the table, rather than using the much less
controllable copy and paste approach?

 

[edisonl via AccessMonster.com]

Hi John,

1. Once again appreciate your reply.

2. Data from excel is pre-key into, Data type for ModelID-Text (Combobox),
lookup properties is
Table/Query.

3. Storing in Excel is 2 field ModelID, Quantity

4. How do I get external data from Access ?

5. Does it Activate from On_Click event ?

Regards, Edison

Hi,

[quoted text clipped - 4 lines]

1. So My Order_Table.ModelName include ModelName (A combobox) that derive its
value from Model_Table.ModelName
Again:

Your table DOES NOT CONTAIN A COMBO BOX.

Your table contains a *numeric model ID*.

The irksome, obnoxious Lookup Wizard conceals that very basic fact from your
view, and makes you *think* that the table contains a combo box. It doesn't!
The combo box is nothing but a display tool.

See http://www.mvps.org/access/lookupfields.htm for a critique.

2. Access Interface only expose Order_Table_Subform to user.

3. User input through pasting of data from excel file in the same number of
field, same data type.

Are you pasting the model name or the modelID from Excel? If the ID, how does
Excel get the ID?

4. But because is mass data pasting, I would like to set it in such that no
other Model could be input into Order_Table_Subform!ModelName (I set limit to
values but it doesn't work for mass data input)

That's because you cannot import data into a Form, because a form *does not
store data*. You can only import data into a Table.

Perhaps I'm still missing the point. What is the datatype of the ModelName
field in Order_Table? What is on the Lookup tab in the field properties? What
is actually stored in the Excel spreadsheet - the name, or the model ID? Have
you considered using TransferSpreadsheet or File... Get External Data...
Import to import data directly into the table, rather than using the much less
controllable copy and paste approach?

 

[John W. Vinson]

Hi John,

1. Once again appreciate your reply.

2. Data from excel is pre-key into, Data type for ModelID-Text (Combobox),
lookup properties is
Table/Query.

Do you have a *LOOKUP* field in Excel? or only in Access?

3. Storing in Excel is 2 field ModelID, Quantity

How can Excel ensure that you have a valid ModelID?

4. How do I get external data from Access ?

File... Get External Data... Link. Choose '.xls' from "Files of type" and
navigate to your spreadsheet.

5. Does it Activate from On_Click event ?

Only if you write code in the On_Click event to do so.

 

[edisonl via AccessMonster.com]

Hi John,

1. Don't have a Lookup in Excel, the data has been a template for years..

2. Besides that lookup external data I don't have other option in setting
attributes right ?

Regards, Edison

Hi John,

[quoted text clipped - 3 lines]

lookup properties is
Table/Query.

Do you have a *LOOKUP* field in Excel? or only in Access?

3. Storing in Excel is 2 field ModelID, Quantity

How can Excel ensure that you have a valid ModelID?

4. How do I get external data from Access ?

File... Get External Data... Link. Choose '.xls' from "Files of type" and
navigate to your spreadsheet.

5. Does it Activate from On_Click event ?

Only if you write code in the On_Click event to do so.

Regards, Edison

 

[John W. Vinson]

Hi John,

1. Don't have a Lookup in Excel, the data has been a template for years..

2. Besides that lookup external data I don't have other option in setting
attributes right ?

Sorry, Edison, but I have no idea what you mean by this. "that lookup external
data"...???

 

[edisonl via AccessMonster.com]

Hi John,

Pardon me I was referring to Get External Data.

Regards, Edison

 

[John W. Vinson]

Hi John,

Pardon me I was referring to Get External Data.

Regards, Edison

Please rephrase and repost your complete question: what are you doing now;
what is it doing that you don't want or not doing that you do want.

 

[edisonl via AccessMonster.com]

Hi Vinson,

Don't have a Lookup in Excel, the data has been a template for years..

2. Besides that GET external data I don't have other option in setting
attributes right ?

Regards, Edison

Hi John,

[quoted text clipped - 11 lines]
Please rephrase and repost your complete question: what are you doing now;
what is it doing that you don't want or not doing that you do want.

 

[John W. Vinson]

Hi Vinson,

Don't have a Lookup in Excel, the data has been a template for years..

2. Besides that GET external data I don't have other option in setting
attributes right ?

Yes, you do. You can set attributes in VBA code, in the design of a table
initially, in macros...

I'm really sorry to say this, Edison, but it's getting very frustrating
attempting to answer your questions because *you don't explain your
questions*.

"other option in setting attributes" - attributes of WHAT? How are you using
Get External Data to set attributes, and why?