Infamous Object Required Error

[Deeber2]

I have been looking through my code for hours trying to see what, if anything, I did. This was working fine yesterday and just decided it didn't want to anymore. Can someone check to see if they can spot what's wrong? I'm using a class to upload data to a table.



The error occurs during the the Update method in the class. It begins and applies the table primary key (ProjectId), but immediately fails with the error as it exits the Get ClientId() property.

Any help would be GREATLY appreciated!!!!


Class
clsProject

Option Compare Database
Option Explicit

Private plProjectId As Long
Private plClientId As Long
Private plClientContactId As Long
Private plOwnerId As Long
Private psProjectNumber As String
Private prsRecordset As Recordset
Private pbLoaded As Boolean

Public Property Get ProjectId() As Long
     ProjectId = plProjectId
  End Property

  Public Property Get ClientId() As Long
     ClientId = plClientId
  End Property

  Public Property Get ClientContactId() As Long
     ClientContactId = plClientContactId
  End Property

  Public Property Get OwnerId() As Long
     OwnerId = plOwnerId
  End Property

  Public Property Get ProjectNumber() As String
     ProjectNumber = psProjectNumber
  End Property

  Public Property Get Recordset() As Recordset
     Set Recordset = prsRecordset
  End Property


' Class Let Properties

  Public Property Let ProjectId(lProjectId As Long)
     plProjectId = lProjectId
  End Property

  Public Property Let ClientId(lClientId As Long)
     plClientId = lClientId
  End Property

  Public Property Let ClientContactId(lClientContactId As Long)
     plClientContactId = lClientContactId
  End Property

  Public Property Let OwnerId(lOwnerId As Long)
     plOwnerId = lOwnerId
  End Property

  Public Property Let ProjectNumber(sProjectNumber As String)
     psProjectNumber = sProjectNumber
  End Property


' Class Set properties
  Public Property Set Recordset(rData As Recordset)
     Set prsRecordset = rData
  End Property


' Class Methods
Public Function FindFirst(Optional Criteria As Variant) As Boolean
   If IsMissing(Criteria) Then
      Recordset.MoveFirst
      FindFirst = Not Recordset.EOF
   Else
      Recordset.FindFirst Criteria
      FindFirst = Not Recordset.NoMatch
   End If
    
   If FindFirst Then Load
End Function

Function EmptyStringHandler(str As String) As Variant
Dim strTrimmed As String: strTrimmed = Trim(str)
    
   If Len(strTrimmed) = 0 Then
       EmptyStringHandler = Null
   Else
       EmptyStringHandler = strTrimmed
   End If
End Function

Private Sub Class_Initialize()       
   Set Recordset = CurrentDb.OpenRecordset("Project", dbOpenDynaset)
End Sub

Private Sub Class_Terminate()
   Recordset.Close
   Set Recordset = Nothing
End Sub

Private Sub Load()
   With Recordset
      ProjectId = Nz(.Fields("ProjectId").Value)
      Me.ClientId = Nz(.Fields("ClientId").Value)
      Me.ClientContactId = Nz(.Fields("ClientContactId").Value)
      Me.OwnerId = Nz(.Fields("ProjectOwnerId").Value)
      Me.ProjectNumber = Nz(.Fields("ProjectNumber").Value)
   End With
   pbLoaded = True
End Sub

Public Sub Update()
   With Recordset
      If pbLoaded = True Then
         .Edit
      Else
         .AddNew
      End If
    
      Me.ProjectId = Nz(.Fields("ProjectId").Value)
      Nz(.Fields("ClientId").Value) = CLng(Me.ClientId)
      Nz(.Fields("ClientContactId").Value) = Me.ClientContactId
      Nz(.Fields("OwnerId").Value) = Me.OwnerId
      Nz(.Fields("ProjectNumber").Value) = EmptyStringHandler(Me.ProjectNumber)

      .Update
   End With
    
   pbLoaded = True
End Sub

Public Sub AddNew()
   pbLoaded = False
End Sub

The calling procedure results in a cmdSave_Click() event (miEditMode is a module level variable tracking the mode the form is in (New, Edit, View) ).

Private Sub cmdSave_Click()
Dim cProject As clsProject

   Set cProject = New clsProject

   With cProject
      Select Case miEditMode
         Case 1
            .AddNew
         Case 2
            .FindFirst "ProjectId=" & .ProjectId
      End Select
            
      .ClientId = CLng(cboProjectClient.Column(0, cboProjectClient.ListIndex))
      .ClientContactId = CLng(cboClientContact.Column(0, cboClientContact.ListIndex))
      .OwnerId = CLng(cboProjectOwner.Column(0, cboProjectOwner.ListIndex))
      .ProjectNumber = txtProjectNumber

      .Update
   End With

