Multiple Autonumbers To Select From On Form

[Guest]

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Jeff Boyce]

The term "autonumber", in this context, is a bit of a misnomer. The Access
"Autonumber" data type is generated by Access, and is intended for use as a
unique row identifier. As such, it isn't generally "fit for human
consumption".

It sounds like you want your application to have custom-generated sequence
numbers. You can learn more about this by checking at Google.com or
mvps.org for "Custom Autonumbers" (yes, yes, I know, they used the "A" word
.... but they aren't really).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

 

[Guest]

I following your directions, and have placed the code in the after update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or what this
error means.

Thanks again.

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Guest]

It is a syntax problem. This is very common when you need to imbed quote
marks in a string. The easiest solution is to change the internal quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")

I following your directions, and have placed the code in the after update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or what this
error means.

Thanks again.

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Guest]

I am still getting a syntax error message, even with the single quotes by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " & Me.cboDept &
" ' "), 24999) + 1

It is a syntax problem. This is very common when you need to imbed quote
marks in a string. The easiest solution is to change the internal quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")

I following your directions, and have placed the code in the after update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or what this
error means.

Thanks again.

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Guest]

I don't know exactly what syntax problem you are getting, but it appears to
be correct except for one thing. You are putting spaces before and after the
value you are using a the criteria. Even if you were not getting the error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

I am still getting a syntax error message, even with the single quotes by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " & Me.cboDept &
" ' "), 24999) + 1

It is a syntax problem. This is very common when you need to imbed quote
marks in a string. The easiest solution is to change the internal quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")

I following your directions, and have placed the code in the after update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Guest]

I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

End Sub

I don't know exactly what syntax problem you are getting, but it appears to
be correct except for one thing. You are putting spaces before and after the
value you are using a the criteria. Even if you were not getting the error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

I am still getting a syntax error message, even with the single quotes by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " & Me.cboDept &
" ' "), 24999) + 1

It is a syntax problem. This is very common when you need to imbed quote
marks in a string. The easiest solution is to change the internal quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in the after update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Guest]

Sorry, I don't see a syntax problem. It could be a reference issue. perhaps.
Try entering the expression in the immediate window and working with it
until you can determine the error.

I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

End Sub

I don't know exactly what syntax problem you are getting, but it appears to
be correct except for one thing. You are putting spaces before and after the
value you are using a the criteria. Even if you were not getting the error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

I am still getting a syntax error message, even with the single quotes by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " & Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you need to imbed quote
marks in a string. The easiest solution is to change the internal quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in the after update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Douglas J. Steele]

Sorry if I'm stating the obvious, but you do have that all on one line I
hope. (In other words, what starts Me.txtReqSeq and ends +1)

The following shouldn't fall victim to word-wrap, and shows how to use
continuation characters:

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq", _
"Departments","DeptID = '" & _
Me.cboDept & "'"), 24999) + 1

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

End Sub

I don't know exactly what syntax problem you are getting, but it appears to
be correct except for one thing. You are putting spaces before and after the
value you are using a the criteria. Even if you were not getting the error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

I am still getting a syntax error message, even with the single quotes by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " & Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you need to imbed quote
marks in a string. The easiest solution is to change the internal quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in the after update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Guest]

Ok, I got it to work now,

But...When I tested it and for example, the first record is department MRN
and it gives number 25000, the next record/requisition is for department BRN
and it gives number 25000, if the following record is again MRN, it gives me
the same number (25000) and not the next increment which should be 25001
which is what I require this form to do.

Is this possible?

Thanks again.

Sorry, I don't see a syntax problem. It could be a reference issue. perhaps.
Try entering the expression in the immediate window and working with it
until you can determine the error.

I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

End Sub

I don't know exactly what syntax problem you are getting, but it appears to
be correct except for one thing. You are putting spaces before and after the
value you are using a the criteria. Even if you were not getting the error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

:

I am still getting a syntax error message, even with the single quotes by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " & Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you need to imbed quote
marks in a string. The easiest solution is to change the internal quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in the after update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Guest]

Yes, everything is on one line.

I got it to work now,

But...When I tested it and for example, the first record is department MRN
and it gives number 25000, the next record/requisition is for department BRN
and it gives number 25000, if the following record is again MRN, it gives me
the same number (25000) and not the next increment which should be 25001
which is what I require this form to do.

Is this possible?

Thank you.

Sorry if I'm stating the obvious, but you do have that all on one line I
hope. (In other words, what starts Me.txtReqSeq and ends +1)

