My unbound data entry form seems to create blank records.

[Zorkmid]

Hi there. I have build a form for data entry. It works just fine, but when I check the table to see what is new there are always lots of blank records interspersed beterrn the correctly filled ones. Im pretty sure that it isnt happening when I click the button on the splash page that sends you to the data entry form.

Here is the code for the button that adds the record.

Private Sub rmAddIncident_Click()
   Dim err As Integer
   Dim cnn1 As ADODB.Connection
   Dim Risk_Data As ADODB.Recordset
   Dim strCnn As String
'Check that all fields are filled in
rmENCON.SetFocus
If rmENCON.Text = "" Then
err = err + 1
MsgBox "Please fill in the ENCON number." & err
End If
rmName.SetFocus
If rmName.Text = "" Then
err = err + 1
MsgBox "Please fill in the name of the person affected by the incident."
End If
rmDate.SetFocus
If rmDate.Text = "" Then
err = err + 1
MsgBox "Please fill in the date the incident occured."
End If
rmInjury.SetFocus
If rmInjury.Text = "" Then
err = err + 1
    MsgBox "Please specify the degree of injury."
End If
rmComments.SetFocus
If rmComments.Text = "" Then
err = err + 1
MsgBox "Please briefly describe the incident in the comments box."
End If
rmResolved.SetFocus
If rmResolved.Text = "" Then
err = err + 1
MsgBox "Please briefly summarize the follow-up."
End If
rmVictimstatus.SetFocus
If rmVictimstatus.Text = "" Then
err = err + 1
MsgBox "Please fill in the Victim status."
End If
rmLocation.SetFocus
If rmLocation.Text = "" Then
err = err + 1
MsgBox "Please fill in the location the incident took place."
End If
rmType.SetFocus
If rmType.Text = "" Then
err = err + 1
MsgBox "Please incicate the type of incident that occured."
End If
rmSpecificType.SetFocus
If rmSpecificType.Text = "" Then
err = err + 1
MsgBox "Please incicate the specific type of incident that occured."
End If
'if no errors insert data
If err < 1 Then
  
  ' Open a connection.
    Set cnn1 = New ADODB.Connection
   mydb = "G:\Admin\Ceo\Human Resources\RISKMGMT\Inhouse data\Risk Database\Inhouse Data.mdb"
   strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
    cnn1.Open strCnn
 
' Open Risk_Data table.
    Set Risk_Data = New ADODB.Recordset
    Risk_Data.CursorType = adOpenKeyset
    Risk_Data.LockType = adLockOptimistic
    Risk_Data.Open "Risk_Data", cnn1, , , adCmdTable

'get the new record data
      Risk_Data.AddNew
        Risk_Data!ENCON = rmENCON
        Risk_Data!Name = rmName
        Risk_Data!Date = rmDate
        Risk_Data!Injury = rmInjury
        Risk_Data![Incident Comments] = rmComments
        Risk_Data![Medication Risk] = Med_Risk
        Risk_Data![Medication/Equipment] = rmMedication
        Risk_Data![Victim Status] = rmVictimstatus
        Risk_Data!Location = rmLocation
        Risk_Data!Type = rmType
        Risk_Data![Specific Type] = rmSpecificType
        Risk_Data![Phys/Empl/Pt Involved] = rmPersonInvolved
        Risk_Data![Follow-up Summary] = rmResolved
        Risk_Data![Resolved or Pending] = ResolvedOrPending
        Risk_Data![Date Resolved] = rmDateResolved
      Risk_Data.Update
'Show the newly added data.
   MsgBox "Incident #: " & Risk_Data!ENCON & " has been successfully added"
'close connections to DB.
  Risk_Data.Close
    cnn1.Close
