Writing to dbf file and clearing form fields

CharlesHuxtable · ‎11-11-2010

Hi, I'm developing a subform for vegetation data collection in which, following completion of the subform, I want to write to a dbf file and then clear all fields in the subform in preparation for entering another record. (I have another sub which I use for the last record entered which writes to dbf and closes the subform). I can get the data written to the dbf file OK, and can enter subsequent records OK which again writes to the dbf file, however it won't clear the form.

Basically, I the subform is called "Plot Disturbance" into which data is entered in fields via combo boxes a text box. I then have a button which, when clicked, calls up a Sub called "WritetoSubFormPlotDist". This reads the subform field contents into dbf fields called "OID" (a reference to the site number), "Disturbance Type", "Severity", Age" and "Observed evidence". Because there may be a number of disturbances (records) at each site, I then want to enter the next disturbance type using the same subform and it would be very useful if the form cleared between record exports.

The text I am using is as follows:

Sub WritetoSubFormPlotDist

   Set page = editform.pages.item("PgPlotDist")
   Dim myRS
   Set myRS = Application.CreateAppObject("RecordSet")

   'open the Plot Disturbance DBF-file
   myRS.Open "P:\Work\SSD\Users\Huxtac\ArcPad Stuff\Veg_16\Plot_DistOut.dbf",2
   myRS.AddNew 'adds a new record
   myRS.Fields("OID").Value = layer.Forms("EDITFORM").Pages("General1").Controls("OID")
   myRS.Fields("DIST_TYPE").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("CboDistType")
   myRS.Fields("SEVERITY").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("CboSevCode")
   myRS.Fields("AGE").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("CboAgeCode")
   myRS.Fields("OBS_EVID").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("EdtObsEvid")
   myRS.Update 'saves information to the recordset Land Use DBF-file
   myRS.Close
   Set myRS = Nothing
   Set page = editform.pages.item("PgPlotDist").Clear
End Sub

Any ideas - I've spent several days on this and it's driving me crazy! I thought that "MyRS.Clear" might work, but no!!

Charles

RolfBroch · ‎11-12-2010

Hi Charles, the page object does not have a clear request. You have to clear your controls manually by for example calling a sub like the one below by replacing the statement

Set page = editform.pages.item("PgPlotDist").Clear

with

Call ClearControls(page)

Sub ClearControls(byRef pPage)
Dim pControl
For each pControl in pPage.Controls
    If pControl.Type = "COMBOBOX" Or pControl.Type = "LISTBOX" Then
      pControl.Clear
    ElseIf pControl.Type = "EDIT" Then
      pControl.Value = ""
    End If
Next
Set pControl = Nothing
End Sub

Rolf

PS, I did not check to see if the code above works (but is should)

CharlesHuxtable · ‎11-14-2010

Hi Rolf, thanks for your help with this, however I tried what you suggested but couldn't get it to work - I may be doing something wrong. Just to clarify:
1. Do I take your meaning that I need to call a separate Sub to clear the form, rather than a sub operating within the Sub "WritetoSubFormPlotDist", ie I would add a button in my form which,when clicked, would call the sub to clear the form? If so, should the Sub be called "ClearControls(page)" or "ClearControls(byRefpPage)"?
2. Should I use the "pPage" or substitute the name of my form page, ie. "PgPlotDist"?

I'm not experienced with VB, so please excuse my poor understanding!

RolfBroch · ‎11-18-2010

Hi again. Sorry for not being clearer. You can add the following code

Dim pControl
For each pControl in page.Controls
If pControl.Type = "COMBOBOX" Or pControl.Type = "LISTBOX" Then
pControl.Clear
ElseIf pControl.Type = "EDIT" Then
pControl.Value = ""
End If
Next
Set pControl = Nothing

into your own routine instead of your statement

Set page = editform.pages.item("PgPlotDist").Clear

Using a subroutine for this was only if you needed to do this several places in your code.

Rolf

CharlesHuxtable · ‎11-22-2010

Hi Rolf, thanks for your help. I got it to work with the code you suggested. The only problem was that, after clearing the form, the drop down lists did not appear when I tried to enter the next record. Thus it appeared that the "clear" command somehow removed the "combobox" function from each field in the form. The code is as follows:

Sub WritetoSubFormPlotDist

Set page = editform.pages.item("PgPlotDist")

'open the Plot Disturbance DBF-file

'Dim myRS
Set myRS = Application.CreateAppObject("RecordSet")

myRS.Open "P:\Work\SSD\Users\Huxtac\ArcPad Stuff\Veg_16\Plot_DistOut.dbf",2

myRS.AddNew 'adds a new record

myRS.Fields("OID").Value = layer.Forms("EDITFORM").Pages("General1").Controls("OID")
myRS.Fields("DIST_TYPE").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("CboDistType")
myRS.Fields("SEVERITY").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("CboSevCode")
myRS.Fields("AGE").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("CboAgeCode")
myRS.Fields("OBS_EVID").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("EdtObsEvid")
myRS.Update 'saves information to the recordset Land Use DBF-file
myRS.Close
Set myRS = Nothing
Dim pControl
For each pControl in layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls
If pControl.Type = "COMBOBOX" Or pControl.Type = "LISTBOX" Then
       pControl.Clear
      ElseIf pControl.Type = "EDIT" Then
       pControl.Value = ""
     End If
    Next
Set pControl = Nothing
End Sub

Is there a way to clear the form without removing the combobox drop-down lists?
Charles

CharlesHuxtable · ‎11-22-2010

Hi Rolf,
Thanks again for your help. I got it to work with the code below as you suggested. I was able to fill out the form and click an "Add New" button, which calls the subroutine "WritetoSubFormPlotDist", which writes the data to a dbf file and clears the form for the next entry, which it did successfully. The only problem was, when I tried to enter the next record, the drop down lists had disappeaerd from the comboboxes. Any ideas?? Charles

Sub WritetoSubFormPlotDist

Set page = editform.pages.item("PgPlotDist")

'open the Plot Disturbance DBF-file

'Dim myRS
Set myRS = Application.CreateAppObject("RecordSet")

myRS.Open "P:\Work\SSD\Users\Huxtac\ArcPad Stuff\Veg_16\Plot_DistOut.dbf",2

myRS.AddNew 'adds a new record

myRS.Fields("OID").Value = layer.Forms("EDITFORM").Pages("General1").Controls("OID")
myRS.Fields("DIST_TYPE").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("CboDistType")
myRS.Fields("SEVERITY").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("CboSevCode")
myRS.Fields("AGE").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("CboAgeCode")
myRS.Fields("OBS_EVID").Value = layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls("EdtObsEvid")
myRS.Update 'saves information to the recordset Land Use DBF-file
myRS.Close
Set myRS = Nothing
Dim pControl
For each pControl in layer.Forms("FrmPlotDist").Pages("PgPlotDist").Controls
   If pControl.Type = "COMBOBOX" Or pControl.Type = "LISTBOX" Then
        pControl.Clear
       ElseIf pControl.Type = "EDIT" Then
        pControl.Value = ""
      End If
    Next
     Set pControl = Nothing
End Sub

RolfBroch · ‎11-25-2010

Clearing a combobox will remove the listvalues. If you only want to "clear" the entry, you have to write
pControl.ListIndex = -1 instead of the pControl.Clear statement

Rolf

CharlesHuxtable · ‎11-30-2010

Thanks Rolf - all working now
Charles