Insert MS Access VBA

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
Where do I go wrong on the insert  code below?
Private Sub CmdPos_Click()
Dim db As DAO.Database
Dim strSql As String
strSql = "INSERT INTO tblPostClearance (" & "tblPostClearance.Debit, " & " tblPostClearance.Credit " & ")VALUES(" & Me.txtCashReceivable & ", " & Me.txtCashReceivable & ")"
db.Execute strSql
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Try this
"INSERT INTO tblPostClearance (Debit,Credit) VALUES('" & Me.txtCashReceivable & "', '" & Me.txtCashReceivable & "')"

Open in new window

NorieAnalyst Assistant

Commented:
Looks OK to me, are you getting errors?

PS You might want to consider using something like this when constructing SQL statements.
    strSql = " INSERT INTO tblPostClearance (tblPostClearance.Debit,tblPostClearance.Credit) "
    strSql = strSql & " VALUES (" & Me.txtCashReceivable.Value & ", " &  Me.txtCashReceivable.Value & ") "

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
You needed a space between the ")" and the "V" in Values

strSql = "INSERT INTO tblPostClearance (Debit, Credit) " _
       & VALUES(" & Me.txtCashReceivable & ", " & Me.txtCashReceivable & ")"

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer

Commented:
First of all: USE THE CODE BUTTON.

The general INSERT syntax is:

INSERT [INTO] TableOrView [( Columns )] VALUES ( Values ) [* , ()][;]

Open in new window

Thus you the question is: do you want to have a dynamic column list? I don't think so.

But I prefer a different approach for debugging and better readability:

Private Sub CmdPos_Click()

  Const SQL_INSERT As String = "INSERT INTO tblPostClearance ( Debit, Credit ) VALUES ( {0}, {0} );"

  Dim Statement As String
  Dim CashReceivable As Double
  
  CashReceivable = CDbl(txtCashReceivable.Value)
  Statement = FormatStr(SQL_INSERT, CashReceivable)
  ' Execute it.
  
End Sub

' With FormatStr() in a standard module:
Public Function FormatStr(AString As String, ParamArray AValues() As Variant) As String

  Dim Count As Long
  Dim Result As String
  
  Result = AString
  
  For Count = 0 To UBound(AValues())
    Result = Replace(Result, "{" & Count & "}", Nz(AValues(Count), "NULL"))
  Next Count
  
  Result = Replace(Result, "{CRLF}", vbCrLf)
  FormatStr = Result

End Function

Open in new window

When using
db.Execute strSql

Open in new window

you should always add the parameter
db.Execute strSql , dbFailOnError

Open in new window


If your regional settings don't match US settings, you might need to format your numbers (for , versus .)
ste5anSenior Developer

Commented:
For plain Access queries this is correct, but when using linked tables, you need also dbSeeChanges.

Refactoring is fun thus:

Private Sub CmdPos_Click()

  Const SQL_INSERT As String = "INSERT INTO tblPostClearance ( Debit, Credit ) VALUES ( {0}, {0} );"

  Dim CashReceivable As Double
  
  CashReceivable = CDbl(txtCashReceivable.Value)  
  SqlExecuteFmt SQL_INSERT, CashReceivable

End Sub

Open in new window

with

Public Enum EnumMousePointerConstants
  ccDefault = 0
  ccHourglass = 11
End Enum

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

  If m_CurrentDb Is Nothing Then
    Set m_CurrentDb = CurrentDb
  End If

  Set CurrentDbC = m_CurrentDb

End Property

Public Function SqlExecute(AStatement As String, Optional ASilent As Boolean, Optional ARecordsAffected As Long) As Boolean

  On Local Error GoTo LocalError

  Dim DBEngineError As Variant
  Dim ErrorDescription As String
  Dim OldMousePointer  As Long

  OldMousePointer = Screen.MousePointer
  Screen.MousePointer = ccHourglass
  SqlExecute = False
  CurrentDbC.Execute AStatement, dbFailOnError Or dbSeeChanges
  ARecordsAffected = CurrentDbC.RecordsAffected
  SqlExecute = True
  Screen.MousePointer = OldMousePointer
  Exit Function

