adding item from popup list to main form multiple times

G

G Stoner

I am using Access 2000. I have a popup window that has a list of
available items, I can select multiple items and place them in my main form
but I am unable to select the same item twice (i.e.-select the item, save it
to main form, open popup window and select same item again). Each item is
checking the table for previous selections to auto check mark a repeat box.

I think my problem is in the table search portion.

I did not originally write this code and am not the greatest
programmer, this is pretty much over my head at this point. I have done
some trial and error but am afraid to change to much for fear of it not
working.

I have placed a sample of the VB code below. If anyone can help I
would appreciate it!!!!!


*********code sample*********************


Private Sub cmdSave_Click()

Dim stritem1 As String
Dim stritem2 As String
Dim stritem3 As String
Dim stritem4 As String
Dim varItem As Variant
Dim rst As New ADODB.Recordset

Dim sqlcheckexist As String
Dim rstcheckexist As Recordset

Dim sqlrepeat As String
Dim rstrepeat As Recordset

rst.Open "tblViolations", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

With Me.lstCod
For Each varItem In .ItemsSelected
stritem1 = .Column(0, varItem)
stritem2 = .Column(2, varItem)
stritem3 = .Column(4, varItem)
stritem4 = .Column(5, varItem)

sqlrepeat = "Select * FROM tblViolations Where ([Item Id] =" &
Chr$(34) & stritem3 & Chr$(34) & " and [EstNumber] =" & Chr$(34) &
Me.txtEstNum & Chr$(34) & " and [DateOfInspection] = #" &
Forms!frmInspection!subfrmInspectionMain!txtDOI & "#);"

Set rstrepeat = CurrentDb.OpenRecordset(sqlrepeat, dbOpenDynaset)
If rstrepeat.RecordCount = 0 Then


sqlrepeat = "Select * FROM tblViolations Where ([Item Id] =" &
Chr$(34) & stritem3 & Chr$(34) & " and [EstNumber] =" & Chr$(34) &
Me.txtEstNum & Chr$(34) & ");"

Set rstrepeat = CurrentDb.OpenRecordset(sqlrepeat, dbOpenDynaset)
If rstrepeat.RecordCount > 0 Then
With rst
.AddNew
![Main Item Id] = stritem1
![Sub Item Id] = stritem2
![Item Id] = stritem3
![Item] = stritem4
![EstNumber] = Me.txtEstNum
![Repeat] = -1
![DateOfInspection] =
Forms!frmInspection!subfrmInspectionMain!txtDOI
.Update
End With
Else
With rst
.AddNew
![Main Item Id] = stritem1
![Sub Item Id] = stritem2
![Item Id] = stritem3
![Item] = stritem4
![EstNumber] = Me.txtEstNum
![DateOfInspection] =
Forms!frmInspection!subfrmInspectionMain!txtDOI
.Update
End With
End If
Else
End If
Me.lstCod.Selected(varItem) = False
Next varItem
End With

***************End code sample***********
 
Top