End Sub

[moke123]

I dont see a .Update in your Public Sub Update() procedure.

BTW - Update, load, recordset, (possibly findfirst and AddNew) are reserved words and probably should not be used as methods or properties.

Why a class?

[moke123]

much easier for people to read if you use code tags and indent

Option Compare Database
Option Explicit


Private plProjectId As Long
Private plClientId As Long
Private plClientContactId As Long
Private plOwnerId As Long
Private psProjectNumber As String
Private prsRecordset As Recordset
Private pbLoaded As Boolean


Public Property Get ProjectId() As Long
    ProjectId = plProjectId
End Property


Public Property Get ClientId() As Long
    ClientId = plClientId
End Property


Public Property Get ClientContactId() As Long
    ClientContactId = plClientContactId
End Property


Public Property Get OwnerId() As Long
    OwnerId = plOwnerId
End Property


Public Property Get ProjectNumber() As String
    ProjectNumber = psProjectNumber
End Property


Public Property Get Recordset() As Recordset
    Set Recordset = prsRecordset
End Property




' Class Let Properties


Public Property Let ProjectId(lProjectId As Long)
    plProjectId = lProjectId
End Property


Public Property Let ClientId(lClientId As Long)
    plClientId = lClientId
End Property


Public Property Let ClientContactId(lClientContactId As Long)
    plClientContactId = lClientContactId
End Property


Public Property Let OwnerId(lOwnerId As Long)
    plOwnerId = lOwnerId
End Property


Public Property Let ProjectNumber(sProjectNumber As String)
    psProjectNumber = sProjectNumber
End Property




' Class Set properties
Public Property Set Recordset(rData As Recordset)
    Set prsRecordset = rData
End Property




' Class Methods
Public Function FindFirst(Optional Criteria As Variant) As Boolean
    If IsMissing(Criteria) Then
        Recordset.MoveFirst
        FindFirst = Not Recordset.EOF
    Else
        Recordset.FindFirst Criteria
        FindFirst = Not Recordset.NoMatch
    End If


    If FindFirst Then Load
End Function


Function EmptyStringHandler(str As String) As Variant
    Dim strTrimmed As String: strTrimmed = Trim(str)


    If Len(strTrimmed) = 0 Then
        NullIfEmptyString = Null
    Else
        NullIfEmptyString = strTrimmed
    End If
End Function


Private Sub Class_Initialize()
    Set Recordset = CurrentDb.OpenRecordset("Project", dbOpenDynaset)
End Sub


Private Sub Class_Terminate()
    Recordset.Close
    Set Recordset = Nothing
End Sub


Private Sub Load()
    With Recordset
        ProjectId = Nz(.Fields("ProjectId").Value)
        Me.ClientId = Nz(.Fields("ClientId").Value)
        Me.ClientContactId = Nz(.Fields("ClientContactId").Value)
        Me.OwnerId = Nz(.Fields("ProjectOwnerId").Value)
        Me.ProjectNumber = Nz(.Fields("ProjectNumber").Value)
    End With
    pbLoaded = True
End Sub


Public Sub Update()
    With Recordset
        If pbLoaded = True Then
            .Edit
        Else
            .AddNew
        End If


        Me.ProjectId = Nz(.Fields("ProjectId").Value)
        Nz(.Fields("ClientId").Value) = CLng(Me.ClientId)
        Nz(.Fields("ClientContactId").Value) = Me.ClientContactId
        Nz(.Fields("OwnerId").Value) = Me.OwnerId
        Nz(.Fields("ProjectNumber").Value) = EmptyStringHandler(Me.ProjectNumber)
    End With


    pbLoaded = True
End Sub


Public Sub AddNew()
    pbLoaded = False
End Sub




'The calling procedure results in a cmdSave_Click() event (miEditMode is a module level variable tracking the mode the form is in (New, Edit, View) ).


Private Sub cmdSave_Click()
    Dim cProject As clsProject


    Set cProject = New clsProject


    With cProject
        Select Case miEditMode
        Case 1
            .AddNew
        Case 2
            .FindFirst "ProjectId=" & .ProjectId
        End Select


        .ClientId = CLng(cboProjectClient.Column(0, cboProjectClient.ListIndex))
        .ClientContactId = CLng(cboClientContact.Column(0, cboClientContact.ListIndex))
        .OwnerId = CLng(cboProjectOwner.Column(0, cboProjectOwner.ListIndex))
        .ProjectNumber = txtProjectNumber


        .Update
    End With


End Sub

[Deeber2]

