parsing a table field through a form

[abcdefghijklmnop]

I have a "Questions" form that reads off of a table and contains a multi-value field called "Keywords". I already know that having a multi-value field is a horrible idea, however, I am patching up a few things in a database I did not create and there are other factors involved in my need to keep that particular multi-valued field. What I would like to do is have 8 drop down boxes on the side that call in a list of keywords from another table.
I want an update button that, when clicked and for a particular record, the keywords from the 8 drop down boxes would be concatenated together with commas between them (7 commas in total) and thrown into one string variable which I will use to replace the multi-value field "Keywords" for that specific record. The other part of it is that at the instance of loading the form, I want to parse the current "Keywords" field (using the comma as the delimiter) into the 8 existing combo boxes. The user will not even need to see the "Keywords" field, because they will just be using the 8 boxes as an interface to edit that "keywords" field.

The recordsource for the forum is based on a [Questions] Table

Any help would be apprciated,

Thanks,

[iburyak]

1. It would be nice to create array of 8 drop down boxes by setting index property to each from 0 to 7.

2. to split sKeywords use following code.
[PHP]Dim arr, i

arr = Split(sKeywords , ",")

For i = 0 To UBound(arr)
DropDown(i) = arr(i)
Next[/PHP]

3. To collect data back from all boxes

[PHP]sKeywords = ""

For i = 0 to 7
sKeywords = sKeywords & IIF(sKeywords = "", "", ",") & DropDown(i).Tex t
Next[/PHP]

Good Luck

[Killer42]

1. It would be nice to create array of 8 drop down boxes by setting index property to each from 0 to 7.

I don't think Access supports control arrays. MS seem to have been quite keen to get rid of them - removed them from VBA, removed them in VB.Net...

If you do a quick search on TheScripts, you can find links to an article at Microsoft on how to simulate a control array. (Plus my own technique which is far superior, of course.:))

[iburyak]

I don't think Access supports control arrays. MS seem to have been quite keen to get rid of them - removed them from VBA, removed them in VB.Net...

If you do a quick search on TheScripts, you can find links to an article at Microsoft on how to simulate a control array. (Plus my own technique which is far superior, of course.:))

You think it is Access..... mmmmmmmmmmmm

[Killer42]

You think it is Access..... mmmmmmmmmmmm

Until the OP says otherwise, yes I do.

[abcdefghijklmnop]

Sorry I did not get to test the Code until now, and Killer42 is right about the fact that it is access that I am using.

Killer42, I looked into your method/code that is posted here:
Killer42's Simulating Arrays in VB for Access
and i have a few questions about adjusting it to serve my purposes.

1. According to your code in the linked post, Will the final array variable name be MyTextBox(Mytex tBoxCount)?

2. Would I place the following code to split up the multi-value field "sKeyword" into the combo boxes right after the completion for statement in the form_load procedure?

For
.....
Next

MyTextbox(MyTextBoxCount) = Split(sKeywords, ",")

3. Instead of your Text0_Change() command, would it be okay if i had a button that combines the fields from the 8 combos back into the multi-value "sKeywords" field.

Private Sub button1_Click()
Dim i As Long
sKeywords = ""

For i = 0 to 7
    sKeywords = sKeywords & IIF(sKeywords = "", "", ",") & MyTextbox(i).Text
Next
End Sub

Thank you for your help on this,

[Killer42]

Sorry I did not get to test the Code until now, and Killer42 is right about the fact that it is access that I am using.

Killer42, I looked into your method/code that is posted here:
Killer42's Simulating Arrays in VB for Access
and i have a few questions about adjusting it to serve my purposes.

Ok, but keep in mind that I just made this up and threw it into TheScripts, after a very quick test. Haven't had a chance to really investigate it yet.

1. According to your code in the linked post, Will the final array variable name be MyTextBox(Mytex tBoxCount)?

Yes, that's right. As far as I know, you should be able to address that array the same as if it were a VB6 control array. Except that (probably) you won't be able to Load and Unload occurrences.

2. Would I place the following code to split up the multi-value field "sKeyword" into the combo boxes right after the completion for statement in the form_load procedure?

For
  .....
Next
MyTextbox(MyTextBoxCount) = Split(sKeywords, ",")

If they are combo boxes, then you might want to use a more accurate name for the array than MyTextBox(). But otherwise, as far as I know this should be fine. I don't actually have any experience with loading an array in one go via the Split function, though, so my advice would be to try it out and see what happens.

