Trouble with appending records 1

[VickyC]

hello gurus

I'm sure I'm missing something simple, but I just can't see my error. I have a form (frmBinMonth) bound to (qryBinMem) that gets values from (tblBinMem). The form has a combobox (cboBin), a multi-select listbox (lstMembers), and a button (cmdAdd). When I click cmdAdd, I want to append records based on the combobox's value and the listbox's selections. But, I only want to add the records that have no matching records in tblBinMem already. My code always appends records, even if they already have matches. Any ideas? I'm suspecting the FindFirst criteria, but I'm not sure.

'if MemID/BinNum combo NOT already in tblBinMem, append it

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblBinMem", dbOpenDynaset, dbAppendOnly)

For Each varItem In Me!lstMembers.ItemsSelected

    With rs
                
       .FindFirst "BinID = " & Me!cboBin.Column(0) & " AND MemID = " & Me!lstMembers.Column(0, varItem)
                                        
       If .NoMatch Then
          .AddNew
          !MemID = Me!lstMembers.Column(0, varItem)
          !BinID = Me!cboBin.Column(0)
          .Update
       Else
           MsgBox "Record already exists."
       End If

    End With

Next varItem

Thanks for any help
Vicky C

 

[TheAceMan1]

How are ya VickyC . . .

At 1st glance you code seems fine! Since all is dependent on your criteria ... just what (in the table) are the [purple]datatypes[/purple] of [blue]BinID[/blue] & [blue]MemID[/blue]?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[VickyC]

hi TheAceMan1

Both BinID and MemID are long integers.

Vicky C

 

[PHV]

I'm not sure you may do a FindFirst on a Recordset open with dbAppendOnly

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

PHV - YES, that was it! Thank you so much.

Vicky C