Database Design for multi value combo boxes (1 Viewer)

Snowflake68

Registered User.
Local time
Today, 17:28
Joined
May 28, 2014
Messages
452
Apologies for the very long post.

I know for some that as soon as they see 'Multi-Valued' field in Access they go "oh you shouldn't have used those" but I built my application without knowing really how many issues these can actually have. Now having read lots of posts on various different forums I understand now that the multi valued field can be pretty useless for many reasons especially when you want to use parameter queries with them which is the issue I have.

I now wish that I could have found an alternative way for allowing the user to select (and Store) multiple values from a list that will allow me to query them using parameters.

So I have spent several weeks building an application which uses the built in Multi-Valued field for some of the combo boxes on a form (I have 7 combo boxes all using this method) and I have already managed to overcome a few issues with these already so I don't really want to give up on them just yet especially as I have spent so much time and effort on this so far, but now I feel I have the biggest obstacle of all.

I have a form which is bound to my main data table (of which has several MV fields) to store the selections made from the list in the combo box as well as lots of other normal combo boxes and text boxes All good so far.

My issue;
I have a query that runs on main data table with a parameter that looks at the current record on the form in order to print and export that record. However tables containing MV fields cannot use parameter queries. But if I type the reference number of the record directly into the query and save it all works OK. I did this just for testing purpose so I know there isn't an issue with the query itself.

How to solve;
Is there a way of dynamically inputting the reference number into the query? Would this even work? Would I have to write the whole SQL statement in VBA?, although I dont know how to write the SQL to work within VBA.

OR
Do I need to completely redesign my database. I have sat and thought about how I can do this but these are the issues/lack of skill that I have.

How do I do the combo box so that the user can select multiple values?
Do I store them in a separate table with each selection as a separate record (along with their reference to join on later). But then if I join on them later how do I group those items into a single record for the report?

Apologies for the long post but I am desperate for some help with this especially now as I have to do a demo to the customer on Monday. I will be able to show them the MV combo boxes working but not the reports yet.

If I need to move away from the MV combos as they currently are then I would rather not show them at this stage.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:28
Joined
Jan 23, 2006
Messages
15,364
Can you step back and describe to readers in simple, plain English (as you would talk to an 8 year old)
what is this data base about? No jargon --what is the subject matter; what is a typical day in the business supported by this proposed database?
You are telling us how you have done something; you seem a little sheepish with your choice of multi valued fields; --we need to know more about the something (in an easily understood context).

Good luck.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Feb 19, 2013
Messages
16,553
First - why continue to build on weak foundations? Time invested in overcoming multivalue shortcomings might be better spent rebuilding?

If this is for a client, what are the chances of upsizing to sql server/express or azure at some point in the future? If anything greater than zero, then anything with multivalue fields will need to be redesigned anyway since no other db supports them.

And for your purposes, if you are making a career out of building db's for clients, some will want to start with one of those db's - so better to know how it is done without them.

However, with regards your queries have you tried using the .value property? I don't really use these types of fields because of their limitations so don't know if it will work, but something like

WHERE mymultivaluefield.value=[Enter a value]

How do I do the combo box so that the user can select multiple values?
answer to this depends on the required look, feel, required purpose and whether multi select is really required (for example if user is only ever going to choose one option) but in principle you create a join table and use a subform based on it instead. Other options include using a form based on a disconnected recordset, multi select listbox etc.

But then if I join on them later how do I group those items into a single record for the report?
again, depends on look and feel but suggest google 'concatrelated' and use that function if appropriate
 

Snowflake68

Registered User.
Local time
Today, 17:28
Joined
May 28, 2014
Messages
452
First - why continue to build on weak foundations? Time invested in overcoming multivalue shortcomings might be better spent rebuilding?
thanks I take onboard what you are saying.

Just to answer your what you are saying about .value
WHERE mymultivaluefield.value=[Enter a value]
I am using that for other areas to the application so I am aware of how to query the values but in this instance I am not using a parameter on any of the MV fields, merely querying other fields in the table that the MV fields exist in. It all works fine with the actual query but like I say when you try and export the query thats when you get the error. (attached)

If you create a report that uses the parameter query then it doesn't error but creates multiple lines for the same record (one for each of the values in the MV field)

And for your purposes, if you are making a career out of building db's for clients, some will want to start with one of those db's - so better to know how it is done without them.
Im still learning and this is not my main role but I help out where I can with building small but functional applications for people when I can. I would like to find out how to do it which is why I came on here to ask for guidance.

Thanks for you reply, it is very much appreciated as the people on here are always very helpful and have helped me no end over the past few years. :)
 

Attachments

  • MV Error.JPG
    MV Error.JPG
    15.2 KB · Views: 96

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:28
Joined
Feb 19, 2002
Messages
42,971
I have many years of experience as well as the scars to prove it. One thing I learned the hard way is that it is always better to go back and eliminate a problem than to try to balance new features on it. One of my early mentors had a sign over his door:

If you don't have time to do it right, what makes you think you have time to do it twice?

The listboxes will become subforms once you come to grips with the fact that you will need to change the design. You can format the subforms to remove the stuff that makes them look like subforms if that fits better with the look you are going for.
 

Snowflake68

Registered User.
Local time
Today, 17:28
Joined
May 28, 2014
Messages
452
I have many years of experience as well as the scars to prove it. One thing I learned the hard way is that it is always better to go back and eliminate a problem than to try to balance new features on it. One of my early mentors had a sign over his door:



The listboxes will become subforms once you come to grips with the fact that you will need to change the design. You can format the subforms to remove the stuff that makes them look like subforms if that fits better with the look you are going for.

Thanks Pat, I totally agree so I am going to take a step back and look to how I can change the design to remove the MV fields.

