Field of multivalue type

[DevAccess]

Hello

I am creating resume builder database, now in that in the resume table I have created a field called "Certifications" which is type of text and user can enter any number of certifications. I also created one form that is bound resume table based query and allowing user to enter the data.

Certification is free text field on the form and can enter any number of certifications. Below is the way user can type in.

Certification 1
Certification 2
........
...........
.............

User generally enters the certification by hitting enter key.

Now I have developed one form wherein I have created list box which refers all the certifications entered by users ( remember one record can query N number of certifications ) but it gives top first certifications per record in listbox, what I want is to show all certification records of all records. I have bound the list box row source to the resume table.

Please assist.

 

[June7]

I don't use multi-value fields. The listbox RowSource would have to be a query that 'expands' the multi-value field. If you really want to use multi-value field, review this https://support.office.com/en-us/ar...d-fields-7C2FD644-3771-48E4-B6DC-6DE9BEBBEC31

 

[DevAccess]

I don't use multi-value fields. The listbox RowSource would have to be a query that 'expands' the multi-value field. If you really want to use multi-value field, review this https://support.office.com/en-us/ar...d-fields-7C2FD644-3771-48E4-B6DC-6DE9BEBBEC31

I think you did not understand my question quite right, I have text field which takes values as

Test1
Test2
....

After being saved this in the table, I want listbox on another form should display all the records certifications and should remove duplicates.

Thanks

 

[June7]

"one record can query N number of certifications" Sounds like a multi-value field. And the thread title is "Field of multivalue type". Therefore I understood this to be a multi-value field issue and the referenced link is pertinent.

However, finally hit me that you said the field is 'free text' and therefore could not be a multi-value field.

So, what is your data structure and table relationships? Do you have a Resumes table and an associated ResumeCertifications table? A 1-to-many relationship? You want listbox to display only the 'child' records associated with 'parent'. Is listbox on a form bound to 'parent' table? The listbox RowSource would be an SQL with WHERE criteria that references the 'parent' ID field. Would need VBA to requery the listbox when moving to another record. Use form Current event.

 

[DevAccess]

"one record can query N number of certifications" Sounds like a multi-value field. And the thread title is "Field of multivalue type". Therefore I understood this to be a multi-value field issue and the referenced link is pertinent.

However, finally hit me that you said the field is 'free text' and therefore could not be a multi-value field.

So, what is your data structure and table relationships? Do you have a Resumes table and an associated ResumeCertifications table? A 1-to-many relationship? You want listbox to display only the 'child' records associated with 'parent'. Is listbox on a form bound to 'parent' table? The listbox RowSource would be an SQL with WHERE criteria that references the 'parent' ID field. Would need VBA to requery the listbox when moving to another record. Use form Current event.

Please find attached screenshot which will make you more easy for you.

 

[June7]

Then what they are entering into the text field is simply one long string of text with CrLf characters to force line breaks. Listbox can't handle multi-line text. Options:

1. a related child table where each certification is a separate record

2. multi-value field and the query to 'expand' the field as demonstrated in the referenced link

3. VBA code that parses the string to an array and then loops the array to add items to listbox

 

[DevAccess]

Then what they are entering into the text field is simply one long string of text with CrLf characters to force line breaks. Listbox can't handle multi-line text. Options:

1. a related child table where each certification is a separate record

2. multi-value field and the query to 'expand' the field as demonstrated in the referenced link

3. VBA code that parses the string to an array and then loops the array to add items to listbox

I think this sounds better:

VBA code that parses the string to an array and then loops the array to add items to listbox

Do you have any code for this ? which loops through table records one specified field and insert into listbox ?

 

[June7]

I don't have a complete example to provide. The code would involve:

1. grab field value from specific record - if the code is behind a form that opens to the specific record, could reference field of form's RecordSource; other methods to grab data from table are DLookup or recordset object

2. parse the string to an array object - use Split function

3. in a looping structure read each array element

4 inside the loop add the array element to listbox list - use listbox AddItem method

There are examples of each of these code components out there. Research and when you develop code tailored to your db that has issues, post question.