Repetative Data Entry

[iain]

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.

 

[Dorian]

It all comes down to keystrokes and mouse movements. You need the design to
require the minimum number of mouse movements and keyclicks as possible.
Also to minimize input errors you want everything possible to be chosen from
prebuilt lists rather than keyed in.
Have you thought about voice input?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[KenSheridan via AccessMonster.com]

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]

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.

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

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.

 

[KenSheridan via 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

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]

 

[KenSheridan via AccessMonster.com]

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

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]

 

[iain]

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

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

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.

 

[iain]

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

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

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.

 

[KenSheridan via AccessMonster.com]

It defaults to a variant if the function is not declared as a specific data
type. The function in this case is not intended to return any value. The
reason its declared as a function rather than a sub procedure is that it can
than be called as an event property in a control's properties sheet rather
than having to call it in the event procedure.

In what way is it not working? Is it raising a runtime error or just not
doing what's intended? If the former can you debug the code and see on which
line the error is occurring?

Or is it not compiling? If so one reason could be that you don't have a
reference to the ADO library. If so create one from Tools | References on
the VBA menu bar and selecting 'Microsoft Active X Data Objects #. # Library'
in the dialogue. Select the highest available version number.

The parentheses are there because the VALUES clause requires its value list
to be parenthesised; the ampersands are the operators which concatenate the
values of the variables into the string, which would thus evaluate to
something like:

INSERT INTO [DocumentsTable]
([LibraryIndex],[Status],[DocumentNumber])
VALUES(1,"Unprocessed", "doc_2001")

Each literal quote character in the above is represented by a pair of
contiguous quotes characters when building the string. This is how a literal
quotes character is represented in a string expression itself delimited by
quotes characters.

Ken Sheridan
Stafford, England

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

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

[quoted text clipped - 49 lines]

 

[iain]

I understand the point about defaulting to a variant and not returning a value.

I'm unsure about what you mean by calling it as an event property. Are you
saying that the event property box contains the function name and not the
words [Event Proceedure]?

The ADO library is selected.

When I try to run the code I receive a message box requesting the parameter
value for n which has to be entered for each pass of the loop. Even if
these values are entered manually the program simply inserts the same
document number for each pass of the loop. So for some reason the document
number is not incrementing.

I understand the use of parenthesis arround the VALUES and use of amepersand
as a cocatenation operator but couldn't follow the syntax clearly.
Appologies for misleading you. I meant to type 'quotation marks' not
'parenthesis'.

I am however, still very confused by the quotation marks and ampersands. and
may have mistakenly typed this statement when trying to correct the syntax
errors I received when I first inserted my object names into the code.

It seemed to me that there were too many ampersands and I would have
expected alternating use of single and double quotations. I couldn't make
sense of why some of the ampersands are required if constructing what is
simply a string.

Is it possible for you to retype this statement as one line using
alternating quotation marks (if that works for this statment) so that I can
see what is going together with what. I tried to do this and I think this
may be where I've gone wrong.

Sorry to be a pain (Can't teach an old dog new tricks I suppose)

Regards,
Iain

It defaults to a variant if the function is not declared as a specific data
type. The function in this case is not intended to return any value. The
reason its declared as a function rather than a sub procedure is that it can
than be called as an event property in a control's properties sheet rather
than having to call it in the event procedure.

In what way is it not working? Is it raising a runtime error or just not
doing what's intended? If the former can you debug the code and see on which
line the error is occurring?

Or is it not compiling? If so one reason could be that you don't have a
reference to the ADO library. If so create one from Tools | References on
the VBA menu bar and selecting 'Microsoft Active X Data Objects #. # Library'
in the dialogue. Select the highest available version number.

The parentheses are there because the VALUES clause requires its value list
to be parenthesised; the ampersands are the operators which concatenate the
values of the variables into the string, which would thus evaluate to
something like:

INSERT INTO [DocumentsTable]
([LibraryIndex],[Status],[DocumentNumber])
VALUES(1,"Unprocessed", "doc_2001")

Each literal quote character in the above is represented by a pair of
contiguous quotes characters when building the string. This is how a literal
quotes character is represented in a string expression itself delimited by
quotes characters.

Ken Sheridan
Stafford, England

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

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

[quoted text clipped - 49 lines]

Thank you.

 

[KenSheridan via AccessMonster.com]

Iain:

To call a function as an event property you do, as you assumed, enter the
function name in the properties sheet in place of [Event Procedure] or a
macro name, but you precede it with an equals sign. The values required by
the function as its arguments are placed within the parentheses following the
function name. In the example I gave you these are references to controls on
a dialogue form in which you select or enter the value for each argument, so
you'd enter the following, all as one line, as the event property:

=InsertDocuments([cboLibIndex],[cboStatus],[cboDocPrefix],[txtDocStartNumber],

[txtNumberOfDocs])

As regards building the SQL statement the following is the line of code as a
single line, though it will have been split into several by your newsreader
when you read it:

strSQL = "INSERT INTO [DocumentsTable] ([LibraryIndex],[Status],
[DocumentNumber]) VALUES(" & lngLibraryIndexNumber & ",""" & Status & ""","""
& DocPrefix & "_" & DocStartNumber + n & """)"

Looking at it again as written in the function over four lines for better
readability:

strSQL = "INSERT INTO [DocumentsTable]" & _
"([LibraryIndex],[Status],[DocumentNumber]) " & _
"VALUES(" & lngLibraryIndexNumber & ",""" & _
Status & """,""" & DocPrefix & "_" & DocStartNumber + n & """)"

what might be confusing you is that ampersands are used before the underscore
continuation characters at the ends of the first two lines to concatenate the
literal strings "INSERT INTO [DocumentsTable]" and "([LibraryIndex],[Status],
[DocumentNumber]) ", so these are not part of the expression when written as
one unbroken line, but the ampersand at the end of the third line
concatenates the value of the variable Status with the preceding literal
string ",""", so is included as part of the expression when written as one
unbroken line.

I have tested the function as posted and can confirm that it does insert the
rows into a table named DocumentsTable with the correct values in the
relevant columns.

Ken Sheridan
Stafford, England

I understand the point about defaulting to a variant and not returning a value.

I'm unsure about what you mean by calling it as an event property. Are you
saying that the event property box contains the function name and not the
words [Event Proceedure]?

The ADO library is selected.

When I try to run the code I receive a message box requesting the parameter
value for n which has to be entered for each pass of the loop. Even if
these values are entered manually the program simply inserts the same
document number for each pass of the loop. So for some reason the document
number is not incrementing.

I understand the use of parenthesis arround the VALUES and use of amepersand
as a cocatenation operator but couldn't follow the syntax clearly.
Appologies for misleading you. I meant to type 'quotation marks' not
'parenthesis'.

I am however, still very confused by the quotation marks and ampersands. and
may have mistakenly typed this statement when trying to correct the syntax
errors I received when I first inserted my object names into the code.

It seemed to me that there were too many ampersands and I would have
expected alternating use of single and double quotations. I couldn't make
sense of why some of the ampersands are required if constructing what is
simply a string.

Is it possible for you to retype this statement as one line using
alternating quotation marks (if that works for this statment) so that I can
see what is going together with what. I tried to do this and I think this
may be where I've gone wrong.

Sorry to be a pain (Can't teach an old dog new tricks I suppose)

Regards,
Iain

It defaults to a variant if the function is not declared as a specific data
type. The function in this case is not intended to return any value. The

[quoted text clipped - 46 lines]

 

[iain]

Sorry Ken, it's still a no go and I have to appologise for taking up so much
of your valuable time with this.

I have checked and double checked your emails and I am certain I have
followed your suggestions correctly. I have tested this on two separate
databases with completely different object names etc and I get the same
result.

The function is called from a button on the dialog box using the OnClick
property.

I have inserted an extra line into the For/Next loop that displays a message
box confirming the program has reached the first pass of the loop and when I
click on the Ok button I receive an error message.

There is no error number just a vague description of the problem which states:

"The expression OnClick you entered as the event property setting produced
the following error:

*Expression may not result in the name of a macro, name of a user-defined
function, or [Event Procedure]
*There may have been an error evaluating the function, event or macro"

The Help message states:

"This error occurs when an event has failed to run because Microsoft Office
Access cannot evaluate the location of the logic for the event. For example,
if the OnOpen property of a form is set to =[Field], this error occurs
because Access expects a macro or event name to run when the event is fired."

This is a repeat of an earlier error that has had me looking for typos and
incorrect syntax but I can't find any. Everything seems to be correct.

I can see from earlier replies that you are beginning to repeat yourself but
if there is anything here that I am missing, my appologies again.

Iain

Iain:

To call a function as an event property you do, as you assumed, enter the
function name in the properties sheet in place of [Event Procedure] or a
macro name, but you precede it with an equals sign. The values required by
the function as its arguments are placed within the parentheses following the
function name. In the example I gave you these are references to controls on
a dialogue form in which you select or enter the value for each argument, so
you'd enter the following, all as one line, as the event property:

=InsertDocuments([cboLibIndex],[cboStatus],[cboDocPrefix],[txtDocStartNumber],

[txtNumberOfDocs])

As regards building the SQL statement the following is the line of code as a
single line, though it will have been split into several by your newsreader
when you read it:

strSQL = "INSERT INTO [DocumentsTable] ([LibraryIndex],[Status],
[DocumentNumber]) VALUES(" & lngLibraryIndexNumber & ",""" & Status & ""","""
& DocPrefix & "_" & DocStartNumber + n & """)"

Looking at it again as written in the function over four lines for better
readability:

strSQL = "INSERT INTO [DocumentsTable]" & _
"([LibraryIndex],[Status],[DocumentNumber]) " & _
"VALUES(" & lngLibraryIndexNumber & ",""" & _
Status & """,""" & DocPrefix & "_" & DocStartNumber + n & """)"

what might be confusing you is that ampersands are used before the underscore
continuation characters at the ends of the first two lines to concatenate the
literal strings "INSERT INTO [DocumentsTable]" and "([LibraryIndex],[Status],
[DocumentNumber]) ", so these are not part of the expression when written as
one unbroken line, but the ampersand at the end of the third line
concatenates the value of the variable Status with the preceding literal
string ",""", so is included as part of the expression when written as one
unbroken line.

I have tested the function as posted and can confirm that it does insert the
rows into a table named DocumentsTable with the correct values in the
relevant columns.

Ken Sheridan
Stafford, England

I understand the point about defaulting to a variant and not returning a value.

I'm unsure about what you mean by calling it as an event property. Are you
saying that the event property box contains the function name and not the
words [Event Proceedure]?

The ADO library is selected.

When I try to run the code I receive a message box requesting the parameter
value for n which has to be entered for each pass of the loop. Even if
these values are entered manually the program simply inserts the same
document number for each pass of the loop. So for some reason the document
number is not incrementing.

I understand the use of parenthesis arround the VALUES and use of amepersand
as a cocatenation operator but couldn't follow the syntax clearly.
Appologies for misleading you. I meant to type 'quotation marks' not
'parenthesis'.

I am however, still very confused by the quotation marks and ampersands. and
may have mistakenly typed this statement when trying to correct the syntax
errors I received when I first inserted my object names into the code.

It seemed to me that there were too many ampersands and I would have
expected alternating use of single and double quotations. I couldn't make
sense of why some of the ampersands are required if constructing what is
simply a string.

Is it possible for you to retype this statement as one line using
alternating quotation marks (if that works for this statment) so that I can
see what is going together with what. I tried to do this and I think this
may be where I've gone wrong.

Sorry to be a pain (Can't teach an old dog new tricks I suppose)

Regards,
Iain

It defaults to a variant if the function is not declared as a specific data
type. The function in this case is not intended to return any value. The

[quoted text clipped - 46 lines]

Thank you.

 

[iain]

Ken,

working at last, so I hope you pick this up before I waste any more of your
time.

the line 'cmd.Execute' seemed to be causing the problems so I've commented
it out and replaced it with 'DoCmd.RunSQL = strSQL' and it works a treat.

I'm still not sure of the syntax involving the quotation marks and the
ampersands but I posted another message with a similar request which came
back excatly as yours so now I know I have to research this particular syntax
to gain a fuller understanding.

Thank you very much for all your help and persistence.

Best regards

Iain

Iain:

To call a function as an event property you do, as you assumed, enter the
function name in the properties sheet in place of [Event Procedure] or a
macro name, but you precede it with an equals sign. The values required by
the function as its arguments are placed within the parentheses following the
function name. In the example I gave you these are references to controls on
a dialogue form in which you select or enter the value for each argument, so
you'd enter the following, all as one line, as the event property:

=InsertDocuments([cboLibIndex],[cboStatus],[cboDocPrefix],[txtDocStartNumber],

[txtNumberOfDocs])

As regards building the SQL statement the following is the line of code as a
single line, though it will have been split into several by your newsreader
when you read it:

strSQL = "INSERT INTO [DocumentsTable] ([LibraryIndex],[Status],
[DocumentNumber]) VALUES(" & lngLibraryIndexNumber & ",""" & Status & ""","""
& DocPrefix & "_" & DocStartNumber + n & """)"

Looking at it again as written in the function over four lines for better
readability:

strSQL = "INSERT INTO [DocumentsTable]" & _
"([LibraryIndex],[Status],[DocumentNumber]) " & _
"VALUES(" & lngLibraryIndexNumber & ",""" & _
Status & """,""" & DocPrefix & "_" & DocStartNumber + n & """)"

what might be confusing you is that ampersands are used before the underscore
continuation characters at the ends of the first two lines to concatenate the
literal strings "INSERT INTO [DocumentsTable]" and "([LibraryIndex],[Status],
[DocumentNumber]) ", so these are not part of the expression when written as
one unbroken line, but the ampersand at the end of the third line
concatenates the value of the variable Status with the preceding literal
string ",""", so is included as part of the expression when written as one
unbroken line.

I have tested the function as posted and can confirm that it does insert the
rows into a table named DocumentsTable with the correct values in the
relevant columns.

Ken Sheridan
Stafford, England

I understand the point about defaulting to a variant and not returning a value.

I'm unsure about what you mean by calling it as an event property. Are you
saying that the event property box contains the function name and not the
words [Event Proceedure]?

The ADO library is selected.

When I try to run the code I receive a message box requesting the parameter
value for n which has to be entered for each pass of the loop. Even if
these values are entered manually the program simply inserts the same
document number for each pass of the loop. So for some reason the document
number is not incrementing.

I understand the use of parenthesis arround the VALUES and use of amepersand
as a cocatenation operator but couldn't follow the syntax clearly.
Appologies for misleading you. I meant to type 'quotation marks' not
'parenthesis'.

I am however, still very confused by the quotation marks and ampersands. and
may have mistakenly typed this statement when trying to correct the syntax
errors I received when I first inserted my object names into the code.

It seemed to me that there were too many ampersands and I would have
expected alternating use of single and double quotations. I couldn't make
sense of why some of the ampersands are required if constructing what is
simply a string.

Is it possible for you to retype this statement as one line using
alternating quotation marks (if that works for this statment) so that I can
see what is going together with what. I tried to do this and I think this
may be where I've gone wrong.

Sorry to be a pain (Can't teach an old dog new tricks I suppose)

Regards,
Iain

It defaults to a variant if the function is not declared as a specific data
type. The function in this case is not intended to return any value. The

[quoted text clipped - 46 lines]

Thank you.