Insert SQL not functioning

[doobybug]

Hi,

I am trying to have a button "Save" so that when clicked it will save the details from the form. It doesn't seem there are any syntax error but somehow the data is not being saved in the table. Any ideas why...I am using INSERT SQL

Private Sub btnSave_Click()    Dim refNo As String
    Dim volume As Integer
    Dim dateStart As Date
    Dim dateEnd As Date
    Dim shelfId As Integer
    Dim sql As String


     
    If (IsNull(Me.cmbNotary.Column(0)) Or IsNull(Me.txtVolume) Or IsNull(Me.txtDateStart) Or IsNull(Me.txtDateEnd) Or IsNull(Me.cmbShelf.Column(0))) Then
       MsgBox "Fields cannot be left blank"
        
    Else
            refNo = cmbNotary.Column(0)
            volume = Me.txtVolume
            dateStart = Me.txtDateStart
            dateEnd = Me.txtDateEnd
            shelfId = Me.cmbShelf.Column(0)
            
            sql = "INSERT INTO tblNotaryIndex(NotaryRefNo, Volume, [Date Start], [Date End], ShelfID) " & _
            "VALUES(" & refNo & "," & volume & ",'" & Format(dateStart, "dd-mmm-yyyy") & "','" & Format(dateEnd, "dd-mmm-yyyy") & "', " & shelfId & ")"
            Debug.Print sql
            On Error GoTo ErrorMessage
            CurrentDb.Execute sql
    
            
            cmbNotary.Value = Null
            Me.txtVolume = Null
            Me.txtDateStart = Null
            Me.txtDateEnd = Null
            Me.cmbShelf.Value = Null
             
    End If
     
    
    
Exit Sub


ErrorMessage:
    MsgBox "Record already exists"
End Sub

[June7]

Are [Date Start] and [Date End] fields date/time type? Format function results in a string value, not the date you would expect. Also, date/time parameters for saving to a date/time field require # delimiter. Try:

"VALUES(" & refNo & "," & volume & ", #" & dateStart & #, #" & dateEnd & "#, " & shelfId & ")"


Best to apply formatting on forms and reports.

[aytee111]

Did you copy the SQL and paste it into a query design window and run it successfully?

[June7]

Straight copy/paste would not work because of reference to form controls. Would have to do some edits.

[doobybug]

Date start and date end are of type Date...still not working and on the immediate window it is showing:

INSERT INTO tblNotaryIndex(NotaryRefNo, Volume, [Date Start], [Date End], ShelfID) VALUES(001,80, #01/09/1998#, #01/10/1999#, 1)

[JoeM]

I always approach these by trying to first create SQL code that will do what I want (either through the Query Builder or by writing the SQL code directly).
Then, once I have that working, then try to write the same code via VBA (and use MsgBox to compare it to the working example code).

[doobybug]

I ran the insert statement in query design and is giving me the following error:

Microsoft Access can't append all the records in the append query.
Microsoft access set 1 field to Null due to a type conversion failure, and it didn't add 0 records to the table due to key violations, 0 due to lock violations and 0 records due to validation rule violations

[June7]

There is no need to reference the combobox column index in the If conditional - you want the value of the combobox.

If that doesn't fix, I would have to examine db to debug. If you want to provide, follow instructions at bottom of my post.

[doobybug]

I attached it below...thanks so much!

Database1 - Copy.zip

[jwhite]

sql = "INSERT INTO tblNotaryIndex(NotaryRefNo, Volume, [Date Start], [Date End], ShelfID) " & _
"VALUES(" & refNo & "," & volume & ",#" & Format(dateStart, "dd-mmm-yyyy") & "#,#" & Format(dateEnd, "dd-mmm-yyyy") & "#, " & shelfId & ")"

[June7]

Why are you using an UNBOUND form?

Open BOUND form, move to new record row, enter data - no VBA needed.

NotaryRefNo is a text type field. Therefore need apostrophe delimiters. Why don't you make these PK/FK fields numeric? What happens when you reach 999? Placeholder zeros prefix can be handled with formatting in forms and reports.

[doobybug]

Supposedly the ref no should be a letter and 3 numbers ex: R450. In the future I am going to put an input mask on that field..that is the reason I made it short text...

How do you create a bound form? Can I fix the current one?

[doobybug]

I think I managed to do a bound form...If it is a bound form how can I add Validation rules such as if a field is left blank you cannot insert a new record?

[June7]

You can use the ValidationRule and ValidationText properties of textbox and combobox.

Or set fields as required in table design and let Access nag the users.

Otherwise, code in form BeforeUpdate event. Common topic, search forum or Google.

[doobybug]

I will try that...thanks a billion