Assigning a Sequential Number

C

ckrogers

Hi. Just FYI -- I'm fairly new to VB programming.... I usually copy code
that is working and make changes from there....

I'm trying to automatically assign a quote number with the following format:
(yyyymm-xxxx) where "xxxx" is a sequential number to be re-set annually.
So, the numbers will go from 200601-0001 to 200612-9999.

Here's the code I'm using:

Dim varNextNum As Variant
Dim strQuotePrefix As String
Dim strQuoteYear As String

strQuoteYear = Format(Date, "yyyy")
strQuotePrefix = Format(Date, "yyyymm") & "-"

varNextNum = DMax("[Quote_Number]", "tbl_Quote_Information", _
Left([Quote_Number], 4) = "'" & strQuoteYear & "'")
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 4) + 1)
Me.[PRP_Quote_No] = strQuotePrefix & Format(varNextNum, "0000")

I'm getting the error "Run-time error '2465': Microsoft Access can't find
the field '|' referred to in your expression." The Debug is highlighting the
two lines starting with "varNextNum = DMax".

I definitely have the field "Quote_Number" in the table
"tbl_Quote_Information". I am trying to fill in the field "PRP_Quote_No" in
a different table....

Can anyone help with what I'm doing wrong?

Thanks!

Cindy
 
D

David F Cox

I have never used the DMAX function but help tells me that

Left([Quote_Number], 4) = "'" & strQuoteYear & "'"

should all be within quotes, something like:

"Left([Quote_Number], 4) = chr(34) & strQuoteYear & chr(34)"

if I am wrong we will learn together.

ckrogers said:
Hi. Just FYI -- I'm fairly new to VB programming.... I usually copy code
that is working and make changes from there....

I'm trying to automatically assign a quote number with the following
format:
(yyyymm-xxxx) where "xxxx" is a sequential number to be re-set annually.
So, the numbers will go from 200601-0001 to 200612-9999.

Here's the code I'm using:

Dim varNextNum As Variant
Dim strQuotePrefix As String
Dim strQuoteYear As String

strQuoteYear = Format(Date, "yyyy")
strQuotePrefix = Format(Date, "yyyymm") & "-"

varNextNum = DMax("[Quote_Number]", "tbl_Quote_Information", _
Left([Quote_Number], 4) = "'" & strQuoteYear & "'")
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 4) + 1)
Me.[PRP_Quote_No] = strQuotePrefix & Format(varNextNum, "0000")

I'm getting the error "Run-time error '2465': Microsoft Access can't find
the field '|' referred to in your expression." The Debug is highlighting
the
two lines starting with "varNextNum = DMax".

I definitely have the field "Quote_Number" in the table
"tbl_Quote_Information". I am trying to fill in the field "PRP_Quote_No"
in
a different table....

Can anyone help with what I'm doing wrong?

Thanks!

Cindy
Click to expand...
 
C

ckrogers

Thanks for responding, David, but THIS code is working in a different database:

varNextNum = DMax("[LogNumber]", "8D Header", _
Left([LogNumber], 9) = "'" & strLogPrefix & "'")

....so I don't think that's my problem.

David F Cox said:
I have never used the DMAX function but help tells me that

Left([Quote_Number], 4) = "'" & strQuoteYear & "'"

should all be within quotes, something like:

"Left([Quote_Number], 4) = chr(34) & strQuoteYear & chr(34)"

if I am wrong we will learn together.

ckrogers said:
Hi. Just FYI -- I'm fairly new to VB programming.... I usually copy code
that is working and make changes from there....

I'm trying to automatically assign a quote number with the following
format:
(yyyymm-xxxx) where "xxxx" is a sequential number to be re-set annually.
So, the numbers will go from 200601-0001 to 200612-9999.

Here's the code I'm using:

Dim varNextNum As Variant
Dim strQuotePrefix As String
Dim strQuoteYear As String

strQuoteYear = Format(Date, "yyyy")
strQuotePrefix = Format(Date, "yyyymm") & "-"

varNextNum = DMax("[Quote_Number]", "tbl_Quote_Information", _
Left([Quote_Number], 4) = "'" & strQuoteYear & "'")
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 4) + 1)
Me.[PRP_Quote_No] = strQuotePrefix & Format(varNextNum, "0000")

I'm getting the error "Run-time error '2465': Microsoft Access can't find
the field '|' referred to in your expression." The Debug is highlighting
the
two lines starting with "varNextNum = DMax".

I definitely have the field "Quote_Number" in the table
"tbl_Quote_Information". I am trying to fill in the field "PRP_Quote_No"
in
a different table....

Can anyone help with what I'm doing wrong?

Thanks!

Cindy
Click to expand...
Click to expand...
 
J

John Spencer

Dim varNextNum As Variant
Dim strQuotePrefix As String
Dim strQuoteYear As String

strQuoteYear = Format(Date, "yyyy")
strQuotePrefix = Format(Date, "yyyymm") & "-"

varNextNum = DMax("Quote_Number", "tbl_Quote_Information", _
"Left([Quote_Number], 4) = '" & strQuoteYear & "'")

'Alternative method
varNextNum = DMax("Quote_Number", "tbl_Quote_Information", _
"Quote_Number Like '" & strQuoteYear & "*'")

varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 4) + 1)

Me.[PRP_Quote_No] = strQuotePrefix & Format(varNextNum, "0000")

I: think that will work for you.

