Repetative Data Entry

Firstly, data should always be entered via a form, never directly into a
table in datasheet view.

Having created a form bound to the table, or better still to a query based on
the table which orders the records by some appropriate field or fields, you
can then set the DefaultValue property of a control on the form to whatever
you enter in the control the first time in each session while the form is
open. This is done by putting code in the form's AfterInsert event procedure.
e.g.

Me.[DoumentNumber].DefaultValue = """" & Me.[DocumentNumber] & """"
Me.[UseStatus].DefaultValue = """" & Me.[UseStatus] & """"

After entering the data for the first new record in the session, when you
move to another new record the values you entered in the controls will
already be in place. Note that the DefaultValue property is always a string
expression regardless of the data type of the underlying field, so should
always be wrapped in quotes characters as above.

Having said that, it may be that the reason you are having to enter so much
repetitive data is that the table is nor properly 'normalized' and needs
decomposing into two or more related tables. Normalization is an extremely
important part of good database design and you should make sure you are
familiar with its principles. You'll find a brief explanation at:

http://www.datamodel.org/NormalizationRules.html

At this stage I'd suggest you concentrate on making sure you understand
normalization up to Third Normal Form (3NF) which is pretty straightforward.
You can come back to the higher normal forms when you have gained more
experience.

Ken Sheridan
Stafford, England

iain wrote:
>I'm working on an Access 2007 database that has a great deal of repetative
>data entry and I'm looking for ways to reduce this.
>
>e.g. one table records basic details about documents, that includes a
>document number and it's use status.
>
>Documents are created in batches which means entering details for any number
>of documents, from say 1 or 2 documents, to several hundred.
>
>As I am a complete novice, I'm struggling to get my head arround the basic
>building blocks of how this might be automated.
>
>Could someone summerise this for me so that I can narrow down my search for
>further information.
>
>Thank you.

--
Message posted via http://www.accessmonster.com

Thank you for your reply. It is certainly a step in the right direction but
I still think I need something else.

The data is being entered via a form generated by a query.

The undelying table has four fields:
- an autonumber primary key field
- a libarary index identifier foreign key(combo box selection form another
table in the query)
- status (default is Unprocessed. Other status values are selected from a
combo box on other forms)
- document number(4 digit number with a 3 character prefix e.g. doc_1234,
img_5678. idi_9876)NB we cannot use this as a unique identifier as we cannot
rule out duplicate numbers

If I wanted to create 100 document numbers starting a 2001, would I not
still have to manually enter each record? This seems laborious if all that
is happening is that the actual document number increments with each new
record.

I'm looking for a solution that would automatically create the numbers for
me then update the table.

4120,1,Unprocessed,doc_2001
4121,1,Unprocessed,doc_2002
4122,1,Unprocessed,doc_2003 etc

I have thought about using a dialog box to enter say the first number and
the last number, or perhaps the number of new documents to create or
whatever, but I can't seem to build the sequence of steps required or find
examples of code that might do this.

Does this make more sense?

Thanks for your help,

Iain



"KenSheridan via AccessMonster.com" wrote:

> Firstly, data should always be entered via a form, never directly into a
> table in datasheet view.
>
> Having created a form bound to the table, or better still to a query based on
> the table which orders the records by some appropriate field or fields, you
> can then set the DefaultValue property of a control on the form to whatever
> you enter in the control the first time in each session while the form is
> open. This is done by putting code in the form's AfterInsert event procedure.
> e.g.
>
> Me.[DoumentNumber].DefaultValue = """" & Me.[DocumentNumber] & """"
> Me.[UseStatus].DefaultValue = """" & Me.[UseStatus] & """"
>
> After entering the data for the first new record in the session, when you
> move to another new record the values you entered in the controls will
> already be in place. Note that the DefaultValue property is always a string
> expression regardless of the data type of the underlying field, so should
> always be wrapped in quotes characters as above.
>
> Having said that, it may be that the reason you are having to enter so much
> repetitive data is that the table is nor properly 'normalized' and needs
> decomposing into two or more related tables. Normalization is an extremely
> important part of good database design and you should make sure you are
> familiar with its principles. You'll find a brief explanation at:
>
> http://www.datamodel.org/NormalizationRules.html
>
> At this stage I'd suggest you concentrate on making sure you understand
> normalization up to Third Normal Form (3NF) which is pretty straightforward.
> You can come back to the higher normal forms when you have gained more
> experience.
>
> Ken Sheridan
> Stafford, England
>
> iain wrote:
> >I'm working on an Access 2007 database that has a great deal of repetative
> >data entry and I'm looking for ways to reduce this.
> >
> >e.g. one table records basic details about documents, that includes a
> >document number and it's use status.
> >
> >Documents are created in batches which means entering details for any number
> >of documents, from say 1 or 2 documents, to several hundred.
> >
> >As I am a complete novice, I'm struggling to get my head arround the basic
> >building blocks of how this might be automated.
> >
> >Could someone summerise this for me so that I can narrow down my search for
> >further information.
> >
> >Thank you.
>
> --
> Message posted via http://www.accessmonster.com
>
>

Ian:

If as you say the only differences between the 100 records is the autonumber
value and that the document number is incremented by 1 for each record then
you can easily automate the process by looping from 0 to 99 in code and
inserting a row into the table each time. This can be wrapped up in a little
function into which the library index identifier, status, document prefix,
the starting number for the sequence and the number of documents are passed:

Function InsertDocuments(lngLibraryIndexNumber As Long, _
Status As String, _
DocPrefix As String, _
DocStartNumber As Long, _
NumberOfDocs As Integer)

Dim cmd As ADODB.Command
Dim strSQL As String
Dim n As Integer

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

For n = 0 To NumberOfDocs - 1
strSQL = "INSERT INTO [DocumentsTable]" & _
"([LibraryIndex],[Status],[DocumentNumber]) " & _
"VALUES(" & lngLibraryIndexNumber & ",""" & _
Status & """,""" & DocPrefix & "_" & DocStartNumber + n & """)"

cmd.CommandText = strSQL
cmd.Execute
Next n

End Function

You can call the function as the On Click event property of a button on a
dialogue form with controls to enter or select the values for its arguments,
e.g. with your sample data to insert 100 rows:

=InsertDocuments([cboLibIndex],[cboStatus],[cboDocPrefic],[txtDocStartNumber],
[txtNumberOfDocs])

Ken Sheridan
Stafford, England

iain wrote:
>Thank you for your reply. It is certainly a step in the right direction but
>I still think I need something else.
>
>The data is being entered via a form generated by a query.
>
>The undelying table has four fields:
>- an autonumber primary key field
>- a libarary index identifier foreign key(combo box selection form another
>table in the query)
>- status (default is Unprocessed. Other status values are selected from a
>combo box on other forms)
>- document number(4 digit number with a 3 character prefix e.g. doc_1234,
>img_5678. idi_9876)NB we cannot use this as a unique identifier as we cannot
>rule out duplicate numbers
>
>If I wanted to create 100 document numbers starting a 2001, would I not
>still have to manually enter each record? This seems laborious if all that
>is happening is that the actual document number increments with each new
>record.
>
>I'm looking for a solution that would automatically create the numbers for
>me then update the table.
>
>4120,1,Unprocessed,doc_2001
>4121,1,Unprocessed,doc_2002
>4122,1,Unprocessed,doc_2003 etc
>
>I have thought about using a dialog box to enter say the first number and
>the last number, or perhaps the number of new documents to create or
>whatever, but I can't seem to build the sequence of steps required or find
>examples of code that might do this.
>
>Does this make more sense?
>
>Thanks for your help,
>
>Iain
>
>> Firstly, data should always be entered via a form, never directly into a
>> table in datasheet view.
>[quoted text clipped - 47 lines]
>> >
>> >Thank you.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200909/1

PS: Sorry about misspelling your name. My younger son's name is Ian, so its
hard to get out of the habit!

KenSheridan wrote:
>Ian:
>
>If as you say the only differences between the 100 records is the autonumber
>value and that the document number is incremented by 1 for each record then
>you can easily automate the process by looping from 0 to 99 in code and
>inserting a row into the table each time. This can be wrapped up in a little
>function into which the library index identifier, status, document prefix,
>the starting number for the sequence and the number of documents are passed:
>
>Function InsertDocuments(lngLibraryIndexNumber As Long, _
> Status As String, _
> DocPrefix As String, _
> DocStartNumber As Long, _
> NumberOfDocs As Integer)
>
> Dim cmd As ADODB.Command
> Dim strSQL As String
> Dim n As Integer
>
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandType = adCmdText
>
> For n = 0 To NumberOfDocs - 1
> strSQL = "INSERT INTO [DocumentsTable]" & _
> "([LibraryIndex],[Status],[DocumentNumber]) " & _
> "VALUES(" & lngLibraryIndexNumber & ",""" & _
> Status & """,""" & DocPrefix & "_" & DocStartNumber + n & """)"
>
> cmd.CommandText = strSQL
> cmd.Execute
> Next n
>
>End Function
>
>You can call the function as the On Click event property of a button on a
>dialogue form with controls to enter or select the values for its arguments,
>e.g. with your sample data to insert 100 rows:
>
>=InsertDocuments([cboLibIndex],[cboStatus],[cboDocPrefic],[txtDocStartNumber],
>[txtNumberOfDocs])
>
>Ken Sheridan
>Stafford, England
>
>>Thank you for your reply. It is certainly a step in the right direction but
>>I still think I need something else.
>[quoted text clipped - 39 lines]
>>> >
>>> >Thank you.

--
Message posted via http://www.accessmonster.com

Thanks Ken,

I came very close to what you have written but couldn't get the SQL to run.
Your code however is more compact and simpler, so I will give this a go.

Don't worry about the name mispelling. I've been called worse.

Thanks again.

Iain

"KenSheridan via AccessMonster.com" wrote:

> PS: Sorry about misspelling your name. My younger son's name is Ian, so its
> hard to get out of the habit!
>
> KenSheridan wrote:
> >Ian:
> >
> >If as you say the only differences between the 100 records is the autonumber
> >value and that the document number is incremented by 1 for each record then
> >you can easily automate the process by looping from 0 to 99 in code and
> >inserting a row into the table each time. This can be wrapped up in a little
> >function into which the library index identifier, status, document prefix,
> >the starting number for the sequence and the number of documents are passed:
> >
> >Function InsertDocuments(lngLibraryIndexNumber As Long, _
> > Status As String, _
> > DocPrefix As String, _
> > DocStartNumber As Long, _
> > NumberOfDocs As Integer)
> >
> > Dim cmd As ADODB.Command
> > Dim strSQL As String
> > Dim n As Integer
> >
> > Set cmd = New ADODB.Command
> > cmd.ActiveConnection = CurrentProject.Connection
> > cmd.CommandType = adCmdText
> >
> > For n = 0 To NumberOfDocs - 1
> > strSQL = "INSERT INTO [DocumentsTable]" & _
> > "([LibraryIndex],[Status],[DocumentNumber]) " & _
> > "VALUES(" & lngLibraryIndexNumber & ",""" & _
> > Status & """,""" & DocPrefix & "_" & DocStartNumber + n & """)"
> >
> > cmd.CommandText = strSQL
> > cmd.Execute
> > Next n
> >
> >End Function
> >
> >You can call the function as the On Click event property of a button on a
> >dialogue form with controls to enter or select the values for its arguments,
> >e.g. with your sample data to insert 100 rows:
> >
> >=InsertDocuments([cboLibIndex],[cboStatus],[cboDocPrefic],[txtDocStartNumber],
> >[txtNumberOfDocs])
> >
> >Ken Sheridan
> >Stafford, England
> >
> >>Thank you for your reply. It is certainly a step in the right direction but
> >>I still think I need something else.
> >[quoted text clipped - 39 lines]
> >>> >
> >>> >Thank you.
>
> --
> Message posted via http://www.accessmonster.com
>
>

Hi Ken,

sorry but I just can't get this code of yours to work.

You say it is a function but there is no 'as' whatever at the end of the
first statement.

Also I cannot make any sense of the part of your SQL statement for VALUES.
eg I don't understand why you require parenthesis and '&' before and after
the variable names.

Iain

"KenSheridan via AccessMonster.com" wrote:

> PS: Sorry about misspelling your name. My younger son's name is Ian, so its
> hard to get out of the habit!
>
> KenSheridan wrote:
> >Ian:
> >
> >If as you say the only differences between the 100 records is the autonumber
> >value and that the document number is incremented by 1 for each record then
> >you can easily automate the process by looping from 0 to 99 in code and
> >inserting a row into the table each time. This can be wrapped up in a little
> >function into which the library index identifier, status, document prefix,
> >the starting number for the sequence and the number of documents are passed:
> >
> >Function InsertDocuments(lngLibraryIndexNumber As Long, _
> > Status As String, _
> > DocPrefix As String, _
> > DocStartNumber As Long, _
> > NumberOfDocs As Integer)
> >
> > Dim cmd As ADODB.Command
> > Dim strSQL As String
> > Dim n As Integer
> >
> > Set cmd = New ADODB.Command
> > cmd.ActiveConnection = CurrentProject.Connection
> > cmd.CommandType = adCmdText
> >
> > For n = 0 To NumberOfDocs - 1
> > strSQL = "INSERT INTO [DocumentsTable]" & _
> > "([LibraryIndex],[Status],[DocumentNumber]) " & _
> > "VALUES(" & lngLibraryIndexNumber & ",""" & _
> > Status & """,""" & DocPrefix & "_" & DocStartNumber + n & """)"
> >
> > cmd.CommandText = strSQL
> > cmd.Execute
> > Next n
> >
> >End Function
> >
> >You can call the function as the On Click event property of a button on a
> >dialogue form with controls to enter or select the values for its arguments,
> >e.g. with your sample data to insert 100 rows:
> >
> >=InsertDocuments([cboLibIndex],[cboStatus],[cboDocPrefic],[txtDocStartNumber],
> >[txtNumberOfDocs])
> >
> >Ken Sheridan
> >Stafford, England
> >
> >>Thank you for your reply. It is certainly a step in the right direction but
> >>I still think I need something else.
> >[quoted text clipped - 39 lines]
> >>> >
> >>> >Thank you.
>
> --
> Message posted via http://www.accessmonster.com
>
>