If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Form Design Question
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... |
#2
|
|||
|
|||
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. news wrote in message ... 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... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Strange stLinkCriteria behaviour on command button | Anthony Dowd | Using Forms | 3 | August 21st, 2004 03:01 AM |
If form is Open Code Question? | Dave | Using Forms | 2 | August 19th, 2004 10:05 AM |
Form design question | Bob | Database Design | 2 | August 18th, 2004 10:13 PM |
Need a design suggestion | BobV | Database Design | 5 | July 30th, 2004 06:37 PM |
Datasheet form question | osmondb | Using Forms | 1 | May 31st, 2004 05:29 PM |