Insert data & generate job no.

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I had a FORM (Job initiation) that let user to initiate a new job and will
generate a Job_No to them.

And the FORM have a button that let user to click, and then it will open
another FORM (Job Details).

They are using two separate table (Job initiation & Job Details).
Both of them had a field call [Job_No] for join.

How can I insert the new [Job_No] from [Job initiation] to [Job Details]
when the user click the button to open [Job Details] form.
and then display this Job_No at the job no. field at FORM [Job Details]?

Using variable to store the Job_No first, and then insert into [Job
Details]? or any other method?

Moreover, for generate Job_No e.g. TYYXXXX
T - Type (P for print, B for bind....)
YY- Year (2004 -> 04 only)
XXXX (auto increment no.)

So, if type=P, then Job_No= P040001,

As i don't want to duplicate the XXXX field, that means if there is P040001
exist,
the next Job_No will be either be P040002 or B040002 (whatever the type is
different).

So how can I generate this kind of Job_No?
By input Mask?

Thanks a lot !

regards,
Joe
 
Hi:

To generate Job Number I would have seperate fields for Type and Year and a
JOBID field which is Number (long Integer).

When creating new job make user type the Type and do something like this --

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim lngID as Long

Me.Year = Right(year(vba.Date),2) 'this is for year field
strSql = Select Max([JobID]) from [Job Initiation] Where [Type] = '" &
me.Type & "' And [Year] = " & Me.Year
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQl,dbOpenSnapShot)
If not rs.Eof then
lngID = Nz(rs.fields(0))
end if
lngID = lngID + 1
Me.JobID = lngId
Me.JobNumber = Me.Type & "-" & Me.Year & "-" & Format(lngID,"0000")

YOu can pass this to Job Details when opening the form in the OpenArgs
argument

docmd.OpenForm "Job Details",acNormal,,"[JobID]='" &
me.JobNumber,acFormEdit,acWindowNormal,me.JobNumber

In the "Job Details" the Me.OpenArgs will give you the JobID.

Hope this helps!

Regards,

Naresh Nichani
Microsoft Access MVP
 
How can I insert the new [Job_No] from [Job initiation] to [Job Details]
when the user click the button to open [Job Details] form.
and then display this Job_No at the job no. field at FORM [Job Details]?
Click to expand...

Once the Job_No exists you can use it as the Master Link Field of a
Subform containing the Job Details; it will fill in automatically.
Using variable to store the Job_No first, and then insert into [Job
Details]? or any other method?

Moreover, for generate Job_No e.g. TYYXXXX
T - Type (P for print, B for bind....)
YY- Year (2004 -> 04 only)
XXXX (auto increment no.)
Click to expand...

This is called an "Intelligent Key" - and that's unfortunately not
meant as a compliment. Storing multiple types of information in a
single field is NOT good design, and should only be done for
compatibility with an established paper system. A job type *is data*;
so is a date - they should be stored in their own fields.

If you do need to do this, you'll need to create the Job_No in VBA
code after the (at least) job type is selected. Incrementing the
sequential number will be difficult since it's not stored as a
separate field. Any chance you could reconsider this 1950's style job
number in favor of a user-invisible autonumber, with forms to display
the job type, date, and so on?
 
thx a lot.

Naresh Nichani MVP said:
Hi:

To generate Job Number I would have seperate fields for Type and Year and a
JOBID field which is Number (long Integer).

When creating new job make user type the Type and do something like this --

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim lngID as Long

Me.Year = Right(year(vba.Date),2) 'this is for year field
strSql = Select Max([JobID]) from [Job Initiation] Where [Type] = '" &
me.Type & "' And [Year] = " & Me.Year
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQl,dbOpenSnapShot)
If not rs.Eof then
lngID = Nz(rs.fields(0))
end if
lngID = lngID + 1
Me.JobID = lngId
Me.JobNumber = Me.Type & "-" & Me.Year & "-" & Format(lngID,"0000")

YOu can pass this to Job Details when opening the form in the OpenArgs
argument

docmd.OpenForm "Job Details",acNormal,,"[JobID]='" &
me.JobNumber,acFormEdit,acWindowNormal,me.JobNumber

In the "Job Details" the Me.OpenArgs will give you the JobID.

Hope this helps!

Regards,

Naresh Nichani
Microsoft Access MVP


Joe said:
I had a FORM (Job initiation) that let user to initiate a new job and will
generate a Job_No to them.

And the FORM have a button that let user to click, and then it will open
another FORM (Job Details).

They are using two separate table (Job initiation & Job Details).
Both of them had a field call [Job_No] for join.

How can I insert the new [Job_No] from [Job initiation] to [Job Details]
when the user click the button to open [Job Details] form.
and then display this Job_No at the job no. field at FORM [Job Details]?

Using variable to store the Job_No first, and then insert into [Job
Details]? or any other method?

Moreover, for generate Job_No e.g. TYYXXXX
T - Type (P for print, B for bind....)
YY- Year (2004 -> 04 only)
XXXX (auto increment no.)

So, if type=P, then Job_No= P040001,

As i don't want to duplicate the XXXX field, that means if there is P040001
exist,
the next Job_No will be either be P040002 or B040002 (whatever the type is
different).

So how can I generate this kind of Job_No?
By input Mask?

Thanks a lot !

regards,
Joe
Click to expand...
Click to expand...
 
thx a lot !

John Vinson said:
How can I insert the new [Job_No] from [Job initiation] to [Job Details]
when the user click the button to open [Job Details] form.
and then display this Job_No at the job no. field at FORM [Job Details]?
Click to expand...

Once the Job_No exists you can use it as the Master Link Field of a
Subform containing the Job Details; it will fill in automatically.
Using variable to store the Job_No first, and then insert into [Job
Details]? or any other method?

Moreover, for generate Job_No e.g. TYYXXXX
T - Type (P for print, B for bind....)
YY- Year (2004 -> 04 only)
XXXX (auto increment no.)
Click to expand...

This is called an "Intelligent Key" - and that's unfortunately not
meant as a compliment. Storing multiple types of information in a
single field is NOT good design, and should only be done for
compatibility with an established paper system. A job type *is data*;
so is a date - they should be stored in their own fields.

If you do need to do this, you'll need to create the Job_No in VBA
code after the (at least) job type is selected. Incrementing the
sequential number will be difficult since it's not stored as a
separate field. Any chance you could reconsider this 1950's style job
number in favor of a user-invisible autonumber, with forms to display
the job type, date, and so on?
Click to expand...
 
Back
Top