LocalError:
  Screen.MousePointer = OldMousePointer
  Debug.Print "Fehler: " & "Fehler währende der Ausführung von SQLExecute."
  Debug.Print "Quelle: " & Err.Source
  Debug.Print "Nummer: &H" & Hex(Err.Number)
  ErrorDescription = Err.Description
  If DBEngine.Errors.Count > 0 Then
    ErrorDescription = ErrorDescription & vbCrLf
  End If
  
  For Each DBEngineError In DBEngine.Errors
    If DBEngineError <> Err.Description And InStr(DBEngineError, "Der Batch wurde abgebrochen.") = 0 Then
      ErrorDescription = ErrorDescription & vbCrLf & DBEngineError
    End If
  Next DBEngineError
  
  Debug.Print "Beschreibung: " & ErrorDescription
  Debug.Print "SQL: " & AStatement
  If Not ASilent Then
     ' Custom message box.. replace with your own.
     ' If FormMsgBox( _
     '   "Fehler währende der Ausführung von SQLExecute." & vbCrLf & vbCrLf & _
     '   "Drücken Sie Nein, um den SQL Befehl anzuzeigen." & vbCrLf & vbCrLf & _
     '   "Quelle: " & Err.Source & vbCrLf & _
     '   "Nummer: &H" & Hex(Err.Number) & vbCrLf & vbCrLf & _
     '   "Beschreibung: " & ErrorDescription, _
     '   vbCritical + vbYesNo) = vbNo Then
     '   FormMsgBox "Folgender SQL Befehl hat den Fehler ausgelöst:" & vbCrLf & vbCrLf & AStatement, vbCritical
     ' End If
  End If

End Function

Public Function SqlExecuteFmt(AStatement As String, ParamArray AValues() As Variant) As Boolean

  Dim Count As Long
  Dim Result As String
  Dim Statement As String

  Statement = AStatement
  For Count = 0 To UBound(AValues())
    Statement = Replace(Statement, "{" & Count & "}", Nz(AValues(Count), "NULL"))
  Next Count

  SqlExecuteFmt = SqlExecute(Statement)

End Function

Open in new window


btw, enforcing type safty using a separate CDbl() makes error control easier.

p.s. use the property CurrentDbC pattern instead of CurrentDb.
Cause CurrentDb is an expensive call, cause when used there is a lot enumerated and Information gathered before you can access it. For normal operations like executing statements this is a big performance penalty. The idea is from Michael Kaplan.
The only difference to CurrentDb is that when you access enumerations in CurrentDbC like QueryDefs you need to call CurrentDbC.QueryDefs.Refresh first.
I have tried all there is always an error here

strSql = "INSERT INTO tblPostClearance (Debit, Credit) " & "Values(" & Me.txtCashReceivable & ", " & Me.txtCashReceivable & ")"
ste5anSenior Developer

Commented:
grr, USE THE CODE BUTTON.

Capture.PNG
Remove unnecessary string concatenation. Then posting the error message would be nice.

But in this case:

1) Does a TextBox named txtCashReceivable exists? Check for typos.
2) Where do you call this code? Is Me the correct object or do you need Forms("CorrectFormName") instead?
I'm using a form on click event see below:

