473,325 Members | 2,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Add columns by clicking a button

I am creating a document management database using access.
Each job will have it's own database to log the documents for the job.
These documents will be issued over the period of the job as different revisions are created (rev A, B, C etc).
To create the issue sheet i need to be able to create a new column (Field) and specify the name of the column (Which will be the date of issue), The row will be the document and the data under the date field will be the revision letter.
I'm new to MS Access and was wondering if there was a way to create a button that would create a new column, maybe referencing a table that has all the possible revisions to choose from, and asks you for a name to call the field i.e. date of issue.

I seem to be going around in circles, any help would be gratefully appreciated.
Jan 30 '07 #1
7 1655
ADezii
8,834 Expert 8TB
I am creating a document management database using access.
Each job will have it's own database to log the documents for the job.
These documents will be issued over the period of the job as different revisions are created (rev A, B, C etc).
To create the issue sheet i need to be able to create a new column (Field) and specify the name of the column (Which will be the date of issue), The row will be the document and the data under the date field will be the revision letter.
I'm new to MS Access and was wondering if there was a way to create a button that would create a new column, maybe referencing a table that has all the possible revisions to choose from, and asks you for a name to call the field i.e. date of issue.

I seem to be going around in circles, any help would be gratefully appreciated.
You can dynamically create a New Field via the Click() Event of a Command Button in the following manner. The following code will create a [SSAN] Field within tblEmployee. It will be a Text Field with a defined length of 10. For further assistance, please reference the CreateField Method (DAO)
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As Database, MyTDF As TableDef
  2.  
  3. Set MyDB = CurrentDb()
  4. Set MyTDF = MyDB.TableDefs("tblEmployee")
  5.  
  6. With MyTDF
  7.   'Append the newly created Field to the Fields Collection
  8.   'of tblEmployee
  9.   .Fields.Append .CreateField("SSAN", dbText, 10)
  10. End With
Jan 30 '07 #2
MMcCarthy
14,534 Expert Mod 8TB
You will also have to be careful over time. There is a limit of 255 fields in an Access table.

It also sounds like you are trying to design the database the way you would an Excel spreadsheet. There are other ways of capturing this information.

Simply add a date field and create a new set of records each time rather than a new column of records. Then you won't have a problem with the column limit.

Mary
Jan 31 '07 #3
If there is an easier way, i'm all for it, simple is in my book always best.
However, Keep It Simple is sometimes easier said than done, i agree i'm probably thinking more in terms of excel than access, but can't get past the fact that for numerous documents in a list, i need to show not only the revision that has been issued (Of which there could be many) but also the dates at which each revision has been sent.
So there are 3 parts to the data all of which needs to be changed by the user.
The limit of 255 columns is not a problem because the database is job specific and not one database for all jobs, also if we were in the position of revising and issuing more than 254 times we have much bigger problems on the job than whether we have recorded the issues or not.
Please if anyone has an easier way to do this i'd be forever grateful.
Feb 2 '07 #4
MMcCarthy
14,534 Expert Mod 8TB
If there is an easier way, i'm all for it, simple is in my book always best.
However, Keep It Simple is sometimes easier said than done, i agree i'm probably thinking more in terms of excel than access, but can't get past the fact that for numerous documents in a list, i need to show not only the revision that has been issued (Of which there could be many) but also the dates at which each revision has been sent.
So there are 3 parts to the data all of which needs to be changed by the user.
The limit of 255 columns is not a problem because the database is job specific and not one database for all jobs, also if we were in the position of revising and issuing more than 254 times we have much bigger problems on the job than whether we have recorded the issues or not.
Please if anyone has an easier way to do this i'd be forever grateful.
Create a second table as a transaction table. This would handle version control.
In the first table you could store the following
jobID, documentID, originalDate, CurrentRevisionDate, revisionNum

In the second table

RevID, JobId, documentID, DateOfRevision, revisionNum

The second table would store an historical record of all document revisions and the current revision date in the first table would be updated to the latest revision date for this job and document in the second table.

I realise this may not fit your requirements exactly but should give you some idea of the kind of thing I'm talking about.

Mary
Feb 3 '07 #5
Thankyou for all your help, I am finally i hope moving in the right direction.
I have created a new table where each issue of each document is a new entry, i then found that if i create a crosstab query, using DocID as the row heading and Date as the column heading i can create the exact table i've been looking for, with the added bonus of having the current revision displayed in the second column.
Without thinking 'out of the box' or in this case 'out of excel mode' i wouldn't have been able to do this, so thankyou all.
My next problem is how to use this data i've created. I'll keep you posted
Feb 9 '07 #6
MMcCarthy
14,534 Expert Mod 8TB
Thankyou for all your help, I am finally i hope moving in the right direction.
I have created a new table where each issue of each document is a new entry, i then found that if i create a crosstab query, using DocID as the row heading and Date as the column heading i can create the exact table i've been looking for, with the added bonus of having the current revision displayed in the second column.
Without thinking 'out of the box' or in this case 'out of excel mode' i wouldn't have been able to do this, so thankyou all.
My next problem is how to use this data i've created. I'll keep you posted
I'm glad it's working for you. Changing from thinking Excel to working with databases is a problem for most people. You've done well picking it up.

Mary
Feb 9 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
I'm glad it's working for you. Changing from thinking Excel to working with databases is a problem for most people. You've done well picking it up.

Mary
You might like to try out this tutorial also:

Normalisation and Table structures


Mary
Feb 9 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

32
by: Rich | last post by:
I'm sure it sounds kinda nutty to display 200 columns and 500,000 rows of data. But I have been pulling data from a Lotus Notes database into Sql Server for a while now, but Lotus Notes is...
10
by: Jim Bayers | last post by:
We need to stop students from clicking on the form button more than once. We have a form that students fill out with their credit card information. They click, the form sends the data in xml to...
0
by: kevin_giles78 | last post by:
hello everyone. There may be a better way to do what I want but please bear with me as I need a simple explanation of how to do something with a data grid. I have a grid showing a status in a...
10
by: Rhino | last post by:
Let me begin by warning you all that I don't have sufficient background in CSS to state my question with the correct formal terminology so I'll just use plain English. Basically, I want to...
5
by: ljungers | last post by:
Hi to all. I hope someone can provide me with a solution to my problem. I have a Inventory master table that is used to produce a report. Currently a form is used so that either part of the item...
0
by: SolomonShavitzMetsFan | last post by:
Is there a way to have a button above each of three columns of a subform to do a runtime sort of the respective columns below the buttons? The only way I can think of is to have three different...
1
kmartinenko
by: kmartinenko | last post by:
I have a table with over 12,000 entries. I have created a form (with the help of this forum) that will return the search results based upon the stop and stop time selected. See post...
0
by: =?Utf-8?B?cGF0YW4uYW5pZkBnbWFpbC5jb20=?= | last post by:
Hi, My requirement is paging datagrid columns. not row paging Actually i have 18 columns at runtime..first 6 columns display at first time and remaining 6 will display by clicking next button...
1
by: miferdin | last post by:
Hi friends, 1. I have a datagrid with 5 columns, in this when i click edit in a row, all the columns changes into textbox but what i need is to make only a single column into a text...
0
by: Glenn Rosenthal | last post by:
I have a Web page with a datagrid for display search results. The first column contains a check box and a hidden field, and are defined with the datagrid. <asp:DataGrid...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.