If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Repetative Data Entry
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 |
#13
|
|||
|
|||
Repetative Data Entry
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 |
|
Thread Tools | |
Display Modes | |
|
|