3. Instead of your Text0_Change() command, would it be okay if i had a button that combines the fields from the 8 combos back into the multi-value "sKeywords" field.

Private Sub button1_Click()
Dim i As Long
sKeywords = ""
For i = 0 to 7
    sKeywords = sKeywords & IIF(sKeywords = "", "", ",") & MyTextbox(i).Text
Next
End Sub

I see no reason why not.

As always, as the programmer it's entirely up to you how you want to go about achieving your goals. That's the beauty of this technique :D - you can deal directly with the controls on the form, or you can work with the array - they're the same thing. So anything you can do with an array or a control, you should be able to do quite easily.

I believe the best way to make use of this technique, or any novel programming idea, is to play around with it, and see what you can make it do. You will probably across both good and bad things that I haven't thought of.

[abcdefghijklmnop]

Well, naturally i am having errors with the code below... and I dont think it is because of the simulated array. The error is
"Run-time error '424': Object Required"
This error occurs at the line
"If Me.Question_Key words.Value Is Not Null Then"

The [Question_Keywor ds] is the multi-value field that I am trying to split up into the combo boxes. This field gets its values from a table control source.

Private Sub Form_Load()
  Dim i As Long
  Dim ctl As Control
  
  For Each ctl In Me.Controls
    If TypeOf ctl Is ComboBox Then
      If Left$(ctl.Name, 7) = "keyword" Then
        MyComboBoxCount = MyComboBoxCount + 1
        ReDim Preserve MyComboBox(1 To MyComboBoxCount)
        Set MyComboBox(MyComboBoxCount) = ctl
      End If
    End If
  Next
  
  If Me.Question_Keywords.Value Is Not Null Then
  MyComboBox(MyComboBoxCount) = Split(Me.Question_Keywords.Value, ",")
  End If
  
  Set ctl = Nothing
End Sub

any ideas?

Thanks again,

[Killer42]

Well, naturally i am having errors with the code below... and I dont think it is because of the simulated array. The error is
"Run-time error '424': Object Required"
This error occurs at the line
"If Me.Question_Key words.Value Is Not Null Then"

Perhaps Form_Load is too early to access it? Assuming we're talking about an MS Access form (I don't remember for sure), I'm not that familiar with the events available. I've just had a look, and there were a dizzying array of them.

I might ask an Access expert or two to look in on this thread, and see whether they can help.

[MMcCarthy]

Try this instead ...

Private Sub Form_Load()
  Dim i As Long
  Dim ctl As Control
  
  For Each ctl In Me.Controls
    If TypeOf ctl Is ComboBox Then
      If Left$(ctl.Name, 7) = "keyword" Then
        MyComboBoxCount = MyComboBoxCount + 1
        ReDim Preserve MyComboBox(1 To MyComboBoxCount)
        Set MyComboBox(MyComboBoxCount) = ctl
      End If
    End If
  Next
  
  If Not IsNull(Me.Question_Keywords) And Me.Question_Keywords <> ""  Then
  MyComboBox(MyComboBoxCount) = Split(Me.Question_Keywords.Value, ",")
  End If
  
  Set ctl = Nothing
End Sub

[NeoPa]

Private Sub Form_Load()
...
  If Me.Question_Keywords.Value Is Not Null Then
...
End Sub

I'm no VB expert, but I'm with Mary on this one. In VB(A at least), I would expect to see IsNull() used instead :

  If Not IsNull(Me!Question_Keywords) Then

[Killer42]

I'm no VB expert, but I'm with Mary on this one. In VB(A at least), I would expect to see IsNull() used instead :

  If Not IsNull(Me!Question_Keywords) Then

Thanks people. Now that I think about it, I can't recall seeing the "Is Not Null" syntax anywhere except in SQL.

So, let's hope this is the solution. (Fingers crossed.)

[abcdefghijklmnop]

thanks for that fix, The form did successfully load after changing that...

However since this form is based from the values of the table, it seems that it only 'tries' to split the value of a new record, which obviously does not even split it because of the multi-value of a new record is NULL. It does not split the values of all the records in that form... So I was wondering if there is anyway i could trigger the split function whenver a user moves to another record on the form. I could not find any event triggers that are based on a user switching between records...

Honestly I am starting to think this band aid fix to the problem is not worth the pain...
I am currently seeing if i can remove the multi-value field and apply the values to a several one-value fields without affecting the functionality of other linked forms.
This is a way a proper database needs to run anyway.

Thanks for all the help,

[NeoPa]

The OnCurrent event of the form should work for you.

Private Sub Form_Current()
...
End Sub