INSERT Statement Help :) (1 Viewer)

[Ryan142]

Hi Guys,

I've been looking for a bit on information about INSERT statements and I really haven't found much. So I find myself here again!

All I want to do is a basic INSERT Statement where the user will input data into text boxes, then when the save button is clicked, the code will insert this into the table

Here's the code I have - but it's complaining about too few parameters which maybe might be to do with how I've inserted the text boxes into the statement I don't know

Private Sub btnSave_Click()

    SQLReg = "INSERT INTO tblAircraftDetails (Registration, Model, SeatsAvailable, Remarks) " & _
             "  VALUES(txtRegistration, txtModel, txtSeats, txtRemarks)"
    CurrentDb.Execute (SQLReg)

End Sub

As always, any help is always appreciated! Thanks,
Ryan

 

[Uncle Gizmo]

The Notorious INSERT INTO SQL Statement - Nifty Access

More Info on My Website:- INSERT INTO SQL Statement

 

[Ryan142]

The Notorious INSERT INTO SQL Statement - Nifty Access

More Info on My Website:- INSERT INTO SQL Statement

Fantastic thanks for you help!

 

[bob fitz]

Why not just use a form with controls bound the fields of the table. So much easier than using unbound forms.

 

[theDBguy]

Hi Ryan. The way I see it, this really has nothing to do with INSERT statements but more about properly creating a String in VBA.

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statemen...

thedbguy.blogspot.com

 

[Uncle Gizmo]

Hi Ryan. The way I see it, this really has nothing to do with INSERT statements but more about properly creating a String in VBA.

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statemen...

thedbguy.blogspot.com

If you have a look at my video you will see that I bypass your step 4 by concatenating the strings and dates with Delimiters before they get passed into the "Values" portion of the SQL string. Adopting this approach simplifies the SQL statement into simple parts so that you reduce syntax problems, which are the main reason people have problems with SQL Statements, and in particular the Notorious Insert Into SQL Statement.

The other benefit of this approach is that you have a universal template for the the insert into SQL statement. It is easily extended to take care of more values, with very little risk of developing a syntax error.

 

[Micron]

The way I see it, this really has nothing to do with INSERT statements but more about properly creating a String in VBA.

I pretty much agree with that. I don't see the point of all that code when you can concatenate, such as
"INSERT INTO Table1(thedate, test, ThreeDecimal) VALUES (#" & frm.Created & "#, '" & frm.Text5 & "', " & frm.Text7 & ")"
(where text5 is text, text7 is a number)
Of course, in that example you have to declare and Set a form object. I just copied that from a test in a standard module, otherwise I would have just used Me.

Uncle, not saying your way isn't good - I'm just saying it's a lot more code instead of learning how to concatenate. There will be times when concatenation will be absolutely necessary, and that code won't fit the bill because it's not meant for say, concatenating an expression or non-sql related task. Plus all that code seems it will work for a particular sql statement, but for an additional situation that requires more or less fields, it needs to be copied and modified. That just adds unnecessary code to the code project IMHO. Besides, I don't see the Insert statement as what might be notorious as much as the task of concatenation.

 

[Uncle Gizmo]

I pretty much agree with that. I don't see the point of all that code

I know many developers pride themselves on condensing the code into the most compact piece of code they can, and I understand the challenge and the need to do that. However on this Forum we are often dealing with people who are right at the beginning of the learning curve, and breaking things down into simple steps is much easier to learn. I'm sure they will quickly aspire to the challenge of building the condensed versions of the SQL Statements, but now, they need to understand the process, and the Stepped approach offers clarity.

I was answering a question where the passed in text contained double quotes:-

Change Quote into an Order​

Solved - Change quote into an order

Before I spend any time looking at it, let me suggest a troubleshooting method. Put a Breakpoint on the strSQLQuoteInsert= line of code. Run your code. When the code breaks on that line, press F8 to execute just that one line. After that one line executes, ask the immediate window this...

www.access-programmers.co.uk

Double Quotes
Double Quotes can be handled by using the Replace() Function. When you have your SQL in a single line, adding Replace() statements in the string, the already complicated SQL could well turn into a nightmare, even for a die-hard developer. There are benefits in using the stepped approach, the divide-and-conquer approach, even for seasoned developers.

Add Replace()
In this thread the user needs to add Replace(), just an extra step in the stepped approach. Imagine the user having to add the replace statement into the SQL in a single line, one wrong move, one accidentally deleted comma, and it blows up.

UK SQL Dates
For UK developers who need to format the dates correctly to have them work in SQL Statements, again the stepped approach means adding of a function to one of the steps. No need to delve into the continuous single string SQL Statement, and face the high risk of the inexperienced (and experienced) user introducing a difficult to find error.

Difficult to Read
Continuous SQL Statements are difficult to read, unless you have the experience of course, and being difficult to read, then that breaks the readability rule of coding. You need to present your code in a readable, clear manner. It is so much easier when you yourself have to go back years later, or someone else has to take over from you.

 

[Micron]

I had to come back in to see how old this was, given that you were addressing my post. A year, almost? Thou dost procrastinate too much!
Anyway, I'm unwatching this one.