Subform adds unwanted record

[Bergja]

Hello,

I have a form called IMO that I use to view and edit information from the table "Helpdesk" On this form there is a worklog command button. When this button is pressed the only code executed is:

sbfrmWorklog.Visible = True

the subform has the following code written in it... (feel free to blast my code all you want it helps me learn)

Option Compare Database
'Dimension Variables
Dim strWrkLg As String
Dim WrkLgUp As String
Dim strUpdateWL As String
Dim strWorklog As String
Dim strConcatenate As String

Private Sub cmdUpdateWL_Click()
'Checks to see if the update textbox is Null
If IsNull(txtUpdateWL.Value) Then
    MsgBox "Please enter the data to update"
    Me!txtUpdateWL.SetFocus
End If
'Assigns variable the value of the update text box
strWrkLgUp = txtUpdateWL.Value
strUpdateWL = txtUpdateWL.Value
'Checks to see if the value is the default
'(meaning the record in the Helpdesk table is Null)
'if so, changes the value to the that of the update text box
'otherwise it combines the data with the current date
'the previous updates, and a return space
If Forms!IMO!txtWorklog.Value = "No information has been submitted" Then
    txtWorklog.Value = Time() & " " & Date & " " & strUpdateWL
    strWorklog = txtWorklog.Value
    Forms!IMO!txtWorklog.Value = strWorklog
Else
    strWrkLg = Forms!IMO!txtWorklog.Value
    strConcatenate = strWrkLg & vbCrLf & vbCrLf & Time() & " " & Date & " " & strWrkLgUp
    txtWorklog.Value = strConcatenate
    Forms!IMO!txtWorklog.Value = strConcatenate
End If
'clear update box
txtUpdateWL.Value = Nothing
 
End Sub
 
Private Sub Command5_Click()

Forms!IMO!cmdUpdate.SetFocus
Me.Visible = False

End Sub
 
Private Sub Form_Load()

lblDate.Caption = Time() & " " & Date
 
End Sub

now everything seems to work fin up untill this point. The subform frmWorklog becomes visible. However once you click inside txtUpdateWL a new record is added to the table "Helpdesk" and I cannot for the life of me figure out why. any ideas?

Thanks!

 

[Bergja]

update - the new record is only added the first time you click inside txtUpdateWL while the form is open. after that no new records are added until you close and reopen the form.

 

[gemma-the-husky]

do you have something in your subforms open event (possibly current event) that sets some data (i dont think the code in the caption event would do it)

that might be inadvertently creating an unrequired record that then gets saved automatically

 

[Bergja]

That is what I thought at first as well. I checked and rechecked but I could not find anything that would add data to the table, and I included the code in case I missed something. That subform was never supposed to add or edit the table and I never wrote code to do that. it is only supposed to concatenate the current string in the worklog field of the table with the string that I put into the text box, then place that value in a text box on the form, not in the table. My second thought was that maybe the subform is being opened in add mode somehow, so that it automatically creates a new record when you activate a control on it. I do not know how that is possible or even how to check/fix it. The record has no data in it except the auto-number and a default value that every new record has. here is the code for the actual form itself, if you see something that might be causing it please let me know

Option Compare Database
Option Explicit
'Dimentions variables by type
Dim TicketNumber As String
Dim dbHelpdesk As Database
Dim rst As Recordset
Dim rstClosed As Recordset
Dim TempList As String
Dim strSQL As String
Dim strWrkLg As String
Public FrmChg As Integer
Dim wrkDefault As Workspace
Dim helpdesk As Form
 
Private Sub cmdNewTicket_Click()
DoCmd.OpenForm "Helpdesk", acNormal
End Sub
 
Private Sub Form_Timer()
Me.lstTickets.Requery
End Sub
 
Private Sub cmdWorkLog_Click()
sbfrmWorklog.Visible = True
End Sub
 
Private Sub Form_Load()
Form.TimerInterval = 5000
'Sets the database type variable dbHelpdesk as the currently opened database
'Set dbHelpdesk = wrkDefault.OpenDatabase(, False)
Set dbHelpdesk = CurrentDb
 
'Populates the Listbox with fields from the linked table "Helpdesk"
   strSQL = "SELECT [Ticket Number], [User Name], [Phone Number DSN], [Computer Name], [Building Number], [Room Number], [Date Problem Started], [Type of Issue], [Status] FROM Helpdesk"
   Me.lstTickets.RowSource = strSQL
   Me.lstTickets.Requery
