Database for Vehicle Movement

[ebincharles]

Hey Friends,

I am in charge of 150 vehicles in a company. i am maintaining the details of each vehicle in separate sheet. it is around 150 sheet. i am finding very difficult to maintain all vehicle in different sheet.

The record of vehicles are described in attached file. It is like

S/N Description Amount SR. Purchased by: Remarks

is there any macro to enter and update such kind of data in the attached excel file.

Thank in advance for help.

regards.

[TMS]

Personally, I think you are confusing data and presentation.

If it were me, I’d have three sheets.

1. Master sheet for vehicle details; registration (key field), make, model, colour, whatever ... all static details
2. Transactions sheet to record details of repairs, maintenance, service, parts, etc., using the registration to link the details back to the master sheet
3. A Summary sheet where you can enter the registration number and it will collate the details from the master record and the transactions. I'd probably use VBA to do that but you could probably do it with formulae.

That structure would be much easier to maintain. And you can view and print a summary sheet for each vehicle as required.

Just my thoughts.

[torachan]

This lends itself to data entry by using a UserForm approach.
Two tables - one with actual vehicle reg details - one master table recording all actions/purchases.
Far simpler to analyse than looking through 150 sheets.
You can then produce individual reports with relative ease at the press of a button.
Post back on this site if of interest and I will throw a quick demo together - just need 2 or 3 more vehicle details to be realistic.
Upload a small typical file with the data desensitised.
torachan.

[ebincharles]

Thanks,

The file i have attached now is have Data and Master sheet.

The Data sheet has all details which need bring to ENTRY sheet for any update.

Ans the new data entered in the ENTRY sheet will saved in Data sheet as new record.

So any vehicle details i want Delete or Update should load in Entry through navigate option.

Please help me to get this.

Thanks.

[ebincharles]

Yeah Bro! It can be maintain with formulae... however in this ENTRY sheet you enter details which would be saving in sheet DATA. so i can put formulae here. every vehicle may have different no of details.

[ebincharles]

not actually from userform. this is like contexturers format. but i would like to get by VBA. I guess that is actual and suitable.

[ebincharles]

hello,

the file i attached is with Adding and Updating few record.

Please Login or Register  to view this content.

However i would like to add data in the description area also, row starting from 11 to 61. I need to edit and update those record in same cells, so formulae will not work.

could you please help me. Thank you.

Regards.

[torachan]

Sorry got waylaid for a day or two, the on sheet approach is 'clunky' and the code does not lend itself to sort techniques that will be needed when your data has grown.
Once you have several thousand enteries do you really want to use the technique of 'find first/find next/find prev/find last'
Decide what your primary search keys would be e.g. vehicle reg - between dates - invoice numbers ????
In the attached is the nature of form I would use, look in the listbox - just enter a random reference from the left column in the left find box to see the speed (there are 9000+ records).
From experience I would not pursue the on sheet form.
torachan.

[ebincharles]

my primary search field will be "Vehi.:". Presently i don't required to search by invoice and date wise. Just by Vehi. reg. is enough.

[torachan]

Give the attached a try as first attempt.
Forms are on a multipage - first tab is the data entry - select vehicle from combobox dropdown.
If you have multiple enteries for the same vehicle select multi option - this retains the vehicle details (lefthand four textboxes) - you cannot type in them.
If you select single option the lefthand textboxes clear after you submit then allowing selection of another vehicle.
Second page tab allows you to edit any entry by either 'find' or selection from listbox.
Third tab is for entering default details for vehicles - to add a new vehicle first clear form by pressing 'NEW', then enter the details and press 'ADD', the rest should be intuitive.
The forth tab enable a simple report to be created - select vehicle (have a USB memory stick in one of your drives) - report is saved and created as a PDF for printing.
any queries post back on this site.
torachan.