'clear all objects
Me.rmENCON.Value = ""
Me.rmDate.Value = ""
Me.rmName.Value = ""
Me.rmInjury.Value = ""
Me.rmComments.Value = ""
Me.Med_Risk.Visible = False
Me.rmMedication.Value = ""
Me.rmLocation.Value = ""
Me.rmVictimstatus.Value = ""
Me.rmType.Value = ""
Me.rmSpecificType.Value = ""
Me.rmPersonInvolved.Value = ""
Me.rmResolved.Value = ""
Me.ResolvedOrPending.Value = ""
Me.rmDateResolved.Value = ""
'send back to main page.
 DoCmd.OpenForm "Main_Page"

Else
MsgBox "An Error has occurred, please check and try again"
End If
DoCmd.Close acForm, "Enter_New_Incident"

End Sub

Anyone see anything in there that would be causing this issue. Its not a huge deal, but a pain in the butt to get rid of the blanks.

-Z

 

[rainman89]

instead of doing an error count, why not just exit the sub after something is discovered that it is blank?

so it would be

If rmENCON.Text = "" Then
MsgBox "Please fill in the ENCON number." & err
exit sub
End If

 

[Zorkmid]

Would that clear the form and send the user back to the main page? Thats no good really.

 

[rainman89]

No it wouldnt do that. It would just act like the button was never pressed!

 

[Zorkmid]

I seem to get blank records even when there are no errors in the entry, so I think there must be a different cause to the problem.

 

[missinglinq]

The first question has to be why are you doing a data entry using an unbound form? That really negates a large part of the advantage in using Access.

And why are you using

rmENCON.SetFocus
If rmENCON.Text = "" Then

If you use rmENCON.Value instead of rmENCON.Text you do away with the need to set focus to the control first. .Value doesn't require the textbox to have focus before referencing the data. And since .Value is the Default Property, you can shorten it more still to simply

If rmENCON


The problem I see, which may be causing your troubles, is that you're using

If rmENCON.Text = "" Then

Even changing this to

If rmENCON = "" Then

leaves you with a problem. The first time you click on your rmAddIncident button, any "empty" textboxes are Null! But you're checking for a Zero Length String, not a Null. They're not the same thing! So empty textboxes will not trigger your validation code for those boxes and incomplete records can be saved!

If, when you first open the form, you should happen to click on the button without having filled in any data at all, no validation code would be triggered and the record would be saved with no data, i.e. a "blank record."

After the first time you click the button, the code at he end of the sub sets them all to Zero Length Strings, and the code will work properly from then until you close the data entry form.

There's a couple ways to check this, but the simplest, since you're assigning a ZLS to it after the first record is entered would be to use


If Len(Me.rmENCON & "") = 0 Then

to replace

If rmENCON = "" Then

This will work whether the fields or Null or ZLSs.

There may be other things at work here, but you've got to fix, if you want your validation code to always work, and then see what happens with the blank records.

 

[Zorkmid]

The first question has to be why are you doing a data entry using an unbound form? That really negates a large part of the advantage in using Access.

I have found this is a better way to limit the number of responses a user can give and clean up the data.

The problem I see, which may be causing your troubles, is that you're using

If rmENCON.Text = "" Then

Even changing this to

If rmENCON = "" Then

leaves you with a problem. The first time you click on your rmAddIncident button, any "empty" textboxes are Null! But you're checking for a Zero Length String, not a Null. They're not the same thing! So empty textboxes will not trigger your validation code for those boxes and incomplete records can be saved!

If, when you first open the form, you should happen to click on the button without having filled in any data at all, no validation code would be triggered and the record would be saved with no data, i.e. a "blank record."

I am currently the only person using this database. (dev stage). I know for a fact that incomplete records are not being saved. As the messages that specify which info is missing work fine, and I have checked and those records are not added. What I am getting are COMPLETELY blank records in my table.

After the first time you click the button, the code at he end of the sub sets them all to Zero Length Strings, and the code will work properly from then until you close the data entry form.

The form closes when I click rmAddIncident


I really appreciate your feedback so far, but I am really not experienced enough to understand what you mean in most of your reply

-Z