Save button

[doobybug1]

I have a form from which I want to save the details entered.
I have coded a save button but somehow it is giving me a syntax error and I cannot seem to find it. Any help? I am a newbie so bare with me

Private Sub btnSave_Click()
    CurrentDb.Execute "INSERT INTO tblNotaryIndex(NotaryRefNo, Volume, Date Start, Date End) " & _
                    " VALUES(" & Me.cmbNotary.Column(0) & ",'" & Me.txtVolume & "','" & _
                    Me.txtDateStart & "','" & Me.txtDateEnd & "')"
                    
                    
End Sub

 

[static]

Field names with spaces need square brackets
[Date Start]

Dates either need to be US format with hashes #mm/dd/yyyy# or a string like '12-may-2017'

 

[static]

+ If volume is numeric it shouldn't have the single quotes.

 

[doobybug1]

Thanks a billion...can you show me a link or somewhere where I can learn how to concatenate?

Volume is numeric but the dates I am saving as date/time but as Long Date

 

[doobybug1]

Tried this but still doesn't work. I have no idea how to concatenate so I tried to cheat and put them in variables before. Any help is very much welcome...thanks for your patience

    Dim refNo As String
    Dim volume As Integer
    Dim dateStart As Date
    
    Dim dateEnd As Date
    
    refNo = cmbNotary.Column(0)
    volume = Me.txtVolume
    dateStart = Me.txtDateStart
    dateEnd = Me.txtDateEnd
    
    CurrentDb.Execute "INSERT INTO tblNotaryIndex(NotaryRefNo, Volume, [Date Start], [Date End]) " & _
                    "VALUES(refNo &", "& volume &", "& dateStart &", "& dateEnd)"
                    
                    
End Sub

 

[static]

Not sure what you mean.
Concatenation is just adding one string to another. You seem to know how to do that.

For nerdy reference stuff w3schools

https://www.w3schools.com/sql/sql_quickref.asp

 

[static]

Oh.

Well the first thing to do is build your base query in the built in editor.

Copy that to vba and make changes.

Learn how to debug your code.
The immediate window is your friend.

Build your sql string

sql = "select whatever from wherever where something =that"

then add debug.print sql

that will print the sql you are executing to the immediate window. If it doesn't work you can copy back into a proper query to find the errors.

 

[doobybug1]

What I mean is I usually use Java and the syntax for concatenation is
String+String
If it is a variable no "" are required but if it is just text "" are required
In this case what is the use of '' and , and &?
Do you convert Date txt Fields into Strings or they are already treated as Strings?

If you can give me an example with my own code it will help me a lot since this is my first time playing with vba

 

[static]

Well the &'s are in the wrong places in your second example.

s = "string " & value & " another string " & value

You can use commas and semicolons for debug.print

debug.print "string1","string2";"string3"

but not for assigning to variables

 

[static]

CurrentDb.Execute "INSERT INTO tblNotaryIndex(NotaryRefNo, Volume, [Date Start], [Date End]) " & _
    "VALUES(" & refNo & "," & volume & ",'" & format(dateStart,"dd-mmm-yyyy") & "','" & format(dateEnd,"dd-mmm-yyyy") & "')"

 

[doobybug1]

I think I am getting what you are saying...I am very close but not close enough to fix this...the debug.print is working well but now I am getting an error stating Number of query values and destination fields are not the same...what do you recon is the problem?

Private Sub btnSave_Click()
 Dim refNo As String
    Dim volume As Integer
    Dim dateStart As Date
    
    Dim dateEnd As Date
    
    refNo = cmbNotary.Column(0)
    volume = Me.txtVolume
    dateStart = Me.txtDateStart
    dateEnd = Me.txtDateEnd
    
    Debug.Print refNo & volume & dateStart & dateEnd
    
    CurrentDb.Execute "INSERT INTO tblNotaryIndex(NotaryRefNo, Volume, [Date Start], [Date End]) VALUES(refNo & volume & dateStart & dateEnd)"
                    
End Sub

 

[doobybug1]

Your code worked beautifully but I still am trying to decipher it..

So if I put it underneath each other I get:

(" & refNo & ",
" & volume & ",
'" & format(dateStart,"dd-mmm-yyyy") & "',
'" & format(dateEnd,"dd-mmm-yyyy") & "')

the , is the concatenation part am I right?

 

[static]

Concatenation is adding one string to another.

a = "hello"
b = "world"

c = a & b

c is a and b concatenated (added together)

VBA isn't very strict about anything

c = a + b would also work but isn't recommended.

The commas are nothing to do with concatenation. They are SQL syntax. They delimit one value from the other just like they do the fields.

insert into sometable (field1,field2,field3) values (val1,val2,val3)

 

[doobybug1]

Thanks a billion this helped so so much