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

It's just a place holder for whatever field in the form's record source table/query that is set to the Next_Complaint_Number.  Replace it with your real field name.

Answer 2

What is [number field]

Answer 3

If you have to ask that question, it implies that you are unaware of the libraries that provide data access.  There are two common libraries DAO and ADO.  If you do not have a specific reason to use ADO, I recommend that you use the VBA - Tools - References menu to uncheck the ActiveX Data Objects library and check the Microsoft DAO 3.6 Object Library.  Once that is done, the code should be ok.

Answer 4

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

Make sure the DAO 3.6 Library is selected in References

---

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