Automatic Generate next available number base on selection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I would appreciate it could you please help me how to create an code so that
the number is generated automatically base on the selection as below.

There are three tables.

Table 1: Project Table
ProjectID: YNA0000
Section: N
AuditType:A


Table 2: Section
Section: N,O, P

Table 3: AuditType
AuditType: A, B, C

I want the system to automatic generate the last three number base on the
selection of AuditType and Section.

ProjectID: YNA000 where Y = 6 of the year 2006, N = Section number, A =
Audit Type number, 000 is the next available number in this range.

Eg, If I select Section = O and AuditType = B then the ProjectID = YOB0001
or YOB0002,

Then If I select Section= N and AuditType = A then the Project ID = YNA0001
or YNA0002, YNA0003 etc.

Thank you very much for your help.

Regards
Hong
 
Mail said:
I would appreciate it could you please help me how to create an code so that
the number is generated automatically base on the selection as below.

There are three tables.

Table 1: Project Table
ProjectID: YNA0000
Section: N
AuditType:A


Table 2: Section
Section: N,O, P

Table 3: AuditType
AuditType: A, B, C

I want the system to automatic generate the last three number base on the
selection of AuditType and Section.

ProjectID: YNA000 where Y = 6 of the year 2006, N = Section number, A =
Audit Type number, 000 is the next available number in this range.

Eg, If I select Section = O and AuditType = B then the ProjectID = YOB0001
or YOB0002,

Then If I select Section= N and AuditType = A then the Project ID = YNA0001
or YNA0002, YNA0003 etc.
Click to expand...


Use the form's BeforeUpdate event.

Dim Prefix As String
Dim Suffix As Integer
Prefix = Right(Format(Date, "yyyy"), 1) & _
Me.txtSection & Me.txtAuditType
Suffix = Nz(DMax("CInt(Right(ProjectID,4))", "Table1", _
"Left(ProjectID,3) = '" & Prefix & "' "), 0) + 1
Me.ProjectID = Prefix & Format(Suffix, "0000")

Note that I use txtSection as the name of the text/combo box
bound to the Section field. Also note that Section is a
reserved word and you should change the name of the field to
simething else.
 
Hi Marshall

I couldn't make it works. Below is a what my table looks like.

I would appreciate it could you please have a look again.Thanks again for
your help.

Table name: Projects
ProjectID: Text
RespSection: Text
AuditType: Text

Table 2: AuditType
AuditTypeID: number
AuditType: Text


Table 3:Section

SectionID: Number
RespSection: Text

Hong
 
You are not telling me the names of the relevant controls on
the form so I am still assuming they are named txt followed
by the name of their bound field.

Dim Prefix As String
Dim Suffix As Integer
Prefix = Right(Format(Date, "yyyy"), 1) & _
Me.txtRespSection & Me.txtAuditType
Suffix = Nz(DMax("CInt(Right(ProjectID,4))", "Projects", _
"Left(ProjectID,3) = '" & Prefix & "' "), 0) + 1
Me.ProjectID = Prefix & Format(Suffix, "0000")

If you need further assistance, please post a Copy/Paste of
the code you actually used along with a detailed explanation
of what happened. "Couldn't make it work" doesn't provide
any clues about what might be wrong.
 
Hi Marshall

Thanks for your help, it works perfectly. Could you please help me a little
bit more.

Since the format for Section and AuditType show in the Projects Table is
text. Could you please change the code so that it select the SectionID and
AuditTypeID to generate the ProjectID, not the Section and AuditType. An
example as below.

Also, Is there a way to change Access so that it recognise the date in
Financial Year, which mean the 1st month of the Financial Year 05/06 is July.
The first quarter of the financial year will be Sept 05.

Thank you very much.

Hong

Eg



Table name: Projects
ProjectID: 6310001 (Y&SectionID&AuditTypeID&0000)
RespSection: Engineering
AuditType: Audit-General

Table 2: AuditType
AuditTypeID: 1
AuditType: Audit-General


Table 3:Section

SectionID: 3
RespSection: Engineering


Marshall Barton said:
You are not telling me the names of the relevant controls on
the form so I am still assuming they are named txt followed
by the name of their bound field.

Dim Prefix As String
Dim Suffix As Integer
Prefix = Right(Format(Date, "yyyy"), 1) & _
Me.txtRespSection & Me.txtAuditType
Suffix = Nz(DMax("CInt(Right(ProjectID,4))", "Projects", _
"Left(ProjectID,3) = '" & Prefix & "' "), 0) + 1
Me.ProjectID = Prefix & Format(Suffix, "0000")

If you need further assistance, please post a Copy/Paste of
the code you actually used along with a detailed explanation
of what happened. "Couldn't make it work" doesn't provide
any clues about what might be wrong.
--
Marsh
MVP [MS Access]

I couldn't make it works. Below is a what my table looks like.

I would appreciate it could you please have a look again.Thanks again for
your help.

Table name: Projects
ProjectID: Text
RespSection: Text
AuditType: Text

Table 2: AuditType
AuditTypeID: number
AuditType: Text


