ADO problem...can this be done? 1

[TKSlayer]

Hello all,


I'm trying to use a combobox pulldown menu with about 50 items listed in it from source "tblTO". Now I've got a command button right next to it. What I want to do is to have people select from the combobox one of the fifty items...and then hit the button next to it to delete it from the underlying table. I realized that I'd have to go with ADO in order to get it done so I started with this:

Private Sub Command5_Click()
'On Error GoTo Delete_Err
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As CurrentData
Dim cbb As ComboBox

Set CurDB = CurrentData
Set CurConn = New ADODB.Connection
Set cbb = Combo19


With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"

I'm getting the error in the next line...it won't let me call a connectionstring and says it doesn't recognize the CurrDB.name part

.ConnectionString = "data source= " & CurrDB.name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "SELECT * FROM [tblTO] where [ROWA] = cbb"

With rst
.Delete
End With
rst.Close
Delete_exit:
Exit Sub
Delete_Err:
MsgBox "WRONG WRONG WRONG!!"
Resume Delete_exit
End Sub

So, the only problems I really have are the connection string and I will probably have a problem with calling the recordset open because I don't know the proper syntax for how to do that. If someone could help me out with the proper syntax to call open the rst and also the proper way to call the connectionstring I'd appreciate it greatly.

Also, if this can't be done or there is an easier way to do it (delete a record from a table with a combobox and button) please let me know...Maybe I'm doing too much for a simple thing. Thanks!

 

[RoyVidar]

Are you using tables (linked or present) within the DB?

If so:

[tt]set rst = new adodb.recordset
with rst
.activeconnection=currentproject.connection
.cursortype=adopendynamic
.locktype=adlockoptimistic
sSql="SELECT * FROM [tblTO] where [ROWA] = " & cbb.Value
.open sSql, options:=adcmdtext
if not .bof and not .eof then
.delete
end if
.close
end with
set rst=nothing[/tt]

- assuming the combos value is numeric, if text;
[tt]"SELECT * FROM [tblTO] where [ROWA] = '" & cbb.Value & "'"[/tt]

Roy-Vidar

 

[TKSlayer]

yes...the combobox is alpha/numeric. It also contains a special character "-"

And yes I am using tables within the DB...linked together? No...just a small one column (labeled ROWA) table (named tblTO) with about 50 alpha/numeric (i.e 11F1-AAQ14-4) entries.

 

[RoyVidar]

And the question is?

The above works in my setup, if something is not working in yours, please provide errormessage, what line provides the error...

Roy-Vidar

 

[TKSlayer]

Ok...here's the error I get:

Run Time error '438'
Object doesn't support this property or method

it calls out .ConnectionString = "data source= " & CurDB.name

(line 15)

So...I think the problem resides in CurDb.name not being a valid function? I dunno..I know it is trying to open a connection with the underlying data with this statement and therefore the rest of the code isn't working since the connection cannot be established. Any input you could provide would be fantastic

 

[RoyVidar]

When giving advice on how to do something, one tends to expect that the other party listens, tries out, and if necessary, asks questions relating to that. If you do have any questions relating to my previous advice (which does work on my setup), I'd be happy to answer. Did you try it?

Took a very short look in the help files (F1 on CurrentData) which told which properties are available for the CurrentData object, name is not one of them.

Roy-Vidar

 

[TKSlayer]

yep..worked.

Had to .connectionstring "data source=C:\mydb.mdb" Thanks for the help....I got busy and didn't get to post immediate results. Sorry if I sound retarded on this...but I've only been doing Access for about a week now. Forgive my stupidity.