›

Access list box multiselect property code

ldv3ldv3
in VBA
I need code for the multiselect property of Access' list box. When a user selects more than one item in the list, how do you get the choices into the table? Once you change to multiselect, the list box no longer puts the items in the table - only does this for the single property.

I can't find any code in the help menu. I've also search Microsoft and the internet.

Comments

  • RikRik
    : I need code for the multiselect property of Access' list box. When a user selects more than one item in the list, how do you get the choices into the table? Once you change to multiselect, the list box no longer puts the items in the table - only does this for the single property.
    :
    : I can't find any code in the help menu. I've also search Microsoft and the internet.
    :
    You need to use VBA code to query the list box directly. Note that what you're intending to do doesn't really make sense. If the list box is bound to a table field, then what selections do you expect to go into that field? All of them?
  • ldv3ldv3
    : : I need code for the multiselect property of Access' list box. When a user selects more than one item in the list, how do you get the choices into the table? Once you change to multiselect, the list box no longer puts the items in the table - only does this for the single property.
    : :
    : : I can't find any code in the help menu. I've also search Microsoft and the internet.
    : :
    : You need to use VBA code to query the list box directly. Note that what you're intending to do doesn't really make sense. If the list box is bound to a table field, then what selections do you expect to go into that field? All of them?
    :
    That's the part I don't know how to do. When the user makes their selections, those selections need to go into the table. There's a selected property that gets set or can be set, to true on each item selected. These selected items should get placed in the table's field but I can't find the code for this. Can you help?


  • RikRik
    : : : I need code for the multiselect property of Access' list box. When a user selects more than one item in the list, how do you get the choices into the table? Once you change to multiselect, the list box no longer puts the items in the table - only does this for the single property.
    : : :
    : : : I can't find any code in the help menu. I've also search Microsoft and the internet.
    : : :
    : : You need to use VBA code to query the list box directly. Note that what you're intending to do doesn't really make sense. If the list box is bound to a table field, then what selections do you expect to go into that field? All of them?
    : :
    : That's the part I don't know how to do. When the user makes their selections, those selections need to go into the table. There's a selected property that gets set or can be set, to true on each item selected. These selected items should get placed in the table's field but I can't find the code for this. Can you help?
    :
    :
    :

    If your list box is on a data-bound form, you could use
    [code]
    Private Sub cmdStoreAllSelections_Click()
    Dim SelectedValues, item

    For Each item In lstItems.ItemsSelected
    SelectedValues = SelectedValues & lstItems.ItemData(item)
    Next item
    Me!CompoundValue = SelectedValues
    End Sub
    [/code]
    This assumes a control on the form called CompoundValue, that's bound to the field you want to put the data in. In this example what actually goes into the field is a concatenation of the text items selected. This is unikely to be useful. You could separate the items with commas like this:
    [code]
    Private Sub cmdStoreAllSelections_Click()
    Dim SelectedValues, item

    For Each item In lstItems.ItemsSelected
    If SelectedValues > "" Then
    SelectedValues = SelectedValues & "," & lstItems.ItemData(item)
    Else
    SelectedValues = lstItems.ItemData(item)
    End If
    Next item
    Me!CompoundValue = SelectedValues
    End Sub
    [/code]

    [b]However[/b] i suspect that you're going about this the wrong way in the first place. If you give more detail on your problem, it would help.
    My guess is that you're trying to associate a list of things with a single thing. For example, you may have a form based on the table Patients. You have a multi-select list showing all the possible ailments: {alopecia, botulism, ...xenophobia}. When you open the Patients form, you want to be able to associate the patient with any number of ailments, so you make selections from the list, hit the OK button, and you want your table to indicate that this patient now has these ailments. Your problem is this: if the database has been designed properly, the Patients table will have one row per patient. If you want to associate a patient with a [italic]list[/italic] of attributes, you have three choices:

    [b]Option 1) Put all the attributes (ailments) in one field in the patient table record (row).[/b]

    Option 1) is what i think you're trying to do. If you want to find out how many patients have scabies, you're pretty much stuffed, because you'd have to go through every patient record and look [italic]inside[/italic] the field that holds all the ailments. And that would be the least of your problems.

    [b]Option 2) Have a field in the patient table for every possible ailment, and call them 'HasAlopcia, HasBotulism, ...HasXenphobia'. [/b] These would be 'Yes/No' type fields.

    Option 2) is completely impractical. (If you're not convinced, think of how many changes you'd have to make to your app if a new disease is discovered. And that would be the least of your problems, too.)

    [b]Option 3) Have a separate table called Ailments.[/b] This table has two fields: ID, and Name. Put each item from your list {alopecia, botulism, ...xenophobia} into a table row. Use anything unique for the ID column (e.g. autonumber). Have another table called PatientAilments. This table has two fields: UserID and AilmentID. Now when i want to record the fact that a patient number 1234 has alopecia and catatonia, i create two entries in PatientAilments:
    [code]
    UserID AilmentID
    1234 0
    1234 3
    [/code]

    Option 3) (you'll be guessing by now) is the only way to go.
    If patient 1234 is cured of catatonia, i simply delete the second row.
    If i want to see who's got what, i can query
    [size=2]'SELECT Patients.Name, Ailments.Name
    FROM Ailments INNER JOIN (Patients INNER JOIN PatientAilments ON Patients.ID = PatientAilments.ID) ON Ailments.ID = PatientAilments.AilmentID;'[/size]
    If i want to know who's all got rabies, i can query the above query with 'Ailments.Name="scabies"' (or even better, Ailments.ID=22).
    If i've spelled an ailment name wrong, i can simply update the entry in the Ailments table. If a cure is found for an ailment, i only have to add a 'cure' field to the the Ailments table and run reports showing this field.
    And on and on.

    All this illustrates something about relational databases: a field can only hold an 'atomic' (single) value, [b]not a set of values[/b]. That's why access doesn't want to put all your selections into a field.

    [b]More importantly:[/b] where one thing has an association with multiple instances of another thing, [italic]you should use a related table[/italic].
    If you're not familiar with the concept of related tables, one-to-many relationships and normalised database design, you won't get far with access, believe me. The concepts may appear a bit difficult at first but once they are grasped, the reasons for many puzzling things in access become obvious. Access blurs the line between using a database and designing one. Relational database design starts with a the table design, which must be correct. Too often access developers approach databases from a users role. It's so easy to build forms and reports it that seems natural to assume that tables would be the most trivial problem. It comes as a shock to the unwarned to learn that they aren't. Identifying what tables you need, and what fields really belong in what tables, and how they relate to each other is the single most important thing a database developer needs to do.

    Fortunately the important concepts to acquire are few: knowing how to identify one-to-many relationships in your problem domain, and being able to handle the special case of many-to-many relationships.

    Anyway, lecture over. Sorry to go on about diseases etc. but i don't have your table names. Also hope i've not missed the point here:-( - let me know.

  • ldv3ldv3
    : : : : I need code for the multiselect property of Access' list box. When a user selects more than one item in the list, how do you get the choices into the table? Once you change to multiselect, the list box no longer puts the items in the table - only does this for the single property.
    : : : :
    : : : You need to use VBA code to query the list box directly. Note that what you're intending to do doesn't really make sense. If the list box is bound to a table field, then what selections do you expect to go into that field? All of them?
    : : :
    : : That's the part I don't know how to do. When the user makes their selections, those selections need to go into the table. There's a selected property that gets set or can be set, to true on each item selected. These selected items should get placed in the table's field but I can't find the code for this. Can you help?
    : :
    : :
    : :
    :
    : If your list box is on a data-bound form, you could use
    : [code]
    : Private Sub cmdStoreAllSelections_Click()
    : Dim SelectedValues, item
    :
    : For Each item In lstItems.ItemsSelected
    : SelectedValues = SelectedValues & lstItems.ItemData(item)
    : Next item
    : Me!CompoundValue = SelectedValues
    : End Sub
    : [/code]
    : This assumes a control on the form called CompoundValue, that's bound to the field you want to put the data in. In this example what actually goes into the field is a concatenation of the text items selected. This is unikely to be useful. You could separate the items with commas like this:
    : [code]
    : Private Sub cmdStoreAllSelections_Click()
    : Dim SelectedValues, item
    :
    : For Each item In lstItems.ItemsSelected
    : If SelectedValues > "" Then
    : SelectedValues = SelectedValues & "," & lstItems.ItemData(item)
    : Else
    : SelectedValues = lstItems.ItemData(item)
    : End If
    : Next item
    : Me!CompoundValue = SelectedValues
    : End Sub
    : [/code]
    :
    : [b]However[/b] i suspect that you're going about this the wrong way in the first place. If you give more detail on your problem, it would help.
    : My guess is that you're trying to associate a list of things with a single thing. For example, you may have a form based on the table Patients. You have a multi-select list showing all the possible ailments: {alopecia, botulism, ...xenophobia}. When you open the Patients form, you want to be able to associate the patient with any number of ailments, so you make selections from the list, hit the OK button, and you want your table to indicate that this patient now has these ailments. Your problem is this: if the database has been designed properly, the Patients table will have one row per patient. If you want to associate a patient with a [italic]list[/italic] of attributes, you have three choices:
    :
    : [b]Option 1) Put all the attributes (ailments) in one field in the patient table record (row).[/b]
    :
    : Option 1) is what i think you're trying to do. If you want to find out how many patients have scabies, you're pretty much stuffed, because you'd have to go through every patient record and look [italic]inside[/italic] the field that holds all the ailments. And that would be the least of your problems.
    :
    : [b]Option 2) Have a field in the patient table for every possible ailment, and call them 'HasAlopcia, HasBotulism, ...HasXenphobia'. [/b] These would be 'Yes/No' type fields.
    :
    : Option 2) is completely impractical. (If you're not convinced, think of how many changes you'd have to make to your app if a new disease is discovered. And that would be the least of your problems, too.)
    :
    : [b]Option 3) Have a separate table called Ailments.[/b] This table has two fields: ID, and Name. Put each item from your list {alopecia, botulism, ...xenophobia} into a table row. Use anything unique for the ID column (e.g. autonumber). Have another table called PatientAilments. This table has two fields: UserID and AilmentID. Now when i want to record the fact that a patient number 1234 has alopecia and catatonia, i create two entries in PatientAilments:
    : [code]
    : UserID AilmentID
    : 1234 0
    : 1234 3
    : [/code]
    :
    : Option 3) (you'll be guessing by now) is the only way to go.
    : If patient 1234 is cured of catatonia, i simply delete the second row.
    : If i want to see who's got what, i can query
    : [size=2]'SELECT Patients.Name, Ailments.Name
    : FROM Ailments INNER JOIN (Patients INNER JOIN PatientAilments ON Patients.ID = PatientAilments.ID) ON Ailments.ID = PatientAilments.AilmentID;'[/size]
    : If i want to know who's all got rabies, i can query the above query with 'Ailments.Name="scabies"' (or even better, Ailments.ID=22).
    : If i've spelled an ailment name wrong, i can simply update the entry in the Ailments table. If a cure is found for an ailment, i only have to add a 'cure' field to the the Ailments table and run reports showing this field.
    : And on and on.
    :
    : All this illustrates something about relational databases: a field can only hold an 'atomic' (single) value, [b]not a set of values[/b]. That's why access doesn't want to put all your selections into a field.
    :
    : [b]More importantly:[/b] where one thing has an association with multiple instances of another thing, [italic]you should use a related table[/italic].
    : If you're not familiar with the concept of related tables, one-to-many relationships and normalised database design, you won't get far with access, believe me. The concepts may appear a bit difficult at first but once they are grasped, the reasons for many puzzling things in access become obvious. Access blurs the line between using a database and designing one. Relational database design starts with a the table design, which must be correct. Too often access developers approach databases from a users role. It's so easy to build forms and reports it that seems natural to assume that tables would be the most trivial problem. It comes as a shock to the unwarned to learn that they aren't. Identifying what tables you need, and what fields really belong in what tables, and how they relate to each other is the single most important thing a database developer needs to do.
    :
    : Fortunately the important concepts to acquire are few: knowing how to identify one-to-many relationships in your problem domain, and being able to handle the special case of many-to-many relationships.
    :
    : Anyway, lecture over. Sorry to go on about diseases etc. but i don't have your table names. Also hope i've not missed the point here:-( - let me know.
    :
    :
    Thank you sooo much ! I'll look it over this evening & see if I can get it working. I don't have enough VB background so I stumble thru the advanced stuff. My tables are related properly tho. I just couldn't find any code for the multiselect except for moving items from one box to another. Fun, but useless for my form ! Thank you again. I'll let you know if I got it working on Monday.
  • ldv3ldv3
    [b][red]This message was edited by ldv3 at 2002-11-18 14:33:27[/red][/b][hr]
    : : : : : I need code for the multiselect property of Access' list box. When a user selects more than one item in the list, how do you get the choices into the table? Once you change to multiselect, the list box no longer puts the items in the table - only does this for the single property.
    : : : : :
    : : : : You need to use VBA code to query the list box directly. Note that what you're intending to do doesn't really make sense. If the list box is bound to a table field, then what selections do you expect to go into that field? All of them?
    : : : :
    : : : That's the part I don't know how to do. When the user makes their selections, those selections need to go into the table. There's a selected property that gets set or can be set, to true on each item selected. These selected items should get placed in the table's field but I can't find the code for this. Can you help?
    : : :
    : : :
    : : :
    : :
    : : If your list box is on a data-bound form, you could use
    : : [code]
    : : Private Sub cmdStoreAllSelections_Click()
    : : Dim SelectedValues, item
    : :
    : : For Each item In lstItems.ItemsSelected
    : : SelectedValues = SelectedValues & lstItems.ItemData(item)
    : : Next item
    : : Me!CompoundValue = SelectedValues
    : : End Sub
    : : [/code]
    : : This assumes a control on the form called CompoundValue, that's bound to the field you want to put the data in. In this example what actually goes into the field is a concatenation of the text items selected. This is unikely to be useful. You could separate the items with commas like this:
    : : [code]
    : : Private Sub cmdStoreAllSelections_Click()
    : : Dim SelectedValues, item
    : :
    : : For Each item In lstItems.ItemsSelected
    : : If SelectedValues > "" Then
    : : SelectedValues = SelectedValues & "," & lstItems.ItemData(item)
    : : Else
    : : SelectedValues = lstItems.ItemData(item)
    : : End If
    : : Next item
    : : Me!CompoundValue = SelectedValues
    : : End Sub
    : : [/code]
    : :
    : : [b]However[/b] i suspect that you're going about this the wrong way in the first place. If you give more detail on your problem, it would help.
    : : My guess is that you're trying to associate a list of things with a single thing. For example, you may have a form based on the table Patients. You have a multi-select list showing all the possible ailments: {alopecia, botulism, ...xenophobia}. When you open the Patients form, you want to be able to associate the patient with any number of ailments, so you make selections from the list, hit the OK button, and you want your table to indicate that this patient now has these ailments. Your problem is this: if the database has been designed properly, the Patients table will have one row per patient. If you want to associate a patient with a [italic]list[/italic] of attributes, you have three choices:
    : :
    : : [b]Option 1) Put all the attributes (ailments) in one field in the patient table record (row).[/b]
    : :
    : : Option 1) is what i think you're trying to do. If you want to find out how many patients have scabies, you're pretty much stuffed, because you'd have to go through every patient record and look [italic]inside[/italic] the field that holds all the ailments. And that would be the least of your problems.
    : :
    : : [b]Option 2) Have a field in the patient table for every possible ailment, and call them 'HasAlopcia, HasBotulism, ...HasXenphobia'. [/b] These would be 'Yes/No' type fields.
    : :
    : : Option 2) is completely impractical. (If you're not convinced, think of how many changes you'd have to make to your app if a new disease is discovered. And that would be the least of your problems, too.)
    : :
    : : [b]Option 3) Have a separate table called Ailments.[/b] This table has two fields: ID, and Name. Put each item from your list {alopecia, botulism, ...xenophobia} into a table row. Use anything unique for the ID column (e.g. autonumber). Have another table called PatientAilments. This table has two fields: UserID and AilmentID. Now when i want to record the fact that a patient number 1234 has alopecia and catatonia, i create two entries in PatientAilments:
    : : [code]
    : : UserID AilmentID
    : : 1234 0
    : : 1234 3
    : : [/code]
    : :
    : : Option 3) (you'll be guessing by now) is the only way to go.
    : : If patient 1234 is cured of catatonia, i simply delete the second row.
    : : If i want to see who's got what, i can query
    : : [size=2]'SELECT Patients.Name, Ailments.Name
    : : FROM Ailments INNER JOIN (Patients INNER JOIN PatientAilments ON Patients.ID = PatientAilments.ID) ON Ailments.ID = PatientAilments.AilmentID;'[/size]
    : : If i want to know who's all got rabies, i can query the above query with 'Ailments.Name="scabies"' (or even better, Ailments.ID=22).
    : : If i've spelled an ailment name wrong, i can simply update the entry in the Ailments table. If a cure is found for an ailment, i only have to add a 'cure' field to the the Ailments table and run reports showing this field.
    : : And on and on.
    : :
    : : All this illustrates something about relational databases: a field can only hold an 'atomic' (single) value, [b]not a set of values[/b]. That's why access doesn't want to put all your selections into a field.
    : :
    : : [b]More importantly:[/b] where one thing has an association with multiple instances of another thing, [italic]you should use a related table[/italic].
    : : If you're not familiar with the concept of related tables, one-to-many relationships and normalised database design, you won't get far with access, believe me. The concepts may appear a bit difficult at first but once they are grasped, the reasons for many puzzling things in access become obvious. Access blurs the line between using a database and designing one. Relational database design starts with a the table design, which must be correct. Too often access developers approach databases from a users role. It's so easy to build forms and reports it that seems natural to assume that tables would be the most trivial problem. It comes as a shock to the unwarned to learn that they aren't. Identifying what tables you need, and what fields really belong in what tables, and how they relate to each other is the single most important thing a database developer needs to do.
    : :
    : : Fortunately the important concepts to acquire are few: knowing how to identify one-to-many relationships in your problem domain, and being able to handle the special case of many-to-many relationships.
    : :
    : : Anyway, lecture over. Sorry to go on about diseases etc. but i don't have your table names. Also hope i've not missed the point here:-( - let me know.
    : :
    : :
    : Thank you sooo much ! I'll look it over this evening & see if I can get it working. I don't have enough VB background so I stumble thru the advanced stuff. My tables are related properly tho. I just couldn't find any code for the multiselect except for moving items from one box to another. Fun, but useless for my form ! Thank you again. I'll let you know if I got it working on Monday.
    :
    :
    Yes ! Yes !! That's EXACTLY what I needed ! Thank you soooo much for
    sharing that ! You're beautiful !! I love you !! {{BIG hug}} and :*
    kiss ! I did find a glitch when I put this in Access 97 (worked in 2000 fine). The text box was showing "invisible" text ! It was there but couldn't see it til I closed or went to another record & back. I deleted the renagade text box & added it back it & it worked fine.

    Here's what I ended up with:

    Private Sub cmdStoreAll_Click()

    'unbound list box lstFullName pulls list of names from query FullName
    based on FullName field in Employees table
    'command button cmdStoreAll places selections in the txtCompoundValues
    text box on Supervisors form
    'txtCompoundValues places entries in CompoundValues field in Supervisors
    table, formats selections w/ comma & space bet. ea.
    'list box is reset after selections are placed in text box

    Dim SelectedVals
    Dim item
    Dim ctlList As Control

    For Each item In lstFullName.ItemsSelected
    If SelectedVals > "" Then
    SelectedVals = SelectedVals & ", " & lstFullName.ItemData(item)
    Else
    SelectedVals = lstFullName.ItemData(item)
    End If
    Next item

    Me!txtCompoundValues = SelectedVals

    ' Return Control object pointing to list box.
    Set ctlList = Me!lstFullName
    ' Requery source of data for list box.
    ctlList.Requery
    End Sub

    ******************

    Employees table:
    autonum ID
    LastName
    FirstName
    FullName

    FullName (query from Employees table)
    FullName only

    Supervisor's table:
    CompoundValues

    Form:
    Text box for CompoundValues
    Unbound list box for FullName - point to FullName query off the Employees
    table (row source: SELECT [FullName].[FullName] FROM FullName)



  • RikRik
    : [b][red]This message was edited by ldv3 at 2002-11-18 14:33:27[/red][/b][hr]
    ::

    Not sure about the 'invisible text' problem you're getting with the list box, but it may be because the '2000 list box control isn't really compatible with '97. Only way i know to check would be to try rebuilding the form in '97. The devil is always in the detail. Also access' pickiness about focus drives me bats.

    Do you really have a Fullname field in the Employees table? I wouldn't. As i'm sure you've noticed, there's a possibility of it being edited out-of-synch with either of the other name fields. Also, when you're creating a record, you have to input the same data twice. Data should never be replicated in tables. A better scheme is to drop the Fullname field and base your form on a query like:

    Query qEmployees:
    [size=2]SELECT Employees.*, [LastName] & ", " & [FirstName] AS Fullname FROM Employees;[/size]

    you've now got a display-only Fullname:

    [code]
    ID FirstName LastName Fullname
    1 John Smith Smith, John
    [/code]

    You can still edit the FirstName and LastName fields on the form. You can use 'select Fullname from qEmployees' in the unbound lists.

    It makes sense if you accept that my full name is really a [b]function[/b] of my first & last names. It isn't a separate data entity requireing its own field, but is simply a convention for displaying my first and last names.

    This is even better:

    [size=2]SELECT Employees.*, FormatName([LastName],[FirstName]) AS Fullname FROM Employees;[/size]
    [code]
    Public Function FormatName(LastName, FirstName) As String
    If FirstName > "" Then
    FormatName = FirstName & " " & LastName
    Else
    FormatName = LastName
    End If
    End Function
    [/code]

    With functions your database can do wonderfull things. I could have a report of Patients records showing [italic]Priority[/italic], where priority is a function of what resources have just become available, how long has the patient been waiting, classification of ailment, etc. which is only really possible in vb code. In turn, the background colour of the row for that patient can be a function of [italic]Priority[/italic]...

    Good luck with this stuff it it can help you any. It's gratifying to be able to help someone who appreciates it and thanks for the hug!

  • ldv3ldv3
    : : [b][red]This message was edited by ldv3 at 2002-11-18 14:33:27[/red][/b][hr]
    : ::
    :
    : Not sure about the 'invisible text' problem you're getting with the list box, but it may be because the '2000 list box control isn't really compatible with '97. Only way i know to check would be to try rebuilding the form in '97. The devil is always in the detail. Also access' pickiness about focus drives me bats.
    :
    : Do you really have a Fullname field in the Employees table? I wouldn't. As i'm sure you've noticed, there's a possibility of it being edited out-of-synch with either of the other name fields. Also, when you're creating a record, you have to input the same data twice. Data should never be replicated in tables. A better scheme is to drop the Fullname field and base your form on a query like:
    :
    : Query qEmployees:
    : [size=2]SELECT Employees.*, [LastName] & ", " & [FirstName] AS Fullname FROM Employees;[/size]
    :
    : you've now got a display-only Fullname:
    :
    : [code]
    : ID FirstName LastName Fullname
    : 1 John Smith Smith, John
    : [/code]
    :
    : You can still edit the FirstName and LastName fields on the form. You can use 'select Fullname from qEmployees' in the unbound lists.
    :
    : It makes sense if you accept that my full name is really a [b]function[/b] of my first & last names. It isn't a separate data entity requireing its own field, but is simply a convention for displaying my first and last names.
    :
    : This is even better:
    :
    : [size=2]SELECT Employees.*, FormatName([LastName],[FirstName]) AS Fullname FROM Employees;[/size]
    : [code]
    : Public Function FormatName(LastName, FirstName) As String
    : If FirstName > "" Then
    : FormatName = FirstName & " " & LastName
    : Else
    : FormatName = LastName
    : End If
    : End Function
    : [/code]
    :
    : With functions your database can do wonderfull things. I could have a report of Patients records showing [italic]Priority[/italic], where priority is a function of what resources have just become available, how long has the patient been waiting, classification of ailment, etc. which is only really possible in vb code. In turn, the background colour of the row for that patient can be a function of [italic]Priority[/italic]...
    :
    : Good luck with this stuff it it can help you any. It's gratifying to be able to help someone who appreciates it and thanks for the hug!
    :
    :
    The invisible text was just a glitch in 2000. Never seen it before either. I have run across a few weird glitches in 2000 tho so I just deleted the text box & added it back to the form & it worked fine.

    Yes, I have a FullName field but I also have an autonumber for the ID and a LastName field and FirstName field. A web programmer suggested I add the FullName field as we couldn't figure out how to merge the two names for the list box. I just did a query on the FullName only & pointed the list box to that. Is this ok or should I try the query code you suggested? I'd really like to learn functions and Sub procedures. Can you suggest a good book?

    I do have one other problem but it's relating the tables. I'd like to add a yearly beginning budget to my purchases' report so I can have a running remaining amount after each purchase. I'm thinking I need a Budget table (w/ the year as the ID w/ a 2nd field w/ the beginning budget amt) as the "one" and the purchases as the "many" then take the regular running amt (a calculation in the report), which is working correctly, and, in the report, subtract it from the yearly budget amt to have a remaining amt after each purchase. In the query the report's based on (from Orders & OrderDetails tables), I only have: ExtendedCost: CCur([Order Details].[UnitPrice]*[Qty]) Any suggestions?

    Thanks so much for your help. ~Leslie
  • ldv3ldv3
    : : : [b][red]This message was edited by ldv3 at 2002-11-18 14:33:27[/red][/b][hr]
    : : ::
    : :
    : : Not sure about the 'invisible text' problem you're getting with the list box, but it may be because the '2000 list box control isn't really compatible with '97. Only way i know to check would be to try rebuilding the form in '97. The devil is always in the detail. Also access' pickiness about focus drives me bats.
    : :
    : : Do you really have a Fullname field in the Employees table? I wouldn't. As i'm sure you've noticed, there's a possibility of it being edited out-of-synch with either of the other name fields. Also, when you're creating a record, you have to input the same data twice. Data should never be replicated in tables. A better scheme is to drop the Fullname field and base your form on a query like:
    : :
    : : Query qEmployees:
    : : [size=2]SELECT Employees.*, [LastName] & ", " & [FirstName] AS Fullname FROM Employees;[/size]
    : :
    : : you've now got a display-only Fullname:
    : :
    : : [code]
    : : ID FirstName LastName Fullname
    : : 1 John Smith Smith, John
    : : [/code]
    : :
    : : You can still edit the FirstName and LastName fields on the form. You can use 'select Fullname from qEmployees' in the unbound lists.
    : :
    : : It makes sense if you accept that my full name is really a [b]function[/b] of my first & last names. It isn't a separate data entity requireing its own field, but is simply a convention for displaying my first and last names.
    : :
    : : This is even better:
    : :
    : : [size=2]SELECT Employees.*, FormatName([LastName],[FirstName]) AS Fullname FROM Employees;[/size]
    : : [code]
    : : Public Function FormatName(LastName, FirstName) As String
    : : If FirstName > "" Then
    : : FormatName = FirstName & " " & LastName
    : : Else
    : : FormatName = LastName
    : : End If
    : : End Function
    : : [/code]
    : :
    : : With functions your database can do wonderfull things. I could have a report of Patients records showing [italic]Priority[/italic], where priority is a function of what resources have just become available, how long has the patient been waiting, classification of ailment, etc. which is only really possible in vb code. In turn, the background colour of the row for that patient can be a function of [italic]Priority[/italic]...
    : :
    : : Good luck with this stuff it it can help you any. It's gratifying to be able to help someone who appreciates it and thanks for the hug!
    : :
    : :
    : The invisible text was just a glitch in 2000. Never seen it before either. I have run across a few weird glitches in 2000 tho so I just deleted the text box & added it back to the form & it worked fine.
    :
    : Yes, I have a FullName field but I also have an autonumber for the ID and a LastName field and FirstName field. A web programmer suggested I add the FullName field as we couldn't figure out how to merge the two names for the list box. I just did a query on the FullName only & pointed the list box to that. Is this ok or should I try the query code you suggested? I'd really like to learn functions and Sub procedures. Can you suggest a good book?
    :
    : I do have one other problem but it's relating the tables. I'd like to add a yearly beginning budget to my purchases' report so I can have a running remaining amount after each purchase. I'm thinking I need a Budget table (w/ the year as the ID w/ a 2nd field w/ the beginning budget amt) as the "one" and the purchases as the "many" then take the regular running amt (a calculation in the report), which is working correctly, and, in the report, subtract it from the yearly budget amt to have a remaining amt after each purchase. In the query the report's based on (from Orders & OrderDetails tables), I only have: ExtendedCost: CCur([Order Details].[UnitPrice]*[Qty]) Any suggestions?
    :
    : Thanks so much for your help. ~Leslie
    :
    PS I did forget to ask, I have 6 list boxes. Is there a way to do one button for them all, maybe an array, or just do a separate "Select Items" button for each?
  • RikRik
    ::
    I don't see the point in having a FullName field, for the reasons i gave before. Let's say you also have HouseNumber, Street, District, City, PostCode and Country fields. This is a reasonable way of building an Address table, because you can search & sort on each of the fields. But you also want to print out the whole address sometimes. Do you create a new field called FullAddress? Will data input staff thank you if they have to type in every address twice? Have you thought about how you would ensure that a user remembers to update the FullName field, if they have to correct the spelling of an employees first name?
    For what it's worth, it also helps keep the database size down, and this always helps when your tables get large. But the main reason is that the FullName field duplicates data you've already got. Not only does this give you extra work, there's no benefit in it either.

    [b]Having said that...[/b] you may [b]want[/b] to allow the fullname to be different from FirstName+LastName. It could be a way of differentiating employees with the same first & last name, or so you could have 'Sir Lawrence Lancelot III' etc. Somewhat similarly a messageboard web site may have my first & last names, but use the name 'rik' as an alias. Even then, i would call that field 'Alias'.
    You should try the query i suggested. Then use that query wherever you currently use the table (i.e. in other queries, in forms and reports, list boxes, whatever). Think of FullName as a field you get for no effort.
    Note that you are already doing exactly the same thing with your ExtendedCost field - ExtendedCost is a function of UnitPrice and Qty, i.e EC = P*Q. You've already got P and Q: you wouldn't dream of having an actual field in the table called ExtendedCost, filled in 'by hand'. Isn't FullName the same?
    In most cases, you could use [italic][LastName] & ", " & [FirstName][/italic] to get a Fullname. This is such a simple expression it can used directly in a query without having to put it in a separate function. If the employee is allowed to have no first or no second name, the comma looks ugly. So i think it's worth writing a function to get a 'smarter' FullName.

    There's another killer benefit to using functions and/or calculated fields. Suppose i've been using my FullName function, which displays names as 'John Smith'. One year down the line, there are 10,000 employee records, and the boss says he prefers FullName in the format 'Smith, John (Junior Clerk)'. It's a trivial matter to write a new formatting function, and use whichever is appropriate for the report.
    As to wether i tell the boss i didn't actually edit 10,000 FullName fields by hand, that's another matter... and when John gets promoted, i don't have to lift a finger.
    Of course there's no real need to trash your FullName field, but there's no reason not try out this approach either - you need the practise, right?

    As for subs & functions in general, they are pretty straightforward once you've got it, and if you are doing any programming at all you soon will. The concept is the same in all programming languages (except the wierd ones). I don't know wether you are learning programming at college or how much you already know. Any questions, feel free to ask.

    I don't know of any books that i could recommend, i tend to use the on-line VB or MSDN documentation for reference and examples, but it is pretty dense and doesn't have any basic programming stuff. If you've got the Programmer's Guide it's prob. worth reading 'Introduction to Variables, Constants and Data Types' and 'Introduction to Procedures'.

    A hint about combo & list boxes: i note you've (wisely) got an ID field in Employees. When i use combos i usually have the ID available in the first column, i.e. base your combo on 'select ID, FullName from qEmployees'. Set the first column width to 0, so only the name is displayed. Usually when a list or combo is clicked, you'll want to find a particular record, which means you need the ID (a textual name is rarely a safe bet for identifying a unique record unless it is the key field or is otherwise guaranteed unique).
    You get the ID of the current selection with
    ID = Combo1.Column(0)
    you can still get the name:
    FullName = Combo1.Column(1)


    Re. your budget report: it is straighforward enough (as i'm sure you suspect) to get totals at the end of a report or at the end of a report section. If you have a report showing Purchase Items grouped by Purchase Order, you can have a text box showing the total cost of each Purchase Order (in the footer of the Purchase Order section). You can get the total for the whole report by putting a text box in the report footer. It's also straightforward to get the annual budget figure (from your Budget table) and subtract the report total.
    [b]However[/b] i know of no easy way to get a 'running' total, with each 'detail' section of the report showing a total of all the previous sections. We could write a function which will accept the 'current' record ID (as an input parameter) and return the number we want (functions to the rescue again.) The function can get that figure essentially by querying 'select sum(amount) of all purchase items up to and including this one'. The only nasty bit is that the function has to perform this query for every Purchase Order section on the report (or every Purchase Item record if you want that level of detail). If you have a large number of purchases, your report could take a [italic]very[/italic] long time to complete.
    An alternative approach could be to have a 'PurchasesToDate' field in your Budget table and also in the Purchases table. Every time a Purchase Order is saved, query the value of purchases to date from the budget table, add the cost of this purchase and store it back into the budget table. Also put this value in the purchase record itself, so it is available directly in reports. The PurchasesToDate value in the Budget table changes each time a purchase is made, so it is easy to get the value remaining. Each purchase record now also holds the PurchasesToDate value at the time the purchase is made.
    Note: essentially i am advocating keeping explicit data which i could get from other data in the database (because i can always get the running total by querying the purchase items table). This would seem to completely condradict what i've been saying about not duplicating data! It could be justified in this case on grounds of efficiency, and in practise most commercial databases do concede some purity of design in a trade-off for performance.

    Re. your six list boxes, what are the buttons for? Come to that, what are the list boxes for? Do they copy data from a multiple selection as in your first problem? You can certainly have one button doing everything, it's really just a matter of deciding what the functional requirements of your interface are and making a decision. As far as possible you should design your interfaces to behave as like existng Windows GUI mechanisms. As far as possible, [b][red]K[/red][/b]eep [b][red]I[/red][/b]t [b][red]S[/red][/b]imple, [b][red]S[/red][/b]tudent.

    [blue]p.s. this topic is gettng rather lenghty, no? Should we start a new one? Remember you can message me directly if you wish.[blue]

    rik
  • USENUSEN
    : ::
    : I don't see the point in having a FullName field, for the reasons i
    : gave before. Let's say you also have HouseNumber, Street, District,
    : City, PostCode and Country fields. This is a reasonable way of
    : building an Address table, because you can search & sort on each of
    : the fields. But you also want to print out the whole address
    : sometimes. Do you create a new field called FullAddress? Will data
    : input staff thank you if they have to type in every address twice?
    : Have you thought about how you would ensure that a user remembers to
    : update the FullName field, if they have to correct the spelling of
    : an employees first name?
    : For what it's worth, it also helps keep the database size down, and
    : this always helps when your tables get large. But the main reason
    : is that the FullName field duplicates data you've already got. Not
    : only does this give you extra work, there's no benefit in it either.
    :
    :
    : [b]Having said that...[/b] you may [b]want[/b] to allow the fullname
    : to be different from FirstName+LastName. It could be a way of
    : differentiating employees with the same first & last name, or so you
    : could have 'Sir Lawrence Lancelot III' etc. Somewhat similarly a
    : messageboard web site may have my first & last names, but use the
    : name 'rik' as an alias. Even then, i would call that field 'Alias'.
    : You should try the query i suggested. Then use that query wherever
    : you currently use the table (i.e. in other queries, in forms and
    : reports, list boxes, whatever). Think of FullName as a field you
    : get for no effort.
    : Note that you are already doing exactly the same thing with your
    : ExtendedCost field - ExtendedCost is a function of UnitPrice and
    : Qty, i.e EC = P*Q. You've already got P and Q: you wouldn't dream
    : of having an actual field in the table called ExtendedCost, filled
    : in 'by hand'. Isn't FullName the same?
    : In most cases, you could use [italic][LastName] & ", " &
    : [FirstName][/italic] to get a Fullname. This is such a simple
    : expression it can used directly in a query without having to put it
    : in a separate function. If the employee is allowed to have no first
    : or no second name, the comma looks ugly. So i think it's worth
    : writing a function to get a 'smarter' FullName.
    :
    : There's another killer benefit to using functions and/or calculated
    : fields. Suppose i've been using my FullName function, which
    : displays names as 'John Smith'. One year down the line, there are
    : 10,000 employee records, and the boss says he prefers FullName in
    : the format 'Smith, John (Junior Clerk)'. It's a trivial matter to
    : write a new formatting function, and use whichever is appropriate
    : for the report.
    : As to wether i tell the boss i didn't actually edit 10,000 FullName
    : fields by hand, that's another matter... and when John gets
    : promoted, i don't have to lift a finger.
    : Of course there's no real need to trash your FullName field, but
    : there's no reason not try out this approach either - you need the
    : practise, right?
    :
    : As for subs & functions in general, they are pretty straightforward
    : once you've got it, and if you are doing any programming at all you
    : soon will. The concept is the same in all programming languages
    : (except the wierd ones). I don't know wether you are learning
    : programming at college or how much you already know. Any questions,
    : feel free to ask.
    :
    : I don't know of any books that i could recommend, i tend to use the
    : on-line VB or MSDN documentation for reference and examples, but it
    : is pretty dense and doesn't have any basic programming stuff. If
    : you've got the Programmer's Guide it's prob. worth reading
    : 'Introduction to Variables, Constants and Data Types' and
    : 'Introduction to Procedures'.
    :
    : A hint about combo & list boxes: i note you've (wisely) got an ID
    : field in Employees. When i use combos i usually have the ID
    : available in the first column, i.e. base your combo on 'select ID,
    : FullName from qEmployees'. Set the first column width to 0, so only
    : the name is displayed. Usually when a list or combo is clicked,
    : you'll want to find a particular record, which means you need the ID
    : (a textual name is rarely a safe bet for identifying a unique record
    : unless it is the key field or is otherwise guaranteed unique).
    : You get the ID of the current selection with
    : ID = Combo1.Column(0)
    : you can still get the name:
    : FullName = Combo1.Column(1)
    :
    :
    : Re. your budget report: it is straighforward enough (as i'm sure
    : you suspect) to get totals at the end of a report or at the end of a
    : report section. If you have a report showing Purchase Items grouped
    : by Purchase Order, you can have a text box showing the total cost of
    : each Purchase Order (in the footer of the Purchase Order section).
    : You can get the total for the whole report by putting a text box in
    : the report footer. It's also straightforward to get the annual
    : budget figure (from your Budget table) and subtract the report total.
    : [b]However[/b] i know of no easy way to get a 'running' total, with
    : each 'detail' section of the report showing a total of all the
    : previous sections. We could write a function which will accept the
    : 'current' record ID (as an input parameter) and return the number we
    : want (functions to the rescue again.) The function can get that
    : figure essentially by querying 'select sum(amount) of all purchase
    : items up to and including this one'. The only nasty bit is that the
    : function has to perform this query for every Purchase Order section
    : on the report (or every Purchase Item record if you want that level
    : of detail). If you have a large number of purchases, your report
    : could take a [italic]very[/italic] long time to complete.
    : An alternative approach could be to have a 'PurchasesToDate' field
    : in your Budget table and also in the Purchases table. Every time a
    : Purchase Order is saved, query the value of purchases to date from
    : the budget table, add the cost of this purchase and store it back
    : into the budget table. Also put this value in the purchase record
    : itself, so it is available directly in reports. The PurchasesToDate
    : value in the Budget table changes each time a purchase is made, so
    : it is easy to get the value remaining. Each purchase record now
    : also holds the PurchasesToDate value at the time the purchase is
    : made.
    : Note: essentially i am advocating keeping explicit data which i
    : could get from other data in the database (because i can always get
    : the running total by querying the purchase items table). This would
    : seem to completely condradict what i've been saying about not
    : duplicating data! It could be justified in this case on grounds of
    : efficiency, and in practise most commercial databases do concede
    : some purity of design in a trade-off for performance.
    :
    : Re. your six list boxes, what are the buttons for? Come to that,
    : what are the list boxes for? Do they copy data from a multiple
    : selection as in your first problem? You can certainly have one
    : button doing everything, it's really just a matter of deciding what
    : the functional requirements of your interface are and making a
    : decision. As far as possible you should design your interfaces to
    : behave as like existng Windows GUI mechanisms. As far as possible,
    : [b][red]K[/red][/b]eep [b][red]I[/red][/b]t
    : [b][red]S[/red][/b]imple, [b][red]S[/red][/b]tudent.
    :
    : [blue]p.s. this topic is gettng rather lenghty, no? Should we start
    : a new one? Remember you can message me directly if you wish.[blue]
    :
    : rik
    :
    : ::
    : I don't see the point in having a FullName field, for the reasons i
    : gave before. Let's say you also have HouseNumber, Street, District,
    : City, PostCode and Country fields. This is a reasonable way of
    : building an Address table, because you can search & sort on each of
    : the fields. But you also want to print out the whole address
    : sometimes. Do you create a new field called FullAddress? Will data
    : input staff thank you if they have to type in every address twice?
    : Have you thought about how you would ensure that a user remembers to
    : update the FullName field, if they have to correct the spelling of
    : an employees first name?
    : For what it's worth, it also helps keep the database size down, and
    : this always helps when your tables get large. But the main reason
    : is that the FullName field duplicates data you've already got. Not
    : only does this give you extra work, there's no benefit in it either.
    :
    :
    : [b]Having said that...[/b] you may [b]want[/b] to allow the fullname
    : to be different from FirstName+LastName. It could be a way of
    : differentiating employees with the same first & last name, or so you
    : could have 'Sir Lawrence Lancelot III' etc. Somewhat similarly a
    : messageboard web site may have my first & last names, but use the
    : name 'rik' as an alias. Even then, i would call that field 'Alias'.
    : You should try the query i suggested. Then use that query wherever
    : you currently use the table (i.e. in other queries, in forms and
    : reports, list boxes, whatever). Think of FullName as a field you
    : get for no effort.
    : Note that you are already doing exactly the same thing with your
    : ExtendedCost field - ExtendedCost is a function of UnitPrice and
    : Qty, i.e EC = P*Q. You've already got P and Q: you wouldn't dream
    : of having an actual field in the table called ExtendedCost, filled
    : in 'by hand'. Isn't FullName the same?
    : In most cases, you could use [italic][LastName] & ", " &
    : [FirstName][/italic] to get a Fullname. This is such a simple
    : expression it can used directly in a query without having to put it
    : in a separate function. If the employee is allowed to have no first
    : or no second name, the comma looks ugly. So i think it's worth
    : writing a function to get a 'smarter' FullName.
    :
    : There's another killer benefit to using functions and/or calculated
    : fields. Suppose i've been using my FullName function, which
    : displays names as 'John Smith'. One year down the line, there are
    : 10,000 employee records, and the boss says he prefers FullName in
    : the format 'Smith, John (Junior Clerk)'. It's a trivial matter to
    : write a new formatting function, and use whichever is appropriate
    : for the report.
    : As to wether i tell the boss i didn't actually edit 10,000 FullName
    : fields by hand, that's another matter... and when John gets
    : promoted, i don't have to lift a finger.
    : Of course there's no real need to trash your FullName field, but
    : there's no reason not try out this approach either - you need the
    : practise, right?
    :
    : As for subs & functions in general, they are pretty straightforward
    : once you've got it, and if you are doing any programming at all you
    : soon will. The concept is the same in all programming languages
    : (except the wierd ones). I don't know wether you are learning
    : programming at college or how much you already know. Any questions,
    : feel free to ask.
    :
    : I don't know of any books that i could recommend, i tend to use the
    : on-line VB or MSDN documentation for reference and examples, but it
    : is pretty dense and doesn't have any basic programming stuff. If
    : you've got the Programmer's Guide it's prob. worth reading
    : 'Introduction to Variables, Constants and Data Types' and
    : 'Introduction to Procedures'.
    :
    : A hint about combo & list boxes: i note you've (wisely) got an ID
    : field in Employees. When i use combos i usually have the ID
    : available in the first column, i.e. base your combo on 'select ID,
    : FullName from qEmployees'. Set the first column width to 0, so only
    : the name is displayed. Usually when a list or combo is clicked,
    : you'll want to find a particular record, which means you need the ID
    : (a textual name is rarely a safe bet for identifying a unique record
    : unless it is the key field or is otherwise guaranteed unique).
    : You get the ID of the current selection with
    : ID = Combo1.Column(0)
    : you can still get the name:
    : FullName = Combo1.Column(1)
    :
    :
    : Re. your budget report: it is straighforward enough (as i'm sure
    : you suspect) to get totals at the end of a report or at the end of a
    : report section. If you have a report showing Purchase Items grouped
    : by Purchase Order, you can have a text box showing the total cost of
    : each Purchase Order (in the footer of the Purchase Order section).
    : You can get the total for the whole report by putting a text box in
    : the report footer. It's also straightforward to get the annual
    : budget figure (from your Budget table) and subtract the report total.
    : [b]However[/b] i know of no easy way to get a 'running' total, with
    : each 'detail' section of the report showing a total of all the
    : previous sections. We could write a function which will accept the
    : 'current' record ID (as an input parameter) and return the number we
    : want (functions to the rescue again.) The function can get that
    : figure essentially by querying 'select sum(amount) of all purchase
    : items up to and including this one'. The only nasty bit is that the
    : function has to perform this query for every Purchase Order section
    : on the report (or every Purchase Item record if you want that level
    : of detail). If you have a large number of purchases, your report
    : could take a [italic]very[/italic] long time to complete.
    : An alternative approach could be to have a 'PurchasesToDate' field
    : in your Budget table and also in the Purchases table. Every time a
    : Purchase Order is saved, query the value of purchases to date from
    : the budget table, add the cost of this purchase and store it back
    : into the budget table. Also put this value in the purchase record
    : itself, so it is available directly in reports. The PurchasesToDate
    : value in the Budget table changes each time a purchase is made, so
    : it is easy to get the value remaining. Each purchase record now
    : also holds the PurchasesToDate value at the time the purchase is
    : made.
    : Note: essentially i am advocating keeping explicit data which i
    : could get from other data in the database (because i can always get
    : the running total by querying the purchase items table). This would
    : seem to completely condradict what i've been saying about not
    : duplicating data! It could be justified in this case on grounds of
    : efficiency, and in practise most commercial databases do concede
    : some purity of design in a trade-off for performance.
    :
    : Re. your six list boxes, what are the buttons for? Come to that,
    : what are the list boxes for? Do they copy data from a multiple
    : selection as in your first problem? You can certainly have one
    : button doing everything, it's really just a matter of deciding what
    : the functional requirements of your interface are and making a
    : decision. As far as possible you should design your interfaces to
    : behave as like existng Windows GUI mechanisms. As far as possible,
    : [b][red]K[/red][/b]eep [b][red]I[/red][/b]t
    : [b][red]S[/red][/b]imple, [b][red]S[/red][/b]tudent.
    :
    : [blue]p.s. this topic is gettng rather lenghty, no? Should we start
    : a new one? Remember you can message me directly if you wish.[blue]
    :
    : rik
    :


    Hi Rik,

    I know this thread is getting long, but I don't know how to message you directly as this is my first time using this site.

    I, too, am having problems getting a MultiSelect field to post to the table. I haven't yet tried the suggestion you mentioned above as I wanted to get your opinion on my particular situation first. I see your point in using Option 3 & setting up a separate table to store multiple items; however, my problem is that all my data is being entered to the table via a form. Our situation is this:

    We have many freelancers who work for us that have multiple skills possibly within multiple categories. What I've setup so far are the following tables:

    tbl_Freelancer_Information Includes 35 - 40 fields...including Skills_ID which is the relationship to tbl_Freelancer_Skills

    tbl_Freelancer_skills Includes these Fields: Skill_ID (autoNumber)...relationship to tbl_Freelancer_Information, Skills_Names (Text), Skills_Categories_ID (Number)...which is the relationshp to tbl_Freelancer_Skills_categories

    tbl_Freelancer_Skills_Categories Includes these Fields: Skills_Categories_ID (autoNumber), Skills_Categories_List (Text)

    I also have form: frm_Freelancer_Information
    This form will eventually include a way to enter all the fields in the corresponding tbl_Freelancer_Information. The field I'm having difficulties with are those that have multiple selections.

    What I've done is to have 3 unbound Combo Boxes which serve only 1 purpose & that is to supply a list of skill categories. This gives them an opportunity to choose up to 3 different categories(i.e. Admin Support, Legal, & Financial) . I don't need this data posted to the table. Row Source = SELECT [tbl_Freelancer_Skills_Categories].[Skills_Categories_ID], [tbl_Freelancer_Skills_Categories].[Skills_Categories_List] FROM tbl_Freelancer_Skills_Categories;

    Then I have 3 bound List Boxes each of which depend on one of the combo boxes. Once a skill category has been selected, the list box automatically runs the query & shows only the skills that fall under that particular category (i.e. when Admin. Support is selected, brings up a list of administrative skills including Data Entry, Word Processing, Office Management, etc.) Here the user needs to be able to choose as many of the skills as they have for this category. If they have other skills under different categories, then they would use the 2nd set of combo/list boxes. Row Source = SELECT [tbl_Freelancer_Skills].[Skills_ID], [tbl_Freelancer_Skills].[Skills_Names] FROM tbl_Freelancer_Skills WHERE ((([tbl_Freelancer_Skills].[Skills_Categories_ID])=[Forms]![frm_Freelancer_Information]![cbo_Skills_Categories_#1])) ORDER BY [tbl_Freelancer_Skills].[Skills_Names];

    I, too, was hoping for a comma delimited list. I'm not certain that I understand what you were saying about complications when trying to later find the freelancers that have a certain skill. Can't you query part of a field using wildcards?

    However, I'm absolutely positive that you know more about these things than I do...lol. Therefore, with my particular situation, what do you suggest? There are simply too many possibilities that each Freelancer could have. I don't see how setting up an additional table for the list of each one's skills would help. I'd still need to somehow incorporate that list back to the main Freelancer_Information table. I've thought about the possibilty of using a subform, but not sure if this would help as I still don't know how to get the data from the form to that main table. I could even set up multiple fields in the table, but again...the possibilites could be many so I'd have no idea how many fields to set up.

    Any suggestions? Was I wrong thinking that we could query & locate 1 skill from a comma separated list using wildcards...i.e. *data entry*? I know that Excel works OK with multiSelect because I once had a client who hired me to set up a drop-down list box using the multiSelect property & VBA code to list the selections separated by commas. It must have worked OK for them because they never contacted me stating otherwise. If this is the case, possibly when we need to search for all the freelancers with a particular skill we could export that data to Excel using comma delimited to separate the skills into different columns & do the query from within Excel?

    Any suggestions would be greatly appreciated.

    USEN

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Categories

In this Discussion