Combo Box delete first record

I am a beginner and so this might be easy.  Here is my situation:

I have a DelEmpFrm.  On this form I have an ADO connection to an Access dbase, 2 command buttons (Delete, and Cancel), and a Datacombo box.

I want to be able to select a name from the combobox and hit the delete command to delete that record from the database.

How it stands now, I select a name and hit delete.  It deletes the first record not the one I have selected.

This is the code I have the delete button.

Private Sub Command1_Click()
   If MsgBox("Are you sure you wish to delete this name?", vbQuestion + vbYesNo, "Delete Record") = vbYes Then
   Adodc1.Recordset.Delete
   Adodc1.Recordset.UpdateBatch
   Adodc1.Recordset.MoveFirst
   End If
    Unload Me
End Sub

any help would be appreciated.  Thanks!
Todd


tdoveAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
In the General Declarations:
Private mdbConnect As ADODB.Connection

In the Form_Load event:
Set mdbConnect = New ADODB.Connection
With mdbConnect
   .Mode = adModeReadWrite
   .ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
                      "Persist Security Info=False;" & _
                      "Data Source=\\srv1\vol1\groupes\siege\Sungard\VB-BD\XYZ.mdb;"    
   .CursorLocation = adUseClient
   .Open
End With

In your command click event:
mdbConnect.Execute "Delete FROM table1 where field1 = '" & combo1.text & "'"
0
 
jjmartinCommented:
You need to insert code to move to the appropriate record before you do the delete.
0
 
Éric MoreauSenior .Net ConsultantCommented:
The problem is that when the combo scrolls, the recordset doesn't.

What you need to do is to retrieve the name you want to delete from the combo box (combo1.text) and execute a SQL query to delete it like this:
"Delete FROM table1 where field1 = '" & combo1.text & "'"

This query must be executed in a Connection object or the DataEnvironment object.

Once done, you have to refresh your datacontrol to make it sees that data have change.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tdoveAuthor Commented:
emoreau-

I sort of understand what your talking about.  Can you give me an example of the connection object and DataEnviorment object you are talking about.  (Sorry I am still learning.)

Thanks,
Todd
0
 
jjmartinCommented:
Do a find on the record you are looking for:



Dim sSearchString as String

sSearchString = "ColumnName = '" & cmbName.text & "'"

adodc1.recordset.find sSearchString


The find will put you on the appropriate recordset you are trying to delete, provided you don't have duplicate names in the database.
0
 
jjmartinCommented:
Replace ColumnName should be the name of the field that contains the data you are doing a find on.
0
 
Éric MoreauSenior .Net ConsultantCommented:
This code is to establish your connection:
Private mdbConnect As ADODB.Connection
Set mdbConnect = New ADODB.Connection
With mdbConnect
   .Mode = adModeReadWrite
   .ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
                      "Persist Security Info=False;" & _
                      "Data Source=\\srv1\vol1\groupes\siege\Sungard\VB-BD\XYZ.mdb;"  
   .CursorLocation = adUseClient
   .Open
End With

This code is to execute your query:
mdbConnect.Execute "Delete FROM table1 where field1 = '" & combo1.text & "'"
0
 
tdoveAuthor Commented:
jjmartin-

Ok I have added the DIM statement into the General declarations and have modified the button as follows:

Private Sub Command1_Click()
   If MsgBox("Are you sure you wish to delete this name?", vbQuestion + vbYesNo, "Delete Record") = vbYes Then
   sSearchString = "Name = '" & DataCombo1.Text & "'"
   Adodc1.Recordset.Find sSearchString
   Adodc1.Recordset.Delete
   Adodc1.Recordset.UpdateBatch
   'Adodc1.Recordset.MoveFirst
   End If
    Unload Me
End Sub

The database is very simple the table has two fields Name & EmployessID (Primary Key).

When I delete...
The name disappears (yeah!), however it looks like it is replacing the first name in the table with the primary key of the record I just deleted. Si I am deleting the name that I wanted plus who ever is first in the table becomes the EmploeeID of the record that was just deleted.  I tried it with removing the MoveFirst command, it still does it.
0
 
tdoveAuthor Commented:
emoreau-

Does the data connection go under the general declarations or under form_load?
0
 
Éric MoreauSenior .Net ConsultantCommented:
Your declaration (Private...) can go in the General Declarations.

The other lines (with...) must go in the Form_Load event.

The last lines goes in your command click event.
0
 
tdoveAuthor Commented:
emoreau-

Does the data connection go under the general declarations or under form_load?
0
 
tdoveAuthor Commented:
emoreau-

I tried as you said, I try to run the program and I get a compile error. Invalid outside procedure.  It highlight "SET" in the general declarations.


I raised the points since you two have been kind enough to help!

It is greatly appreciated!
Todd

0
 
tdoveAuthor Commented:
emoreau-

I tried as you said, I try to run the program and I get a compile error. Invalid outside procedure.  It highlight "SET" in the general declarations.


I raised the points since you two have been kind enough to help!

It is greatly appreciated!
Todd

0
 
Éric MoreauSenior .Net ConsultantCommented:
My last comment said to put the set statement in the Form_Load event!
0
 
tdoveAuthor Commented:
Thanks for all your help!
0
All Courses

From novice to tech pro — start learning today.