If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Form with "unique number"
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. |
#2
|
|||
|
|||
Form with "unique number"
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 -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jay" wrote in message news 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. |
#3
|
|||
|
|||
Form with "unique number"
On Mon, 7 Dec 2009 02:58:01 -0800, Jay
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 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. |
#4
|
|||
|
|||
Form with "unique number"
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/acc...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 wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 |
#5
|
|||
|
|||
Form with "unique number"
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. "BruceM via AccessMonster.com" wrote: 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/acc...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 wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 . |
#6
|
|||
|
|||
Form with "unique number"
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. Jay wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 |
#7
|
|||
|
|||
Form with "unique number"
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! "BruceM via AccessMonster.com" wrote: 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. Jay wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 . |
#8
|
|||
|
|||
Form with "unique number"
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. Jay wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 |
#9
|
|||
|
|||
Form with "unique number"
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! "BruceM via AccessMonster.com" wrote: 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. Jay wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 . |
#10
|
|||
|
|||
Form with "unique number"
Glad it helped. Good luck with the project.
Jay wrote: 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] the job. Is it possibble to generate such a document, as well as giving each document an unique number. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 |
Thread Tools | |
Display Modes | |
|
|