Full Version: Using VBA To Decide Whether To Use Text Or Combo Box To Populate Field
UtterAccess Forums > Microsoft® Access > Access Forms
pjarroues
I have a form whose subform is populated base upon check boxes. This is all working great except for one field.

If the chkRPB is clicked I want the text box Result_Value to become a combo box with four choices (Clear, Slight Turb,, Turbid, Opaque). Otherwise the Result Value should remain free text. Is there a way to do this?

Here is my current code - Thinking some sort of if / then or validation or something. I have also created a table with the four choices named tblTurb if that would make things easier


Private Sub chkRBP_Click()
If Me.chkRBP = True Then
Me!frmFieldMeasurements_Sub.SetFocus
Form_frmFieldMeasurements_Sub.Activity_ID = DEQ_SiteVisitCode.Value & "F"
Form_frmFieldMeasurements_Sub.Characteristic_Name = "RBP Turbidity Code (choice list)"
Form_frmFieldMeasurements_Sub.Result_Value = "I want this to be a combo box"
Form_frmFieldMeasurements_Sub.Analytical_Method_ID = "NA"
Form_frmFieldMeasurements_Sub.Value_Type = "Estimated"
'Form_frmFieldMeasurements_Sub.Sample_Collection_Method_ID = "IWS"
DoCmd.GoToRecord , , acNewRec
End If
End Sub

Private Sub chkSC_Click()
If Me.chkSC = True Then
Me!frmFieldMeasurements_Sub.SetFocus
Form_frmFieldMeasurements_Sub.Activity_ID = DEQ_SiteVisitCode.Value & "F"
Form_frmFieldMeasurements_Sub.Characteristic_Name = "Specific Conductance"
Form_frmFieldMeasurements_Sub.Result_Value_Unit = "uS/cm"
Form_frmFieldMeasurements_Sub.Analytical_Method_ID = "NA"
Form_frmFieldMeasurements_Sub.Value_Type = "Actual"
'Form_frmFieldMeasurements_Sub.Sample_Collection_Method_ID = "IWS"
DoCmd.GoToRecord , , acNewRec
End If
End Sub
GroverParkGeorge
It's been my experience over the years that trying to solve a design problem with VBA can lead to a lot of code and not a lot of efficiency.

I would strongly recommend dropping this approach and going to something a lot less complicated.

An even better plan would be to use ONLY the combo box, with the Limit To List Property set to No. That offers the users the four canned choices, but allows them to add anything else they want. Plus, it could allow a list of alternative choices to build up as users enter new records.

And even then, you're going to get a lot of variations of spelling, abbreviation, etc. Some people will select "Turbid" and some will free-text enter "Turbod".

Sometimes, in cases like this I try to guide people to appropriate selections by putting in the list of "official" choices as the first component of a union query, and the list of "non-official" choices previously entered with an asterisk "*" in front of them to show that they were previously used as non-official free choices. The reason for that is to let all of those previously entered free-form choices appear in the combo box in the records where they were previously entered, but clearly flag them as being different.


Something like this:

SELECT YourOfficialNameField AS NameChoices, 0 AS SortOrder
FROM tblOfficialNames
UNION
SELECT DISTINCT "*" & [Result_Value] AS NameChoices, 1 AS SortOrder
FROM tblYourtableNameGoesHere
ORDER BY SortOrder, NameChoices

Make this the row source for the combo box, set the limit to list property to "NO" and bind it to the appropriate field in the table. Hide the second column, SortOrder.
pjarroues
Thank you for your prompt reply. I considered your suggestion but I have a problem in that the result value for all my other entries are various numeric values. This is the only record type to have a set list. The reason I wanted to go with a pick list for these is due to the misspellings and such. I think I will probably have to keep it free text.
GroverParkGeorge
Keeping it free form text guarantees spelling variations, of course. So, while that's an option, it's not optimal.

What I don't understand--because I'm not familiar with the business rules--is why only four options are "official", but almost any other value is also okay. Maybe a different approach is to have a second field. Limit the choices in this one to the four official choices plus "other". Then, if they select "other", enable the freeform text box to accommodate it. Otherwise keep that textbox disabled. That allows you to create the lookup table for those values and store the appropriate Foreign Key for it. The "other" field will only be populated for some records, and that's a violation of normalization, but it's probably better than trying to marshal complicated code to manipulate the data entry.
doctor9
pjarroues,

It might help to know more about what sort of data you're trying to track. Right now it seems like you're storing two distinctly different types of data in the same field. Can you explain what chkRPB represents, and what those four values are, in real-world terms?

