Deleting and adding from a list

[romio]

Deleting and adding from a table

I have a form that has a list; the list is retrieving some names from a table.

e.g

Table name is “ITname”
and I have a filed named “name” with the following: john, peter, mike, rock.

On my form under my list I have a delete button, I want to the button to delete the selected record from the list, how can I do that?

Adding, I also have a textbox and a button which will add a new IT which is inserted in the textbox, how can I do that?

Any help will be appreciated.

Thanks.

 

[ejstefl]

What kind of control are you using on the form? Combox, textbox, listbox, etc. Also, is it a bound control?

 

[romio]

What kind of control are you using on the form? Combox, textbox, listbox, etc. Also, is it a bound control?

as for deleting the record it should be the selected one from the list, as for entering the new record i use a textbox(unbound).

 

[WayneRyan]

romio,

After the code that Adds/Deletes items from a ListBox, just do:

Me.YourListBox.Requery

Then it should be in synch with your table.

Wayne

 

[ejstefl]

You basically have two choices - you can write code on your form to add and delete records, or you can just open a bound form to handle adds/deletes. Either one will work, its probably easier to use the bound form method.

 

[romio]

You basically have two choices - you can write code on your form to add and delete records, or you can just open a bound form to handle adds/deletes. Either one will work, its probably easier to use the bound form method.

Thanks ejstefl, I already know that, i would prefer the first option which is write my own code, though i need some help so i can start with.

 

[romio]

I have added this code for adding my a new IT from a textbox:

Private Sub Command26_Click()
On Error GoTo Err_Command26_Click

Dim addme As String

addme = (txtname.Text)

strSql = "INSERT INTO ITList([ITName]) " & "VALUES ('" & addme & "')"
                 
    

Exit_Command26_Click:
    Exit Sub

Err_Command26_Click:
    MsgBox Err.Description
    Resume Exit_Command26_Click
    
End Sub

But I get an error saying:
you cant reference a property or method for a control unless the control has the focus....!!!!!! I dont understand that

 

[ejstefl]

Try this code to add:

DoCmd.SetWarnings = False

addme = Me.txtname

strsql = "INSERT INTO ITList([ITName]) " & "VALUES ('" & addme & "')"

DoCmd.RunSQL strsql

Me.List0.Requery

Me.txtname = Null

DoCmd.SetWarnings = True

Note: Replace "List0" with the name of your listbox.

 

[romio]

Thanks , I have found my way out:

this code for the new IT:

Private Sub Command26_Click()
On Error GoTo Err_Command26_Click

Dim addme As String
Dim StrSQL As String
addme = txtname
DoCmd.RunSQL "INSERT INTO ITList ([ITName]) VALUES ('" & addme & "')"
Me.Refresh

Exit_Command26_Click:
    Exit Sub

Err_Command26_Click:
    MsgBox Err.Description
    Resume Exit_Command26_Click
    
End Sub

And this code for deleting the IT from a list:

Private Sub Command25_Click()

Dim StrSQL As String
Dim RecID As Integer

If MsgBox("Record selected, '" & Me.List35.Column(1) & "' will be deleted. Continue?", vbYesNo, "Confirmation") = vbYes Then
RecID = Me.List35.Column(0)
StrSQL = "Delete ITList.RecordID, ITList.ITName FROM ITList WHERE ITList.RecordID= " & RecID & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
Me.List35.Requery
End If

Me.txtname = ""
Me.txtname.SetFocus
    
End Sub