ckrogers said:
Hi. Just FYI -- I'm fairly new to VB programming.... I usually copy code
that is working and make changes from there....

I'm trying to automatically assign a quote number with the following
format:
(yyyymm-xxxx) where "xxxx" is a sequential number to be re-set annually.
So, the numbers will go from 200601-0001 to 200612-9999.

Here's the code I'm using:

Dim varNextNum As Variant
Dim strQuotePrefix As String
Dim strQuoteYear As String

strQuoteYear = Format(Date, "yyyy")
strQuotePrefix = Format(Date, "yyyymm") & "-"

varNextNum = DMax("[Quote_Number]", "tbl_Quote_Information", _
Left([Quote_Number], 4) = "'" & strQuoteYear & "'")
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 4) + 1)
Me.[PRP_Quote_No] = strQuotePrefix & Format(varNextNum, "0000")

I'm getting the error "Run-time error '2465': Microsoft Access can't find
the field '|' referred to in your expression." The Debug is highlighting
the
two lines starting with "varNextNum = DMax".

I definitely have the field "Quote_Number" in the table
"tbl_Quote_Information". I am trying to fill in the field "PRP_Quote_No"
in
a different table....

Can anyone help with what I'm doing wrong?

Thanks!

Cindy
Click to expand...
 
C

ckrogers

That worked! Thank you go much!
Cindy

John Spencer said:
Dim varNextNum As Variant
Dim strQuotePrefix As String
Dim strQuoteYear As String

strQuoteYear = Format(Date, "yyyy")
strQuotePrefix = Format(Date, "yyyymm") & "-"

varNextNum = DMax("Quote_Number", "tbl_Quote_Information", _
"Left([Quote_Number], 4) = '" & strQuoteYear & "'")

'Alternative method
varNextNum = DMax("Quote_Number", "tbl_Quote_Information", _
"Quote_Number Like '" & strQuoteYear & "*'")

varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 4) + 1)

Me.[PRP_Quote_No] = strQuotePrefix & Format(varNextNum, "0000")

I: think that will work for you.

ckrogers said:
Hi. Just FYI -- I'm fairly new to VB programming.... I usually copy code
that is working and make changes from there....

I'm trying to automatically assign a quote number with the following
format:
(yyyymm-xxxx) where "xxxx" is a sequential number to be re-set annually.
So, the numbers will go from 200601-0001 to 200612-9999.

Here's the code I'm using:

Dim varNextNum As Variant
Dim strQuotePrefix As String
Dim strQuoteYear As String

strQuoteYear = Format(Date, "yyyy")
strQuotePrefix = Format(Date, "yyyymm") & "-"

varNextNum = DMax("[Quote_Number]", "tbl_Quote_Information", _
Left([Quote_Number], 4) = "'" & strQuoteYear & "'")
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 4) + 1)
Me.[PRP_Quote_No] = strQuotePrefix & Format(varNextNum, "0000")

I'm getting the error "Run-time error '2465': Microsoft Access can't find
the field '|' referred to in your expression." The Debug is highlighting
the
two lines starting with "varNextNum = DMax".

I definitely have the field "Quote_Number" in the table
"tbl_Quote_Information". I am trying to fill in the field "PRP_Quote_No"
in
a different table....

Can anyone help with what I'm doing wrong?

Thanks!

Cindy
Click to expand...
Click to expand...
 
C

ckrogers

That worked ... thank you so much!
Cindy

John Spencer said:
Dim varNextNum As Variant
Dim strQuotePrefix As String
Dim strQuoteYear As String

strQuoteYear = Format(Date, "yyyy")
strQuotePrefix = Format(Date, "yyyymm") & "-"

varNextNum = DMax("Quote_Number", "tbl_Quote_Information", _
"Left([Quote_Number], 4) = '" & strQuoteYear & "'")

'Alternative method
varNextNum = DMax("Quote_Number", "tbl_Quote_Information", _
"Quote_Number Like '" & strQuoteYear & "*'")

varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 4) + 1)

Me.[PRP_Quote_No] = strQuotePrefix & Format(varNextNum, "0000")

I: think that will work for you.

ckrogers said:
Hi. Just FYI -- I'm fairly new to VB programming.... I usually copy code
that is working and make changes from there....

I'm trying to automatically assign a quote number with the following
format:
(yyyymm-xxxx) where "xxxx" is a sequential number to be re-set annually.
So, the numbers will go from 200601-0001 to 200612-9999.

Here's the code I'm using:

Dim varNextNum As Variant
Dim strQuotePrefix As String
Dim strQuoteYear As String

strQuoteYear = Format(Date, "yyyy")
strQuotePrefix = Format(Date, "yyyymm") & "-"

varNextNum = DMax("[Quote_Number]", "tbl_Quote_Information", _
Left([Quote_Number], 4) = "'" & strQuoteYear & "'")
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 4) + 1)
Me.[PRP_Quote_No] = strQuotePrefix & Format(varNextNum, "0000")

I'm getting the error "Run-time error '2465': Microsoft Access can't find
the field '|' referred to in your expression." The Debug is highlighting
the
two lines starting with "varNextNum = DMax".

I definitely have the field "Quote_Number" in the table
"tbl_Quote_Information". I am trying to fill in the field "PRP_Quote_No"
in
a different table....

Can anyone help with what I'm doing wrong?

Thanks!

Cindy
Click to expand...
Click to expand...
 
Top