The following shouldn't fall victim to word-wrap, and shows how to use
continuation characters:

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq", _
"Departments","DeptID = '" & _
Me.cboDept & "'"), 24999) + 1

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

End Sub

I don't know exactly what syntax problem you are getting, but it appears to
be correct except for one thing. You are putting spaces before and after the
value you are using a the criteria. Even if you were not getting the error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

:

I am still getting a syntax error message, even with the single quotes by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " & Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you need to imbed quote
marks in a string. The easiest solution is to change the internal quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in the after update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to select the
[AREA_ID]. I would suggest a combo box that will contain all the
abbreviations for the areas. Then, in the After Update event of the combo
box you will find the highest current number for the area and add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] = '" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that several areas will be
using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one single form,
but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Douglas J. Steele]

Do you in fact have a row in table "Departments" with ReqSeq equal to 25000
and the appropriate value for DeptID?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, everything is on one line.

I got it to work now,

But...When I tested it and for example, the first record is department MRN
and it gives number 25000, the next record/requisition is for department BRN
and it gives number 25000, if the following record is again MRN, it gives me
the same number (25000) and not the next increment which should be 25001
which is what I require this form to do.

Is this possible?

Thank you.

Sorry if I'm stating the obvious, but you do have that all on one line I
hope. (In other words, what starts Me.txtReqSeq and ends +1)

The following shouldn't fall victim to word-wrap, and shows how to use
continuation characters:

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq", _
"Departments","DeptID = '" & _
Me.cboDept & "'"), 24999) + 1

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

End Sub


:

I don't know exactly what syntax problem you are getting, but it

appears
to

be correct except for one thing. You are putting spaces before and after the
value you are using a the criteria. Even if you were not getting the error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &

Me.cboDept
&

"'"), 24999) + 1

:

I am still getting a syntax error message, even with the single

quotes
by

entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " & Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you need to

imbed
quote

marks in a string. The easiest solution is to change the

internal
quotes to

single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in the

after
update as

follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" & Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why

or
what this

error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of

the
two, in the

order desdcribed above.

Now when you want to assign an new number, the user will

have to
select the

[AREA_ID]. I would suggest a combo box that will contain

all
the

abbreviations for the areas. Then, in the After Update

event of
the combo

box you will find the highest current number for the area

and
add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable",

"[AREA_ID] =
'" &

Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that

several
areas will be

using.

They want each area to be identified by abbreviations i.e. BRN, FRN, etc.
but they want each area to have their own autonumbers and starting at the
same number, i.e. 25000 for each area (Each area will have their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on

one
single form,

but only want the option to choose one of these for each requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Guest]

The DeptID is text (an abbreviation of each dept) and a number (long integer)
field for the ReqSeq field.

I did place the value 24999 for each dept in the table. Both the DeptID and
the ReqSeq field are primary keys.

Do you in fact have a row in table "Departments" with ReqSeq equal to 25000
and the appropriate value for DeptID?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, everything is on one line.

I got it to work now,

But...When I tested it and for example, the first record is department MRN
and it gives number 25000, the next record/requisition is for department BRN
and it gives number 25000, if the following record is again MRN, it gives me
the same number (25000) and not the next increment which should be 25001
which is what I require this form to do.

Is this possible?

Thank you.

Sorry if I'm stating the obvious, but you do have that all on one line I
hope. (In other words, what starts Me.txtReqSeq and ends +1)

The following shouldn't fall victim to word-wrap, and shows how to use
continuation characters:

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq", _
"Departments","DeptID = '" & _
Me.cboDept & "'"), 24999) + 1

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

End Sub


:

I don't know exactly what syntax problem you are getting, but it appears
to
be correct except for one thing. You are putting spaces before and
after the
value you are using a the criteria. Even if you were not getting the
error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept
&
"'"), 24999) + 1

:

I am still getting a syntax error message, even with the single quotes
by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " &
Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you need to imbed
quote
marks in a string. The easiest solution is to change the internal
quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" &
Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in the after
update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" &
Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why or
what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of the
two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to
select the
[AREA_ID]. I would suggest a combo box that will contain all
the
abbreviations for the areas. Then, in the After Update event of
the combo
box you will find the highest current number for the area and
add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] =
'" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that several
areas will be
using.