Table 3:Section

SectionID: Number
RespSection: Text

Hong
Click to expand...
Click to expand...
 
Can't you just change the Prefix expression to use the ID
fields instead of the text fields?

To adjust for the fiscal year just subtract 6 months (or add
7 months)

Prefix = Right(Format(DateAdd("m", -5, Date), "yyyy"), 1) _
& Me.txtRespSection & Me.txtAuditType
 
Marshall

I don't understand what do you mean by change the Prefix expresion.

Do you mean that I change my form so that instead to select the ID not the
text. If this is the case, then in my table there will lots of number 1 not
ENGINEERING that doesn't mean any thing. I did change the RespSection and
AuditType to select ID number, the Project ID turn out right but then the
field in the RespSection and AuditType is all numbers.

If this is not what you mean, then I don't know how to fix it. I am very
appreciate your time to help me out. Could you please help me a lot bit more.
as I am not good with this.

RespSection Field: SELECT DISTINCT Employees.Section, Employees.SectionID
FROM Employees ORDER BY Employees.Section;
Bound Column : 1

AuditType Field: SELECT AuditTypes.AuditType, AuditTypes.AuditTypeIDFROM
AuditTypes ORDER BY AuditTypes.AuditType;

Column Bound: 1

Thanks
Hong


Marshall Barton said:
Can't you just change the Prefix expression to use the ID
fields instead of the text fields?

To adjust for the fiscal year just subtract 6 months (or add
7 months)

Prefix = Right(Format(DateAdd("m", -5, Date), "yyyy"), 1) _
& Me.txtRespSection & Me.txtAuditType
--
Marsh
MVP [MS Access]

Thanks for your help, it works perfectly. Could you please help me a little
bit more.

Since the format for Section and AuditType show in the Projects Table is
text. Could you please change the code so that it select the SectionID and
AuditTypeID to generate the ProjectID, not the Section and AuditType. An
example as below.

Also, Is there a way to change Access so that it recognise the date in
Financial Year, which mean the 1st month of the Financial Year 05/06 is July.
The first quarter of the financial year will be Sept 05.

Eg
Table name: Projects
ProjectID: 6310001 (Y&SectionID&AuditTypeID&0000)
RespSection: Engineering
AuditType: Audit-General

Table 2: AuditType
AuditTypeID: 1
AuditType: Audit-General

Table 3:Section
SectionID: 3
RespSection: Engineering
Click to expand...
Click to expand...
 
Marshall

Also the last 4 digit number doesn't add 1, this mean if I create a new
project by select the same RespSection and AuditType, this won't take me
anywhere since it will create duplication.

Below is the code that you wrote for me the other day.

Thanks
Hong

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Prefix As String
Dim Suffix As Integer
Prefix = Right(Format(DateAdd("m", -5, Date), "yyyy"), 1) _
& Me.txtRespSection & Me.txtAuditType
Suffix = Nz(DMax("CInt(Right(ProjectID,4))", "Projects", _
"Left(ProjectID,3) = '" & Prefix & "' "), 0) + 1
Me.ProjectID = Prefix & Format(Suffix, "0000")

End Sub

Mail Merge Help said:
Marshall

I don't understand what do you mean by change the Prefix expresion.

Do you mean that I change my form so that instead to select the ID not the
text. If this is the case, then in my table there will lots of number 1 not
ENGINEERING that doesn't mean any thing. I did change the RespSection and
AuditType to select ID number, the Project ID turn out right but then the
field in the RespSection and AuditType is all numbers.

If this is not what you mean, then I don't know how to fix it. I am very
appreciate your time to help me out. Could you please help me a lot bit more.
as I am not good with this.

RespSection Field: SELECT DISTINCT Employees.Section, Employees.SectionID
FROM Employees ORDER BY Employees.Section;
Bound Column : 1

AuditType Field: SELECT AuditTypes.AuditType, AuditTypes.AuditTypeIDFROM
AuditTypes ORDER BY AuditTypes.AuditType;

Column Bound: 1

Thanks
Hong


Marshall Barton said:
Can't you just change the Prefix expression to use the ID
fields instead of the text fields?

To adjust for the fiscal year just subtract 6 months (or add
7 months)

Prefix = Right(Format(DateAdd("m", -5, Date), "yyyy"), 1) _
& Me.txtRespSection & Me.txtAuditType
--
Marsh
MVP [MS Access]

Thanks for your help, it works perfectly. Could you please help me a little
bit more.

Since the format for Section and AuditType show in the Projects Table is
text. Could you please change the code so that it select the SectionID and
AuditTypeID to generate the ProjectID, not the Section and AuditType. An
example as below.

Also, Is there a way to change Access so that it recognise the date in
Financial Year, which mean the 1st month of the Financial Year 05/06 is July.
The first quarter of the financial year will be Sept 05.

Eg
Table name: Projects
ProjectID: 6310001 (Y&SectionID&AuditTypeID&0000)
RespSection: Engineering
AuditType: Audit-General

Table 2: AuditType
AuditTypeID: 1
AuditType: Audit-General

