By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

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

P: 55
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.

Expand|Select|Wrap|Line Numbers
  1. Set dbs = OpenDatabase("C:\Documents and Settings\lmunyere.DPSM\My Documents\Submission.mdb")
  2.  
  3.  strSQL = "INSERT INTO Logtable (IdentityNumber, Surname, FirstName, " & _
  4.  "Username, Action, Time) SELECT " & Me.IdentityNumber.Value & ", " & _
  5.   Me.Surname.Value & ", " & Me.FirstName.Value & ", " & Me.lblCapture.Caption & ", " & _
  6.   Me.cmdAdd.Caption & "," & Date & ";"
  7.  
  8. ' Me.IdentityNumber & Me.Surname & Me.FirstName & Me.Username
  9. dbs.Execute (strSQL)
  10. 'DoCmd.RunSQL strSQL
  11.  
  12.     On Error Resume Next
  13.     DoCmd.GoToRecord , "", acNewRec
  14.     If (MacroError <> 0) Then
  15.         Beep
  16.         MsgBox MacroError.Description, vbOKOnly, ""
  17.     End If
  18.  
  19.  
  20.  
  21.  
  22. cmdAdd_Click_Exit:
  23.     Exit Sub
  24.  
  25. cmdAdd_Click_Err:
  26.     MsgBox Error$
  27.     Resume cmdAdd_Click_Exit
  28. dbs.Close
  29.  
Feb 16 '09 #1
Share this Question
Share on Google+
8 Replies


beacon
100+
P: 579
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.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO Logtable (IdentityNumber, Surname, FirstName, " & _ 
  2.  "Username, Action, Time) SELECT " & Me.IdentityNumber.Value & ", '" & _ 
  3.   Me.Surname.Value & "''" & Me.FirstName.Value & "''" & Me.lblCapture.Caption & "'#" & _ 
  4.   Me.cmdAdd.Caption & "," & Date & "#;" 
  5.  
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)
Feb 16 '09 #2

NeoPa
Expert Mod 15k+
P: 31,419
@beacon
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 (#).
Feb 16 '09 #3

beacon
100+
P: 579
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...
Feb 16 '09 #4

NeoPa
Expert Mod 15k+
P: 31,419
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).
Feb 17 '09 #5

beacon
100+
P: 579
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....
Feb 17 '09 #6

P: 55
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.
Feb 17 '09 #7

NeoPa
Expert Mod 15k+
P: 31,419
@Lebbsy
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.
Feb 17 '09 #8

beacon
100+
P: 579
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.
Feb 17 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.