They want each area to be identified by abbreviations i.e.
BRN, FRN, etc.
but they want each area to have their own autonumbers and
starting at the
same number, i.e. 25000 for each area (Each area will have
their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on one
single form,
but only want the option to choose one of these for each
requisition.

How does one go about doing this and is it possibel to do so?

Thank you.

 

[Douglas J. Steele]

You're computing a value for Me.txtReqSeq: what is that used for? Does it
update the appropriate row in the Departments table, or does it simply get
used in some other table? Unless the Departments table is updated, it'll
never give you any other possible values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The DeptID is text (an abbreviation of each dept) and a number (long integer)
field for the ReqSeq field.

I did place the value 24999 for each dept in the table. Both the DeptID and
the ReqSeq field are primary keys.

Do you in fact have a row in table "Departments" with ReqSeq equal to 25000
and the appropriate value for DeptID?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, everything is on one line.

I got it to work now,

But...When I tested it and for example, the first record is department MRN
and it gives number 25000, the next record/requisition is for

department
BRN

and it gives number 25000, if the following record is again MRN, it

gives
me

the same number (25000) and not the next increment which should be 25001
which is what I require this form to do.

Is this possible?

Thank you.

:

Sorry if I'm stating the obvious, but you do have that all on one line I
hope. (In other words, what starts Me.txtReqSeq and ends +1)

The following shouldn't fall victim to word-wrap, and shows how to use
continuation characters:

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq", _
"Departments","DeptID = '" & _
Me.cboDept & "'"), 24999) + 1

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept &
"'"), 24999) + 1

End Sub


:

I don't know exactly what syntax problem you are getting, but it appears
to
be correct except for one thing. You are putting spaces before and
after the
value you are using a the criteria. Even if you were not getting the
error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" & Me.cboDept
&
"'"), 24999) + 1

:

I am still getting a syntax error message, even with the

single
quotes

by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " &
Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you need

to
imbed

quote
marks in a string. The easiest solution is to change the internal
quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" &
Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in

the
after

update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" &
Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure

why
or

what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination

of
the

two, in the
order desdcribed above.

Now when you want to assign an new number, the user will have to
select the
[AREA_ID]. I would suggest a combo box that will

contain
all

the
abbreviations for the areas. Then, in the After Update event of
the combo
box you will find the highest current number for the

area
and

add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable", "[AREA_ID] =
'" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that several
areas will be
using.

They want each area to be identified by abbreviations i.e.
BRN, FRN, etc.
but they want each area to have their own autonumbers and
starting at the
same number, i.e. 25000 for each area (Each area will have
their own
autonumbers, each starting at 25000)

They would like to be able to have all these available

on
one

single form,
but only want the option to choose one of these for each
requisition.

How does one go about doing this and is it possibel to

do
so?

Thank you.

 

[Guest]

Ok, I have it now updating the field in the table, but this is the problem.

I am using this Departments table to create "autonumbers" for each
department starting at 25000. I also have a requisition table with a combo
box to look up the department abbreviation. I created a form based on the
requisition table and departments table using the ReqSeq form the departments
table.

It does update the departments table to the next increment, but whenever I
add another record in the requisition form, it changes all previous ReqSeq
numbers to the newer, higher number on each previous record for that
department on the form and does not "keep" or save that autonumber for that
previous record.

I must be doing something wrong, or missed something but I'm not sure what.

You're computing a value for Me.txtReqSeq: what is that used for? Does it
update the appropriate row in the Departments table, or does it simply get
used in some other table? Unless the Departments table is updated, it'll
never give you any other possible values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The DeptID is text (an abbreviation of each dept) and a number (long integer)
field for the ReqSeq field.

I did place the value 24999 for each dept in the table. Both the DeptID and
the ReqSeq field are primary keys.

Do you in fact have a row in table "Departments" with ReqSeq equal to 25000
and the appropriate value for DeptID?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes, everything is on one line.

I got it to work now,

But...When I tested it and for example, the first record is department MRN
and it gives number 25000, the next record/requisition is for department
BRN
and it gives number 25000, if the following record is again MRN, it gives
me
the same number (25000) and not the next increment which should be 25001
which is what I require this form to do.

Is this possible?

Thank you.

:

Sorry if I'm stating the obvious, but you do have that all on one line I
hope. (In other words, what starts Me.txtReqSeq and ends +1)

The following shouldn't fall victim to word-wrap, and shows how to use
continuation characters:

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq", _
"Departments","DeptID = '" & _
Me.cboDept & "'"), 24999) + 1

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept &
"'"), 24999) + 1

End Sub


:

