Lookup Value in DBF Files

[CharlesHuxtable]

Hello all,
I want to update the value of a textbox called "txtCommon" based on the value of a Combobox called "cboScientific". I am using ArcPad Studio to design my form.

This is almost identical to a request posted to this site by OisinK on 28th April 2010. I have tried the script suggested but it still won't work.
The combobox is populated by a dbf file called "NameTest.dbf" with 2 fields - "COMMON" and "SCIENTIFIC".

I want to attach a function to the onselchange event of "cboScientific" which looks up the corresponding COMMON name in the dbf file.

I have the following script:
Sub UpdateCommonName
Dim objEditForm, cboSCI, txtCOM, objRecords, dbfSCI, dbfCOM
Set objEditForm = layer.Forms("EDITFORM")
Set cboSCI = objEditForm.Pages("Grassnames").Controls("CboGrScientific")
Set txtCOM = objEditForm.Pages("Grassnames").Controls("TxtGrCommon")
Set objRecords = Application.CreateAppObject("Recordset")

Call objRecords.open("P:\Work\SSD\Users\Huxtac\ArcPad Stuff\Hunter Rapid Survey\GrassNameTest.dbf",1)
Set dbfSCI = objRecords.fields.item("SCIENTIFIC")
Set dbfCOM = objRecords.fields.item("COMMON")

objRecords.movefirst
While not objRecords.EOF
If cboSCI.Value = dbfSCI.value then
txtCOM.Setfocus
txtCOM.value = dbfCOM.value
MsgBox "cboSCI: "& cboSCI & " " & "dbfSCI: " & dbfSCI & " " & "dbfCOM: " & dbfCOM
End if
objRecords.movenext
wend
End Sub

When I run this, I can select the scientific name from the combobox list, which works OK, but the Common name textbox is not populated.

Also, in the combobox controls, I have the "Values" field set to "SCIENTIFIC" and the "Text" field set to "COMMON", ie. these are the fields in the dbf file "NameTest.dbf" . Also, I find that if I reverse this and set the "Values" field to "COMMON", and the "Text" field to "SCIENTIFIC", I can selct the common name from the drop-down menu, and the common name textbox is also populated with the common name! (The message box also shows)

Anyone got any ideas??

[IvanKautter]

I might try to confirm that your code can:

1. Capture and assign the value of the combo box to a variable.
2. Connect to, query, and return values from both fields in the dbf
3. Assign a value to the text control

In my code I have used the Find method of the recordset object in place of your While loop.

I think While...Wend is generally not recommended in VBScript.  I would substitute a Do...Loop if you intended to retain that portion of the code.

In general I would suggest when you get a value from an object such as a combo box or a record from a recordset that you assign that to a variable. 

Also I would recommend when you get or set of value of a control that you do it more explicitly rather than from a referenced object (e.g. objEditForm.Pages("Grassnames").Controls("CboGrSci entific").Value instead of cboSCI.Value).  I can't say why this is a better approach but I think I have experienced some problems getting and setting values of members of an object from a passed reference to an object.

Also I am not sure why you are using Call objRecords.Open().  That may certainly work but I think in general when you use arguments in parenthesis you are asking the method to return a value or object that you will assign to variable or object.  I might try this instead:

objRecords.open "P:\Work\SSD\Users\Huxtac\ArcPad Stuff\Hunter Rapid Survey\GrassNameTest.dbf",1


I'd also recommend that you close your recordset and set the object reference to it to Nothing:

objRecords.Close
Set objRecords = Nothing


Additionally, I am a bit confused as to why all this code is necessary.  You have a combo box with the scientific name as value and common name as text.  Can't you merely get the text from the combo box of the currently selected item and pass that as the value for the text box you are trying to populate?  ArcPad is already linked in essence to that dbf table by your populating the combo box with its values.  Why requery the values from the dbf when ArcPad already has access to those values and the selected text in the combo box is what you want in the text box?

[CharlesHuxtable]

Ivan, thanks for your help. I'm not a programmer and have very limited exerience with Visual Basic. I'm all for making things as simple as possible.

In your last paragraph, you suggested I might be able to simply "get the text from the combo box of the currently selected item and pass that as the value for the text box I am trying to populate".

Do you know how I could do this?

[IvanKautter]

Use:

Dim strCOM
strCOM = cboSCI.Text
txtCOM.Value = strCOM


in place of everything from

Set objRecords = Application.CreateAppObject("Recordset")

to

Wend

In your script.