Select database 1

[tekthis]

is there a way to select a table from a combobox and have it open to be updated...this is the code i have, but it won't work...

Set db = OpenDatabase("C:\test\orders.mdb")
Set rs2 = db.OpenRecordset(" & cmbTables.Text & ", dbOpenDynaset)

obviously the cmbTables.Text is giving me the error...i dont know how else to have that particular table open...
thanks for your time...

 

[bombdropVB]

Try changing the code to this

Set db = OpenDatabase("C:\test\orders.mdb")
Set rs2 = db.OpenRecordset(cmbTables.Text, dbOpenDynaset)

Hope this helps!!!

 

[tekthis]

that worked...but it now gives me a Type Mismatch error...
thanks for your reply...

 

[bjd4jc]

Post your declarations for db and rs2

 

[tekthis]

here is the code i use for the connection and adding...

Public Sub AddIt()

Set db = OpenDatabase("C:\test\test.mdb")
Set rs2 = db.OpenRecordset(cmbTables.Text, dbOpenDynaset)
TempName = txtName.Text
TempDate = txtDate.Text
TempNum = txtNum.Text
TempOrder = txtOrder.Text

rs2.AddNew
rs2!Name = TempName
rs2!Date = TempDate
rs2!Num = TempNum
rs2!Order = TempOrder

rs2.Update
MsgBox "Item Added Successfully!!" _
, vbInformation, "Added"

rs2.Close
Set rs2 = Nothing
db.Close
Set db = Nothing
Unload Form4
Load Form4
Form4.Show
End Sub

hope this helps...thanks all...

 

[bjd4jc]

I am looking more for your Dim or Public/Private statments for rs2 and db

I am guessing that you have bot ADO and DAO checked as references and you are saying

Public rs2 as Recordset

In which case it is probably using the ADODB.recordset instead of DAO.Recordset

But without seeing your declarations it's hard to tell.

Also if you do a

Debug.Print cmbTables.Text

right before you open the recordset, what does it say?

 

[tekthis]

sorry i meant to include...
Dim db As Database
Dim rs2 As Recordset

Debug.Print cmbTables.Text doesn't say anything...i keep getting a type mismatch error...hope this helps...thanks

 

[bjd4jc]

Are you getting the type mismatch error on the Debug.Print statement? If not, then comment out the line for now that is getting the type mismatch error.

Change Dim rs2 As Recordset

to

Dim rs2 As DAO.Recordset

 

[tekthis]

that work!!!
it inserted the data in the table i selected from the db...

the only problem i have now is that i get an error after its says that the item was added successfully...

the error reads "Operation is not allowed when the object is open."

heres the code it points to...

Private Sub Form_Load()

///////////////ERROR///////////////////////////////////
con.Open "Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source=c:\test\test.mdb"
///////////////ERROR//////////////////////////////////
Set Rs = con.OpenSchema(adSchemaTables)

Do While Not Rs.EOF
cmbTables.AddItem Rs("TABLE_NAME")
Rs.MoveNext
Loop

End Sub
-----------------------------------------------------

i wonder if its because i open the same database twice???
any thoughts???
thanks again heres a star...

 

[tekthis]

i got the error to go away...
i just closed RS and con in the form load...i should have noticed that sooner...thanks all for your help!!!!