Form with "unique number"

[Jay]

Hi,
I'm in the transport industry and busy getting a fleet service register
databae started, cause at this stage none exists. Now the Major Services
gets done by our Suppliers, and the Minor ones by our own mechanics at our
depot.
This is my problem. Services done by the Suppliers get invoiced, so there's
a Document for the event (an Invoice).
Services in our depot are filled in on blank job cards.
I would like to use Access to generate such a Job Card which can be printed,
with the vehicle's details listed on it, as well as tne technician asigned to
the job. Is it possibble to generate such a document, as well as giving each
document an unique number.

 

[Arvin Meyer [MVP]]

Sure, the unique number can be an autonumber, which is built in to Access,
or you can generate your own unique number. Here's one example:

http://www.datastrat.com/Download/AlphaNumeric2K.zip

 

[Tom van Stiphout]

On Mon, 7 Dec 2009 02:58:01 -0800, Jay <[email protected]>
wrote:

Yes. But if this is your first Access project it will be a tall order.
You need to start with creating the correct relational database
design. Then building forms and reports on top of that should be
fairly easy. With the wrong db design, it will be a challenge or
impossible.

The Autonumber data type generates unique numbers automatically.

Consider a few more basic projects before you take on this one, and/or
hire professional help at least for the database design. "Microsoft
Solution Provider" in your yellow pages may be a place to start.

-Tom.
Microsoft Access MVP

 

[BruceM via AccessMonster.com]

In Access you generate records, which may be used to print a report. What
you print depends in part on how the database is designed.

It sounds as if you need at least a Vehicle table, a Technician (or Employee)
table, and a Job table. You may need a VehicleDetails table, depending on
just what is included in a detail. If it is the vehicle number and such, the
main Vehicle table may be suitable. If a job could include several items,
you will need a JobDetails table. At its simplest it may look something like
this:

tblVehicle
VehicleID (primary key, or PK)
VIN
Make
etc.

tblEmployee
EmployeeID (PK)
FirstName
LastName
etc.

tblJob
JobID (PK - Number field)
VehicleID
EmployeeID
JobDate
JobDescription
etc.

Make a form based on tblJob, with combo boxes that get their Row Source from
tblVehicle and tblEmployee, bound to VehicleID and EmployeeID, respectively.
A subform based on tblVehicle could be used to display Vehicle details.

For a unique number, bind a text box to JobID. Set its Control Source to:
=Nz(DMax("[JobID]","[tblJob]"),0) + 1

Reports are used for printing only. The report's Record Source could be a
query that includes tblJob, tblVehicle, and tblEmployee.

This is a very general sketch. It is beyond the scope of the newsgroup to
talk you all of the way through the project. It may help to learn more about
how relational databases in general, and Access in particular, are used.
Access is a powerful tool, but there is definitely a learning curve. Here
are some links to basic information. IMHO Crystal's tutorial is a good place
to get started.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Jay]

First off thanx to all for thd quick reply.
OK, I'm not completely new to access and is familliar with relationships etc.

So here's what I got so far:

tFleet
FleetNo [PK]
CurrentReg
VinNo
EngineNo
etc.

tService
ServiceID [PK]
DateBooked
FleetNo [FK]
ODO
ServiceKm
ServiceProvider
Technician
DateIn
DateOut
OrderNo
DocNo

tSeviceDocument tDocDetails
DocID [PK] DocDetID
DocNo DocID
ServiceID [FK] Product
etc Qty
etc

I've got a form: FleetService with an unbound lookup cbo to filter for the
FleetNo I want to use/find. Then on the sam form I've got tService as a
subform which allows me to view all service records for selected vehicle. In
the subform I've got a subdatasheet tDocDetails so I can view all "documents
related to a specific Service.
I was wondering if and how I could select a tchnician as well as a Fleet No
and have this data added to a blank template/check sheet [The JobCard] which
I could then print out for the Technician to use. Lastly I would like to
give each Jobcard a unique number that's also generated so that I could look
up which technician still have outstanding Jobcards.
I know I'm asking a lot, but thanks in advance for any ideas.

In Access you generate records, which may be used to print a report. What
you print depends in part on how the database is designed.

It sounds as if you need at least a Vehicle table, a Technician (or Employee)
table, and a Job table. You may need a VehicleDetails table, depending on
just what is included in a detail. If it is the vehicle number and such, the
main Vehicle table may be suitable. If a job could include several items,
you will need a JobDetails table. At its simplest it may look something like
this:

tblVehicle
VehicleID (primary key, or PK)
VIN
Make
etc.

tblEmployee
EmployeeID (PK)
FirstName
LastName
etc.

tblJob
JobID (PK - Number field)
VehicleID
EmployeeID
JobDate
JobDescription
etc.

Make a form based on tblJob, with combo boxes that get their Row Source from
tblVehicle and tblEmployee, bound to VehicleID and EmployeeID, respectively.
A subform based on tblVehicle could be used to display Vehicle details.

For a unique number, bind a text box to JobID. Set its Control Source to:
=Nz(DMax("[JobID]","[tblJob]"),0) + 1

Reports are used for printing only. The report's Record Source could be a
query that includes tblJob, tblVehicle, and tblEmployee.

This is a very general sketch. It is beyond the scope of the newsgroup to
talk you all of the way through the project. It may help to learn more about
how relational databases in general, and Access in particular, are used.
Access is a powerful tool, but there is definitely a learning curve. Here
are some links to basic information. IMHO Crystal's tutorial is a good place
to get started.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Hi,
I'm in the transport industry and busy getting a fleet service register
databae started, cause at this stage none exists. Now the Major Services
gets done by our Suppliers, and the Minor ones by our own mechanics at our
depot.
This is my problem. Services done by the Suppliers get invoiced, so there's
a Document for the event (an Invoice).
Services in our depot are filled in on blank job cards.
I would like to use Access to generate such a Job Card which can be printed,
with the vehicle's details listed on it, as well as tne technician asigned to
the job. Is it possibble to generate such a document, as well as giving each
document an unique number.

--



.

 

[BruceM via AccessMonster.com]

I described a way of getting a sequential number. It could be the PK or
another field (DocNo?).

To select a technician you need a way to generate the list. Best way is
almost surely a technician table.

It seems you have the right general idea for the structure. I assume records
in tFleet are individual vehicles, in tService of occasions when the vehicle
was in for service; and tDocDetails the service items (rotate tires, oil
change, etc.) for a particular tService record. I'm not sure what
tServiceDocument is for.

The way I understand it, you want to generate a service record, print out a
job card, and give it to the technician. When the service is complete you
will go back to the record and fill in the information from the handwritten
notes on the job card.

As mentioned, I described a way of generating a sequential number. It would
take place in the form bound to tService. If this is a multi-user database
(i.e. more than one concurrent user) you need to guard against duplication of
the number, which I can describe if needed.

As for the printed job card, base a report on tService. I expect it would
consist of the sequential number, the technician's name, the date booked, and
maybe a few others. For the rest you can use labels and lines for the
technician to write information.

To start the record you can either go to the fleet (vehicle) form, and add a
new record to the Service subform; or you can use a standalone Service form
to select the vehicle. In either case FleetNo is the FK. In the first case
it is generated due to the Link Child and Link Master properties of the
subform control; in the second it is selected manually.

Let me point out that the Details records are in a subform within the Service
form (or subform). This subform within a subform can be in datasheet view,
but it is not quite the same as a subdatasheet. This may be just a
terminology issue, but I want to be sure.

For the first part of your reply, the combo box wizard can guide you toward
selecting a Fleet record.

First off thanx to all for thd quick reply.
OK, I'm not completely new to access and is familliar with relationships etc.

So here's what I got so far:

tFleet
FleetNo [PK]
CurrentReg
VinNo
EngineNo
etc.

tService
ServiceID [PK]
DateBooked
FleetNo [FK]
ODO
ServiceKm
ServiceProvider
Technician
DateIn
DateOut
OrderNo
DocNo

tSeviceDocument tDocDetails
DocID [PK] DocDetID
DocNo DocID
ServiceID [FK] Product
etc Qty
etc

I've got a form: FleetService with an unbound lookup cbo to filter for the
FleetNo I want to use/find. Then on the sam form I've got tService as a
subform which allows me to view all service records for selected vehicle. In
the subform I've got a subdatasheet tDocDetails so I can view all "documents
related to a specific Service.
I was wondering if and how I could select a tchnician as well as a Fleet No
and have this data added to a blank template/check sheet [The JobCard] which
I could then print out for the Technician to use. Lastly I would like to
give each Jobcard a unique number that's also generated so that I could look
up which technician still have outstanding Jobcards.
I know I'm asking a lot, but thanks in advance for any ideas.

In Access you generate records, which may be used to print a report. What
you print depends in part on how the database is designed.

[quoted text clipped - 73 lines]

 

[Jay]

I've now set up a form fService bound to table tService based on your advice,
which has two FK fields: FleetID and TechnicianID. Using VBA I also have a
form load called rServiceJobCard with it's data source being
tService/fService.
My problem now, is that after I've entered the Fleet No and the Technician,
I have to go to the next record first and then back again before the data is
reflected in the report/JobCard.
Is it possible to put a button on the form that would update the report/Job
Card with the data on the form, upon clicking it, without having to go back
and forth the whole time?
Much appreciated!

I described a way of getting a sequential number. It could be the PK or
another field (DocNo?).

To select a technician you need a way to generate the list. Best way is
almost surely a technician table.

It seems you have the right general idea for the structure. I assume records
in tFleet are individual vehicles, in tService of occasions when the vehicle
was in for service; and tDocDetails the service items (rotate tires, oil
change, etc.) for a particular tService record. I'm not sure what
tServiceDocument is for.

The way I understand it, you want to generate a service record, print out a
job card, and give it to the technician. When the service is complete you
will go back to the record and fill in the information from the handwritten
notes on the job card.

As mentioned, I described a way of generating a sequential number. It would
take place in the form bound to tService. If this is a multi-user database
(i.e. more than one concurrent user) you need to guard against duplication of
the number, which I can describe if needed.

