Populate and Link 3 Combo boxes from a single DFB

DennisPomo · ‎01-04-2011

Hi, I have seen many posts on topics related to this - but nothing that quite covers my problem.

I have a single DBF with 3 fields (SppCode, SppComm, SppSci) I have 3 combo boxes on a custom ArcPad form for field workers to choose names from. We anticipate that some field workers will know the Latin binomial name (held in SppSci) but others may only know the common name (SppComm). To make it easier for them I want to link the 3 combo boxes so that when any one of the combo boxes is used an OnSelChange event will change the other 2 boxes to the matching fields for the selected record.

My initial approach was to get the ListIndex value from the active combo box and then set the other two combo boxes ListIndex attribute to the same value. This worked well until I turned on the sort function and the ListIndex became meaningless as an index between fields.

Should I add an extra field in the DBF as a unique index and try to use this to make selections? or is the .bookmark property for recordset objects capable of doing what I want?

Any help is greatly appreciated.

Dennis

RolfBroch · ‎01-04-2011

You can keep your sorted lists but you have to use code to set the correct value in the combobox. This will require that you loop through the items in the combobox to find the correct one.

You can search on the text item or value item using one of the functions below.

Rolf

''=================================================================
''
'' ---------- Function intSetCBXValue(ByRef pCBX, pValue) ----------
''
'' Generic function to set a combo box's listindex based on the
'' value attribute.
''
'' Parameters:
'' pCBX (a combobox)
'' pValue (the value to find in the combobox)
''
'' Returns: the listindex or -1 if no match was found
''
''=================================================================
Function intSetCBXValue(ByRef pCBX, pValue)
For intSetCBXValue = 0 To pCBX.ListCount-1
    pCBX.ListIndex = intSetCBXValue
    If CStr(pCBX.Value) = CStr(pValue) Then
      Exit For
    End If
Next

' If the value was not found, make nothing selected in combobox
If CStr(pCBX.Value) <> CStr(pValue) Then
    pCBX.ListIndex = -1
    intSetCBXValue = -1
End If
End Function

''=================================================================
''
'' ---------- Function intSetCBXText(ByRef pCBX, pText) ----------
''
'' Generic function to set a combo box's listindex based on the
'' text attribute.
''
'' Parameters:
'' pCBX (a combobox)
'' pValue (the value to find in the combobox)
''
'' Returns: the listindex or -1 if no match was found
''
''=================================================================
Function intSetCBXText(ByRef pCBX, pText)
For intSetCBXText = 0 To pCBX.ListCount-1
    pCBX.ListIndex = intSetCBXText
    If CStr(pCBX.Text) = pText Then
      Exit For
    End If
Next

' If the text was not found, make nothing selected in combobox
If CStr(pCBX.Text) <> pText Then
    pCBX.ListIndex = -1
    intSetCBXText = -1
End If
End Function

DennisPomo · ‎01-09-2011

Thanks for the help Rolf.

I used your code snippets and a some other pieces on the forum to solve this problem. As a complete beginner I really appreciate the help, thanks very much. I have seen so many people looking to do similar things that I will paste the code below.

This function takes the name of the calling combo box, the names of the other two combo boxes you wish to change. It also takes the field names used in the DBF, first the key or lookup field (depending on the calling combo box) and the other two fields we want to look up.

The function is called using the onselchange event for the combobox and the parameters sent depend on the calling combobox e.g:
Call UpdateFields("cboSppCode", "cboCommName", "cboSciName", "CODE", "COMMON_NAM", "LATIN_NAME")

My DBF has 3 fields: CODE: Unique code for each species, COMMON_NAM: Common name, LATIN_NAME: the scientific binomial for the species.

The code..:

'Function recieves string values which must be valid cbo names for page controls.
Sub UpdateFields(strCallingCBO, strControl1, strControl2, strKeyField, dbfField1, dbfField2)
Dim objPage, rsTrees, cboCallingCBO, cboControl1, cboControl2, txtUpd1, txtUpd2, txtCallValue, BookMark
'Set the environmental parameters
Set objPage = Layers("Tree_Inventory").Forms("EDITFORM").Pages("Species")

Set cboCallingCBO = objPage.Controls(strCallingCBO)
Set cboControl1 = objPage.Controls(strControl1)
Set cboControl2 = objPage.Controls(strControl2)

'Assign the calling cbo value to a variable: txtCallValue
txtCallValue = cboCallingCBO.Value

