Need Help using Inputbox value with INSERT query

[pduncan]

I am trying to take the value a user enters with an inputbox and insert it into a table.

Here is my code:

      Dim response As Integer

      response = InputBox("What was the Regional Total?", "Input The Total")
      
      DoCmd.RunSQL "INSERT INTO tblInvoiceTotals (RegionalTotalInv) VALUES (" & [response] & ");"

the value entered by the user will be numeric... it's a dollar amount ####.## - is this my problem?


Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[RoyVidar]

Sorry, what is the problem, doesn't it work? Try removing the [brackets] around the variable name (response).

Roy-Vidar

 

[PHV]

And be aware that an Integer can't hold decimal value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

One thing to remember, the InputBox only returns string values, and if the user cancels, it returns an empty string even if a default value is supplied. A more robust solution would check for this:

  Dim strResponse As String
  strResponse = InputBox("What was the regional total?", "Input The Total")
  
  If strResponse <> "" Then
    If IsNumeric(strResponse) Then
      strResponse = Format(strResponse, "####.##")
      DoCmd.RunSQL "INSERT INTO tblInvoiceTotals (RegionalTotalInv) VALUES (" & strResponse & ");"
    Else
      MsgBox "Please enter a dollar amount."
    End If
  Else
    [green]'nothing - user cancelled.[/green]
  End If

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[pduncan]

VBslammer,
thanks - I didnt consider that...
also, I need to add a few more things to the query - I have this code

    Dim sstrSQL As String
            sstrSQL = "INSERT INTO tblInvoiceTotals (InvoiceNo, InvDate, PONo) " & _
                        "SELECT DISTINCT tblInvoice.InvoiceNo, tblInvoice.InvDate, PONo " & _
                        "FROM tblInvoice " & _
                        "WHERE (([lstApprovalSent]=[tblInvoice]![InvoiceNo]));"
                DoCmd.RunSQL sstrSQL, 0
    
    
      Dim strResponse As String
  strResponse = InputBox("What was the regional total?", "Input The Total")
  
  If strResponse <> "" Then
    If IsNumeric(strResponse) Then
      strResponse = Format(strResponse, "####.##")
      DoCmd.RunSQL "INSERT INTO tblInvoiceTotals (RegionalTotalInv) VALUES (" & strResponse & ")" '& _
       '"WHERE (([lstApprovalSent]=[tblInvoice]![InvoiceNo]));"
    Else
      MsgBox "Please enter a dollar amount."
    End If
  Else
    'nothing - user cancelled.
  End If

but it enters the first query on one line, and then the second on a second line. Is there a way to combune these? I am having problems because one query is using "select" and the other query states the values with "values" -
what is the best way to combine these so that all data ends up where it belongs?

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[PHV]

Take a look at the UPDATE instruction in SQL:
DoCmd.RunSQL "UPDATE tblInvoiceTotals SET RegionalTotalInv=" & strResponse & _
"WHERE (([lstApprovalSent]=[tblInvoice]![InvoiceNo]));"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pduncan]

also - i posted the wrong code.. the last query is not supposed to have the WHERE condition remarked out

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.