Object variable or With Variable not Set?? Need some help

[mrdod]

I'm writing some code to pull in data from an Access DB. I haven't done it in a while and when I close the form I'm getting an Object Variable wor With Variable not set. Here's the code I'm using. Be gentle again I haven't done this in a while. Thanks for any help provided.

Private Sub UserForm_Initialize()
'Path = P:\Safety\Audit Database\Audit_Database.mdb

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strpath As String, strSQL As String
Dim strconn As String, Item, i As Long, strbu As String
strpath = "P:\Safety\Audit Database\Audit_Database.mdb"
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strpath & ";"
Sheets("Data").Select
strSQL = "SELECT * FROM tblbu"
cnt.Open strconn
Set rst = cnt.Execute(strSQL)
With rst
.MoveFirst
Do While Not .EOF
strbu = .Fields(i)
frmmain.cmbareas.AddItem strbu
rst.MoveNext

Loop
frmmain.Show
End With


rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub

 

[PHV]

You may try this:
With rst
.MoveFirst
Do While Not .EOF
strbu = .Fields(i) ' where is i coming from ?
frmmain.cmbareas.AddItem strbu
rst.MoveNext
Loop
[!]frmmain.Show[/!]
.Close
End With
cnt.Close
Set rst = Nothing
Set cnt = Nothing
[!]frmmain.Show[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrdod]

LOL I'm not sure where i came from but it works and if I take out i it doesn't work. I tried moving the "frmain.show" down but it still bombs out when I close the form.

Thanks for your help PH!

Private Sub UserForm_Initialize()
'Path = P:\Safety\Audit Database\Audit_Database.mdb

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strpath As String, strSQL As String
Dim strconn As String, Item, i As Long, strbu As String
strpath = "P:\Safety\Audit Database\Audit_Database.mdb"
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strpath & ";"
Sheets("Data").Select
strSQL = "SELECT * FROM tblbu"
cnt.Open strconn
Set rst = cnt.Execute(strSQL)
With rst
.MoveFirst
Do While Not .EOF
strbu = .Fields(i)
frmmain.cmbareas.AddItem strbu
rst.MoveNext
Loop
End With

rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
frmmain.Show

End Sub

 

[mrdod]

Sorry for the trouble but I figured it out. I have to remove the frmmain.show. I originally had the code in a Module and moved it to the Intialize section of the form. So as it's trying to initialize it's being told to open it as well. I know very stupid.

Thanks for any help though