Hope this helps,

Dennis
pjarroues
Those values are real world terms for turbidity. Also in this field are things like water temp, flow rates, Specific Conductivity, etc. It is a text field although must of the entries are numeric
GroverParkGeorge
So, in a field where "Turbid" is a valid entry, you can ALSO have "33.33" as a valid entry (or whatever the actual numbers would be)?

Is there a second field indicating what the measurement type is?
doctor9
pjarroues,

All of those things sound important... Why are you storing such a variety of things in one field? Why not have a field for the temperature, and one for the flow rate, etc.? Right now it seems like you're going to a lot of work to only store one of them at a time. Do you ever record the temperature AND the flow rate for the same test? If so, are those stored in separate records?

I'm just trying to understand your setup.

Dennis
pjarroues
We have a "Characteristic Name" field which we enter things like Turbidity, Flow, Water temp., etc. Then a "result value" field (which I am attempting to populate when Turbidity is selected) and a "result value units" field which we enter the unit of measure for the result value; ft/s3, C, F, ntu, etc.

What happens is our assessors go out, collect various types of data regarding a stream. When back in office these data are entered into the database using the forms created based on their field form. This data is all stored in the "FieldMeasurements" table which is then uploaded into a national database
doctor9
pjarroues,

So, you are actually tracking multiple values... but you're not storing them in the same record. Why? Why not have this table setup?

tblTests
TestID [Autonumber, Primary Key]
dteTestDate
lngTestLocationID
sngTurbidity
strFlow
intWaterTemp
etc.

This way you have all of the characteristics in their own field, which is the simplest setup. I'm wondering why you've gone to the trouble of storing each characteristic in one field, and in a separate table of test characteristics, which seems to be making things more complicated. Sorry if my question wasn't clear before.

Dennis
pjarroues
This is the way it is set up in the national database. Ours is basically a replica for easy import.
doctor9
pjarroues,

Okay... so I guess we'll make the best of it.

Here's one strategy, I think: Create a textbox that is bound to the result field, and place it on your form. Then, create a combobox that is also bound to the result field, and place it on your form, preferably taking up the exact same spot as the textbox. Then, create a subroutine that makes either the textbox or the combobox invisible (and the other one visible), based on the value in Characteristic Name. (Characteristic Name should probably have a default value so when you go to a new record you still only see one of the controls.) Then, call this subroutine from the Current event of the form (so it changes as the user browses through existing data) and the AfterUpdate event of the Characteristic Name bound control (which I believe is chkRBP based on your original post). That way the textbox is visible when the user wants to enter any old text, and the combobox is visible when the user wants to be limited to the four choices in the combobox.

Hope this helps,

Dennis
pjarroues
I like it...I will give it a try and let you know. Thanks so much!!
River59
QUOTE
Private Sub chkRBP_Click()
If Me.chkRBP = True Then
Me!frmFieldMeasurements_Sub.SetFocus
Form_frmFieldMeasurements_Sub.Activity_ID = DEQ_SiteVisitCode.Value & "F"
Form_frmFieldMeasurements_Sub.Characteristic_Name = "RBP Turbidity Code (choice list)"
Form_frmFieldMeasurements_Sub.Result_Value = "I want this to be a combo box"
Form_frmFieldMeasurements_Sub.Analytical_Method_ID = "NA"
Form_frmFieldMeasurements_Sub.Value_Type = "Estimated"
'Form_frmFieldMeasurements_Sub.Sample_Collection_Method_ID = "IWS"
DoCmd.GoToRecord , , acNewRec
End If
End Sub


Not sure if this will work but you can create a list like this.

Private Sub chkRBP_Click()
If Me.chkRBP = True Then
Me!frmFieldMeasurements_Sub.SetFocus
Form_frmFieldMeasurements_Sub.Activity_ID = DEQ_SiteVisitCode.Value & "F"
Form_frmFieldMeasurements_Sub.Characteristic_Name = "RBP Turbidity Code (choice list)"
Form_frmFieldMeasurements_Sub.Result_Value = Replace("Clear, Slight Turb, Turbid, Opaque", ",", vbCrLf)
Form_frmFieldMeasurements_Sub.Analytical_Method_ID = "NA"
Form_frmFieldMeasurements_Sub.Value_Type = "Estimated"
'Form_frmFieldMeasurements_Sub.Sample_Collection_Method_ID = "IWS"
DoCmd.GoToRecord , , acNewRec
End If
End Sub

I don't know if one of these selections can be recognized as a variable in the List box but you can give it a try.
The Doc and Grover make some very good points.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.