Sudden "Compile error: method or data member not found"

Question

Can anyone tell me why all of a sudden my 2010 Access database is not recognizing a key field in one of my tables? VBA recognizes every other field defined in the table? I've double and triple checked that everything is spelled correctly.

Code where compile error appears:

Me.benefitID = Nz(DMax("benefitID", "tblBenefits"), 0) + 1

tblBenefits fields:

benefitID (Primary key)
benefitAnimalWelfare
benefitSafety
benefitCostSavings
benefitImprovedPractice
benefitAdministrative
benefitOther

Entire code:

Private Sub NewIdeaButton_Click()
On Error GoTo NewIdeaButton_Click_Err

    'new idea boolean is true
    newIdea = True

    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    'assign IDs for ideaID, benefitID, statusID 
    Me.ideaID = Nz(DMax("ideaID", "tblIdeaDetails"), 0) + 1
    Me.benefitID = Nz(DMax("benefitID", "tblBenefits"), 0) + 1
    Me.statusID = Nz(DMax("statusID", "tblStatus"), 0) + 1

    Me.PrintIdeaButton.Visible = False
    Me.DeleteIdeaButton.Visible = False
    Me.IdeaStatusFormButton.Visible = False
    Me.CancelButton.Visible = True
    Me.ClearListBoxButton.Visible = False
    Me.AttachedLabel.Visible = False
    Me.FileList.Visible = False
    Me.FileList.RowSourceType = "Value List"

    Me.ideaSubmitter.SetFocus
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If

NewIdeaButton_Click_Exit:
    Me.NewIdeaButton.Visible = False
    Exit Sub

NewIdeaButton_Click_Err:
    MsgBox Error$
    Resume NewIdeaButton_Click_Exit

End Sub

I've tried selecting the Microsoft DAO 3.6 Object Library Reference, but I receive a "Name conflicts with existing module, project, or object library". I read that if you deselect the Microsoft Office 14.0 Object Library, it'll take care of that error. But I don't think this is something I want to deselect?

I've also tried deleting the relationship between this table and another, renaming the field, resetting the primary key to a different field, it still won't recognize this field.

Also tried a compact and repair database, but got the message "The compact and repair operation has been canceled. You might not have adequate permissions to the folder that the database is located in. You need full permission to the directory the database is located to compact and repair. Contact your system administrator for more information." I've been able to run a C & R in the past?

You might consider using "Bang" (an exclamation point) instead of a dot, particularly if you are accessing a field. My own practice is to use dot for everything except referencing underlying fields, in which case I use bang exclusively. And my controls are always prefixed using Hungarian notation so that a text box is txtIdeaSubmitter. You can read more about bang versus dot: http://bytecomb.com/the-bang-exclamation-operator-in-vba/ and here http://www.datagnostics.com/dtips/bangdot.html and here http://stackoverflow.com/questions/2923957/bang-notation-and-dot-notation-in-vba-and-ms-access — HK1, Sep 09 '13 at 14:37
with the form in design mode, blank out the data source of the form, and tab out. Now tab back in, and re-enter the data source (table or whatever), and now tab out of the data source property. Access will then re-generate the "dot" list of properties for you. This is a design time list of properties that Access adds. If you use code to swap the data source, or even add a new colum to the table, then the list is not re-generated. If a control with the same name is not on the form, and you use code to swap out the data source, then using ! in place is . (dot) is recommended. — Albert D. Kallal, Mar 25 '19 at 23:03

score 2 · Answer 1 · answered Mar 21 '17 at 01:14

Access caches the field names as WithEvents AccessField objects within the form/report at the time you assign the form/report's Record Source property. That means that changes to your table or query are not propagated to the Form/Report unless you explicitly reassign the Record Source property, thereby forcing a refresh of the cached fields.

If you've previously used a benefitID as a member of the Form/Report, but the cache no longer includes that field name (even if the Record Source does contain the field name), then you'll need to update the RecordSource property for the cache to refresh and the code to compile.

If the name of the benefitID field has changed in the RecordSource, then you'll still need to refresh the RecordSource, but you'll also need to update the references to benefitID so that they use the new field name.

upvoted. This is exactly the issue. If control is not on the form, Access still generates the properties for me "dot" column name at design time. If a new field is added, or even VBA swaps out the forms recordsource, the me "dot" list of properties is NOT re-generated. One can go into the property sheet, blank it out, tab out, and then tab back in and re-enter the data source for the form during design, and the property list of columns will be re-generated. If controls with that name exist, then no problem, but without a control, then the dot list is generated at design time. me! always works. — Albert D. Kallal, Mar 25 '19 at 23:00

score 0 · Answer 2 · edited Mar 25 '19 at 22:49

0

Exclamations points ! worked for me. I replaced the periods . with them wherever I found this error.

edited Mar 25 '19 at 22:49

MarredCheese

17,541
8
92
91

answered Mar 30 '15 at 15:05

Eddy Jawed

457
6
17

score 0 · Answer 3 · answered Jul 04 '16 at 07:43

I have just come across the very same issue. I found that the exclamation mark that I usually use no longer does. So, I have had to use .value, e.g.:

me.benefitID.value

This appears to have fixed my issue, but I am still confused about the conflicting references.

Sudden "Compile error: method or data member not found"

3 Answers3