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?
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.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
Éric MoreauSenior .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

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
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
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
Visual Basic Classic

From novice to tech pro — start learning today.