End Sub
Private Sub lstTickets_Click()
If FrmChg = 13 Then rst.Close
FrmChg = 1
Set rst = Nothing
Set wrkDefault = DBEngine.Workspaces(0)
'Opens the linked tables "Helpdesk" and "Closed" as a recordset of type dynaset
Set rst = dbHelpdesk.OpenRecordset("Helpdesk", dbOpenDynaset, dbSeeChanges, dbOptimistic)
Set rstClosed = dbHelpdesk.OpenRecordset("Closed", dbOpenDynaset, dbSeeChanges, dbOptimistic)
'Assigns the "Ticket Number" value from lstTickets to the variable TicketNumber
TicketNumber = lstTickets.Value
 
'Looks for the first record in the linked table "Helpdesk"
'where the Ticket Number Field is the same as the value of
'the TicketNumber variable
rst.FindFirst "[Ticket Number]=" & TicketNumber
'checks the value of the work log to ensure that
'there will not be an invalid null error
If IsNull(rst![WorkLog]) Then
strWrkLg = "No information has been submitted"
Else
strWrkLg = rst![WorkLog]
End If
'Assignes values found to the corresponding controls
txtName.Value = rst![Name]
txtRank.Value = rst![Rank]
txtUsername.Value = rst![User Name]
txtPhone.Value = rst![Phone Number DSN]
txtUserID.Value = rst![User ID Number]
txtComputerName.Value = rst![Computer Name]
txtBldgNumber.Value = rst![Building Number]
txtRmNumber.Value = rst![Room Number]
txtDate.Value = rst![Date Problem Started]
cmbType.Value = rst![Type of Issue]
cmbStatus.Value = rst![Status]
txtDetailRemarks.Value = rst![Remarks]
txtWorklog.Value = strWrkLg
Me!sbfrmWorklog!txtWorklog.Value = strWrkLg

End Sub
Private Sub cmdUpdate_Click()
If Not (cmbStatus.Value = "Resolved") Then
wrkDefault.BeginTrans
    With rst
        
        'Updates current record with changes
        .Edit
            ![Computer Name] = Me!txtComputerName
            ![Building Number] = Me!txtBldgNumber
            ![Room Number] = Me!txtRmNumber
            ![Date Problem Started] = Me!txtDate
            ![Type of Issue] = Me!cmbType
            ![Remarks] = Me!txtDetailRemarks
            ![Rank] = Me!txtRank
            ![Phone Number DSN] = Me!txtPhone
            ![WorkLog] = Me!txtWorklog.Value
            ![Status] = Me!cmbStatus.Value
        .Update
        '.Close
        FrmChg = 13
    End With
wrkDefault.CommitTrans
    
Else
    With rstClosed
        wrkDefault.BeginTrans
        'Updates current record with changes
        .AddNew
            ![Name] = Me!txtName
            ![User Name] = Me!txtUsername
            ![User ID Number] = Me!txtUserID
            ![Computer Name] = Me!txtComputerName
            ![Building Number] = Me!txtBldgNumber
            ![Room Number] = Me!txtRmNumber
            ![Date Problem Started] = Me!txtDate
            ![Type of Issue] = Me!cmbType
            ![Remarks] = Me!txtDetailRemarks
            ![Rank] = Me!txtRank
            ![Phone Number DSN] = Me!txtPhone
            ![WorkLog] = txtWorklog.Value
            ![Status] = cmbStatus.Value
            ![Ticket Number] = rst![Ticket Number]
        .Update
        wrkDefault.CommitTrans
        .Close
        FrmChg = 12
    End With
    
txtName.Value = Nothing
txtRank.Value = Nothing
txtUsername.Value = Nothing
txtPhone.Value = Nothing
txtUserID.Value = Nothing
txtComputerName.Value = Nothing
txtBldgNumber.Value = Nothing
txtRmNumber.Value = Nothing
txtDate.Value = Nothing
cmbType.Value = Nothing
cmbStatus.Value = "Resolved"
txtDetailRemarks.Value = Nothing
txtWorklog.Value = Nothing
Me!sbfrmWorklog!txtWorklog.Value = Nothing
    
    With rst
        .Delete
        .Close
        '.Update
    End With
End If
'DoCmd.Close
'DoCmd.OpenForm "IMO", , , , , acDialog
End Sub
Private Sub cmdClose_Click()
'asks user if they want to quit
If FrmChg > 11 Then
'    rst.Close
    DoCmd.Close
Else
    Dim Msg, Style, Title, Response
        Msg = "Any changes you have made since your last update will be lost.  Are you sure you want to quit?"    ' Define message.
        Style = vbYesNo    ' Define buttons.
        Title = "Helpdesk"    ' Define title.
        Response = MsgBox(Msg, Style, Title)
    
    If Response = vbYes Then    ' User chose Yes.
       DoCmd.Close
    End If
End If
End Sub

as I said before, feel free to blast my code, just keep in mind that I am very new to VBA.

Thanks!