Re: Generate Sequential numbers for new records
- From: "jtidwell" <jtidwell@xxxxxxxxxxxxxxxx>
- Date: Mon, 2 Jul 2007 11:44:20 -0400
In my query Im getting an error "The specified field "[contractnum]" could
refer to more than one table in the From clause of SQL statement"
"Graham Mandeno" <Graham.Mandeno@xxxxxxxxxxxxx> wrote in message
news:3DBB3928-D7DB-41AA-B06F-0AC74C651736@xxxxxxxxxxxxxxxx
In your table tblMaintWO you should have a field named WOContractNum
(text) and another named WOSeqNum (numeric/integer). tblContractList and
tblMaintWO should have a one-to-many relationship, with ContractNum in
tblContractList related to WOContractNum in tblMaintWO.
Additionally, you should add a multi-field index to tblMaintWO comprising
both these fields and disallowing duplicates, so that you cannot
accidentally get two records with the same contract number AND sequence
number.
When you add a new work order, assuming you are on a form where the
related contract number is available in a control named WOContractNum, you
can find the next sequence number using this code:
Me!WOSeqNum = Nz( DMax( "WOSeqNum", "tblMaintWO", _
"[WOContractNum]='" & Me![WOContractNum] & "'" ), 0 ) + 1
This could be in your Form_BeforeInsert event procedure (which will run
the moment you start entering data for a new record) or in
Form_BeforeUpdate with a conditional statement to test for a new record:
If Me.NewRecord Then
Me!WOSeqNum = Nz( DMax( "WOSeqNum", "tblMaintWO", _
"[WOContractNum]='" & Me![WOContractNum] & "'" ), 0 ) + 1
End If
A third option is to put it in Form_Current to set the default value for
the sequence number:
If Me.NewRecord Then
Me!WOSeqNum.DefaultValue = Nz( DMax( "WOSeqNum", "tblMaintWO", _
"[WOContractNum]='" & Me![WOContractNum] & "'" ), 0 ) + 1
End If
All your forms and reports and combo boxes that display work order numbers
should be based on queries which include a calculated field:
FormattedWONum: Right([WOContractNum], 1) & "1"
& Format([WOSeqNum], "00000")
This calculated field can be used as a displayed column in a combo box, or
bound to a textbox in a form or report to display the formatted work order
number.
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"jtidwell" <jtidwell@xxxxxxxxxxxxxxxx> wrote in message
news:eIXOD5NuHHA.1184@xxxxxxxxxxxxxxxxxxxxxxx
Sorry all of this is new to me. Where do I put the codes at?
"Graham Mandeno" <Graham.Mandeno@xxxxxxxxxxxxx> wrote in message
news:57BCC851-5966-40B0-A433-8BE61E522E7F@xxxxxxxxxxxxxxxx
OK, then just do as I suggested with two fields in tblMainWO: one for
the contract number and one for the sequence number.
The actual WO number for display can be formatted on-the-fly in a query
as I showed you.
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"jtidwell" <jtidwell@xxxxxxxxxxxxxxxx> wrote in message
news:u41W0V%23tHHA.4952@xxxxxxxxxxxxxxxxxxxxxxx
the different contract numbers that are in the combo box will always be
the same.
contract number work order number I want to generate
sequential (when I choose a contract number I want the next WO number
to come up)
A0009041E E100001, E100002, E100003, etc.
A0009041M M100001, M100002, M100003, etc.
A0009041N N100001, N100002, N100003, etc.
"Graham Mandeno" <Graham.Mandeno@xxxxxxxxxxxxx> wrote in message
news:4A8B673E-BD86-4613-A2C6-4773470E295F@xxxxxxxxxxxxxxxx
If I understand you correctly, this is just a formatting problem.
I gather the WO number is constructed from:
<last char of contract number> + "1"
+ <sequence number formatted to 5 digits with leading zeroes>
Is this correct?
In which case, your WO table just needs a field for the related
contract number (which I guess it has already) and a numeric field for
a sequence number. The WO number can be generated in a query using a
calculated field:
Right([ContractNum], 1) & "1" & Format([WOSeqNum], "00000")
Such a query can be used to populate a combo box, as well as to
display the WO number on forms and reports.
To get the next WOSeqNum for a new WO, look up the last one (if there
isn't one, use 0) and then add 1:
Me!WOSeqNum = Nz( DMax( "WOSeqNum", "tblMaintWO", _
"[WOContractNum]='" & Me![ContractNum] & "'" ), 0 ) + 1
The one thing that puzzles me is that it seems different contract
numbers with the same final letter will get the same work order
numbers. For example, A0009041E and A0009042E will both get WO numbers
E100001, E100002, E100003, etc. Is this OK?
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"jtidwell" <jtidwell@xxxxxxxxxxxxxxxx> wrote in message
news:ugYq$s2tHHA.3468@xxxxxxxxxxxxxxxxxxxxxxx
I am developing a Work Order Database for my job. I have a combo box
with "Contract Numbers" to select from. When you select on any
Contract Number I need a new "Work Order Number" to appear. There are
10 different contract numbers (A0009041N, A009041P) so I need 10
different work order numbers (N10001, P100001, etc..) to generate
sequential for each new record.
This is my layout.
tblcompanies
PK - CompanyID
tblcontractlist
PK - Contract NUmbers (this is where I have my combo box)
- Company ID
tblMaintWO
PK - MaintWorkorderID
- Contract Numbers
Right now I have one form (FrmMaintWO).
I want to select a contract number from the combo box. This should
automatically bring up a new work order number for that specific
contract number
example:
A0009041E - (Work Order Num) E100001, E100002, E100003
A0009041G - G100001, G100002
Also, I would like to see the Work Order Numbers stored in a Combo So
I will see the Work Orders that have already been entered
.
- Follow-Ups:
- Re: Generate Sequential numbers for new records
- From: John W . Vinson
- Re: Generate Sequential numbers for new records
- Prev by Date: Re: Searching Through ComboBoxes
- Next by Date: Re: Tabs
- Previous by thread: Re: Generate Sequential numbers for new records
- Next by thread: Re: Generate Sequential numbers for new records
- Index(es):
Relevant Pages
|
