openrecordset is not working

[AccessProgram]

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rst As DAO.Recordset

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(CurrentProject.Path & "\container\config.accdb", False, False, "MS Access;PWD=""")
Set rst = db.OpenRecordset("confpath",dbopentable)

rst.close
Set rst = Nothing
db.close
Set db = Nothing
Set ws = Nothing
End Sub


I have code above. I dont know where is the error in it. It is not showing records on my form textboxes.

 

[RuralGuy]

Add error handling to your procedure.

 

[jdraw]

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rst As DAO.Recordset

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(CurrentProject.Path & "\container\config.accdb", False, False, "MS Access;PWD=""")
Set rst = db.OpenRecordset("confpath",dbopentable)

rst.close
Set rst = Nothing
db.close
Set db = Nothing
Set ws = Nothing
End Sub


I have code above. I dont know where is the error in it. It is not showing records on my form textboxes.

What error are you getting?
What textboxes? What did you expect to happen?
You Open the recordset then close itimmediately? How do you know it didn't open?

After your Set rst statement , you could put a few lines like
rst.movelast
Msgbox "There are " & rst.recordcount & " records"
just to stop the process.



Note: I don't have Acc2007, but I modified your code as follows and it works for me. Access 2003

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rst As DAO.Recordset

   [B]On Error GoTo Form_Open_Error[/B]

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("[B]C:\Documents and Settings\NewAdmin\My Documents[/B]" & "[B]\MyForumsAndPwd.mdb[/B]", False, False, "MS Access;PWD=""")
Set rst = db.OpenRecordset("[B]main[/B]", dbOpenTable)
[B][COLOR="SeaGreen"]rst.MoveLast
MsgBox "there are " & rst.RecordCount & " records"[/COLOR][/B]
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Set ws = Nothing

   On Error GoTo 0
   Exit Sub

[B]Form_Open_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Form_Open of VBA Document Form_Form7"
End Sub[/B]

Opened a separate database, and count the records in Table "main"

 

[boblarson]

Of course it isn't putting anything in the text boxes. Nowhere in the code does it assign the fields to the text boxes. The code doesn't do that. You don't need to use a recordset object. Just bind the form to the query.

 

[AccessProgram]

I cant use the record source property because if I do that the underlying database of it will be opened and I need to do backup of the said db. If I will do that, I will get permission denied error message.

Anyway, I saw the problem, that is I should not close the recordset, the database object variable yet until such time I will proceed to backup the db.

 

[boblarson]

Again, your response makes no sense. What do you expect to happen? Opening the recordset is one thing but opening a recordset opens the underlying database too. You can't open something up without it opening the database.

Also, you stated that nothing is showing on your form. Well the code you have doesn't do anything with the form. Why you would be opening a recordset object in the form's open event has me wondering what in the world you are wanting because the code you posted does absolutely NOTHING. NOTHING at all does it do except open a recordset and the recordset is not being used for anything. Why are you opening the recordset anyway? What do you think it will do?

 

[AccessProgram]

Dim rs As Recordset
Dim ws As Workspace
Dim db As Database
Dim dbname As String
dbname = CurrentProject.Path & "\container\config.accdb"
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(dbname)
Set rs = db.OpenRecordset("confpath", dbOpenSnapshot)
Set Me.Form.Recordset = rs


This is the code in form_load event. I have preset the control source of my textbox to what will be in my recordset. I done that because I cant figure out yesterday how I can set the control source of my textbox using " rs! ".

Though it worked but I also need to know how to set the source of my fields to rs!

 

[LPurvis]

I'm not entirely clear on what you're asking either.
It sounds like you don't want default form binding (which is ultimately the same whether using the RecordSource and a local table/query or an assigned recordset as in your code above) - but you want to bind a textbox... without binding the form.

That makes little sense, and isn't possible.
You can certainly assign a value to a textbox, either through a function as a controlsource or as simple display text in an unbound control. But to what end? Do you plan on editing this textbox (field) value?

If you want an unbound form (your mentioning a concern of "underlying database of it will be opened" imples that you do want to operate in a more disconnected process) then you need a full unbound form example (the likes of which is shown, very simply, in the Unbound example found in the link in my sig).
If not then you'll need to be more clear of your intention - and your fears.

FWIW though it's not a particularly great idea - I've often copied open MDB files that have connections open to them.
What you don't want is a copy made during an database write. But you can't prevent a user from doing that operating in bound or unbound scenarios (it's true that updates tend to be less often in unbound applications due to their explicit nature - having to be deliberately instigated).


As a total aside - in the code suggested earlier with:

Set rst = db.OpenRecordset("main", dbOpenTable)
rst.MoveLast
MsgBox "there are " & rst.RecordCount & " records"

Since that it a table type recordset, then there's no need to move to the end of the recordset to force it to fully populate. The recordcount is therefore available immediately. (It's actually an efficient way of determining the count - a table type. Though not as fast as the RecordCount property of a TableDef object.

Cheers.