As for the printed job card, base a report on tService. I expect it would
consist of the sequential number, the technician's name, the date booked, and
maybe a few others. For the rest you can use labels and lines for the
technician to write information.

To start the record you can either go to the fleet (vehicle) form, and add a
new record to the Service subform; or you can use a standalone Service form
to select the vehicle. In either case FleetNo is the FK. In the first case
it is generated due to the Link Child and Link Master properties of the
subform control; in the second it is selected manually.

Let me point out that the Details records are in a subform within the Service
form (or subform). This subform within a subform can be in datasheet view,
but it is not quite the same as a subdatasheet. This may be just a
terminology issue, but I want to be sure.

For the first part of your reply, the combo box wizard can guide you toward
selecting a Fleet record.

First off thanx to all for thd quick reply.
OK, I'm not completely new to access and is familliar with relationships etc.

So here's what I got so far:

tFleet
FleetNo [PK]
CurrentReg
VinNo
EngineNo
etc.

tService
ServiceID [PK]
DateBooked
FleetNo [FK]
ODO
ServiceKm
ServiceProvider
Technician
DateIn
DateOut
OrderNo
DocNo

tSeviceDocument tDocDetails
DocID [PK] DocDetID
DocNo DocID
ServiceID [FK] Product
etc Qty
etc

I've got a form: FleetService with an unbound lookup cbo to filter for the
FleetNo I want to use/find. Then on the sam form I've got tService as a
subform which allows me to view all service records for selected vehicle. In
the subform I've got a subdatasheet tDocDetails so I can view all "documents
related to a specific Service.
I was wondering if and how I could select a tchnician as well as a Fleet No
and have this data added to a blank template/check sheet [The JobCard] which
I could then print out for the Technician to use. Lastly I would like to
give each Jobcard a unique number that's also generated so that I could look
up which technician still have outstanding Jobcards.
I know I'm asking a lot, but thanks in advance for any ideas.

In Access you generate records, which may be used to print a report. What
you print depends in part on how the database is designed.

[quoted text clipped - 73 lines]

the job. Is it possibble to generate such a document, as well as giving each
document an unique number.

--



.

 

[BruceM via AccessMonster.com]

You enter the data on the form, then open a report based on the same data,
but it does not show the new record? You could try explicitly saving the
record in the command button that opens the report:
Me.Dirty = False

You could also try Me.Refresh. This may help if the report is grabbing data
from the controls on the form rather than getting it directly from the table
or query on which the form is based.

I've now set up a form fService bound to table tService based on your advice,
which has two FK fields: FleetID and TechnicianID. Using VBA I also have a
form load called rServiceJobCard with it's data source being
tService/fService.
My problem now, is that after I've entered the Fleet No and the Technician,
I have to go to the next record first and then back again before the data is
reflected in the report/JobCard.
Is it possible to put a button on the form that would update the report/Job
Card with the data on the form, upon clicking it, without having to go back
and forth the whole time?
Much appreciated!

I described a way of getting a sequential number. It could be the PK or
another field (DocNo?).

[quoted text clipped - 86 lines]

 

[Jay]

Sorry for taking so long to reply. Reading your last post I didn't take in
"everything" you posted. See, I innitially tried to load the form and report
at the same time, add my data afterwards, and then tried to filter the report
with the data I had just entered. This caused a lot of error messages. Only
after reading your post for about the 4th time did I realize that I should
open the form, enter the data and only then open the report! Thanks for the
help!

You enter the data on the form, then open a report based on the same data,
but it does not show the new record? You could try explicitly saving the
record in the command button that opens the report:
Me.Dirty = False

You could also try Me.Refresh. This may help if the report is grabbing data
from the controls on the form rather than getting it directly from the table
or query on which the form is based.

I've now set up a form fService bound to table tService based on your advice,
which has two FK fields: FleetID and TechnicianID. Using VBA I also have a
form load called rServiceJobCard with it's data source being
tService/fService.
My problem now, is that after I've entered the Fleet No and the Technician,
I have to go to the next record first and then back again before the data is
reflected in the report/JobCard.
Is it possible to put a button on the form that would update the report/Job
Card with the data on the form, upon clicking it, without having to go back
and forth the whole time?
Much appreciated!

I described a way of getting a sequential number. It could be the PK or
another field (DocNo?).

[quoted text clipped - 86 lines]

the job. Is it possibble to generate such a document, as well as giving each
document an unique number.

--



.

 

[BruceM via AccessMonster.com]

Glad it helped. Good luck with the project.

Sorry for taking so long to reply. Reading your last post I didn't take in
"everything" you posted. See, I innitially tried to load the form and report
at the same time, add my data afterwards, and then tried to filter the report
with the data I had just entered. This caused a lot of error messages. Only
after reading your post for about the 4th time did I realize that I should
open the form, enter the data and only then open the report! Thanks for the
help!

You enter the data on the form, then open a report based on the same data,
but it does not show the new record? You could try explicitly saving the

[quoted text clipped - 22 lines]