Form Design Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a PO database. Everything was working great until I got
thrown a curve ball in the 11th hour of development. This is primarily
going to be a PO DB, unless the cost exceeds a certain $ amount, then it
doesn't use the PO# anymore, it uses an AR#.

I have all of this working fine and dandy. The problem is, the AR number is
entered in manually and could be added to at different time and dates from
different departments. The powers that be, want the report (and the tables
for future referencing) to have the AR#, and if there are more than one...
a letter A, B, C, etc... behind it (could be a seperate field in the
table).

The way everything was designed was using the PO# as an autonumber. I've
hit a wall on this one and need to ask for help. Anyone have any thoughts
on how to get the letters to consecutively show up? For example, I may have
a PO#: 23456. Then I have an AR#: 12345. That AR may not get added to for
2 weeks, although 12 POs may be assigned. But the next time an AR is added
with AR#: 12345, it needs to somehow have an "A" added into the DB for that
record. All entries, whether PO or AR still get assigned a PO# if that
helps... Let me know if I didn't explain this well...
 
Probably the easiest way is to make the letter suffix a separate field (you
can always concatenate it for reports).

In the beforeupdate for the form, do a DLookup to see if you have the AR#
already. If so, do a DMax on the suffix field using the AR# as your
criteria. This gives you the maximum suffix used for that AR#. Increment
the suffix by 1 (i.e., the next letter) and store that as your suffix for
the new record.
 
Back
Top