Error message when compiling Access 2010 form

[johnpaulh]

Hello,
I stole some code from another Access DB that I manage (well...attempt to manage anyhow)

The code uses DMax to assist with generating a unique audit number with the last two digits of the current year as a prefix.

I am trying to adapt this code to an order entry system, and same thing, just want a unique order number.

Here is the string that is causing me the issues:

Me.txtSequence = Nz(DMax("[Sequence]", "TblOrders", "Year([OrderDate]) = " & Year(Me.OrderDate)), 0) + 1

When I compile, the portion in blue is highlighted, and the message "Method or data member not found."

OrderDate is a valid field in the DB.

Here is the code I swiped from my other DB:
Me.txtSequence = Nz(DMax("[Sequence]", "audlog", "Year([MAAssignDat]) = " & Year(Me.MAAssignDat)), 0) + 1

There is a "Me.Dirty=False" statement following the line of code (in both instances)

If I take out the Me. and simply leave OrderDate, the form prompts me to enter the Order Date manually (there is a date picker entry box on the form for the order date) then when I press the Generate Order # radio button on the form, the field Order # is populated with the Order Date (long date)

Original DB was created in Access 2007
New DB is Access 2010

Any help will be greatly appreciated!
John

 

[boblarson]

If the control that is bound to the field - OrderDate is named the same, change it to txtOrderDate and refer to that instead. However, sometimes the VBA gets screwy and if you run a decompile it can help fix things that have been working but aren't now.

See this page for more about decompiling:
http://www.granite.ab.ca/access/decompile.htm
And make a copy BEFORE doing it on your db file.

 

[johnpaulh]

Hmmmm, I don't believe OrderDate is bound. And Sequence is unbound (there is a hidden, unbound text box embedded in my input form) I tried the Decompile, but no help there. Still getting the same error condition.

I'm kind of working backwards, the DB I took the code from is one I worked on back in 2009 with assistance. Here is an excerpt from the instructions I was given to accomplish the unique number function. I don't recall binding anything to MAAssignDat (the field in the original DB, which is OrderDate in the new DB)

"assuming your record includes a date field that identifies the year. That's one part of your audit number. The second part is an integer field, that I woould name Sequence.

Now to populate Sequence you would use the following expression:

Nz(DMax("[Sequence]","tablename","Year([datefield]) = " & Year(Me.datecontrol)),0)+1

This adds 1 to the sequence number for that year. If there are n o records for the year, then it returns a zero before adding the 1.

To display the audit number, you would use the following expression wherever you need to display it:

=Format([datefield],"yy") & "-" & Format([Sequence],"0000")

Now there is one final piece to this and that's where to use the DMax expression. You need to generate this number AFTER the date is entered, but immediately before you save the record. Otherwise someone else might be entering at the same time and duplicate the number. This is something you need to figure out because it depends on your workflow. There are several options. You can use the After Update event of the date control, a button on the form, the Before Insert event of the form, to name a few. You don't want to commit the record before the user has entered all the info in case a mistake is made and he needs to delete.

What you would do is have a hidden text control on your form bound to the Sequence field. You would then use the code:

Me.txtSequence = Nz(DMax("[Sequence]","tablename","Year([datefield]) = " & Year(Me.datecontrol)),0)+1

In most instances (except for the Before Insert event) you would want to add:
Me.Dirty = False
to commit the record making sure the sequence is saved. "

Thanks again,
John

 

[boblarson]

Make sure that the VBA project name is not named OrderDate. I've run into cases before where that has happened and Access doesn't like that. You might also try running a compact and repair on the database and/or import (not copy and paste) everything into a new database shell and see if that works. Sometimes things just get corruption happening and you have to do that to make things work.

 

[johnpaulh]

Gave that a go, still no joy.

Started with DB shell, imported input form, queries, macro and module from original (working number generator) DB into my new DB. Modified queries, macro and module by changing appropriate field names. Created a text box inside entry form bound to the Sequence field, modified VB code to reflect correct field names.

still getting error.

 

[boblarson]

What if you change the control name from OrderDate to txtOrderDate (making sure that Name AutoCorrect is turned off before you do)?

 

[johnpaulh]

I think the datecontrol is what is kicking my butt here.....where have I defined that particular control? I am going thru my notes from the first DB build, and cannot find the reference to that.

This line Nz(DMax("[Sequence]","tablename","Year([datefield]) = " & Year(Me.datecontrol)),0)+1

Me.datecontrol

Where the heck is my control?

 

[boblarson]

I think the datecontrol is what is kicking my butt here.....where have I defined that particular control? I am going thru my notes from the first DB build, and cannot find the reference to that.

This line Nz(DMax("[Sequence]","tablename","Year([datefield]) = " & Year(Me.datecontrol)),0)+1

Me.datecontrol

Where the heck is my control?

OrderDate would be the date control name, if that text box is what is used for the date.