Object required

jpaul · November 8th, 2007, 04:41 PM

I'm using the example from the book "Beginning Access 2003 VBA
the "PopulateControlsOnForm" sub.
Code:
=================================

Private Sub Form_Load()
On Error GoTo Err_Form_Load_Click

     Dim Cnx As ADODB.Connection 'Declares the Object Varibles
     Dim Rst As ADODB.Recordset

    Set Cnx = New ADODB.Connection 'sets the Connection
    Set Rst = New ADODB.Recordset 'sets the Recordset

    Cnx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Access.CurrentDb.Name & ";Persist Security Info=False"
    Cnx.Open

    If Rst.State = 1 Then Rst.Close

   Rst.Open "Select * From tblLog order by [Date]", Cnx, adOpenKeyset, adLockOptimistic

    If Rst.BOF And Rst.EOF Then
        Exit Sub
        Else
            Rst.MoveFirst
            Call PopulateControlsOnForm
    End If

         Rst.Close
         Cnx.Close
         Set Rst = Nothing
         Set Cnx = Nothing
         Me.Filter = Me.Filter
         Me.Refresh

Exit_Form_Load_Click:
        Exit Sub

Err_Form_Load_Click:
        MsgBox Err.Description
        Resume Exit_Form_Load_Click

=================================
Sub PopulateControlsOnForm()

If Not Rst.BOF And Not Rst.EOF Then
    txtLogDate.Value = Rst.Field("Date")
    Me.tbProj = Rst.Fields("Project")
    Me.tbInTime = Rst.Fields("InTime")
    Me.txtOuttime = Rst.Fields("outTime")
    Me.CrgHrs = Rst.Fields("CrgHrs")
    Me.MLog = Rst.Fields("Log")
Else
If Rst.BOF Then
    Rst.MoveNext
Else
If Rst.EOF Then
   Rst.MovePrevious
End If
End If
End If

End Sub
===========================
I've also used the book code example" Me.tbLogDate = rst!Date

Using my recordset as "Rst." and my table & object names.

I'm getting and error "no object"
So it cant find the recordset object. I've also duplicated the book code exactly (except my table names etc.)

Has anyone else experienced the same problems from this book and is there a solution?
Thanks

BrianWren · November 9th, 2007, 03:32 PM

I don't know where this way of doing things originated, but I personally find it out of keeping with consistency.

All statements within a block typically are indented. The Sub/End Sub statements delineate a block. Yet so many people don't indent the On Error statement, and many also don't indent the Dim statements.

Your comment, ' Declares the Object Varibles (sic) only states the obvious. Think about removing the comment. The same with the ' Sets . . . statments.
Try to avoid âMagic Numberâ statements like If Rst.State = 1 ... Use the predefined constants (adStateOpen).
PopulateControlsOnForm is a sub. Don't use Call.

Code:

    If Rst.BOF And Rst.EOF Then
        Exit Sub
    Else ' This else is unnecesary.  If the If were true, 
         ' you have already left the Sub.  All after that
         ' is a de facto else...
        Rst.MoveFirst   ' This is unnecesary.  RecSets open to 1st.
        PopulateControlsOnForm
    End If



    If Rst.BOF And Rst.EOF Then Exit Sub

    Call PopulateControlsOnForm
    ...

Rst has local scope only within Sub Form_Load().

Do this:

Code:

    ...
    PopulateControlsOnForm Rst
    ...

Sub PopulateControlsOnForm(ByRef Rst As ADODB.Recordset)

    ...

jpaul · November 12th, 2007, 05:26 PM

Not to offend,
The code you wrote on is actually from one of the books purchased
from this web site. In taking your advice, that would mean the writer didn't know what she was talking about. If this is true then I need to be concerned with any book purchased here. I do know that, there are errors in the code we are discussing. I really don't like spending cash on a book that misleads you. The code they present, at least, should work.
The ADO connection code is cut and paste and I'm not looking for proper indent at this time. And, the comment is left over from a tutorial.
I've seen a lot of programmers code on the same subjects with their own way of doing there code. I'm sure its all good. The point is the results and does it work. I can clean up the rest if the instructions work. (I know that there is cleaner and sloppy code)
The "If Rst.State = 1 Then Rst.Close" statement is just in case the connection has accidentally been left open somewhere. It has solved a few problems before but maybe there is a better way. Thanks for you input and I will give your answer a try. Hopefully It will work and I can learn from it. Thanks again

jpaul · November 13th, 2007, 08:58 AM

Sorry to say, it didn't work. Ambiguous Name error. Adding Rst to the name of the sub when called is most likely the error. but I most likely don't understand this yet and didn't place it correctly. I've got an idea on how to make this work using a different approach.
Thanks and I'll try again

BrianWren · November 13th, 2007, 04:32 PM

Well, because VB is robust and tolerant, programmers can have these quirks, and still write functional programs. The use of the books is to show the concepts, and irrespective of formatting, the code can work.

Also, thematically-flawed code such as running a sub with "Call" still runs, though the writing makes it confusing to follw what the intent was.

If you have an amiguous name error, then there are at least two things that can be the thing being refered to by Rst.

Since not all of the code is posted, it would be hard to say where the other possible reference is.

If you have a public sub named Rst, that could be it. Search for the string Rst, and see if you can solve the unresolved reference.

jpaul · November 14th, 2007, 12:10 PM

Thanks again,
I posted a topic "If BOF and EOF then" Question.
I actually meant to post it as a reply here.