I don't know exactly what syntax problem you are getting, but it
appears
to
be correct except for one thing. You are putting spaces before and
after the
value you are using a the criteria. Even if you were not getting the
error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept
&
"'"), 24999) + 1

:

I am still getting a syntax error message, even with the single
quotes
by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " &
Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you need to
imbed
quote
marks in a string. The easiest solution is to change the
internal
quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" &
Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in the
after
update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" &
Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure why
or
what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination of
the
two, in the
order desdcribed above.

Now when you want to assign an new number, the user will
have to
select the
[AREA_ID]. I would suggest a combo box that will contain
all
the
abbreviations for the areas. Then, in the After Update
event of
the combo
box you will find the highest current number for the area
and
add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable",
"[AREA_ID] =
'" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that
several
areas will be
using.

They want each area to be identified by abbreviations i.e.
BRN, FRN, etc.
but they want each area to have their own autonumbers and
starting at the
same number, i.e. 25000 for each area (Each area will have
their own
autonumbers, each starting at 25000)

They would like to be able to have all these available on
one
single form,
but only want the option to choose one of these for each
requisition.

How does one go about doing this and is it possibel to do
so?

Thank you.

 

[Douglas J. Steele]

Sounds as though you're using a continuous form.

Try putting your code in the form's BeforeInsert event, rather than the
combo box's AfterUpdate event.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, I have it now updating the field in the table, but this is the problem.

I am using this Departments table to create "autonumbers" for each
department starting at 25000. I also have a requisition table with a combo
box to look up the department abbreviation. I created a form based on the
requisition table and departments table using the ReqSeq form the departments
table.

It does update the departments table to the next increment, but whenever I
add another record in the requisition form, it changes all previous ReqSeq
numbers to the newer, higher number on each previous record for that
department on the form and does not "keep" or save that autonumber for that
previous record.

I must be doing something wrong, or missed something but I'm not sure what.

You're computing a value for Me.txtReqSeq: what is that used for? Does it
update the appropriate row in the Departments table, or does it simply get
used in some other table? Unless the Departments table is updated, it'll
never give you any other possible values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The DeptID is text (an abbreviation of each dept) and a number (long integer)
field for the ReqSeq field.

I did place the value 24999 for each dept in the table. Both the

DeptID
and

the ReqSeq field are primary keys.



:

Do you in fact have a row in table "Departments" with ReqSeq equal

to
25000

and the appropriate value for DeptID?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes, everything is on one line.

I got it to work now,

But...When I tested it and for example, the first record is

department
MRN

and it gives number 25000, the next record/requisition is for department
BRN
and it gives number 25000, if the following record is again MRN,

it
gives

me
the same number (25000) and not the next increment which should be 25001
which is what I require this form to do.

Is this possible?

Thank you.

:

Sorry if I'm stating the obvious, but you do have that all on

one
line I

hope. (In other words, what starts Me.txtReqSeq and ends +1)

The following shouldn't fall victim to word-wrap, and shows how

to
use

continuation characters:

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq", _
"Departments","DeptID = '" & _
Me.cboDept & "'"), 24999) + 1

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept &
"'"), 24999) + 1

End Sub


:

I don't know exactly what syntax problem you are getting, but it
appears
to
be correct except for one thing. You are putting spaces

before
and

after the
value you are using a the criteria. Even if you were not

getting
the

error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept
&
"'"), 24999) + 1

:

I am still getting a syntax error message, even with the single
quotes
by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " &
Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you

need
to

imbed
quote
marks in a string. The easiest solution is to change the
internal
quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" &
Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code

in
the

after
update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID

= ""
&

Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not

sure
why

or
what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a

combination
of

the
two, in the
order desdcribed above.

Now when you want to assign an new number, the user will
have to
select the
[AREA_ID]. I would suggest a combo box that will contain
all
the
abbreviations for the areas. Then, in the After Update
event of
the combo
box you will find the highest current number for the area
and
add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable",
"[AREA_ID] =
'" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that
several
areas will be
using.

They want each area to be identified by

abbreviations
i.e.

BRN, FRN, etc.
but they want each area to have their own

autonumbers
and

starting at the
same number, i.e. 25000 for each area (Each area

will
have

their own
autonumbers, each starting at 25000)

They would like to be able to have all these

available
on

one
single form,
but only want the option to choose one of these

for
each

requisition.

How does one go about doing this and is it

possibel to
do

so?

Thank you.

 

[Guest]

