Solved Adding a record to a table from form data (1 Viewer)

[TajikBoy]

Hi Guys,

Trying to add a record to an existing table from the form data, which is already linked to another table and of course, failing spectacularly !!!

Below is my simple code and I am getting a syntax error in the HotelName field to start with, probably next ones will also follow

Where am I going wrong?

Dim strSQL As String

strSQL = "INSERT INTO tblFFEBudgets (HotelName, BudgetYear, Reference, Description, Justification) VALUES (" & _
               Me.HotelName & ", '" & _
               Me.RFAYear & "', " & _
               Me.BudgetRef & ", " & _
               Me.Description & ", " & _
               Me.Justification & ");"
    
DoCmd.RunSQL strSQL

 

[JHB]

You need to surround text string with an apostrophe, numbers doesn't need apostrophe

VALUES ('" & _
Me.HotelName & "', '" & _
Me.RFAYear & "', " & _

 

[TajikBoy]

Hi Guys,

Trying to add a record to an existing table from the form data, which is already linked to another table and of course, failing spectacularly !!!

Below is my simple code and I am getting a syntax error in the HotelName field to start with, probably next ones will also follow

Where am I going wrong?

Dim strSQL As String

strSQL = "INSERT INTO tblFFEBudgets (HotelName, BudgetYear, Reference, Description, Justification) VALUES (" & _
               Me.HotelName & ", '" & _
               Me.RFAYear & "', " & _
               Me.BudgetRef & ", " & _
               Me.Description & ", " & _
               Me.Justification & ");"
   
DoCmd.RunSQL strSQL

Found it..... It's bloody quotes again...I'm never gonna learn this !!!

Have a fantastic quote error free day chaps !

 

[Gasman]

Another way I beleive, is to use a udf and just set it's parameters.
No special enclosing of charcaters needed then.

Search here as it has been mentioned several times.

Here is one such example

INSERT SQL

My 2c is that running SQL in VBA is simpler, safer, more readable, and easier to maintain, if you use a temporary DAO.QueryDef object. Consider code like... Sub TempQDFSample() Const SQL As String = _ "INSERT INTO MyTable " & _ "( SomeDate, StringField ) " & _...

www.access-programmers.co.uk

and in slightly a different way

Should work but doesn't

This was working last week and now it is not and I am stumped as to why. Nothing involving these fields have been changed except I removed clearing the boxes after the SQL runs. Private Sub cmdComment_Click() Dim dbCurr As DAO.Database Dim strComment As String Set dbCurr =...

www.access-programmers.co.uk

 

[Pat Hartman]

Any particular reason you are not using a bound form? When you use bound forms, Access does the heavy lifting. That is the whole point of using Access