books

  • Thread starter Thread starter padge
  • Start date Start date
P

padge

hello, was wandering if anyone could help me with the
problem i have.Ive created a small library system and im
trying to reference each item in the system by combining
the ref code, eg EDU, and a number, so that the reference
will be in the format EDU/1. i was doing it by combining
the ref code with item number(incremental autonumber)and
the results for the first 3 records references were as
follows: EDU/1
PCE/2
EDU/3.
What i want do do though is create a count or something so
that EDU/3 will be EDU/2 in my system. In this example
its the 3rd record, but its only the 2nd EDU record. I
know that im going to have to get rid of the autonumber,
but can anyone help me create a count so that for example
if i dont enter another EDU record in the next 100
records, that when i do the count works and the system
knows that the previous EDU number was 3. ur help would be
most appreciated!
 
Principles of good relational database design prohibit decomposeable fields.
In this case that can be read as: "The key, the whole key, and nothing but
the key" IOW, your key should be broken into several fields. That said, what
you want to do is still possible either way, using the DMax() function to
look up the highest value where the reference code field equals the value of
its first 3 letters.

You would either use the On Change event if using the same field, or the
AfterUpdate event if using separate fields. Since the composite key is more
complex to code, I'll write you an air code example:

Sub txtRef_Change()
Dim lngKey As Long

If Len(Me.txtRefCode) = 3 Then
lngKey = DMax("RefCode", "MyTable", "Left(RefCode, 3) =" & "'" &
Me.txtRefCode & "'") + 1
Me.txtRefCode = lngKey
End If

End Sub

Of course you need to use your own field and variable names.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top