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

[emlimeng]

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

[ADezii]

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:
   

   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

3. Good luck and let me know how you make out.

[emlimeng]

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

[ADezii]

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.