Tried that too - does not work - The DeptAbbr cannot be selected with an
accompanying message in the lower left-hand of the form "To make changes to
this field, first save record which does not even save anything. Only the
ReqSeq appears and nothing else can be displayed/selected.

Sounds as though you're using a continuous form.

Try putting your code in the form's BeforeInsert event, rather than the
combo box's AfterUpdate event.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, I have it now updating the field in the table, but this is the problem.

I am using this Departments table to create "autonumbers" for each
department starting at 25000. I also have a requisition table with a combo
box to look up the department abbreviation. I created a form based on the
requisition table and departments table using the ReqSeq form the departments
table.

It does update the departments table to the next increment, but whenever I
add another record in the requisition form, it changes all previous ReqSeq
numbers to the newer, higher number on each previous record for that
department on the form and does not "keep" or save that autonumber for that
previous record.

I must be doing something wrong, or missed something but I'm not sure what.

You're computing a value for Me.txtReqSeq: what is that used for? Does it
update the appropriate row in the Departments table, or does it simply get
used in some other table? Unless the Departments table is updated, it'll
never give you any other possible values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The DeptID is text (an abbreviation of each dept) and a number (long
integer)
field for the ReqSeq field.

I did place the value 24999 for each dept in the table. Both the DeptID
and
the ReqSeq field are primary keys.



:

Do you in fact have a row in table "Departments" with ReqSeq equal to
25000
and the appropriate value for DeptID?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes, everything is on one line.

I got it to work now,

But...When I tested it and for example, the first record is department
MRN
and it gives number 25000, the next record/requisition is for
department
BRN
and it gives number 25000, if the following record is again MRN, it
gives
me
the same number (25000) and not the next increment which should be
25001
which is what I require this form to do.

Is this possible?

Thank you.

:

Sorry if I'm stating the obvious, but you do have that all on one
line I
hope. (In other words, what starts Me.txtReqSeq and ends +1)

The following shouldn't fall victim to word-wrap, and shows how to
use
continuation characters:

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq", _
"Departments","DeptID = '" & _
Me.cboDept & "'"), 24999) + 1

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am still getting an error message as follows:

Compile Error:

Syntax Error

I am entering exactly as follows and have removed the spaces

Private Sub DeptID_AfterUpdate()

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept &
"'"), 24999) + 1

End Sub


:

I don't know exactly what syntax problem you are getting, but it
appears
to
be correct except for one thing. You are putting spaces before
and
after the
value you are using a the criteria. Even if you were not getting
the
error,
it would not find anything. Take the space out:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept
&
"'"), 24999) + 1

:

I am still getting a syntax error message, even with the
single
quotes
by
entering as you suggested as follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ' " &
Me.cboDept &
" ' "), 24999) + 1


:

It is a syntax problem. This is very common when you need
to
imbed
quote
marks in a string. The easiest solution is to change the
internal
quotes to
single qoutes. Instead of:
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = "" &
Me.cboDept &
""), 24999) + 1

Use
Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = '" &
Me.cboDept &
"'"), 24999) + 1

Expanded for easy viewing:
"DeptID = ' "
" ' ")


:

I following your directions, and have placed the code in
the
after
update as
follows:

Me.txtReqSeq = Nz(DMax("ReqSeq","Departments","DeptID = ""
&
Me.cboDept &
""), 24999) + 1

but I get the following compile error:

Expected: list separator or )

I am not too familiar with Visual Basic and I am not sure
why
or
what this
error means.

Thanks again.

:

You do not want to use autonumbers.
You do want to use two fields.
The first should be text
[AREA_ID]
The second should be Long Integer
[REQ_SEQ]

The Primary Key for your table should be a combination
of
the
two, in the
order desdcribed above.

Now when you want to assign an new number, the user will
have to
select the
[AREA_ID]. I would suggest a combo box that will
contain
all
the
abbreviations for the areas. Then, in the After Update
event of
the combo
box you will find the highest current number for the
area
and
add 1 to it:

Me.txtReqSeg = Nz(DMax("[REQ_SEQ]", "MyTable",
"[AREA_ID] =
'" &
Me.cboArea & "'"), 24999) + 1

:

I have been asked to create a requisition form that
several
areas will be
using.

They want each area to be identified by abbreviations
i.e.
BRN, FRN, etc.
but they want each area to have their own autonumbers
and
starting at the
same number, i.e. 25000 for each area (Each area will
have
their own
autonumbers, each starting at 25000)

They would like to be able to have all these available
on
one
single form,