Auto generate bid number in a form

[kathrynwoning via AccessMonster.com]

In this phase of our database I have created a form through which data is
entered for a bid proposal. After the data is entered a report prints that is
faxed to general contractors.

Prior to using Access we typed these bids in MS Word. Each bid was assigned a
bid number with this format: YYMMDD-1, -2, -3 etc. according to the number of
bids in a day. The bids are filed with architectural drawings etc according
to this number. We kept track of all these bids in Excel.

I have been able to format a bid number that auto generates according to this
expression:
=(Format([bidDate],"yymmdd-")) & (Format([bidNumbr],"00"))
However, I am getting duplicate bid numbers.

Bids are produced according to an invitation from General Contractors. They
have deadlines we call Bid Dates. Duplicates are created when I call a bid
date for the future. I select a date for the bid in the form and the above
expression generates a number. However, when that date actually rolls around
and I select that date it gives me -01 again. Naturally, this causes
difficulty when the general contractor comes back and refers to that number,
which is on more than one bid.

 

[kathrynwoning via AccessMonster.com]

Here's the question: Can anyone give me an expression or code that will
generate a unique bid number with this format: YYMMDD-00 etc. based on a date
entered on a form?

In this phase of our database I have created a form through which data is
entered for a bid proposal. After the data is entered a report prints that is
faxed to general contractors.

Prior to using Access we typed these bids in MS Word. Each bid was assigned a
bid number with this format: YYMMDD-1, -2, -3 etc. according to the number of
bids in a day. The bids are filed with architectural drawings etc according
to this number. We kept track of all these bids in Excel.

I have been able to format a bid number that auto generates according to this
expression:
=(Format([bidDate],"yymmdd-")) & (Format([bidNumbr],"00"))
However, I am getting duplicate bid numbers.

Bids are produced according to an invitation from General Contractors. They
have deadlines we call Bid Dates. Duplicates are created when I call a bid
date for the future. I select a date for the bid in the form and the above
expression generates a number. However, when that date actually rolls around
and I select that date it gives me -01 again. Naturally, this causes
difficulty when the general contractor comes back and refers to that number,
which is on more than one bid.

 

[John W. Vinson]

In this phase of our database I have created a form through which data is
entered for a bid proposal. After the data is entered a report prints that is
faxed to general contractors.

Prior to using Access we typed these bids in MS Word. Each bid was assigned a
bid number with this format: YYMMDD-1, -2, -3 etc. according to the number of
bids in a day. The bids are filed with architectural drawings etc according
to this number. We kept track of all these bids in Excel.

I have been able to format a bid number that auto generates according to this
expression:
=(Format([bidDate],"yymmdd-")) & (Format([bidNumbr],"00"))
However, I am getting duplicate bid numbers.

Bids are produced according to an invitation from General Contractors. They
have deadlines we call Bid Dates. Duplicates are created when I call a bid
date for the future. I select a date for the bid in the form and the above
expression generates a number. However, when that date actually rolls around
and I select that date it gives me -01 again. Naturally, this causes
difficulty when the general contractor comes back and refers to that number,
which is on more than one bid.

This kind of key is called an "Intelligent Key". Unfortunately that's not a
compliment! Storing two different kinds of data (a date and a sequential
number) in one field is really not good design. The only reason one would ever
want to do it is for consistancy with a long-established tradition which would
cause a staff rebellion were it to be broken.

Since that's likely the case...

please post your code. Somehow you're assigning a bid number; I suspect that
the code that does so should be using bidDate but it's actually using Date.
Your code should be using DMax() to find the maximum bid number for that
date... how are you in fact doing so?

 

[John W. Vinson]

Here's the question: Can anyone give me an expression or code that will
generate a unique bid number with this format: YYMMDD-00 etc. based on a date
entered on a form?

Me!BidNumbr = Format(NZ(DMax("[BidNumbr]", "[tablename]", "[BidDate] = #" &
NZ([Me!BidDate], Date()) & "#")))

 

[Steve Sanford]

I think I understand wht you want to do. So here goes....

The field bidnumbr does not (shoudl not) store "YYMMDD-00", it stores the
sequence (1,2,3,...) of the bids for that day. If you need "YYMMDD-00",
create it on-the-fly.

I didn't know your form name or the table name, so in the code you will have
to change the names.

Another thing you need to watch out for is if there is a day and a sequence
number, then someone changes the date that would create a duplicate
date-sequence bid number.

One way to prevent this (no code) would be to use both fields as the primary
key. Another way would be to use code to do the checks.

The code is in the form Before Update event. And it uses DAO. With the
warnings given, here is the code:

'--------code begin---------
Private Sub Form_BeforeUpdate(Cancel As Integer)
'!!!! uses DAO !!!!
Dim rst As DAO.Recordset
Dim strSQL As String

'in the following code,
'txtBidDate and txtbidNumbr are the
'names I used for the controls on
' the form I created

'don't change bid number if already filled
'** change "frmbids" to the name of your form
If Len(Nz(Forms!frmbids.txtbidNumbr, "")) = 0 Then

'change "tblBidProposal" to the name of your table
strSQL = "SELECT TOP 1 bidnumbr from tblBidProposal"

strSQL = strSQL & " WHERE biddate = #"
strSQL = strSQL & Forms!frmbids.txtBidDate & "#"
strSQL = strSQL & " ORDER BY bidnumbr DESC;"

Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.BOF And rst.EOF Then
'no bid number - first bid for this date
Forms!frmbids.txtbidNumbr = 1
Else
rst.MoveLast
rst.MoveFirst
'add 1 to the max bic number
Forms!frmbids.txtbidNumbr = rst.Fields(0) + 1
End If

'cleanup
rst.Close
Set rst = Nothing
End If
End Sub
'--------code end-----------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Here's the question: Can anyone give me an expression or code that will
generate a unique bid number with this format: YYMMDD-00 etc. based on a date
entered on a form?

In this phase of our database I have created a form through which data is
entered for a bid proposal. After the data is entered a report prints that is
faxed to general contractors.

Prior to using Access we typed these bids in MS Word. Each bid was assigned a
bid number with this format: YYMMDD-1, -2, -3 etc. according to the number of
bids in a day. The bids are filed with architectural drawings etc according
to this number. We kept track of all these bids in Excel.

I have been able to format a bid number that auto generates according to this
expression:
=(Format([bidDate],"yymmdd-")) & (Format([bidNumbr],"00"))
However, I am getting duplicate bid numbers.

Bids are produced according to an invitation from General Contractors. They
have deadlines we call Bid Dates. Duplicates are created when I call a bid
date for the future. I select a date for the bid in the form and the above
expression generates a number. However, when that date actually rolls around
and I select that date it gives me -01 again. Naturally, this causes
difficulty when the general contractor comes back and refers to that number,
which is on more than one bid.

 

[kathrynwoning via AccessMonster.com]

Right now in the control source I have the following format:

=(Format([bidDate],"yymmdd-")) & (Format([bidNumbr],"00"))

and there is a button which opens the form. It's code:

'Private Sub Command5_Click()
'On Error GoTo Err_Command5_Click

'Dim stDocName As String
'Dim stLinkCriteria As String

' stDocName = "frmBidsTabs"
' DoCmd.OpenForm stDocName, , , stLinkCriteria
' DoCmd.GoToRecord , , acNewRec
'Me.bidDate.SetFocus


Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmBidsMemo"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.GoToRecord , , acNewRec
'Me.BidDate.SetFocus

Dim dtmBidDate As Date
Dim intBidNumber As Integer
Dim varLastBid As Variant
varLastBid = DLookup("bidDate", "tblBids", "bidDate = #" & Date & "#")

If IsNull(varLastBid) Then
dtmBidDate = Date
intBidNumber = 1
Else
dtmBidDate = varLastBid
intBidNumber = DMax("bidNumbr", "tblBids", "bidDate = #" & Date & "#")
+ 1
End If

'Me.txtbidNumbr = Format(dtmBidDate, "yymmdd") & "-" & Format(intBidNumber,
"00")
'Me.bidNumbr = intBidNumber
'Me.bidNumbr = Nz(DMax("bidID", "tblBids", "bidDate"), 0) + 1
Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub

In this phase of our database I have created a form through which data is
entered for a bid proposal. After the data is entered a report prints that is

[quoted text clipped - 17 lines]

difficulty when the general contractor comes back and refers to that number,
which is on more than one bid.

This kind of key is called an "Intelligent Key". Unfortunately that's not a
compliment! Storing two different kinds of data (a date and a sequential
number) in one field is really not good design. The only reason one would ever
want to do it is for consistancy with a long-established tradition which would
cause a staff rebellion were it to be broken.

Since that's likely the case...

please post your code. Somehow you're assigning a bid number; I suspect that
the code that does so should be using bidDate but it's actually using Date.
Your code should be using DMax() to find the maximum bid number for that
date... how are you in fact doing so?

 

[John W. Vinson]

Right now in the control source I have the following format:

=(Format([bidDate],"yymmdd-")) & (Format([bidNumbr],"00"))

sorry... you *d8d* say that didn't you1 OK, so you do properly have two
fields.

and there is a button which opens the form. It's code:

'Private Sub Command5_Click()
'On Error GoTo Err_Command5_Click

'Dim stDocName As String
'Dim stLinkCriteria As String

' stDocName = "frmBidsTabs"
' DoCmd.OpenForm stDocName, , , stLinkCriteria
' DoCmd.GoToRecord , , acNewRec
'Me.bidDate.SetFocus

This is all commented out and duplicates the sub below - you can safely delete
the lines above.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmBidsMemo"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.GoToRecord , , acNewRec
'Me.BidDate.SetFocus

Dim dtmBidDate As Date
Dim intBidNumber As Integer
Dim varLastBid As Variant
varLastBid = DLookup("bidDate", "tblBids", "bidDate = #" & Date & "#")

If IsNull(varLastBid) Then
dtmBidDate = Date
intBidNumber = 1
Else
dtmBidDate = varLastBid
intBidNumber = DMax("bidNumbr", "tblBids", "bidDate = #" & Date & "#")
+ 1
End If

'Me.txtbidNumbr = Format(dtmBidDate, "yymmdd") & "-" & Format(intBidNumber,
"00")
'Me.bidNumbr = intBidNumber
'Me.bidNumbr = Nz(DMax("bidID", "tblBids", "bidDate"), 0) + 1
Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub

This assigns a new bid number *based on today's date* when you first open the
form, regardless of what's already in the table!! Much of this code needs to
be moved from the Command5_Click event to the Form's BeforeInsert event (and a
lot of the leftover commented out test code should probably be removed). I
presume that this code is in the form which *opens* the form where the bid
number is assigned, rather than on that form itself?? You have frmBidsMemo -
what other form is in play here?

 

[kathrynwoning via AccessMonster.com]

This code is in two places: frmStartup and frmBids. Both have buttons that
say New Bid and the code is On Click.

How does the scenario change if I move the code from a button?

[I have left the comments in until everything is final.... NO problem about
deleting them of course.]

Right now in the control source I have the following format:

=(Format([bidDate],"yymmdd-")) & (Format([bidNumbr],"00"))

sorry... you *d8d* say that didn't you1 OK, so you do properly have two
fields.

and there is a button which opens the form. It's code:

[quoted text clipped - 8 lines]

' DoCmd.GoToRecord , , acNewRec
'Me.bidDate.SetFocus

This is all commented out and duplicates the sub below - you can safely delete
the lines above.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

[quoted text clipped - 33 lines]

Resume Exit_Command5_Click
End Sub

This assigns a new bid number *based on today's date* when you first open the
form, regardless of what's already in the table!! Much of this code needs to
be moved from the Command5_Click event to the Form's BeforeInsert event (and a
lot of the leftover commented out test code should probably be removed). I
presume that this code is in the form which *opens* the form where the bid
number is assigned, rather than on that form itself?? You have frmBidsMemo -
what other form is in play here?

 

[John W. Vinson]

This code is in two places: frmStartup and frmBids. Both have buttons that
say New Bid and the code is On Click.

How does the scenario change if I move the code from a button?

[I have left the comments in until everything is final.... NO problem about
deleting them of course.]

Well.... there should be two separate functions for the two separate
operations. You would not typically want to increment the ID value on just
opening the form (just *open the form!*); and you wouldn't want to open the
form on creating a new record.

What are the real-life roles of these two forms? At what point do you want to
create a new record, with a new ID?

 

[kathrynwoning via AccessMonster.com]

Our company produces custom architectural woodwork and cabinetry for
commercial uses. For example at hospitals or schools. We bid to general
contractors who have solicited our bids for the purpose of being awarded a
job from an architect.

The form I am creating produces the bid proposal we fax to the GC. Currently
all these bids are formatted in Word. However, once we are awarded a bid all
the data from Word is transfered to Excel where price breakouts are created.
Ultimately in our current process data is duplicated at least 3 or 4 times.
And so the DB is being created to eleminate this.

