Record Insert via UnBound Controls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with several unbound textbozes and an unbound multiselect
listbox. The user enters data into the texboxes and selects one or more
items from teh list. That part works fine. What's happeining now is that when
I close the form the table gets a blank row inserted. I ran through the code
line by line and cannot find where the blank row is getting inserted. Any
ideas would be greatly appreciated. here is my code:

Private Sub cmdProcess_Click()
' Comments : Update the INcidnts table based on the selections in
' the unbound multiselect listbox lstIncidents.
' Newly selected rows will be added to the table, newly
cleared
' rows will be deleted.
' Parameters: None
' Modified : 07/11/06 RN
'
' --------------------------------------------------
' Populate the Incidents table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("Reports", dbOpenDynaset)
With Me!lstIncidents
' Loop through all rows in the Listbox
For iItem = 0 To lstIncidents.ListCount - 1
lngCondition = lstIncidents.Column(0, iItem)
' Determine whether this CISID-INCIDENTID combination is currently
' in the table
rs.FindFirst "[CISID] = " & Me.CISID & " AND " _
& "[INCIDENTID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If lstIncidents.Selected(iItem) Then
' add it
rs.AddNew
rs!CISID = Me.CISID
rs!INCIDENTID = lngCondition
rs!FName = Me.FName
rs!LName = Me.LName
rs!DATE = Me.DATE
rs!TIME = Me.TIME
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not lstIncidents.Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.Refresh
Me.CISID = Null
Me.INCIDENTID = Null
Me.FName = Null
Me.LName = Null
Me.DATE = Null
Me.TIME = Null

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub
 
orRichard,

The controls are unbound, but does the form have a recordsource property
set? Also, do you have any code in the form load, open, or close events that
are of any consequence?

Barry
 
Barry,

the form is connected to a recordsource even though the controls are all
unbound and there is no other code except for what i included in my post
which is tied to a command button.

Barry Gilbert said:
orRichard,

The controls are unbound, but does the form have a recordsource property
set? Also, do you have any code in the form load, open, or close events that
are of any consequence?

Barry

Richard said:
I have a form with several unbound textbozes and an unbound multiselect
listbox. The user enters data into the texboxes and selects one or more
items from teh list. That part works fine. What's happeining now is that when
I close the form the table gets a blank row inserted. I ran through the code
line by line and cannot find where the blank row is getting inserted. Any
ideas would be greatly appreciated. here is my code:

Private Sub cmdProcess_Click()
' Comments : Update the INcidnts table based on the selections in
' the unbound multiselect listbox lstIncidents.
' Newly selected rows will be added to the table, newly
cleared
' rows will be deleted.
' Parameters: None
' Modified : 07/11/06 RN
'
' --------------------------------------------------
' Populate the Incidents table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("Reports", dbOpenDynaset)
With Me!lstIncidents
' Loop through all rows in the Listbox
For iItem = 0 To lstIncidents.ListCount - 1
lngCondition = lstIncidents.Column(0, iItem)
' Determine whether this CISID-INCIDENTID combination is currently
' in the table
rs.FindFirst "[CISID] = " & Me.CISID & " AND " _
& "[INCIDENTID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If lstIncidents.Selected(iItem) Then
' add it
rs.AddNew
rs!CISID = Me.CISID
rs!INCIDENTID = lngCondition
rs!FName = Me.FName
rs!LName = Me.LName
rs!DATE = Me.DATE
rs!TIME = Me.TIME
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not lstIncidents.Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.Refresh
Me.CISID = Null
Me.INCIDENTID = Null
Me.FName = Null
Me.LName = Null
Me.DATE = Null
Me.TIME = Null

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub
Click to expand...
Click to expand...
 
If the controls are all unbound, there's probably no reason to have a
recordsource set for the form. Try removing this and see if you get the same
results.

Barry

Richard said:
Barry,

the form is connected to a recordsource even though the controls are all
unbound and there is no other code except for what i included in my post
which is tied to a command button.

Barry Gilbert said:
orRichard,

The controls are unbound, but does the form have a recordsource property
set? Also, do you have any code in the form load, open, or close events that
are of any consequence?

Barry

Richard said:
I have a form with several unbound textbozes and an unbound multiselect
listbox. The user enters data into the texboxes and selects one or more
items from teh list. That part works fine. What's happeining now is that when
I close the form the table gets a blank row inserted. I ran through the code
line by line and cannot find where the blank row is getting inserted. Any
ideas would be greatly appreciated. here is my code:

Private Sub cmdProcess_Click()
' Comments : Update the INcidnts table based on the selections in
' the unbound multiselect listbox lstIncidents.
' Newly selected rows will be added to the table, newly
cleared
' rows will be deleted.
' Parameters: None
' Modified : 07/11/06 RN
'
' --------------------------------------------------
' Populate the Incidents table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("Reports", dbOpenDynaset)
With Me!lstIncidents
' Loop through all rows in the Listbox
For iItem = 0 To lstIncidents.ListCount - 1
lngCondition = lstIncidents.Column(0, iItem)
' Determine whether this CISID-INCIDENTID combination is currently
' in the table
rs.FindFirst "[CISID] = " & Me.CISID & " AND " _
& "[INCIDENTID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If lstIncidents.Selected(iItem) Then
' add it
rs.AddNew
rs!CISID = Me.CISID
rs!INCIDENTID = lngCondition
rs!FName = Me.FName
rs!LName = Me.LName
rs!DATE = Me.DATE
rs!TIME = Me.TIME
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not lstIncidents.Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.Refresh
Me.CISID = Null
Me.INCIDENTID = Null
Me.FName = Null
Me.LName = Null
Me.DATE = Null
Me.TIME = Null

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top