Table 3:Section
SectionID: 3
RespSection: Engineering


:
You are not telling me the names of the relevant controls on
the form so I am still assuming they are named txt followed
by the name of their bound field.

Dim Prefix As String
Dim Suffix As Integer
Prefix = Right(Format(Date, "yyyy"), 1) & _
Me.txtRespSection & Me.txtAuditType
Suffix = Nz(DMax("CInt(Right(ProjectID,4))", "Projects", _
"Left(ProjectID,3) = '" & Prefix & "' "), 0) + 1
Me.ProjectID = Prefix & Format(Suffix, "0000")

If you need further assistance, please post a Copy/Paste of
the code you actually used along with a detailed explanation
of what happened. "Couldn't make it work" doesn't provide
any clues about what might be wrong.


Mail Merge Help wrote:
I couldn't make it works. Below is a what my table looks like.

I would appreciate it could you please have a look again.Thanks again for
your help.

Table name: Projects
ProjectID: Text
RespSection: Text
AuditType: Text

Table 2: AuditType
AuditTypeID: number
AuditType: Text


Table 3:Section

SectionID: Number
RespSection: Text

Hong

:

Mail Merge Help wrote:
I would appreciate it could you please help me how to create an code so that
the number is generated automatically base on the selection as below.

There are three tables.

Table 1: Project Table
ProjectID: YNA0000
Section: N
AuditType:A


Table 2: Section
Section: N,O, P

Table 3: AuditType
AuditType: A, B, C

I want the system to automatic generate the last three number base on the
selection of AuditType and Section.

ProjectID: YNA000 where Y = 6 of the year 2006, N = Section number, A =
Audit Type number, 000 is the next available number in this range.

Eg, If I select Section = O and AuditType = B then the ProjectID = YOB0001
or YOB0002,

Then If I select Section= N and AuditType = A then the Project ID = YNA0001
or YNA0002, YNA0003 etc.


Use the form's BeforeUpdate event.

Dim Prefix As String
Dim Suffix As Integer
Prefix = Right(Format(Date, "yyyy"), 1) & _
Me.txtSection & Me.txtAuditType
Suffix = Nz(DMax("CInt(Right(ProjectID,4))", "Table1", _
"Left(ProjectID,3) = '" & Prefix & "' "), 0) + 1
Me.ProjectID = Prefix & Format(Suffix, "0000")

Note that I use txtSection as the name of the text/combo box
bound to the Section field. Also note that Section is a
reserved word and you should change the name of the field to
simething else.
Click to expand...
Click to expand...
Click to expand...
 
I'm afraid that I am completely lost about what you are
asking for here. Because of that, I may have misinterpreted
what you wanted after you said it worked perfectly. I had
no intention of suggesting that you change your form and I
am concerned that we seem to be talking about two different
things.

Let's go back to the point where you said that it worked and
try to explain your next issue in a different way. Please
use the names of any controls that you are using. Also,
make a clear distinction between fields and controls.
Fields are columns in a table or query. Controls are the
text boxes, combo boxes, etc on a form/report that are used
to display the values in a field.

It seems like the things you are calling fields are really
combo box controls, but I do not understand how they relate
to your question.

Perhaps if you could tell me what controls and or VBA code
you are using along with the desired result and the actual
result, I might get a better understanding if what your are
trying to accomplish.
 
Marshall

Thanks for your help, but it's hard for me to explain in words, It's a bit
complicate.

I think I can live with manually work out the projectID for the time being
and will find the answer later on.

I am very much appreciate your effort in trying to help me out.

Thanks
hong


Marshall Barton said:
I'm afraid that I am completely lost about what you are
asking for here. Because of that, I may have misinterpreted
what you wanted after you said it worked perfectly. I had
no intention of suggesting that you change your form and I
am concerned that we seem to be talking about two different
things.

Let's go back to the point where you said that it worked and
try to explain your next issue in a different way. Please
use the names of any controls that you are using. Also,
make a clear distinction between fields and controls.
Fields are columns in a table or query. Controls are the
text boxes, combo boxes, etc on a form/report that are used
to display the values in a field.

It seems like the things you are calling fields are really
combo box controls, but I do not understand how they relate
to your question.

Perhaps if you could tell me what controls and or VBA code
you are using along with the desired result and the actual
result, I might get a better understanding if what your are
trying to accomplish.
--
Marsh
MVP [MS Access]

Also the last 4 digit number doesn't add 1, this mean if I create a new
project by select the same RespSection and AuditType, this won't take me
anywhere since it will create duplication.

Below is the code that you wrote for me the other day.


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Prefix As String
Dim Suffix As Integer
Prefix = Right(Format(DateAdd("m", -5, Date), "yyyy"), 1) _
& Me.txtRespSection & Me.txtAuditType
Suffix = Nz(DMax("CInt(Right(ProjectID,4))", "Projects", _
"Left(ProjectID,3) = '" & Prefix & "' "), 0) + 1
Me.ProjectID = Prefix & Format(Suffix, "0000")

End Sub
Click to expand...
Click to expand...
 
Back
Top