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,
13 1843
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).Text
Next[/PHP]
Good Luck
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.:))
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
You think it is Access..... mmmmmmmmmmmm
Until the OP says otherwise, yes I do.
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(MytextBoxCount)?
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,
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(MytextBoxCount)?
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.
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_Keywords.Value Is Not Null Then"
The [Question_Keywords] 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,
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_Keywords.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.
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 32,556
Expert Mod 16PB - 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
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.)
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 32,556
Expert Mod 16PB
The OnCurrent event of the form should work for you. - Private Sub Form_Current()
-
...
-
End Sub
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Old Timer |
last post by:
I wish to type in a number in my "Code" field, for instance 1060, I
then wish the number 1060 to trigger an event that will fill in the
next field (township field) For instance, 1060 brings up and...
|
by: Andre |
last post by:
I am needing to Parse some text fields, where a 3rd party application i
use stores data from a user created expression.
the user creates and expression such as this:
ActualSizeYN =Y?...
|
by: Aaron Walker |
last post by:
At the beginning of my program, I open a config file and load the contents
into a structure (please disregard the non-portable sockaddr_in struct as it is
irrelevant to the problem):
struct...
|
by: Peter |
last post by:
Could someone please point me to a function/module for Access 2000 that will
enable me to parse a string field. So if a field consists of "word_1
word_2 ...... word_n", I would like to be able...
|
by: Ørjan Langbakk |
last post by:
I'm parsing a CSV-file into a table on a webpage - and I'd like to be
able to change the alignment for the _last_ <td> in each <tr> - but, as
the file is today, it's not possible for me to assign a...
|
by: bruce |
last post by:
hi...
it appears that i'm running into a possible problem with
mechanize/browser/python rgarding the "select_form" method. i've tried the
following and get the error listed:
br.select_form(nr...
|
by: john |
last post by:
In my form I have a master table and a details table linked 1xM. I can
search through the whole parent table but I also like to be able to search
through the child table fields to find parent...
|
by: programming |
last post by:
Hi all,
i have been having trouble with a login script that works on my
windows machine, however when i upload it to the Unix server through
VPN, the same script won't work! It won't parse...
|
by: Chris Carlen |
last post by:
Hi:
Having completed enough serial driver code for a TMS320F2812
microcontroller to talk to a terminal, I am now trying different
approaches to command interpretation.
I have a very simple...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |