By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,640 Members | 2,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,640 IT Pros & Developers. It's quick & easy.

Insert Into..Values(function())..

P: 8
hi everyone, I need help on an error message. I am working on an environment that Access2003 (adp) as a front end and links to SQL server as a back end.
I am creating a table, which requests users insert a value(varchar) into the table throuth an input box on a form.

Please see the code is below:

Expand|Select|Wrap|Line Numbers
  1. Public Sub cmdAdd_Click()
  2.     Dim cmd As ADODB.Command
  3.     Set cmd = New ADODB.Command
  4.     Dim getStrUR As String
  5.         cmd.ActiveConnection = CurrentProject.Connection
  6.         cmd.CommandText = "INSERT INTO dbo.NoteStatEditRecord" & _
  7.             "(InputURNo)" & _
  8.             "VALUES(getInputNumber())"
  9.         cmd.Execute
  10. End Sub
the function getInputNumber() valides user input through input box and extract valide values(8 digits numeric values) from the input box. I have test the function in immediate windows. it works fine.

the code is below:

Expand|Select|Wrap|Line Numbers
  1. Public Function getInputNumber() As String
  2.     getInputNumber = InputBox("Enter an existing URNo to populate records.", "Please enter the URNo")
  3.     If getInputNumber <> "" Then
  4.         Do While Not IsNumeric(getInputNumber) Or Len(getInputNumber) <> 8
  5.         getInputNumber = InputBox("URNo is 8 digits numeric value, which contains" & _
  6.                         "no Letters and space. Please re-enter the URNo.", "Caution: Invalid input found")
  7.                         If getInputNumber = "" Then
  8.                             Exit Function
  9.                         End If
  10.         Loop
  11.  
  12.         Do While IsNull(DLookup("PID", "dbo.URs", "URNo = '" & getInputNumber & "'"))
  13.            getInputNumber = InputBox("URNo is not exist!")
  14.                If getInputNumber = "" Then
  15.                    Exit Function
  16.                End If
  17.         Loop
  18.     End If
  19.  
  20.  End Function
however, I am keeping receiving error message for the function cmdAdd_Click() above said that getInputNumber is not a recognised function name. See below:

Run-time error'-2147217900 (80040e14)':
'getInputNumber' is not a recognized function name.


Had anyone came across this issue before? Is there a solution? Thanks in advance for any suggestions. meng
Feb 11 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,638
hi everyone, I need help on an error message. I am working on an environment that Access2003 (adp) as a front end and links to SQL server as a back end.
I am creating a table, which requests users insert a value(varchar) into the table throuth an input box on a form.

Please see the code is below:

Public Sub cmdAdd_Click()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim getStrUR As String
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "INSERT INTO dbo.NoteStatEditRecord" & _
"(InputURNo)" & _
"VALUES(getInputNumber())"
cmd.Execute
End Sub

the function getInputNumber() valides user input through input box and extract valide values(8 digits numeric values) from the input box. I have test the function in immediate windows. it works fine.

the code is below:

Public Function getInputNumber() As String
getInputNumber = InputBox("Enter an existing URNo to populate records.", "Please enter the URNo")
If getInputNumber <> "" Then
Do While Not IsNumeric(getInputNumber) Or Len(getInputNumber) <> 8
getInputNumber = InputBox("URNo is 8 digits numeric value, which contains" & _
"no Letters and space. Please re-enter the URNo.", "Caution: Invalid input found")
If getInputNumber = "" Then
Exit Function
End If
Loop

Do While IsNull(DLookup("PID", "dbo.URs", "URNo = '" & getInputNumber & "'"))
getInputNumber = InputBox("URNo is not exist!")
If getInputNumber = "" Then
Exit Function
End If
Loop
End If

End Function

however, I am keeping receiving error message for the function cmdAdd_Click() above said that getInputNumber is not a recognised function name. See below:

Run-time error'-2147217900 (80040e14)':
'getInputNumber' is not a recognized function name.


Had anyone came across this issue before? Is there a solution? Thanks in advance for any suggestions. meng
  1. The Error you are describing would probably be generated if getInputNumber() were declared Privately in a Standard Code Module or Publicly in a Form's Code Module. getInputNumber() must be declared as Public in a Standard Code Module in order to be recognized.
  2. There also seems to be a problem with syntax. Since the getInputNumber() Function returns a String, you must qualify it before it can be used in the SQL Statement:
    Expand|Select|Wrap|Line Numbers
    1. Dim cmd As ADODB.Command
    2. Set cmd = New ADODB.Command
    3.  
    4. Dim getStrUR As String
    5.  
    6. cmd.ActiveConnection = CurrentProject.Connection
    7. cmd.CommandText = "INSERT INTO dbo.NoteStatEditRecord (InputURNo) VALUES('" & getInputNumber() & "')"
    8. cmd.Execute
  3. Good luck and let me know how you make out.
Feb 11 '08 #2

P: 8
Many thanks ADezii, It works out!!!

As your pointed out that my problem is I didn't qualify the String returned by the getInputNumber() Function. I have replaced the string getInputNumber() with '" & getInputNumber() & "'. it works now!

Thanks for the two tips, I will keep that in mind in future :o)

Cheers

meng
Feb 11 '08 #3

ADezii
Expert 5K+
P: 8,638
Many thanks ADezii, It works out!!!

As your pointed out that my problem is I didn't qualify the String returned by the getInputNumber() Function. I have replaced the string getInputNumber() with '" & getInputNumber() & "'. it works now!

Thanks for the two tips, I will keep that in mind in future :o)

Cheers

meng
You are quite welcome, meng.
Feb 11 '08 #4

Post your reply

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