Insert.....Into statement to capture form fields into an external table

[Lebbsy]

On clicking a button I want to add data in to tables. The first table captures all information specified in the form and works fine. The second table that does not have a recordsource, captures information from the selected form. Pls help, part of the code is specified below.

Set dbs = OpenDatabase("C:\Documents and Settings\lmunyere.DPSM\My Documents\Submission.mdb")

 strSQL = "INSERT INTO Logtable (IdentityNumber, Surname, FirstName, " & _
 "Username, Action, Time) SELECT " & Me.IdentityNumber.Value & ", " & _
  Me.Surname.Value & ", " & Me.FirstName.Value & ", " & Me.lblCapture.Caption & ", " & _
  Me.cmdAdd.Caption & "," & Date & ";"

' Me.IdentityNumber & Me.Surname & Me.FirstName & Me.Username
dbs.Execute (strSQL)
'DoCmd.RunSQL strSQL

    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If

    


cmdAdd_Click_Exit:
    Exit Sub

cmdAdd_Click_Err:
    MsgBox Error$
    Resume cmdAdd_Click_Exit
dbs.Close

[beacon]

What are the data types for each of the fields that you are trying to insert into the table? It looks like your SQL statement is trying to insert all number values, even though you have what looks to be string values for some of the fields (like surname).

Are you getting an error message of any kind when you execute the code?

Perhaps the following would work? Notice the extra quotes and pound symbols for strings and dates, respectively.

strSQL = "INSERT INTO Logtable (IdentityNumber, Surname, FirstName, " & _ 
 "Username, Action, Time) SELECT " & Me.IdentityNumber.Value & ", [B]'[/B]" & _ 
  Me.Surname.Value & "[B]'[/B], [B]'[/B]" & Me.FirstName.Value & "[B]'[/B], [B]'[/B]" & Me.lblCapture.Caption & "[B]'[/B], [B]#[/B]" & _ 
  Me.cmdAdd.Caption & "," & Date & "[B]#[/B];"

I'm also curious about the following:

The second table that does not have a recordsource

Can tables have a recordsource? (This isn't sarcastic, I really wasn't sure whether a table could have a recordsource or not)

[NeoPa]

Can tables have a recordsource? (This isn't sarcastic, I really wasn't sure whether a table could have a recordsource or not)

No. A table can be used as a record source. I too am a little confused by that.

Your answer's quite helpful I thought. A common confusion in SQL is how to handle the various types of literal values. This may help Quotes (') and Double-Quotes (") - Where and When to use them & maybe even Literal DateTimes and Their Delimiters (#).

[beacon]

That's what I thought NeoPa, but I'm never one to doubt someone else on a forum because it's so easy for what you're saying to get lost in translation.

I referenced the first article a bunch when I first got over my phobia of using SQL in VBA. Now I'm happy to report that I just have a healthy fear of it...

[NeoPa]

I like it Beacon :D

I could see you seem to have got that down pretty well. Few understand the rationale of where to use the different types of quotes.

The second one is possibly more worthwhile though, as dates trip people up most it seems. The most common problem is found outside of the USA (or for code that is required to be multi-national) as people often forget that SQL dates are explicitly formatted as for the USA and are NOT effected by local standards.

Because there is so much deduction possible, it is rare that Access ever gets it wrong, which leads many people to fail to realise that they actually have it wrong in their SQL code. It only tends to go wrong after they release their projects (as it's so rare to find such problems while testing).

PS. I too started by avoiding getting my hands dirty with SQL, but about five years ago I started to play when I had to take over somebody else's project. It was more fun than I'd expected (quite logical, which I like).

[beacon]

Sounds like we've had similar experiences. Mine was probably more along the lines of 'monkey see, monkey do' as the majority of my support came from this forum.

SQL was one of those languages that seemed really easy to understand at first, until I started trying to learn about Unions and Inner Joins before I really understood the relationship/normalization concepts.

Now, I think I'm well on my to reaching the pinnacle of mediocrity....

[Lebbsy]

The first four columns are strings and the last column is a date. I tried your code but still geting the same error. As for:

Can tables have a recordsource? (This isn't sarcastic, I really wasn't sure whether a table could have a recordsource or not)

I meant to say the table is not a record source to any form instead it gets its data from the form fields on hitting the submit button.

Thanx for the reply.

[NeoPa]

The first four columns are strings and the last column is a date. I tried your code but still geting the same error.

You haven't told us anything about your error as far as I can see. We'd like to help, but it's hard without knowing what your problem is.

[beacon]

You have six fields listed in your statement. The first column looks like a number, the next four look like strings, and the last one looks like a date.

It looks like part of the problem is that you are using the keyword 'Date' as the name of one of your fields. Access doesn't like that.

Name it something like 'fDate' or 'frmDate'

The other part, because I didn't realize that 'Date' was in there, I put one of the # delimiters in the wrong place. It should go in the set of double quotations immediately before the 'Date' value in the SELECT part of the statement. I imagine that if you fix that the code will probably work, along with putting single quotes around the strings as I showed in my example. Leave the identity number as it is.

Check out the link that NeoPa provided for an in-depth look at how to identify date or string fields in SQL.