lots of combo's -better to have a value list or a table?

[broj1]

hi all,

i am just wondering... if you need to have a lots (30) of combo boxes ... most of them, for now are at most 2-10 lines long
but it need's to have a user input posibility
the question is: is it better to have a value list or a table with right link(one to many)
or is there some other beter way?
thanks

 

[Remou]

Thirty combo boxes sounds a lot. Have you read

http://www.r937.com/relational.html

 

[broj1]

@Remou
thanks for quick reply.
I read the article,,, so, your suggestion is to make additional 30 dependent tables

tblSomething
ID PK
description

Should that be right?

 

[GingerR]

I think what Remou is saying is that having 30 fields in a table shouts out that your table structure may be not as optimized as it could be and perhaps could be improved upon. This is a comment in addition to what you are asking about, not really answering your question.

As for your question, i use both values and tables as RowSources for my combo boxes, depends on the situation; some are just value lists "N/A, Yes, No" and some are in tables: that way users can maintain them too (i.e. an Admin screen where certain users enter additional choices, then they don't have to come find me). I think this is particularly useful if/when the data that fills the combo box has more than just a single field, i.e.

P Pressurized Area
U Unpressurized Area
T Transitional

so you can view the additional data as well as the 'code'. So there's probably not a single answer to your question, it depends on how each is used, what data the user needs to see, etc. and how the data is to be maintained.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[broj1]

i strongly agree with you...and my other project's followed that lead. But this one is somehow different.
My problem is similar to this:
let us say you try to sell custom cars.
you offer client to pick up a part, up from many different parts from catalog, or make up his mind of his on (like color or something ... add value to the list )
and you can choose:
cbocolour: red,white,purple ,....
cboengine: 1.6, 2.0, v8, v12 ....
cbotype: suv, limo, waggon, .....
...
...
...
30 of this kind
and don't need nothin more then

tblSomething
ID PK
description

or value list, or there is something more flexible or better
and i realy do need your suggestion, opinion in this matter

what would you do?

thank you both

p.s. if you already given me a pointer in your previous post , and i didn't see it ..please, explain again and sorry

 

[Remou]

I think I would have a separate table and a sub form:

[tt]So, roughly:

Properties
PropertyID Property
1 Colour
2 Engine Size
3 Number Doors

PropertyValues
PropertyID Value
1 Red
1 Blue
2 1.6
3 1
3 4

Requirements
ClientID PropertyID Value
1 1 Red
1 3 4[/tt]

You will need some coding for this to work.

 

[broj1]

yes, @Remou. I like the idea-it just might work.
And a question before trying. Do you think this code will still work for cbo on notinlist event?

Dim rsttabela As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Add " & NewData & " as new record?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
Set rsttabela = New ADODB.Recordset
rsttabela.Open tabela, CurrentProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdTable

rsttabela.AddNew
rsttabela(polje) = NewData

rsttabela.Update
Response = acDataErrAdded
rsttabela.Close
Set rsttabela = Nothing

Else

Response = acDataErrContinue 'acDataErrDisplay
End If

 

[Remou]

Your value combo now will read something like:

[tt]Select PropertyID, Value From PropertyValues Where PropertyID=cboProperty[/tt]

So a new value will have a property as well as a value:

   If intAnswer = vbYes Then
        Set rsttabela = New ADODB.Recordset
        rsttabela.Open tabela, CurrentProject.Connection, _
        adOpenStatic, adLockOptimistic, adCmdTable

        rsttabela.AddNew
        rsttabela(PropertyID) = Me.cboProperty
        rsttabela(Value) = NewData
        
        rsttabela.Update
        Response = acDataErrAdded
         rsttabela.Close
    Set rsttabela = Nothing

    Else ...

I should add that value is a reserved word and should not be used as a field name, I have only used it for convenience.

It can be useful to use SQL, roughly:

cn=CurrentProject.Connection
strSQL="INSERT INTO tableA (PropertyID, Value) Values (" _
& Me.cboProperty & ",'" & NewData & "')"
cn.Execute strSQL, dbfailOnError