Using VBA to edit record and delete records

I have table (supplier) which simply combines two primary keys from two other tables Contacts and Items, supplier uses the primary key from items as its own primary key. In a certain form there are two list boxes and an option box with 3 radio buttons. The 3 options are, Do nothing, Change to another free item and delete item. The first list box uses a query to show all items that are currently attached to the contact which is in question (as a contact can have more than one different item) from the supplier table.  The other list box runs a similar query that compares the items in the supplier table and those that are in the items table, and displays those that are not in supplier. These are free items and are awaiting to be combined with a contact in the supplier table. When the first and third options are selected the second list box is disabled, when  the second option is selected the second list box is enabled. What I want to happen is the user selects an option from the first list box, then choose an option, should that be option 1, nothing should happen. When option 2 is selected, the itemID from the first list box is replaced by that in the second list box. When the third option is selected the record selected in the first list box should be deleted. These events are triggered by a button click. I am currently using the code shown to achieve this, nothing happens to any records although there is no errors. Any suggestions?
Private Sub btnSaveChange_Click()
Select Case Me!FrameOptions
Case 1
DoCmd.Close
 
Case 2
    If [LstItems].Value <> 0 Then
    
        Dim OldItems As New ADODB.Recordset
    
        OldItems.Open "Supplier", CurrentProject.Connection, _
            CursorType:=adOpenKeyset, LockType:=adLockBatchOptimistic
            
        Do While Not OldItems.EOF
            If OldItems("ItemID") = [LstItems].Value Then
                OldItems("ItemID") = [lstFreeItems].Value
                OldItems.Update
            End If
            OldItems.MoveNext
        Loop
    Else
        MsgBox ("No items are assigned to this contact")
    End If
    DoCmd.Close
Case 3
    Dim Items As New ADODB.Recordset
    
    Items.Open "Supplier", CurrentProject.Connection, _
        CursorType:=adOpenKeyset, LockType:=adLockBatchOptimistic
        
    Do While Not Items.EOF
        If Items("ItemID") = [LstItems].Value Then
            Items.Delete
            Items.Update
        End If
        Items.MoveNext
    Loop
    DoCmd.Close
End Select
End Sub

Open in new window

Dan89Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ee_rleeCommented:
try this
Private Sub btnSaveChange_Click()
Select Case Me!FrameOptions
Case 1
DoCmd.Close
 
Case 2
    If [LstItems].Value <> 0 Then
    
        Dim OldItems As New ADODB.Recordset
    
        OldItems.Open "Supplier", CurrentProject.Connection, _
            CursorType:=adOpenKeyset, LockType:=adLockBatchOptimistic
            
        Do While Not OldItems.EOF
            If OldItems("ItemID") = [LstItems].Value Then
                OldItems.Edit
                OldItems("ItemID") = [lstFreeItems].Value
                OldItems.Update
            End If
            OldItems.MoveNext
        Loop
    Else
        MsgBox ("No items are assigned to this contact")
    End If
    DoCmd.Close
Case 3
    Dim Items As New ADODB.Recordset
    
    Items.Open "Supplier", CurrentProject.Connection, _
        CursorType:=adOpenKeyset, LockType:=adLockBatchOptimistic
        
    Do While Not Items.EOF
        If Items("ItemID") = [LstItems].Value Then
            Items.Delete
        End If
        Items.MoveNext
    Loop
    DoCmd.Close
End Select
End Sub

Open in new window

0
Dan89Author Commented:
ee

That brings up an error message saying "Method or Data member not found" and highlights the line      .
.Edit
?
Cheers
Dan
0
ee_rleeCommented:
just delete rs.edit and try again
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dan89Author Commented:
That doesnt work either, no error message but the original problem remains.

Cheers
Dan
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What are the "Data Value" for the 3 options in your Option Group? Open your form in Design view, the select the option button or checkbox you're using and make sure that the Data Values for those controls correspond to the values you are using in your Select Case.

Are you sure you're referring to the lstItems listbox correctly? Generally you use Me.lstItems.Column(0) to refer to a specific value in the listbox (note that Columns are zero-based, so Column(0) is the first column). The "value" of your listbox may not be the actual value you expect.

Are your listboxes set to multiselect? If so, you'll have to modify this code.

Easiest way to verify this is to set a breakpoint in your code, then "run" it by clicking your command button. To set a breakpoint, place your cursor in the first or second line of your code and press F9 ... then click the button and you should be thrown into the Editor window. Use the F8 key to step through your code. You can also use the Immediate window to examine the values of the various items ... for example to see the value of the first column in your lstItems listbox type this in the Immediate window and press Enter:

?Me.lstItems.Column(0)

Also: I don't see the need to use Recordsets to do this ... straight SQL would be faster, assuming you don't need a "handle" on the data afterwards.

For the Second Case:

CurrentProject.Connection.Execute "UPDATE Supplier SET ItemID=" & Me.lstFreeItems & " WHERE ItemID=" & Me.lstItems

Third Case:

CurrentProject.Connection.Execute "DELETE * FROM Supplier WHERE ItemID =" & Me.LstItems

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dan89Author Commented:
Perfect solution, used the SQL statements in the end as wouldnt work the other way.
Cheers
Dan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.