put stored variable in form field

CharlottePeters · ‎04-19-2010

Hi, I have a form with 4 pages. On page 2, I have a combobox that is tied to an external DBF table with 3 fields (code, obs, and test). When the user selects an item from the list, the DBF table's code value is stored in the combobox. Now I have a textbox on page 4 that I want to be autopopulated with a value based on the combobox's selected item. So if a single record in the DBF table is CP, CarPatrol, Test1 then when the user selects CP (code field) for the combobox, I want Test1 (test field) to autopopulate in the textbox on page 4.

I am able to open the DBF table, find the record based on the combobox entry and store the "CP" and "Test1" values into variables and display them in a messagebox, but I can't get "Test1" to show up in the textbox.

The following code is what I have so far. It is being called from the combobox's OnSelChanged event.
Any suggestions on what I'm missing?
Thanks,
Charlotte

Option Explicit

'autopopulate a second field based on entry in first field
Sub AutoPop
Dim objDBF, strCode, strTest, i , strObs, strLoc
 strObs= Layers("camera_stn.shp").Forms("EDITFORM").Pages("PAGE2").Controls("obs").value    'user selected combo box 
 strLoc= Map.Layers("camera_stn.shp").Forms("EDITFORM").Pages("PAGE4").Controls("loc_desc").value    'autopopulate this field based on combo box selection

Set objDBF = Application.CreateAppObject("recordset")   
 objDBF.Open "L:\GPS\Trimble_base\luObsTest.dbf", 1    'open the external DBF table used for combobox list
 objDBF.MoveFirst     'go to first record in DBF table
For i=0 to objDBF.RecordCount-1
 strCode = objDBF.Fields("CODE").Value     'store value from CODE field
 strTest= objDBF.Fields("TEST").Value       'store value from TEST field
 If strObs=strCode then                   'compare value user selected from combobox list to the 1st CODE value from the DBF table
  strLoc=strTest              'if 1st CODE value = user selection then put the TEST value in the textbox on page 4...
 Else  
  objDBF.MoveNext                   '...otherwise go to the next record in the DBF table
 End If

Next
 msgbox ("CODE = " & strCode & vbnewline & "TEST = " & strLoc)   'message box returns DBF values being stored as variables
   
objDBF.Close  

Set objDBF = Nothing 

End Sub

EricHajek · ‎04-21-2010

The problem is you're only storing the VALUE of the text box to strLoc. So if it's blank, (because the user hasn't gotten to that page yet) you're setting the value of strLoc to "". Then down in the for loop you're setting the value of strLoc to strTest, but there isn't any code setting the value of the text box on page 4 to that value.

I'd replace:
strLoc=strTest
with
Map.Layers("camera_stn.shp").Forms("EDITFORM").Pages("PAGE4").Controls("loc_desc").value = strTest

and see if works. Hope this helps!

CharlottePeters · ‎04-21-2010

Thanks for the suggestion. I tried the code change, but it didn't quite work. As a test, I put a msgbox outside the loop to catch the value that is suppose to populate the textbox and it did return the correct value, but it didn't show up on the form. I don't have any 'read-only' or other restrictions on either of the controls. I ended up taking the code away from the combobox and put it on the 'onsetfocus' event of the PAGE4 textbox. So now the user selects a value for the combobox, goes to PAGE4, taps the textbox and the value associated with the combobox is entered. The only drawback is that the user has to go back to the textbox everytime they update the combobox, but it works otherwise. The code below works for now.
Thanks, Charlotte

Option Explicit

'autopopulate a field based on entry in another field
Sub AutoPop
Dim objDBF, strCode, strTest, i , strObs
 strObs= Layers("camera_stn.shp").Forms("EDITFORM").Pages("PAGE2").Controls("obs").value 'get value user selected in obs field
Set objDBF = Application.CreateAppObject("recordset")   
 objDBF.Open "L:\GPS\Trimble_base\luObs.dbf", 1  'open the external DBF table
 
 objDBF.MoveFirst 
For i=0 to objDBF.RecordCount-1   'loop through DBF list until end of list 

 If strObs=objDBF.Fields("CODE").Value then 'compare selected obs value form with 1st CODE value from table
  strTest = objDBF.Fields("TEST").Value 'if form value = CODE value then store associated TEST value in variable
  ThisEvent.Object.value=strTest   'enter stored variable into text box on form
 Else  
 End If
 objDBF.MoveNext  'goto next record in DBF table
Next
 objDBF.Close    'close DBF table

Set objDBF = Nothing 

End Sub

CharlottePeters · ‎04-22-2010

Just to follow up. I was working on another form with a similar problem to the one I initially posted where I can't get a stored variable to "stick" in another field. I think page activation may have something to do with it. I notice that if I assign a stored variable to a control on the first page, which by default is activated when the form loads, then I can see the value in the control. However, if I assign the stored variable to a control on another (not active) page then the control remains blank. I found that the following sequence works if I need to assign a stored variable on a page other then the currently active page.

run the code I need to get a value stored in a variable

make the page that contains the control I need active using code something like:

Application.Map.Layers("camera_stn.shp").Forms("EDITFORM").Pages("PAGE4").Activate

enter the stored variable into the desired control using code something like:

Application.map.Layers("camera_stn.shp").Forms("EDITFORM").pages("PAGE4").controls("obs").value=StoredVariable

make the first page active again so user can start data entry

The process of activating PAGE4, entering the value and reactivating PAGE1 happens really fast so the user isn't left wondering why the pages are out of order.
I should also note that I am modifying the forms/code ArcPad8 Studio and using the files in ArcPad 7.1.
Charlotte