Reference table value in VB code

Question

Using Access 2002.  I am trying to use a value in a table (not used by a form) to generate a value in another table that is used by the form.

DoCmd.GoToRecord , , acNewRec

    NextComplaintNum = Me!CompNum!Next_Complaint_Number

    RightYear = "1" + Right(Year(Now()), 2) + "-"

The number starts with a 1 and the last two numbers of the current year (eg. 110) followed by a hyphen.  That is the RightYear variable.  The next part of the number comes from a table called CompNum and uses the Next_Complaint_Number value.  How do I use the value in that field?

Answer 1

I get a "User defined type not define" error when I try to use "Dim rs As DAO.recordset"

Answer 2

I'm new to Access.  What is "Me"?

As Marshall said its a shortcut to indicate the Current container object. Recent versions have made it not required, but I recommend continuing to use it for two reasons. First, when you type Me. Intellisense will display a list of members of the container object's collection, this make it easier to select the object name and eliminate misspellings. Second, it reminds you that the reference is to a control on the form or report rather than another type of variable.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 3

Me is a special keyword that is sort of a shortcut to the form or report object that contains the code.  It is shorter and better than using a full object reference like  Forms![the name of this form]...

But, most of the time it is not required.  In the code I posted it is just a reminder that [number field] is a field or control in the form, not a VBA variable name.  The object reference would be required if you should use a variable name that is the same as a field or control name.

Answer 4

I'm new to Access.  What is "Me"?

Answer 5

Thanks for getting back.

It is ALMOST that easy.  The complaint number is structured like this: (110-0651)

1 + last 2 digits of the current year + "-" + sequential complaint number starting at 0001 on January 1.

The part I didn't ask about is how to store the next sequential number in the table for the next record.

Your recomendation on how to handle the number (two integer fields representing the year and another to reference the sequence number, and keyed to those two fields, I would imagine) is a good idea.  I did it this way because it was easy in the database I am migrating from (Delphi).

How do you put the value back in the table using code?

I've written a blog on sequential numbering. You can find it here.

http://scottgem.spaces.live.com/blog/cns!AB37C52C6D3752C!180.entry

You might find it helpful:

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries