Custom Format the Bid Number Field

[cw via AccessMonster.com]

I know this type is question is constantly asked & searched for, but I have
had no luck with any of the other posts.

- I have the following field: bidNumbr (text type)
- which needs to be formatted like this: 071202-1
(where 07=Year, 12=Month, 02=Day and 1=Increment by bid per day)
- This field will be auto-filled by Access for each new Bid entered.

Currently, I have the following code in the bidNumbr Default Value field
=Format(Date(),"yymmdd") & "-" (This works fine to properly create the Date
part of the field, but not the Auto-Incrementing value after the Dash..?)

Any ideas on how to:
1) Have Access look at the Bids already on file for today in tblBids
2) And Auto-Fill the bidNumbr field with the properly formatted yymmdd -1, -2,
-3, etc value?

Here are some examples:
071202-1 (If this is the first Bid entered today)
071202-2 (If this is the second Bid entered today)
071202-3 (If this is the third Bid entered today)
071203-1 (If this is the second Bid entered next day)

Thanks,
cw

 

[Klatuu]

The better approach would be to carry the date in a date filed and the number
in an integer field and format the output. Here is a routine that would work
for that.

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

If IsNull(varLastBid) Then
dtmBidDate = Date
intBidNumber = 1
Else
dtmBidDate = varLastBid
intBidNumber = DMax("BidNumber","BidTable", "BidDate = #" & Date &
"#") + 1
End If

Me.txtBidNUmber = Format(dtmBidDAte, "yymmdd-") & Format(intBidNumber,
"00")

 

[cw via AccessMonster.com]

Klatuu, Thanks,..Let me think this thru..

- I do have a field in the database already called bidDate which is set to
Default: =Now()

- Are you saying to create a new Integer field intBidNumber in my tblBids? ..
good idea!

- And then this field gets assigned it's value from the intBidNumber = DMax..
section.. right?

- Lastly, your code below would be called using my NewBid button. right?

The better approach would be to carry the date in a date filed and the number
in an integer field and format the output. Here is a routine that would work
for that.

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

If IsNull(varLastBid) Then
dtmBidDate = Date
intBidNumber = 1
Else
dtmBidDate = varLastBid
intBidNumber = DMax("BidNumber","BidTable", "BidDate = #" & Date &
"#") + 1
End If

Me.txtBidNUmber = Format(dtmBidDAte, "yymmdd-") & Format(intBidNumber,
"00")

I know this type is question is constantly asked & searched for, but I have
had no luck with any of the other posts.

[quoted text clipped - 21 lines]

Thanks,
cw

 

[Klatuu]

See responses below:
--
Dave Hargis, Microsoft Access MVP

Klatuu, Thanks,..Let me think this thru..

- I do have a field in the database already called bidDate which is set to
Default: =Now()

Good, but I would suggest using Date instead of Now. Now contains a time
component and when you try to filter on a date field with the time in it, you
may not get the results you want.

- Are you saying to create a new Integer field intBidNumber in my tblBids? ..
good idea!

Yes, except I would not call it intBidNumber as a field name, I would just
use BidNumber. That is the way the code is structured.

- And then this field gets assigned it's value from the intBidNumber = DMax..
section.. right?

- Lastly, your code below would be called using my NewBid button. right? That is correct.

The better approach would be to carry the date in a date filed and the number
in an integer field and format the output. Here is a routine that would work
for that.

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

If IsNull(varLastBid) Then
dtmBidDate = Date
intBidNumber = 1
Else
dtmBidDate = varLastBid
intBidNumber = DMax("BidNumber","BidTable", "BidDate = #" & Date &
"#") + 1
End If

Me.txtBidNUmber = Format(dtmBidDAte, "yymmdd-") & Format(intBidNumber,
"00")

I know this type is question is constantly asked & searched for, but I have
had no luck with any of the other posts.

[quoted text clipped - 21 lines]

Thanks,
cw

 

[cw via AccessMonster.com]

Klatuu , I have not been able to get this to work.
Below is the code I tried last, which gives no errors and leaves the
txtbidNumbr field on my Form blank? (I have made the Record Source for
txtbidNumbr = bidNumbr
------------------------------------------------------------------------------
--------
Private Sub Command116_Click()
On Error GoTo Err_Command116_Click

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")

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click
End Sub
------------------------------------------------------------------------------
--

Here is a list of my fields in the tblBids table which are listed on my Main
Form:
-------------------------
Field Name:
----------------
bidID
bidNumbr
bidDate
bidType
bidTo
bidCity
bidSt
bidJobName
bidDescPro
bidRmNbID
bidTotal
bidOther1
bidOther2
bidOther3
bidNote
bidEstimator
bidArchitect
bidFaxDate
dtmAdded
strModifiedBy
dtmModified

Any other ideas?
Thanks,
cw

See responses below:

Klatuu, Thanks,..Let me think this thru..

- I do have a field in the database already called bidDate which is set to
Default: =Now()

Good, but I would suggest using Date instead of Now. Now contains a time
component and when you try to filter on a date field with the time in it, you
may not get the results you want.

- Are you saying to create a new Integer field intBidNumber in my tblBids? ..
good idea!

Yes, except I would not call it intBidNumber as a field name, I would just
use BidNumber. That is the way the code is structured.

- And then this field gets assigned it's value from the intBidNumber = DMax..
section.. right?

- Lastly, your code below would be called using my NewBid button. right?

That is correct.

The better approach would be to carry the date in a date filed and the number
in an integer field and format the output. Here is a routine that would work [quoted text clipped - 22 lines]
Thanks,
cw

 

[cw via AccessMonster.com]

By the way, I have placed a Text Box on my Form:
Name: txtbidNumbr
Record Source: bidNumbr

Klatuu , I have not been able to get this to work.
Below is the code I tried last, which gives no errors and leaves the
txtbidNumbr field on my Form blank? (I have made the Record Source for
txtbidNumbr = bidNumbr
------------------------------------------------------------------------------
--------
Private Sub Command116_Click()
On Error GoTo Err_Command116_Click

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")

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click
End Sub
------------------------------------------------------------------------------
--

Here is a list of my fields in the tblBids table which are listed on my Main
Form:
-------------------------
Field Name:
----------------
bidID
bidNumbr
bidDate
bidType
bidTo
bidCity
bidSt
bidJobName
bidDescPro
bidRmNbID
bidTotal
bidOther1
bidOther2
bidOther3
bidNote
bidEstimator
bidArchitect
bidFaxDate
dtmAdded
strModifiedBy
dtmModified

Any other ideas?
Thanks,
cw

See responses below:

Klatuu, Thanks,..Let me think this thru.. [quoted text clipped - 23 lines]
Thanks,
cw

 

[Klatuu]

Run the code in debug mode and step through each line to see what is
happening. The code looks okay, but obviously, something is not correct.
--
Dave Hargis, Microsoft Access MVP

By the way, I have placed a Text Box on my Form:
Name: txtbidNumbr
Record Source: bidNumbr

Klatuu , I have not been able to get this to work.
Below is the code I tried last, which gives no errors and leaves the
txtbidNumbr field on my Form blank? (I have made the Record Source for
txtbidNumbr = bidNumbr
------------------------------------------------------------------------------
--------
Private Sub Command116_Click()
On Error GoTo Err_Command116_Click

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")

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click
End Sub
------------------------------------------------------------------------------
--

Here is a list of my fields in the tblBids table which are listed on my Main
Form:
-------------------------
Field Name:
----------------
bidID
bidNumbr
bidDate
bidType
bidTo
bidCity
bidSt
bidJobName
bidDescPro
bidRmNbID
bidTotal
bidOther1
bidOther2
bidOther3
bidNote
bidEstimator
bidArchitect
bidFaxDate
dtmAdded
strModifiedBy
dtmModified

Any other ideas?
Thanks,
cw

See responses below:
Klatuu, Thanks,..Let me think this thru..

[quoted text clipped - 23 lines]

Thanks,
cw

 

[cw via AccessMonster.com]

- Getting "The Value you entered isn't valid for this field" when running the
New Record button/code
- Debug shows the following:

varLastBid = 12-04-2007 (This is correct)
intBidNumber = 20 (This is correct)
Me.txtbidNumbr = 1 (This is not correct..?)

Thanks,
cw

Run the code in debug mode and step through each line to see what is
happening. The code looks okay, but obviously, something is not correct.

By the way, I have placed a Text Box on my Form:
Name: txtbidNumbr

[quoted text clipped - 74 lines]

 

[Klatuu]

It may be that txtBidNumber is bound to the wrong field. To show the
combined bid number (date + number) should be in an unbound field. You could
have two hidden controls, one for the date and one for the number. Then you
can use the control source of the combined bidnumber as

=(txtBidDAte, "yymmdd-") & Format(txtBidNumber, "00")

Where txtBidDate is bound to the date field and txtBidNumber is bound to the
bid number field.



--
Dave Hargis, Microsoft Access MVP

- Getting "The Value you entered isn't valid for this field" when running the
New Record button/code
- Debug shows the following:

varLastBid = 12-04-2007 (This is correct)
intBidNumber = 20 (This is correct)
Me.txtbidNumbr = 1 (This is not correct..?)

Thanks,
cw

Run the code in debug mode and step through each line to see what is
happening. The code looks okay, but obviously, something is not correct.

By the way, I have placed a Text Box on my Form:
Name: txtbidNumbr

[quoted text clipped - 74 lines]

Thanks,
cw

 

[cw via AccessMonster.com]

I found the trouble! But not sure exactly what to do:
Me.txtbidNumbr = 071204-20 (This is now correct!)

I had to change the Record Source of the Text Box to another text field in
the table to get it to work.
I do not see that my bidNumbr (Integer) field getting updated in the Table?

Almost there..
Thanks,
cw

- Getting "The Value you entered isn't valid for this field" when running the
New Record button/code
- Debug shows the following:

varLastBid = 12-04-2007 (This is correct)
intBidNumber = 20 (This is correct)
Me.txtbidNumbr = 1 (This is not correct..?)

Thanks,
cw

Run the code in debug mode and step through each line to see what is
happening. The code looks okay, but obviously, something is not correct.

[quoted text clipped - 3 lines]

 

[Klatuu]

The bidNumber(integer) needs to be bound to a a control on your form. See my
previous post for more info.
--
Dave Hargis, Microsoft Access MVP

I found the trouble! But not sure exactly what to do:
Me.txtbidNumbr = 071204-20 (This is now correct!)

I had to change the Record Source of the Text Box to another text field in
the table to get it to work.
I do not see that my bidNumbr (Integer) field getting updated in the Table?

Almost there..
Thanks,
cw

- Getting "The Value you entered isn't valid for this field" when running the
New Record button/code
- Debug shows the following:

varLastBid = 12-04-2007 (This is correct)
intBidNumber = 20 (This is correct)
Me.txtbidNumbr = 1 (This is not correct..?)

Thanks,
cw

Run the code in debug mode and step through each line to see what is
happening. The code looks okay, but obviously, something is not correct.

[quoted text clipped - 3 lines]

Thanks,
cw

 

[cw via AccessMonster.com]

I have this working except that the bidNumbr field will not update in my
Table?
- I have it listed as a regular field on my Form
- It's Record Source is set to : bidNumbr

What is the mechanism that pulls the Dmax value from the intBidNumber
formula/equation & places it in the bidNumbr database field for that record?

Thanks for the help.
cw

The bidNumber(integer) needs to be bound to a a control on your form. See my
previous post for more info.

I found the trouble! But not sure exactly what to do:
Me.txtbidNumbr = 071204-20 (This is now correct!)

[quoted text clipped - 23 lines]

 

[cw via AccessMonster.com]

This is now working fine!
I added the second line below to the code:

Me.txtbidNumbr = Format(dtmBidDate, "yymmdd") & "-" & Format(intBidNumber,
"00")
Me.bidNumbr = intBidNumber '.....added this to make it save the
bidNumbr..

Lastly, How do I force Access to Save the Date without Saving the Time
portion with it?
- I have the bidDate set to: Date/Time, with Short Date as the Format &
Default =Date()

Thanks again,
cw

I have this working except that the bidNumbr field will not update in my
Table?
- I have it listed as a regular field on my Form
- It's Record Source is set to : bidNumbr

What is the mechanism that pulls the Dmax value from the intBidNumber
formula/equation & places it in the bidNumbr database field for that record?

Thanks for the help.
cw

The bidNumber(integer) needs to be bound to a a control on your form. See my
previous post for more info.

[quoted text clipped - 3 lines]

 

[Klatuu]

I guess that is not in the code. You will need to add to the current code to
populate that text box.
--
Dave Hargis, Microsoft Access MVP

I have this working except that the bidNumbr field will not update in my
Table?
- I have it listed as a regular field on my Form
- It's Record Source is set to : bidNumbr

What is the mechanism that pulls the Dmax value from the intBidNumber
formula/equation & places it in the bidNumbr database field for that record?

Thanks for the help.
cw

The bidNumber(integer) needs to be bound to a a control on your form. See my
previous post for more info.

I found the trouble! But not sure exactly what to do:
Me.txtbidNumbr = 071204-20 (This is now correct!)

[quoted text clipped - 23 lines]

Thanks,
cw

 

[cw via AccessMonster.com]

Thanks, I added it and everything is working great...just this last little
issue!

No matter what I do to the Table or Form fields, The bidDate is always saving
as: 12-4-2007 12:51:52 PM (including that time portion)
which is causing the Dmax function to fail, and the intBidNumber does not
increment?

I guess that is not in the code. You will need to add to the current code to
populate that text box.

I have this working except that the bidNumbr field will not update in my
Table?

[quoted text clipped - 14 lines]

 

[Klatuu]

Take the time out. It will only cause problem. Use Date instead of Now.
--
Dave Hargis, Microsoft Access MVP

Thanks, I added it and everything is working great...just this last little
issue!

No matter what I do to the Table or Form fields, The bidDate is always saving
as: 12-4-2007 12:51:52 PM (including that time portion)
which is causing the Dmax function to fail, and the intBidNumber does not
increment?

I guess that is not in the code. You will need to add to the current code to
populate that text box.

I have this working except that the bidNumbr field will not update in my
Table?

[quoted text clipped - 14 lines]

Thanks,
cw

 

[cw via AccessMonster.com]

I'll keep trying..have already changed it to Date(), but it still saves with
the time.

Thanks for all your help, Klatuu

Take the time out. It will only cause problem. Use Date instead of Now.

Thanks, I added it and everything is working great...just this last little
issue!

[quoted text clipped - 11 lines]

 

[Klatuu]

It the time is being included, it means that where ever you populate the
field it is using Now instead of Date. Now includes the time, Date does not.
--
Dave Hargis, Microsoft Access MVP

I'll keep trying..have already changed it to Date(), but it still saves with
the time.

Thanks for all your help, Klatuu

Take the time out. It will only cause problem. Use Date instead of Now.

Thanks, I added it and everything is working great...just this last little
issue!

[quoted text clipped - 11 lines]

Thanks,
cw

 

[cw via AccessMonster.com]

Klatuu, Please accept my apologies for being blind!
Just as you suggested, I had the bidDate on my Form Formatted for: Default
Value =Now()

Everything is now working just fine & your code is an excellent suggestion.
Thanks again,
cw

It the time is being included, it means that where ever you populate the
field it is using Now instead of Date. Now includes the time, Date does not.

I'll keep trying..have already changed it to Date(), but it still saves with
the time.

[quoted text clipped - 7 lines]