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:

[CODE=vb]Public Sub cmdAdd_Click()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim getStrUR As String
cmd.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = "INSERT INTO dbo.NoteStatEdi tRecord" & _
"(InputURNo )" & _
"VALUES(getInputNumber( ))"
cmd.Execute
End Sub[/CODE]

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:

[CODE=vb]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(getIn putNumber) Or Len(getInputNum ber) <> 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[/CODE]

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.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = "INSERT INTO dbo.NoteStatEdi tRecord" & _
"(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(getIn putNumber) Or Len(getInputNum ber) <> 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:
   [CODE=sql]
   Dim cmd As ADODB.Command
   Set cmd = New ADODB.Command
   
   Dim getStrUR As String
   
   cmd.ActiveConne ction = CurrentProject. Connection
   cmd.CommandText = "INSERT INTO dbo.NoteStatEdi tRecord (InputURNo) VALUES('" & getInputNumber( ) & "')"
   cmd.Execute[/CODE]
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.