Repetative Data Entry

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

iain wrote:
>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.

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

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

"KenSheridan via AccessMonster.com" wrote:

> 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
>
> iain wrote:
> >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.
>
> --
> Message posted via http://www.accessmonster.com
>
>

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

"KenSheridan via AccessMonster.com" wrote:

> 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
>
> iain wrote:
> >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.
>
> --
> Message posted via http://www.accessmonster.com
>
>