Hey theoretical question for you guys

[ConcordMan733]

Greetings, my name is James I'm new to the forum and new to access. Like any software though, I learn best by diving right into to working with it. That being said as I'm desigining my database (before I attempt to actually build it) I'm wondering if this is theoretically possible.....

Currently our company has several forms in excel that we print out, fill out, and use in the our day to day process. It would be in our interest to keep the forms the same way since we print out and file the hard copy for record keeping. Would it be possible in access to create a table that has all the fields that any of the given forms has (1 table per form of course) and have some sort of macro so that when you hit a button in access (like new such and such form) it would open the excel form you fill it out and it stores the information in hte database?

Sorry for the length of my post, I hope to contribute some in the future on this forum. thank you very much

 

[NigelShaw]

Hi

welcome to AWF

in answer to your question. Yes, yes and yes. A report would do what you want not a form. in essence, Access works like this-

Table - hold the data
Query - collect the data
Form - input the data
Report - display the data

you wouldnt actually need excel at all as any information can be stored striaght to the table with a report displaying it on the fly. Also, you are not limited to 1 table per form / report. in fact you can have a lot of data gathering to be displayed on 1 report. its how far you want to go that would bethe restriction.

in my humble opinion, you limit to either Access or Excel and only cross the pasths if you really need to. Access can do almost everything that Excel does with the limitation of the standard grid sheet and formula in cells but Access can manage the formulas in code.

it would be interesting to see how your forms are laid out and what type of data you need to display.

as a pointer, lets say that the form would display information stored in 3 tables. you could create query and link the tables together then use the query to base your report.

if you need any help, give me a shout, i'm happy to look at stuff and re-create (within reason) to assist learning.


thanks

Nidge

 

[ConcordMan733]

Hi there, thanks for the quick response. Interesting information, I have miss explained my overall goal though after reading what I wrote. Also more info about what forms we have.
I work in the Engineering/Testing Department of our company. The forms I'm working with now are Testing Request Forms and Inspection Request Forms. Each has about 46 blank fields for information. Not all are used each time necessarily though. The forms are actually used to specify all the information needed to run the test.
So currently we have these forms just set up in an excel file, and when I say forms I just mean an excel spreed sheet that's organized with blank fields for information to be put in. And a log of numbers that everytime we print one out and fill out we go in and add it to the next number on the log.

Hope I didn't bore you with the extra background information lol. Basically yes I understand Excel really isn't necessary to store anything, the interest is solely in keeping the same format as the excel file currently has.
Breakdown of how I envision the database working.
1. You have Tables set up for all the information in the forms (in access)
2. When you open the Database the Main page has boxes that show "Pending" Test and Inspection Requests
3. There's buttons on the main page that say "New Test Request". Upon clicking it does 1 of the following
A. Opens the Excel Spreedsheet with the form in it, you fill it out and somehow indicate that your done. It takes the information from each field and stores it in the database tables and prints your form out. (Assigns sequential number aswell)
B. A form within access opens and upon completion does as you explained create a report, with the report being the excel spreedsheet.

Once again sorry for the length of my post, I get a charge out of programming and once I start I just don't stop lol looking forward to mastering access.

 

[Shenlung]

Sounds to me like you could create a single table with columns for each of the myriad blank spaces in each of the forms, then recreate each of the ?2? forms you are using, point all the boxes on each form to whatever column they would need, then code the backend with a doCMD.RunSQL Update to add a new record to the table. Make the log number the primary key and autonumber it sequentially, and the log will increment by one every time a new record was created. You could code in error checking as well, to ensure required fields are not left blank, and use the properties sheet for certain textboxes to keep data consistent, with drop boxes for stuff that is more multiple choice than user data entry. When the user hits the "ok" button, it would run the doCMD code, and print the form, all in one go.

One thing though (and this one thing has saved my bacon with my codes!) when using DoCMD.RunSQL, always do it in "baby steps" with a debug.print command so you can get an idea of where codes are going sour.

Dim strSQL as String 

strSQL = "INSERT INTO tbl_ats_Comm_Draw_Hist " & _
  "( [Key], Cust_ID, Inv_Num, Tran_Type, Inv_Date, Inv_Amt,  Sls_RepID ) " & _
  "SELECT Seq_no, CustID, InvNum, TranType, [Date], Amount, Rep_1) " & _
  "FROM ARTAP " & _
  "WHERE TranType='L' AND Contract_type ='B'"
debug.Print strSQL
DoCmd.RunSQL strSQL

Stole that line from tek-tips.com, as I'm too lazy right now to write my own
The debug.print will dump the string into the immediate window(ctrl-g in the vba screen) to make debugging easier.

Good luck, I can't wait to see how this turns out, should be educational

 

[plog]

1 and 2 are correct and possible with Access. #3 you are tackling from an Excel perspective and that's incorrect.

#3 should work like this:

A form within access opens allowing user to enter new data. Upon completion there is a button that triggers code which allows the user to either exports just the data to Excel or generates the report you want.

It really depends on what the purpose of this report is for and what it is to look like, but most likely, it doesn't need to be sent to Excel.

 

[Lightwave]

CM

In your example most developers would do everything in Access with reports so they would go down path B with no export to excel.

I note that within B you say on completion you export to Excel.

Most would not bother unless certain users request specifically that the output SIMPLY HAS TO BE to in an Excel Spreadsheet rather than a report. The whole thing with databases is generally that they are brilliant at manipulating information.

And that includes complicated mathematics trig, calculus etc...

The main reason to export to excel would be to perform a complicated one off task.

ANY task that is repeated multiple times if at all possible

Do it in the database.

One off complicated tasks ok to go to a spreadsheet. As such sometimes if you have no idea what the information will be used for or there are millions of different ways of using it then a simple spreadsheet dump of the information can be useful.

For example I have a function that converts UK national grid coordinates to longitude and latitude.

Does it all in the database because I need it everday.

 

[ConcordMan733]

Ok thanks for the great advice guys, that is pretty much laid out for me can't get much simpler than that haha. I think your right in that in it would be better just to recreate the form within access, less complication in the database. Any advice in creating the user interface I referred to briefly though? Upon opening the main screen has like the pending, and then buttons for new forms. Would that kind of thing itself be a form? Thanks guys

 

[Lightwave]

The main difference between Spreadsheets and database is the idea of relationships. From a spreadhsheet perspective relationships indicate the way in which different worksheets within a single spreadsheet are connected. Tables by themselves are very similar to a single worksheet.

First thing to do is think about the structure.

The key concepts are

Identifying the main records
Identifying any sub records
Identifying any relationships between these Parent / Child records.

You may or may not have child records its difficult to say from your explanation.

A starting point might be to list all the fields on the form and simply put them all in a table. Then take that table and design it on a form on the screen.

If you have never designed a database before you will quickly come up with specific questions as you start to see how it all comes together.

One point

Create the table
Ensuring it has a Primary Key
You can set the data type of this to autonumber. It will be a unique number that identifies the record. It can be made to increment automatically and Access will ensure that the number is never allocated to more than one record.

Within this table add additional fields.

If a particular parent has children then you can add further tables as required and you will link it by refering to this primary key.