I know that I need to have a separate table for the fields that require multiple values but how do I change list boxes so that the user can select multiple values and store them in this separate table.
The listboxes will become subforms once you come to grips with the fact that you will need to change the design. You can format the subforms to remove the stuff that makes them look like subforms if that fits better with the look you are going for.

This is the what I dont understand how to do.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Feb 19, 2013
Messages
16,553
Code:
how do I change list boxes so that the user can select multiple values and store them in this separate table.

you have your main table (tbl1) (where you had the mv fields), another table (tbl2) - where the mv got its values from) and now instead of mv's a third joining table (tbl3) which at it s simplest contains just two fields - as FK to the main table (tbl1) and a FK to the other table. (tbl2) Note there is no need for the mv field at all

your form will be based on the main table tbl1. In that main form you will have a subform based on the new joining table tbl3. The subform will only show one control, a combo with a rowsource based on tbl2 but bound to the FK field in tbl3.

Providing you have setup up your relationships, everything else will be populated automatically, but to check, your subform linkmaster field should be the PK of the main table (tbl1), and the linkchild field the FK to tbl1 in tbl3
 

moke123

AWF VIP
Local time
Today, 13:28
Joined
Jan 11, 2013
Messages
3,852
I know that I need to have a separate table for the fields that require multiple values but how do I change list boxes so that the user can select multiple values and store them in this separate table.

In the property sheet of the listbox you can change Multiselect from no to simple or extended.

I use this function in a standard module to return the first column of the listbox selected items in a comma delimited string.

Code:
Public Function getLBX(LBx As ListBox) As String

    Dim strList As String
    Dim varSelected As Variant

       If LBx.ItemsSelected.Count = 0 Then
        MsgBox "You haven't selected anything"
    Else
        For Each varSelected In LBx.ItemsSelected
            strList = strList & LBx.ItemData(varSelected) & ", "
        Next varSelected
        strList = Left$(strList, Len(strList) - 2)
        'MsgBox "You selected the following items:" & vbCrLf & strList
    End If

    getLBX = strList

End Function

You can then call and process the returned string with something like

Code:
strReturn = split(getLBX(Me.YourListboxName),",")

for i = 0 to Ubound(strReturn)

strSql = "Insert into YourTable(YourFieldName) Values( & strReturn(i) & ")"

currentdb.execute strSql,dbFailOnError

next i
 

Snowflake68

Registered User.
Local time
Today, 17:28
Joined
May 28, 2014
Messages
452
Code:
how do I change list boxes so that the user can select multiple values and store them in this separate table.

you have your main table (tbl1) (where you had the mv fields), another table (tbl2) - where the mv got its values from) and now instead of mv's a third joining table (tbl3) which at it s simplest contains just two fields - as FK to the main table (tbl1) and a FK to the other table. (tbl2) Note there is no need for the mv field at all

your form will be based on the main table tbl1. In that main form you will have a subform based on the new joining table tbl3. The subform will only show one control, a combo with a rowsource based on tbl2 but bound to the FK field in tbl3.

Providing you have setup up your relationships, everything else will be populated automatically, but to check, your subform linkmaster field should be the PK of the main table (tbl1), and the linkchild field the FK to tbl1 in tbl3

thanks for this; so when I am creating a new record (which has a unique reference) do I also need to create a new record in the tbl3 with that unique reference for all of the values in the list box?
 

Snowflake68

Registered User.
Local time
Today, 17:28
Joined
May 28, 2014
Messages
452
In the property sheet of the listbox you can change Multiselect from no to simple or extended.

I use this function in a standard module to return the first column of the listbox selected items in a comma delimited string.

Code:
Public Function getLBX(LBx As ListBox) As String

    Dim strList As String
    Dim varSelected As Variant

       If LBx.ItemsSelected.Count = 0 Then
        MsgBox "You haven't selected anything"
    Else
        For Each varSelected In LBx.ItemsSelected
            strList = strList & LBx.ItemData(varSelected) & ", "
        Next varSelected
        strList = Left$(strList, Len(strList) - 2)
        'MsgBox "You selected the following items:" & vbCrLf & strList
    End If

    getLBX = strList

End Function

You can then call and process the returned string with something like

Code:
strReturn = split(getLBX(Me.YourListboxName),",")

for i = 0 to Ubound(strReturn)

strSql = "Insert into YourTable(YourFieldName) Values( & strReturn(i) & ")"

currentdb.execute strSql,dbFailOnError

next i

Where is the property for the List box to change it to 'simple' or 'Extended'?
In the property sheet of the listbox you can change Multiselect from no to simple or extended.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Feb 19, 2013
Messages
16,553
I also need to create a new record in the tbl3 with that unique reference for all of the values in the list box?
think we are at cross purposes. I was suggesting a subform, not a listbox
 

moke123

AWF VIP
Local time
Today, 13:28
Joined
Jan 11, 2013
Messages
3,852
I'm confused too as it evolved from a combo to list to subform.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Feb 19, 2013
Messages
16,553
@moke

The list came from you, the subform from me, with Pat suggesting both. On reviewing the OP's requirement, I think the list is probably the better way to go but does depend on the requirement - we have no idea what the mv fields are/were being used for. If multiple days of the week, list is probably better, if choosing multiple staff, subform is probably better. If user is only required to choose one option, a standard combo is probably better
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:28
Joined
Feb 19, 2002
Messages
42,971
I think I suggested the subform. I just said that you could make it look more like a listbox if that suited your form design better.

change Multiselect from no to simple or extended.
NO, It needs to be simple. Extended will make the control unbound and you'll need to write all your own code behind the scenes to save and display the individual items. It would be far better to stick with multiselect. At least the control itself takes care of all the populating and saving.

I'm stepping out. CJ please continue.
 

Users who are viewing this thread

Top Bottom