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

Assuming you have (or will have) more than one user, you should put the code in the form's BeforeUpdate event.  If you keep the date and number parts in separate fields, you could use something like this air code:

Dim db As Database

Dim rs As DAO.Recordset

If Me.NewRecord Then

   Set db = CurrentDb()

   Set rs = db.OpenRecordset("SELECT Next_Complaint_Number FROM CompNum  " _

                            & "WHERE Year(datefield) = Year(Date())", dbOpenDynaset)

   If rs.RecordCount = 0 Then    ' check if first complain in new year

      Me.[number field] = 1

   Else

      Me.[number field] = rs!Next_Complaint_Number

   End If

   rs!Next_Complaint_Number = Me.[number field] + 1

   rs.Close : Set rs = Nothing

   Set db = Nothing

End If

Answer 2

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?

Answer 3

If the CompNum table is guaranteed to have one record, you can use

RightYear = "1" + Right(Year(Now()), 2) + "-" & DLookup("Next_Complaint_Number", "CompNum")

But I can't believe it's that simple, there must be more to your problem than that.  Probably need to do something to prepare Next_Complaint_Number for the next time you want to get it.

Actually, what you are trying to do is not a good way to male a complaint "number" (it's actually Text).  You should not be packing several values into a single field.  Instead, you should have a date field and a number field.  Then whenever you want to display it to users on a form or report,  you can use a text box expression to put it together:

   ="1" & Right(Year(datefield), 2) & "-" & numberfield

Remember that it's easy to put values together, but it can be difficult to take a combined values apart.

Answer 4

You could use DLookup to get the value in the other table.  The problem is that you probably need to increment the number once you have grabbed it.

NextComplaintNum = DLookup("NextComplaintNumber","CompNum")

Post back if you need to increment the number stored in the table.

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

Answer 5

For this you'd most probably have to use DLookUp or Allen Browne's ELookUp (see:http://allenbrowne.com/ser-42.html)

DLookUp("FieldName","TableName","Criteria to base the search on")

DLookup("FieldNameToReturnTheValueOf", "CompNum","[FieldName]=" & NextComplaintNum )

Look in the help file for other examples or post back if you need further assistance.  If you do post back, please give a little more detail what field do you need to get the value of from the CompNum table and based on what criteria.

I hope this helps,

---

Daniel Pineault, 2010-2011 Microsoft MVP

http://www.cardaconsultants.com

MS Access Tips and Code Samples: http://www.devhut.net