'Open the DBF file in read only.
Set rsTrees = Application.CreateAppObject("RecordSet")
Call rsTrees.Open ("C:\TreeInventory\APCheckout\DataForArcPad\TreeName.DBF",1)
'Mobile Version has the DBF in a different location, uncomment before final use.
'rsTrees.Open ("C:\Documents and Settings\My Documents\DataForArcPad\TreeName.DBF",1)

'Search the recordset for the other values
'BookMark = rsTrees.Find("

=""" & txtCallValue & """")

BookMark = rsTrees.Find("[" & strKeyField & "]=""" & txtCallValue & """")

'Now Capture the other two field values if there is a valid match for the selected value
If BookMark = 0 Then
MsgBox "Value Not Found in Database!",vbExclamation,"No Code Found"
'Reset all combo boxes to default value
Set cboCallingCBO.ListIndex = -1
Set cboControl1.ListIndex = -1
Set cboControl2.ListIndex = -1
Exit Sub
Else
txtUpd1 = rsTrees.Fields(dbfField1).Value
txtUpd2 = rsTrees.Fields(dbfField2).Value

'Now change the other two combo boxes.
Call intSetCBXText(cboControl1, txtUpd1)
Call intSetCBXText(cboControl2, txtUpd2)
End If
End Sub

'Code from Rolf
'--------------------------------------------------------------
'Function to Set ComboBox ListIndex values to the matching text
Function intSetCBXText(ByRef pCBX, pText)
For intSetCBXText = 0 To pCBX.ListCount-1
pCBX.ListIndex = intSetCBXText
If CStr(pCBX.Text) = pText Then
Exit For
End If
Next

'If the text was not found, make nothing selected in combobox
If CStr(pCBX.Text) <> pText Then
pCBX.ListIndex = -1
intSetCBXText = -1
End If
End Function

I am always interested in suggestions for improvement to help me learn Arcpad 8 and up, so comments are appreciated.

Regards

Dennis

CharlesHuxtable · ‎01-26-2011

Dennis & Rolf, I am trying to do a similar thing to what you have shown in this demo - which has been very useul thanks. However I still can't get it to work properly. I have 3 comboboxes for: a code, a common name and a scientific name (binomial). If I select a code number from the code combobox, it successfully updates the other 2 comboboxes - so far, so good. However if I try to select a common name from the common name combobox, I get an error ""cboCallingCBO.ListIndex [Line 758, Col 2] Source text unavailable." A similar thing happens when I attempt to select a scientific name from that combobox. It seem that somehow it's not recognising the Calling combobox, or that I haven't set the parameters properly. The way I call the function is using the onselchange event for the combobox. For example, in the combobox I have for the code it is:

Call UpdateField("cboSppCode", "cboCommName", "cboSciName", "CODE", "COMMON_NAM", "LATIN_NAME")

I'm not sure if I should be using all these parameters

The code I'm using is as follows:

'Function receives string values which must be valid cbo names for page controls.
Sub UpdateFields(strCallingCBO, strControl1, strControl2, strKeyField, dbfField1, dbfField2)
Dim objPage, rsTrees, cboCallingCBO, cboControl1, cboControl2, txtUpd1, txtUpd2, txtCallValue, BookMark
'Set the environmental parameters
Set objPage = Layers("Veg16_Main.shp").Forms("EDITFORM").Pages("Triple")

Set cboCallingCBO = objPage.Controls(strCallingCBO)
Set cboControl1 = objPage.Controls(strControl1)
Set cboControl2 = objPage.Controls(strControl2)

'Assign the calling cbo value to a variable: txtCallValue
txtCallValue = cboCallingCBO.Value

'Open the DBF file in read only.
Set rsTrees = Application.CreateAppObject("RecordSet")
Call rsTrees.Open("P:\Work\SSD\Users\Huxtac\ArcPad Stuff\Veg_16\Triple.dbf",1)
'Mobile Version has the DBF in a different location, uncomment before final use.
'rsTrees.Open ("C:\Documents and Settings\My Documents\DataForArcPad\TreeName.DBF",1)

'Search the recordset for the other values
'BookMark = rsTrees.Find("

=""" & txtCallValue & """")

BookMark = rsTrees.Find("[" & strKeyField & "]=""" & txtCallValue & """")

'Now Capture the other two field values if there is a valid match for the selected value
If BookMark = 0 Then
MsgBox "Value Not Found in Database!",vbExclamation,"No Code Found"
'Reset all combo boxes to default value
Set cboCallingCBO.ListIndex = -1
Set cboControl1.ListIndex = -1
Set cboControl2.ListIndex = -1
Exit Sub
Else
txtUpd1 = rsTrees.Fields(dbfField1).Value
txtUpd2 = rsTrees.Fields(dbfField2).Value

'Now change the other two combo boxes.
Call intSetCBXText(cboControl1, txtUpd1)
Call intSetCBXText(cboControl2, txtUpd2)
End If
End Sub

'Function to Set ComboBox ListIndex values to the matching text
Function intSetCBXText(ByRef pCBX, pText)
For intSetCBXText = 0 To pCBX.ListCount-1
pCBX.ListIndex = intSetCBXText
If CStr(pCBX.Text) = pText Then
Exit For
End If
Next

'If the text was not found, make nothing selected in combobox
If CStr(pCBX.Text) <> pText Then
pCBX.ListIndex = -1
intSetCBXText = -1
End If
End Function

DennisPomo · ‎01-26-2011

Hi Charles,

You are learning from a beginner but I did get this to work. Hopefully Rolf will also chime in, his code snippets are very useful on these forums. Here is my suggestion:
1. UpdateField is called with the onselchange event of each combo box. The order is extremely important. For example, the first combo in my form is the Species Code (cboSppCode). the onselchange event sends the first parameter as the Index field, the known field (Unique code for this species) if you will. the sequence has to match exactly i.e. Call function(combo1, combo2, combo3, 'followed by' field for combo1, field for combo2, field for combo3).
I will copy and paste the three events below.

2. Be sure that the field names you pass as parameters match the field names in your DBF file. A good way to debug would be to replace the string values with the actual field names just to test.

3. You may want to correct the error handling. I used "Set cboControl1.ListIndex = -1" - Remove the "Set".

Hope this helps.

onselchange events for the 3 combo boxes:
cboSppCode
Call UpdateFields("cboSppCode", "cboCommName", "cboSciName", "CODE", "COMMON_NAM", "LATIN_NAME")

cboCommName
Call UpdateFields("cboCommName", "cboSciName", "cboSppCode", "COMMON_NAM", "LATIN_NAME", "CODE")

cboSciName
Call UpdateFields("cboSciName", "cboSppCode", "cboCommName", "LATIN_NAME", "CODE", "COMMON_NAM")

CharlesHuxtable · ‎01-26-2011

Hi Dennis, thanks very much for your help. I'm also a novice and am learning as I go.

Your instructions worked ! Interestingly, my code field contains integers. When I selected from the Code combo box, it autofilled the comon and scientific name values with no problem, however if I selected from either the common or scientific combo boxes, it left the Code field blank. I then changed the integers to text to see what would happen, and it worked perfectly. I'm wondering now how to change the code to allow integers to be used.
Charles

CharlesHuxtable · ‎01-26-2011

Dennis, I figured out how to solve this. I simply changed the format of the CODE field in the dbf file from number to text. All working now. Thanks again for your help.

DennisPomo · ‎01-27-2011

Glad to hear it is working for you.

-Dennis

KyleSato · ‎02-22-2011

Hello,
I am trying to empliment this for my tree inventory as well. I am TOTALLY new to coding, and so am now the reigning novice of this thread.
It seems I have missed something in the translation, or am just slow, but I keep getting errors.
I have not seen in the codes that have been posted where exactly the differentiation is made for each combobox.

Does the:
Call UpdateFields
("cboBOTNAME", "cboCOMMON", "cboCODE", "BOTNAME", "COMMONNAME", "SP_CODE")
come at the beginning of the code?

Or is it what replaces the:

Sub UpdateFields(strCallingCBO, strControl1, strControl2, strKeyField, dbfField1, dbfField2)

And if so, is that the only other place where the code is changed (other than at the "Set objpage" and .DBF location lines)?

I am sure that this is more simple than I am making it, but I have managed to thouroughly confuse myself.

Any assistance would be great.

Thanks,
Patrick

RyanPaquette1 · ‎05-23-2018

ArcPad DEV newb here, looking for an easy button.

ArcPad 10.2 - Windows 10

So In ArcPad, the Limit To List property of a Combo Box cannot be set programmatically via VB?

That's a downer

Form.apl - Limit to List was set to false in all combo boxes which was triggering the Win10 virtual keyboard

- Wasn't Ideal so I set them to true. easy peasy lemon squeezy

Well... that's true for initial data entry, but there is also an option to open the form in edit mode, where the original developer is loading the text values, not the cbo's index values.

Because Limit to List is set, no values are being loaded, throwing errors (app logic dictates these values must be present).

hundreds of fields affected.

Easy button?