As I have it right now here is the process: the Admin Asst. clicks a button
on a splash screen that opens frmBids. This brings up an empty form, however
to generate the bid number (which we use as a reference for the general
contractor who contacts us and to file all the drawings and other paper
materials) she must click another button: New Bid. [right now I see I don't
need that code on the first button] On average we generate as many as 12 bids
a day. Because that turns out to be a large number of bids the unique bid
number is important so we can keep data together. Frequently our bids are
filed for 2 to 3 months or longer before we receive approval, so tracking
them is vital.

frmBids incorporates financial data, contact information, and follow-up
information.

This code is in two places: frmStartup and frmBids. Both have buttons that
say New Bid and the code is On Click.

[quoted text clipped - 3 lines]

[I have left the comments in until everything is final.... NO problem about
deleting them of course.]

Well.... there should be two separate functions for the two separate
operations. You would not typically want to increment the ID value on just
opening the form (just *open the form!*); and you wouldn't want to open the
form on creating a new record.

What are the real-life roles of these two forms? At what point do you want to
create a new record, with a new ID?

 

[Bill MacKenzie]

I am trying to do something similar to auto generating a Bid number. I am trying to auto generate a project id that has the format of yyyymmdd-00 similar to the bid number that has been mentioned. The DAO code that you listed is on the before update event. My question is: Can I use the same code or similar code on the On Current event of the form? When I create a new record, I want the project id field in my table to auto generate. I also need the sequence number to reset to 01 when a new date occurs. I have tried my own code and can auto generate the project id and reset the sequence number to 1 when a new date occurs but cannot auto increment the sequence number. The sequence number just remains at 1. My table name is tblprojects with the following fields: project_date, project_id, sequence_num. Below is the code that I am currently using:
Private Sub Form_Current()
Dim prevDate As String
prevDate = " "
If Me.NewRecord Then
If prevDate <> Me.project_date Then
prevDate = Me.project_date
Me.project_sequencenum = 0
End If
Me.project_sequencenum = Me.project_sequencenum + 1
Me.project_id = Me.project_date & "-" & Me.project_sequencenum
Me.project_id = Format(Me.project_date, "yyyymmdd") & "-" & Format(Me.project_sequencenum, "00")
End If
End Sub
Let me know if there is an easy fix to my existing code or if you think I should use DAO.

Thanks!

 

[Arvin Meyer [MVP]]

Try:

Format(DMax("sequence_num", "tblprojects ", "project_date = Date()"), "00")

 

[Dale Fye]

Bill,

Personally, I prefer to store the project date and sequence number in
separate fields. It is much easier to manipulate data that is not
concatenated together.

The problem with doing this in the Current event, is that in a multi-user
environment (you don't indicate whether that is the case or not), the new
project_ID gets created when the new record gets created, but does not get
saved until you save the record. So another user who may be creating a new
project at the same time you are might get the same value. If you are not in
a multi-user environment, then this should not be a problem, but if you are,
it becomes an issue. Another issue is that doing this in the Current event
and testing for NewRecord will generate a new number, but if the user cancels
the new record, you may have gaps in your sequence numbers.

Generally, the way to get around this is to store the ProjectDate and
NextSeqNum in a table (I usually use my tbl_db_Parameters table, which is
located in the database backend). Then, when I need a new SeqNum, I run a
function like the following:

Public Function fnNextSeqNum(ProjectDate as Date) as integer

Dim strSQL as string
Dim rs as DAO.Recordset

strSQL = "SELECT ProjectDate, NextSeqNum " _
& "FROM tbl_db_Parameters WHERE ID = 1"
Set rs = currentdb.openrecordset (strsql,,dbfailonerror)
rs.Edit
if rs("ProjectDate") <> ProjectDate Then
rs("ProjectDate") = ProjectDate
rs("NextSeqNum") = 1
endif
fnNextSeqNum = rs("NextSeqNum")
rs("NextSeqNum") = rs("NextSeqNum") + 1
rs.Update
rs.close
set rs = nothing

End Function

This method almost guarantees that if two users are creating new projects at
the same time, the will get different sequence numbers, although this method
can also resort in gaps in sequence numbers if used during the Current event
instead of the BeforeUpdate event.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.