database spreadsheet set up for EASY data entry
database spreadsheet set up for EASY data entry
(OP)
I would like to set up a very simple database with the goal of making data entry as “easy” (intuitive) as possible. Hopefully someone who doesn’t know excel can walk up, launch the file, and be immediately presented with a data entry screen.
Is it possible. The fields I have in mind are:
DATE
MACHINE ID
VOLUME OF OIL ADDED (OUNCES)
REASON FOR ADD
COMMENTS
If possible we would like REASON FOR ADD to offer the following choices.
LEAKAGE
NORMAL USEAGE
SAMPLE
MAINTENANCE OIL CHANGE
UNKNOWN
I have looked a little at the help for the forms feature but I’m not famiiliar with it and any suggestions would be appreciated to get me started.
One hurdle is getting the form to launch immediately upon opening the file.
Another hurdle - From brief experimenting with forms it seems like the form sometimes seems to come up with the last data filled in and I have to press “new” button to get a new data entry screen. I don’t want that... want a fresh (blank) data entry screen immediately when open the file.
Another hurdle - I don't know how to create the "list-of-values" choice for "REASON FOR ADD" using forms.
Any thoughts, suggestions, comments, or examples?
Is it possible. The fields I have in mind are:
DATE
MACHINE ID
VOLUME OF OIL ADDED (OUNCES)
REASON FOR ADD
COMMENTS
If possible we would like REASON FOR ADD to offer the following choices.
LEAKAGE
NORMAL USEAGE
SAMPLE
MAINTENANCE OIL CHANGE
UNKNOWN
I have looked a little at the help for the forms feature but I’m not famiiliar with it and any suggestions would be appreciated to get me started.
One hurdle is getting the form to launch immediately upon opening the file.
Another hurdle - From brief experimenting with forms it seems like the form sometimes seems to come up with the last data filled in and I have to press “new” button to get a new data entry screen. I don’t want that... want a fresh (blank) data entry screen immediately when open the file.
Another hurdle - I don't know how to create the "list-of-values" choice for "REASON FOR ADD" using forms.
Any thoughts, suggestions, comments, or examples?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.





RE: database spreadsheet set up for EASY data entry
To get you started:
To display your form at workbook open:
in a module insert a sub procedure to display your form:
SUB ShowForm
USERFORM1.SHOW
END SUB
In the THISWORKBOOK object add the call
Private Sub Workbook_Open()
Module1.ShowForm
End Sub
RE: database spreadsheet set up for EASY data entry
RE: database spreadsheet set up for EASY data entry
WMO - So far I launch the form by selecting range and selecting data/form. I think yours works with a form template. I haven't figured out how to use a form template to update a database spreadsheet.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: database spreadsheet set up for EASY data entry
Are you wanting to do this in VBA?
RE: database spreadsheet set up for EASY data entry
Do you really need to use forms, or can you just use the standard Excel spreadsheet? As per the Excel help, forms are used "to collect data from Web and e-mail users."
RE: database spreadsheet set up for EASY data entry
I agree with Guido on this. If you want the benefit of a dropdown list choice then data validation is the ticket but it will not work with data/form.
By selecting the range and using data/forms if you enter into a cell (where data validation governs)and you violate the data validation it generates an error with a retry or cancel option. You would have to know what the data validation rule is. So if you set up a data validation list that included "LEAKAGE" and someone enters "LEAKS" it kicks out and the user would not know that it has to be leakage.
I think either keep it as a regular spreadsheet as Guido suggested or delve into creating it with VBA
RE: database spreadsheet set up for EASY data entry
So, data entry on some kind of data entry screen (not the bottom of a lspreadsheet) is mandatory.
Data type checking in the "REASON FOR ADD" field is nice but optional.
I have used two or three vba functions. I want to learn more about it one day and maybe this is the day. If it can be broken into manageable chunks like the example above to open a form upon opening a spreadsheet, I think I can handle that.
One thing I am thinking is that I can almost mimick an excel form by creating a data entry tab with instructions and macro's.
Still open to ideas/suggestions and comments. I haven't devoted a lot of time to the problem yet, just hoping to get some input before I really get started.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: database spreadsheet set up for EASY data entry
You can then use VBA to take the validated data from the cells and transfer the data to where you want. The VBA could be activated by a simple button embedded in the sheet.
TTFN
RE: database spreadsheet set up for EASY data entry
Ranges A1 through I1 on the sheet have a Counta formula to count the number of items in the list. This number is loaded into i and used as a counter which then adds each cell item into the combobox, The ComboBoxes are named Company, Project, Price_index, Reciever and Contact. The .Additem method is what puts the info into the combobox.
When the form opens the user will either type in the boxes or use a pull down list to select a choice. If typing, the combobox can auto complete the most logical choice. Mine is currently set to allow entries which do not appear in the pre loaded list, I am not sure if you can limit the input but it seems likely that you can.
When the user is done entering data he can click the finish button which then transfers the values of the combo boxes to the applicable cells. You should not have problems with persistent values remaining in the boxes. If you do you can have the code set all values to "" or 0 after writing chosen valuse to the Excel spreadsheet.
Your validation data will probably not be on the same sheet as the information to be kept, therefore you will have to activate the appropriate sheets before reading or writing information. If you need help with any of this respond with your questions.
CODE
On Error Resume Next
Dim x As Integer, i As Integer, NM As String
Windows("^Customer Data.xls").Activate
Sheets("Sheet3").Select
i = Range("A1")
For x = 1 To i
NM = Cells(x + 2, 1)
Company.AddItem NM
Next
i = Range("D1")
For x = 1 To i
NM = Cells(x + 2, 4)
Project.AddItem NM
Next
i = Range("C1")
For x = 1 To i
NM = Cells(x + 2, 3)
Price_index.AddItem NM
Next
i = Range("I1")
For x = 1 To i
NM = Cells(x + 2, 9)
Receiver.AddItem NM
Next
Sheets("Sheet2").Select
i = Range("B1")
For x = 1 To i
NM = Cells(x + 2, 1)
Contact.AddItem NM
Next
End Sub
RE: database spreadsheet set up for EASY data entry
If you're not confident analyzing data in Access, you can always export the Table data to Excel.
RE: database spreadsheet set up for EASY data entry
I chose to learn VBA due to my opinion that it would be easy since I was adept at using Excel. It was not easy.
Learning VBA may have been the long route but I feel that it was more versatile since VBA can be applied to more than one application.
Using Excel as a database is awkward and prone to error. Excel's strength remains in calculating and information translation. The best solution would be to use Access (or another database system) to manage the data and Excel to interpret what the data means.
RE: database spreadsheet set up for EASY data entry
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: database spreadsheet set up for EASY data entry
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: database spreadsheet set up for EASY data entry
the excel help system provides information to get you started in creating what you need. btw, it is possible to use the excel template and store the data in an access database without having ms access installed. the computer needs the drivers. i would suggest searching the mighty web for further help with this feature. perhaps john walkenbach, ozgrid, mrexcel, etc, websites may be of further help. sorry for not listing the exact website, but i'm being sloppy today - sort of anxious too.
also, excel can also be used to extract data (via query) from ms access & other databases. the user has many options to select data and then sort and arrange as needed.
concluding, investigate the template wizard with data tracking feature or add-in within excel and then search the web for further help.
good luck!
-pmover