topic Lookup value in DBF file in ArcPad Questions

Lookup value in DBF file

OisinKelly — Wed, 28 Apr 2010 15:26:32 GMT

Hi folks

I want to update the value of a textbox (i.e. EDIT field) called "txtCOMMON" based on the value of a Combobox called "cboSCIENTIFIC".

The Combobox is populated by a dbf file called "ListSpecies.dbf", with two fields, [SCIENTIFIC] amd [COMMON].

I want to attach a function to the onselchange event of "cboSCIENTIFIC" which looks up the corresponding COMMON name in the dbf file, i.e. return [COMMON] where [SCIENTIFIC]=cboScientific.value.

I am making no headway and would appreciate assistance.

Regards

Oisink

Lookup value in DBF file

JoshMoloney — Wed, 28 Apr 2010 22:57:35 GMT

Hi folks

I want to update the value of a textbox (i.e. EDIT field) called "txtCOMMON" based on the value of a Combobox called "cboSCIENTIFIC".

The Combobox is populated by a dbf file called "ListSpecies.dbf", with two fields, [SCIENTIFIC] amd [COMMON].

I want to attach a function to the onselchange event of "cboSCIENTIFIC" which looks up the corresponding COMMON name in the dbf file, i.e. return [COMMON] where [SCIENTIFIC]=cboScientific.value.

I am making no headway and would appreciate assistance.

Regards

Oisink

Try something like this:
set objFORM = layer.Forms("EDITFORM").controls
set objRecords = Application.CreateAppObject("Recordset")
call objRecords.open("c:\ListSpecies.dbf",1)
objrecords.movefirst
While not objrecords.EOF
if objrecords.fields.item("SCIENTIFIC").value = cboSCIENTIFIC.value then
                                        objForm("txtCOMMON").value = objrecords.fields.item("COMMON").value
end if
                 objWriteRecords.movenext
Wend

you may need objFORM("txtCOMMON").setfocus in the if then else statement and use objFORM("txtCOMMON").text = "......

Good luck bro.

Lookup value in DBF file

RobChouinard — Sat, 11 Dec 2021 14:12:52 GMT

This should get you pointed in the right direction. Search "recordset" in the help index for more help.

Sub ShowCommonName()

    Dim objControl, objPage, objRecords, ScientificName Bookmark
    Set objPage = ThisEvent.Object.Parent

    'Get cboScientific value
    ScientificName = ThisEvent.Object.Value

    'Open the dbf
    Set objRecords = Application.CreateAppObject("Recordset")
    Call objRecords.open("C:\ListSpecies.dbf",1)

    'Find the matching item
    BookMark = rsAI.Find("[SCIENTIFIC]=""" & ScientificName & """")
 
 
    'Check for a match
    If BookMark = 0 Then
        'No match, return the default value
        'TODO: is there a valid default value
        objPage.Controls("txtCOMMON").Value = ""
        Exit Sub
    Else
 objPage.Controls("txtCOMMON").Value = objRecords.Fields("COMMON").Value
    End If

End Sub

Lookup value in DBF file

OisinKelly — Thu, 29 Apr 2010 15:03:50 GMT

Try something like this:
set objFORM = layer.Forms("EDITFORM").controls
set objRecords = Application.CreateAppObject("Recordset")
call objRecords.open("c:\ListSpecies.dbf",1)
objrecords.movefirst
While not objrecords.EOF
if objrecords.fields.item("SCIENTIFIC").value = cboSCIENTIFIC.value then
                                        objForm("txtCOMMON").value = objrecords.fields.item("COMMON").value
end if
                 objWriteRecords.movenext
Wend

you may need objFORM("txtCOMMON").setfocus in the if then else statement and use objFORM("txtCOMMON").text = "......

Good luck bro.

Hi Joshua

Thanks for the help. Very close now. I have the code below (field names slightly changed but you will recognise it):

Sub UpdateCommonName

Dim cboSCI, txtCOM, objRecords, dbfSCI, dbfCOM
Set cboSCI = Survey_Controls("txtSCISPECIE")
Set txtCOM = Survey_Controls("txtCOMSPECIE")
Set objRecords = Application.CreateAppObject("Recordset")

Call objRecords.open("C:\Survey\ArcPad\_TMS Inventory\New\ListSpecies.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

Note, I don't need to use variable objForm, because I have a variable objEditForm and its pages, such as "Survey_Controls" already set. I have added a MsgBox line to debug and have found 2 problems.

Firstly, the cboSCI.Value, as returned by the IF statement, is the value that was in the combobox before I selected a new value! So, is there a way to force an update or save so it returns the right value, or alternatively is there some other event property (like afterupdate) which would use the updated combobox value?

Secondly, the function is not writing the result to the txtCOM field, even with the setfocus line.

Any ideas?

Where is Help, Programming Reference. Object Model

OisinKelly — Thu, 29 Apr 2010 15:08:51 GMT

Hi citrusmosquito

I'm interested in your comment:
[INDENT][/INDENT]"Search "recordset" in the help index for more help"

I have only ArcPad 7.1. No ArcBuilder/ArcStudio etc. I've never been able to find any help or porgramming reference relating to the API, not even an Object Model. I'm probably being dumb, but can you point me in the right direction?

Thanks

Oisin

Doh!!!

OisinKelly — Thu, 29 Apr 2010 18:32:08 GMT

Joshua (and citrusmosquito)

Many thanks for your help. It is now working. My code in reply to Joshua was wrong in that I referred to the wrong page of my form. It did not throw an error because for another reason I have all the fields from the whle form hidden on the page as readonly text fields.

Once again many thanks for your assistance.

Oisin

Lookup value in DBF file

RobChouinard — Thu, 29 Apr 2010 19:23:30 GMT

Hi citrusmosquito

I'm interested in your comment:
[INDENT][/INDENT]"Search "recordset" in the help index for more help"

I have only ArcPad 7.1. No ArcBuilder/ArcStudio etc. I've never been able to find any help or porgramming reference relating to the API, not even an Object Model. I'm probably being dumb, but can you point me in the right direction?

Thanks

Oisin

In ArcPad Studio 8 Help. It should also be in 7.1 Studio help. Sorry I just figured you where using ArcPad Studio. I was pressed on time to put in a reply this morning.