Private Sub CmdPos_Click()
Dim db As DAO.Database
Dim strSql As String
Dim Cancel As Integer
Dim Shortage As Currency
Dim str As String
Shortage = Nz(Me.txtCashReceivable, 0) - Nz(Me.CashReceived, 0)
If IsNull(Me.CashReceived) Or (Me.CashReceived = 0) Then
Beep
MsgBox "Please enter the cash received in the cash received box", vbOKOnly, "Internal Audit Manager"
Cancel = True
Exit Sub
ElseIf Me.txtShortage > 0 Then
Beep
MsgBox "Please note that you have received not enough cash for this transaction," & _
"and so you have a shortage of " & Format(Me.txtShortage, "Currency") & "." & "Please check again", vbOKOnly, "Internal Audit Manager"
Cancel = True
Exit Sub
ElseIf Me.txtShortage <> "" Then
DoCmd.Save
DoCmd.Close
DoCmd.OpenForm "frmPosStocksSold"
DoCmd.SetWarnings False
MsgBox "Journal Posting successful", vbInformation, "Please Proceed"
strSql = "INSERT INTO tblPostClearance (Debit,Credit) VALUES('" & Me.txtCashReceivable & "', '" & Me.txtCashReceivable & "')"
db.Execute strSql, dbFailOnError
End If
End Sub
Senior Developer
Commented:
What about the CODE button?

Capture.PNG
You can edit your posts.

1) Format your code.
2) Remove the redundant Exit Sub's.
3) Cancel is redundant. Also is Shortage and str.
4) Cash receivable is a number, not string, thus you don't need quotation marks in the dynamic SQL statement.
5) Always use the explicit access properties .Value or .Text.
6) THE ERROR: you're closing your form, before you access Me.txtCashReceivable, thus the error. Take the CDbl() hint serious. Parameter checking should be the first thing you do.

Thus, after some refactoring:

Private Sub CmdPos_Click()

  Const MSG_CAPTION As String = "Internal Audit Manager"
  Const MSG_INPUT_CASHRECEIVED As String = "Please enter the cash received in the cash received box"
  Const MSG_ERROR_CASHSHORTAGE As String = "Please note that you have received not enough cash for this transaction, and so you have a shortage of {0}. Please check again."
  
  Const SQL_INSERT As String = "INSERT INTO tblPostClearance ( Debit, Credit ) VALUES ( {0}, {0} );"
  
  Dim CashReceivable As Double
  Dim Shortage As Double
  Dim Statement As String    
  
  CashReceivable = CDbl(txtCashReceivable.Value)     
  Shortage = CDbl(txtShortage.Value)
  If CashReceivable = 0 Then
    Beep
    MsgBox MSG_INPUT_CASHRECEIVED, vbOKOnly, MSG_CAPTION        
  ElseIf Shortage > 0 Then
    Beep
    MsgBox Replace(MSG_ERROR_CASHSHORTAGE, {0}, Format(Shortage, "Currency"), vbOKOnly, MSG_CAPTION
  Else
    DoCmd.Save
    DoCmd.Close
    DoCmd.OpenForm "frmPosStocksSold"
    DoCmd.SetWarnings False
    MsgBox "Journal Posting successful", vbInformation, "Please Proceed"
    Statement = Replace(SQL_INSERT, "{0}", CashReceivable)
    CurrentDb.Execute Statement, dbFailOnError Or dbSeeChanges
  End If

End Sub 

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
in your code (below)
strSql = "INSERT INTO tblPostClearance (Debit,Credit) VALUES('" & Me.txtCashReceivable & "', '" & Me.txtCashReceivable & "')"

Open in new window

you have wrapped the two values in single quotes, why?  Assuming those are currency or at least numeric fields, then the SQL should not include the single quotes:
strSql = "INSERT INTO tblPostClearance (Debit,Credit) VALUES(" & Me.txtCashReceivable & ", " & Me.txtCashReceivable & ")"

Open in new window

BTW, why are you inserting the same value into both the Debit and Credit fields of your table?  That makes absolutely no sense.
ste5anSenior Developer

Commented:
btw, I missed the language problem of your system.

Depending on your locales you need to ensure the correct decimal formatter, cause Access expects the dot.
Thus to be safe:

    Statement = Replace(SQL_INSERT, "{0}", Str(CashReceivable))

Open in new window

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
What is the exact error number and description?

For the insert query what are the name of the fields (from the table) and their data types?

What I normally do is build the query in the Query By Example designer, once it is proper switch to SQL view to extract the proper syntax to port to VBA.  I'd suggest you try that here.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial