Hi,

I have an ongoing project with my company for the duration of my internship.

The portion that I am trying to work on currently is a quick and user friendly way to record the work preformed by the operators. I want them to be able to check a few boxes and maybe put in some comments into a text box, if possible, and then have that data be exported to an Excel. I broke it down into a few different components:

1) Record the date and time in Cells A and B of the line.
2) Read from a combobox, that the operator already has selected earlier in the program, and put that data in Cell C
3) Read which checkbox is clicked and put it into cell D. Only one check box can be click at a time. There are three boxes.

Those are the three major requirements, the rest are minor things, they will be a great aid, though they are not entirely essential

4) A space for the user to input comments which then would be input to Cell E
5) A way to create an excel based on year, i.e. "2012 Data", that will be create in a specified location on a server. if it does not exist in that location.
6) A way to create a sheet based on the Month, i.e. "April", that will be created if it does not exist.
7) A way to protect the excel so that it can only be modified through Access, if possible.

Having posted this in the worng sub-forum and being directed here, it was suggested that it may be easier to write the information to an Access database and then export it over.

If you have any suggestions or comments about any of the components, or need additional information don't hesitant to message.

Thanks,
CarsonJ

*edit* Access 2007

is there a business reason that requires you to store the data in Excel files?

hth
tina

Mostly it is due to Excel being the prefered program. While I am getting increasingly familiar with Access, this data needs to be accessable by the supervisors and engineers who aren't Access-saive.

It is my goal to make this, so that if there is an issue with the work being done, the records can be easily checked and sorted for the needed information. Excel is simply the best option I know of that everyone has a basic understanding of.

Thanks for asking,
CarsonJ

hmm, okay. an easier compromise might be to enter (via forms) and store the data in normalized Access tables. then, again via forms, provide your users with options to choose a subset (by date parameters, for instance) of the data, and export to Excel, whenever they need to manipulate it on-the-fly. for recurring, standardized data analysis needs, you can provide reports in Access for the users to review.

hth
tina

We definately could store it in Access tables and then export it from there. I would just like to have it be automated. Since this program is used by the operators, I have no way of knowing the computer knowledge of the user. This is blunted silightly by traing the users, however, I still want to try and make it as easy as possible on them.

As to showing the information stored, I see what you are suggesting however, I think I didn't clarify enough for you to grasp the whole situation. I am an intern for this company. I leave every three months to go back to school for three months. When I graduate in a year and a half, I may or may not be working for this specific plant. So any actions that involve someone going into Access to recover information need to be avoided.

The overall goal of the program is to provide operators on the floor with instructions and quality standards, while providing the supervisors with an easily accessable record of the work done. Since I have already worked the majority of the bugs out of the operator's side of the program, all that's left is the recording side. The supervisors won't be manipulating the record, just viewing it. Access isn't a familar program to most, I have been asked to export the data to Excel so that it can easily be looked at by any of the office staff.

It is my hope that once the program is complete, there should be no need to open the code and data storage side of it again. It will simply work autonomously to display and record information. The less work needed to maintain the program and recover the information stored, the better it is since I am the only one in the plant that has a working knowledge of Access.

I understand I am asking for a lot of help, but if there is any scrap of code you think could help, any resource of Access that would complete this operatoration, or any alternative that would fit the basic guide lines then I would be most appreciative. I am at a loss of how to do this.

-CarsonJ

i think you're not familiar with the power of Access automation, hon. just as we can use this website and forum without needing to know a thing about how to build a webpage or write code to run a webpage, or ever have to "go behind the scenes"...so too can you set up an Access application for data entry via forms, with storage in tables, and data retrieval/export again via forms, and fully automated. if a user wanted to see all records for March 2012, for instance, s/he could enter the year and month in two controls on a form, click a button, and the relevant data would automatically be output to an Excel file - where the user could review and manipulate it to his/her heart's content.

if you can convert a copy of your db to Access 2003, zip it, and post it to this thread, i'll take a look and see if i can give you a basic setup with a basic form or two, including the ability to export datasets to Excel. i can post the demo for you to download; once you have a better picture of what i'm talking about, you'll be in a better position to decide if this is how you want to handle your project.

hth
tina

You are absolutely correct in saying I don't understand the automation process of Access. However, I don't really have a database to show you. I have a database of powerpoints, excels and pictures that are displayed through a Visual Basic code, but other than that I don't have much. I've attached what I do have.

Like I said, I am not sure how to do this. The way I envisioned this is that an operator can choose between three check boxes, and tyoe some comments in if neccesary. They can then press a button, and everything is recorded. From there, I am limited by my knowledge as to how it should work so that it is easiest for the operators and supervisors to use.

I thank you again for helping me with this project. It's because of people like you that I have been able to do as much as I have.

okay, i'll take a look in the next day or two.

hth
tina

Thanks for checking it out tina.

I took some time to further research, and I think I understand what it is you are suggesting. On the form I have, I need to create someway to record information to an Access Database. Then I need to create a Record that will use that database to display the information based on search criteria. Am I close?

If I am, do you, or anyone reading this, have an ideas on how to do this? So far my experience with Access is bringing files from external datbases to be shown in Access. I've never really tried to use Access for it's original purpose.

TIA
CarsonJ

okay, Carson, here is a very rough demo that shows you one way you can enter data via a form, storing it in the underlying table, and then review it in another form and output the data to Excel. sorry i took so long to get back to you; this is the first free minute i've had since my last post.

i couldn't look at the setup in the db you posted because A2003 does not support navigation forms, so i could only see the tab control in Design view. so i reread the posts in this thread, and built tables and forms based on the very sparse information i gathered. i have no doubt that the setup is not suitable for your work process - but that doesn't matter. the point was just to show you how data can be entered/stored in tables via forms, then viewed/exported via forms.

if you choose to go this route in your project, you'll need to read more on multi-user dbs. for instance, you'll need to split the db into a backend - tables only - and a frontend - all other objects, with links to the tables in the backend db. the backend should reside on a server that all users have access to, and each user should have a frontend db on his/her PC.

hth
tina

Click to view attachment

Thank you for the help tina t. It may be rough, but your database does show me quite a bit about how Access can be used to store and show records.

Which leads me to my current dilemma. My company just informed me that they will not be using Access to view these records. This is due to the added cost of getting Access for the people who will be using it. It would appear that they wish to have the power of Access but not the ease of it.

I believe that leaves us with trying to export a query to excel. I know this is the harder way to go and that it is something that you have been suggesting against from the start of this topic. I see no way to change the circumstances to allow for access to be used.

That being said, I have started to look for ways to do this. While I am not entirely sure how to write the information to a query, table, or a format that can be exported, I do know that we can get a system in place to export it to Excel once I do figure that part out.

Looking at the code you sent me, this line: DoCmd.OutputTo acOutputQuery, "qryMoldActions", acFormatXLS looks to do the job nicely. How much flexibility can we get out of this. Does it take the whole query as is, or can we move 1 line at a time? Specifically, if I add one line to the query per entry can I then export just that one line to the Excel? Will that kine add to the Excel or replace the previous entry? Can I make it go to a specific work sheet based on the date it was recorded?

Thank you for your support and time,
CarsonJ

DoCmd.OutputTo acOutputQuery, "qryMoldActions", acFormatXLS

you can go into the code module, put your cursor anywhere in the word "OutputTo", and press F1. that will take you to the topic in VBA Help. in the topic, there will probably be a link to the OutputTo Action. read that related topic as well. i think that will answer some of your questions.

if you're going to store the data in Excel, and only intend to use Access as a user interface, you can certainly do that. you might also want to consider using VB6 to create the interface, and write the data directly into Excel. for that matter, you could write the data directly into Excel using VBA behind an Access form, rather than writing the data to an Access table and then outputting to Excel. just a thought.

hth
tina

I've looked into the Outputo command and I think I have a handle on that. You mentioned move the data right to Excel with out going through Access. How would that be achomplished? If you have any ideas, I'd love to hear them.

-CarsonJ

i'm afraid we're moving outside the area where i can offer any useful assistance, Carson. i've asked the VIPs for help; hopefully somebody with some experience with be able to give you a hand. you've stuck with this thread for a couple weeks now - partly due to my slow responses - so i hope you can give it another day or two to see if anyone else can help you out. good luck with your project. :) tina

Thank you for your help Tina. You definately helped me understand more about Access even if I can't go with your suggestions. Congrats on passing the 1000 post mark as well.

-CarsonJ

From your first post it sounds like you need to develope a database (or write a program in a programming language). Clarity what role Excel is supposed to play in this and why you need it.

The entirety of the program is to display powerpoints, excels, and pictures in a single window so that the users does not have to navigate through windows to find and see them. The purpose of this is that it is being used on a plant floor where there is no garuntee of the user's computer skills. The less work they have to do to get the imformation they need the better. I currently have most of the program functional. I can display all the required documents and have built in fail-safes that will act if there is a missing document.

The current issue is trying to figure out a way to record the work that the operators did electronically and in such a way that the supervisors can easily access the information. Tina suggested that I record the work done within Access and have a Report that would display the information for the supervisors. This idea was shot down by my bosses due to the added expense of buy Access for all the personel that would need to have access to the information (it comes out a a hefty sum).

This leaves us with exporting the information to Excel so that it can be accessed by the supervisors using software they already own. I currently have somehow (I'm not very good with Access) figured out a way to get the information into a query in this topic. I am now trying to figure out a way that will relay that information to a specified excel in such a way that the information gets there and the previous information is not deleted or written over. Personally, in my non-programming mind, I think it should be something that adds a line to the top of the Excel and then inputs the data. However, I know very little about the power of Access as demonstraited earlier in this topic. I am open to any and all ideas about how to accomplish this goal.

I hope this gives you an idea as to what I am trying to accomplish. I hope you all can help me with this project.

-CarsonJ

*edit* minor spelling errors

They have Excel but they don't have Access? Are they using the home or student version. Access 2010 is available separately and the last time I priced it, it was $139.

Creating an Excel spreadsheet from Access is simple enough. Using VBA code you would either create an Excel object or do transferspreadsheet. You can find lots of examples of both at this site or on the net. You can also export to Excel from Access by clicking on External Data.

Let's say it's $150 for sake of math. That $150 per person. There are 12 people at this plant that would need Access, so $1800. Then if this program works as expected it has a good chance to be used throughout the plants in the U.S. and Mexico. There are at least 10 poeple, probably more, in each of the 25 other plants that would need to use it. So 250 people or $37,500 to buy a software for which the company would only have 1 use for currently. True, if we had it I'm sure we could find more uses for it, but I'm not going to be able to talk my bosses into spending anywhere between $1800 and $37,500 dollars for something that should be possible without. It adds a ton of burden to the programming side, but such is our lot.

I'll continue to search for examples, but if you find any please post them here. It'll help me out quite a bit.

-CarsonJ

*edit* rewrote sentance for more clarity

there's one here

That would do the job nicely, but I think I might have been able to make soemthing else work. Tina suggested earlier that I use a query to get the information from the form to a table. Teh table would then be used to display the information in a report.

If I do this:

It should accomplish the same goal. The question now is which is better?

I know this works, but can you forsee a reason that the other code would work better? I'm asking because I'd rather make the best program I can so that noone has to do maintenance on this.

CarsonJ,
Here is my two cents on this.

Excel really not a good option for this many people to share the data. A multi user application would be so much more productive than Excel.

If this were my client I would build an Access application. No Excel needed. This would allow lots of workflow to be built in.

To deploy the front end to 250 people (desktop) cost is $0 with a potential savings of over $37,500. What? How can that be? Use the free Access runtime. The saving comes from not having to purchase Excel.


The using a properly normalized database will help reduce future support maintenance. Using Excel will probably guarantee a lot more future support and maintenance that is done in Access.

If the Access application is well design the user will not need to be very skilled with Access. This is not true with Excel.

Access runtime sounds like a good option. I hope you'll consider that. What version of Office are you using right now?

To answer your question, that type of code is okay if you just want to dump something on a speadsheet. I think it's the same as just clicking up an export manually. It's fine if it satisfies your requirements.

We're using Office 2007. I like the option of using Access Runtime. It would not only keep everything "in house" but it would also make it so I didn't have to figure out a way to prevent changes from being made by "unauthorized personel". I've presented thsi to my bosses and am currently waiting to hear back from them and IT before I can go ahead.

If I do get to go with Runtime, then the whole thing would simply be making a query to store the information in a table and then create a Report to search through and display the table, right? I say simply but there is a bit of work in there. I could actually reuse the whole process I have been working on to get the info to Excel. Instead of dumping it out fo Access I would keep it in the table and work with it from there. I think I'm on the right track.

Thanks for the ideas, this should be a lot less finicky than going to Excel.


You got me there, but the point is that I want to create the most user friendly program given the restrictions placed upon me. That really is what everone wants for their programs.

-CarsonJ

You're welcome. I have never used runtime myself.

I think you are seeing the benefits of using Access. I find the Access runtime really helps to reduce software costs.

The may also help:
What is the Access Runtime Version?

Access 2007 runtime deployment - FAQs

That it does, my boss approved the idea, though IT hasn't gotten back to me yet, so I will be moving forward. Since I have been working in another sub-forum to get the query to work properly, all I have left to figure out is the report. I'll start a different topic in that sub-forum if I need some help (9 to 1 odds that I will).

Thank you Tina t, HiTechCoach, and projecttoday for the great advice and helpful tips and links. You all really helped me understand more about Access.

-CarsonJ

CarsonJ,

You're welcome. I am glag that Tina t, projecttoday, and I could assist you.

Good luck with your project.

you're welcome, hon. i'm glad the guys stepped in and gave you some suggestions that will really work for your situation - UA is tops! good luck with your project, and come back to the forums anytime you need a hand. :) tina