Sorry! To do EXACTLY as you had suggested, I had attempted to do indentions and formatting, but I SUCK at it!!!

I consider myself a competent coder, but really have a difficult time with formatting on the web (my code is actually WELL structured!).

Let me try to do a better job such as what you just did....any pointers?

[Deeber2]

Thank you moke123....I looked into it and restructured the code. Additionally, I added the .Update back into the code (that was inadvertantly deleted when I was tying to format it).

I tried to keep everything understandable for the client (AddNew, Update, Edit, FindFirst, etc....). Like I said before, it was all working just fine. All of a sudden it decided not to.

Short of the possibility of the keywords, can you see anything structurally wrong? I'll work on changing the method names.

I'm using classes to keep the amount of SQL coding to a minimum. Also, I'm used to working in the OOP world so this makes me feel more....at home so to speak.

[Micron]

any pointers?

Use code tags as suggested (# on forum menu bar) or else your indentation will never hold. Uniform spacing is another bonus that makes code easier to read. Also, this forum will inject a space at or about 50 continuous characters and that often throws some responders off when they see spaces where they should not be. Code tags will prevent that also.

[Deeber2]

Thanks Micron....I added that....how does it appear now?

Also, I've tried changing all of the method names and still get the same outcome....that darn Object Required error. This is frustrating!

[Micron]

Looks much better, save for a few End statements that don't line up with the opening statement, but with so little code in between it's easy to grasp the grouping.
I'm not going to be able to help with your issue because I think that while it might be OK for you to build a lunar rover to get groceries, it's far more complicated than what it needs to be and custom classes are not my strong point. I can't imagine how that is better/safer than standard sql when I have seen so much simpler stuff break because of M$ updates and you might be limiting your chances of quickly getting a solution as a result.

Aside from that, can only suggest that if you have not stepped through that code and verified the existence of properties/variable values and such that it's a must do.
I will follow this thread though because I find it intriguing. Good luck!

[Deeber2]

No worries Micron....I appreciate you helping with the formatting.

Using classes help compartmentalize and tighten the code more.

For the most part, I can call a single query and allow the class to control everything.

I started with queries everywhere and things just started to look...messy. I might end up returning back and re-organizing if I can't get the simplest of things to work.

Access is and awesome thing to work with, but it definitely doesn't handle complexity well.

[Welshgasman]

Sorry! To do EXACTLY as you had suggested, I had attempted to do indentions and formatting, but I SUCK at it!!!

I consider myself a competent coder, but really have a difficult time with formatting on the web (my code is actually WELL structured!).

Let me try to do a better job such as what you just did....any pointers?

There are plenty of sites that will do one off jobs. https://www.google.com/search?q=inde...hrome&ie=UTF-8

However I use https://www.add-ins.com/macro-produc...t-vba-code.htm

HTH

[moke123]

I was also about to recommend SmartIndenter but I see Gasman beat me to it.

Custom Classes can be pretty tricky. I've been playing around with them for some time now. A lot of trial and error.

Any chance of posting a stripped down version of your db? May make it easier to spot the problem.

[Deeber2]

It'll take me a little bit, but sure.

I did find one strange thing last night. The VBA version of Coalesce, Nz(), was causing the errors. Wierd, huh? It still didn't fix all of the problem though. It still won't update the database.

[Deeber2]

Ok, I fixed it!

My only guess as to why it was failing with the Nz() function is because the fields in question were Long datatypes and it didn't like NOT putting a 0 if it was null. The table was originally set to not allow nulls in the field, but I changed that during my troubleshooting (so that wasn't the problem).

The initial culprit was using the Nz() function in the table insert.

The secondary issue of the table not inserting new or updating existing records was self-inflicted.

During my long, Long, LONG (did I say long enough?) hours of troubleshooting, I somehow deleted the .Update line in the class Update statement. Mea culpa!!

Things like that happen when you don't take a break after awhile. Something I have learned through many years of programming, but still seem to ignore quite regularly!

Thanks everyone....sometimes it just helps to take a break and throw ideas out to people.

[Micron]

My only guess as to why it was failing with the Nz() function is because the fields in question were Long datatypes and it didn't like NOT putting a 0 if it was null.

I don't buy that because zero (0) is a valid value for long data type. What I would venture to say is that because you don't specify a value if null, you get one of the 2 possible data types which IIRC is either a variant or a zero length string. In your case I imagine you were trying to put a zls in that field. I would never use Nz without specifying the value if null.

[Deeber2]

VERY good assertion!! I believe you're right! Lazy programming as I didn't catch that I hadn't assigned an if null value. I'll try that and check back with you to let you know if it was the culprit.