Lookup value in DBF file

[OisinKelly]

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

[JoshMoloney]

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.

[RobChouinard]

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

[OisinKelly]

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?

[OisinKelly]

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

[OisinKelly]

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

[RobChouinard]

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.