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

Simple Append Query Not Working

100+
P: 171
Hi,
I have a simple append query which takes data from a form and appends it into a table.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblSmsSent ( MobileNumber, ClientName, TimeSent, AppointmentTime, AppointmentDate )
  2. SELECT [Forms]![frmSMS]![tbMobile] AS Expr1, [Forms]![frmSMS]![tbFName] AS Expr2, Now() AS Expr3, [Forms]![frmSMS]![tbTime] AS Expr4, [Forms]![frmSMS]![tbDate] AS Expr5
  3. FROM tblSmsSent;
  4.  
The Table has the following fields
Field Type
ID Autonumber
Mobile Text (10 Charecters)
ClientName Text (50 Charecters) -This is only the first name
TimeSent Date/Time
AppointmentTime Date/Time
AppointmentDate Date/Time

Form Name: frmSms

Text Boxes in Form
tbMobile: Mobile # Won't run append query until the mobile number is 10 charecters
tbFName: First Name of Client
tbTime: Time of Appointment in hh:mm AMPM format
tbDate: Date of Appointment in dd/mm/yyyy format

Issue: Whenever I run the query it doesn't append anything to tblSmsSent which is the table which the data should be going to

Please Help, before I pull my hair out !
Jul 23 '09 #1
Share this Question
Share on Google+
16 Replies


Expert 100+
P: 1,287
I would suggest, rather than use an external query, put a button on your form that you would click to add the record, and use some code like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddRecord_Click()
  2. On Error GoTo ErrorHandler
  3.  
  4.   Dim strSQL as String
  5.  
  6.   strSQL = "INSERT INTO tblSmsSent " _
  7.          & "(MobileNumber, ClientName, TimeSent, AppointmentTime, AppointmentDate) " _
  8.          & "VALUES (" _
  9.          & tbMobile & ", " _
  10.          & tbFName & ", #" _
  11.          & Now() & "#, #" _
  12.          & tbTime & "#, #" _
  13.          & tbDate & "#);"
  14.  
  15.   'MsgBox strSQL     'For testing
  16.  
  17.   DoCmd.SetWarnings False
  18.   DoCmd.RunSQL strSQL, 0
  19.   DoCmd.SetWarnings True
  20.  
  21. ExitCode:
  22.   Exit Sub
  23.  
  24. ErrorHandler:
  25.   MsgBox "Error adding record. " & vbCrLf & "Error Number: " & Err.Number & vbCrLf & " Description: " & Err.Description
  26.   Resume ExitCode
  27.  
  28. End Sub
  29.  
Jul 23 '09 #2

100+
P: 171
Thanks ChipR,
I had tried previously to get query to work through vba by creating a Sql string and opening it via docmd.openquery strSql and also tried DoCmd.RunSQL strSQL both did not work, then I just deleted the table and made a new table, and it was working fine, even though I still don't see any reason why the old table would cause any issues. That's what I hate about fixing using trial and error, nothing is learnt in that manner. Any suggetions on why it wouldn't work, keeping in mind the specs of the table I have outlined in my initial post.

Thanks
Jul 27 '09 #3

Expert 100+
P: 1,287
You have to be careful with the format of the field. Dates surrounded by #'s and text surrounded by quotes. The only other thing that comes to mind immediately is having a field with a name that was a reserved word or used elsewhere.
Jul 28 '09 #4

100+
P: 171
Thanks for the response Chip,
To your first point, in the access query builder when reffering to a text box etc dates don't have to be surrounded by #'s and text doesn't have to be surrounded by quotes.
To your second point none of the field names were reserved words, and this I can confirm because the field names used in the new table were exactly the same as the old table

Thanks
Jul 28 '09 #5

FishVal
Expert 2.5K+
P: 2,653
@iheartvba
This query will append to tblSmsSent as many records as tblSmsSent has had before the query runs. Sure, unless the records to be appended do not violate table constraints.

Kind regards,
Fish
Jul 28 '09 #6

100+
P: 171
Thanks for the comment FishVal, from what I understand what your saying is that if tblSmsSent had records 1,2 and 3 my query would have effectivley, appended records 1,2 and 3 to tblSmsSent again. If my understanding is correct, then the issue becomes: why is the same query working now :S


Thanks
Jul 28 '09 #7

FishVal
Expert 2.5K+
P: 2,653
I'm saying that if tblSmsSent has records 1,2 and 3, then 3 records containing the same set of values taken from your form controls will be added by the query from post #1. The only reason why it works now I could see so far is that your table has a unique index disallowing addition of duplicate records.
Try to run SELECT part of your query separately to see what you are going to add to your table.

Kind regards,
Fish
Jul 29 '09 #8

100+
P: 171
Hi FishVal thanks for explaining,
Actually when I was running just the SELECT part of my query before I was still getting a blank query, alot of times when I have a query with purley inputs from Form Controls it comes blank when I run it as a SELECT query, but Appends perfectly when I run it as an Append query. (Just a note it is working now as a Select query and Append query)

Sorry I don't understand your comment about the table now having a unique index, it only has 1 unique index which is the primary key, and that is an AutoNumber, that has remained the same with the old and new tables.

I think my main issue for understanding errors is poor documentation, I just can't figure out how to comprehensivley document every part of my database plus all changes in a coherent manner, very frustrating!
Jul 29 '09 #9

FishVal
Expert 2.5K+
P: 2,653
:D I don't believe in miracles, at least would not rely upon them.
IMHO, if some code works when it is not expected to work, then it is likely because of several bugs neutralizing each other in most cases.

Is the query syntax still the same as in post #1?
Jul 29 '09 #10

100+
P: 171
yes, to the best of my knowledge, but as you said, miracles don't happen when it comes to software, so I have definatley screwed up somewhere!

Thanks FishVal
Jul 29 '09 #11

Expert Mod 2.5K+
P: 2,545
Hmm, you are trying to insert fields FROM table tblSmsSent via the textboxes of a bound form INTO the self-same tblSmsSent?

Is that your intention?

Your SQL is referring to form textboxes, not fields in tblSmsSent. At most you would be appending one record to tblSmsSent - reflecting what is in the form you are really referring to - if you are not violating relational constraints by doing so.

If you could tell us what you need to insert, what table the fields come from, and why you are referring to form frmSms at present in your SELECT it would help us to understand what you are doing with this one.

-Stewart
Jul 30 '09 #12

100+
P: 171
Hi Stewart Ross Inverness,
My Intention is to insert data into a table (tblSms) from a form (frmSms), the data is coming from unbound text boxes in the form, and as I said before after I deleted the table and created the same table with the same properties again the append query started working.
Jul 30 '09 #13

Expert Mod 2.5K+
P: 2,545
I'm confused. You want to append unbound textbox data into tblSMS from a form which is bound to the same table? Why not simply bind the textboxes to appropriate fields from that table? You have not mentioned any reason why you are not already binding these textboxes, given that you are appending them to particular fields in the table (all of them from what you mention of the table design). Binding them would save you the hassle of recreating what Access already does - appending data from textboxes into a new record on your form using bound fields.

If you do not want to bind the textboxes regardless, there should be nothing to stop you appending unbound values from your form (although as I have said I am confused as to why you want to do so). I can see no logical reason why deleting and recreating the bound table should have made any difference.

You will not be able to append more than one record at a time this way given the single-value nature of the textboxes concerned when you run the append query even if you are successful in your current approach.

-Stewart
Aug 2 '09 #14

100+
P: 171
Hi Stewart Ross Inverness,
Quote1:
"You want to append unbound textbox data into tblSMS from a form which is bound to the same table"
Reply1: The form is not bound to any table
Quote2:
"You have not mentioned any reason why you are not already binding these textboxes"
Reply2: The reason I never bind a text box to a table is because I always have the data validated via VBA before it goes into the table. Also by using an append query I can getting a time stamp showing me when the entry was made and a current user stamp showing me which user made the entry, so to me the difference between binding a table to the form vs using unbound text boxes w/an append query is huge.
Quote3:
"You will not be able to append more than one record at a time this way"
Reply3: I only want to append 1 record at a time


Thank You
Aug 2 '09 #15

Expert Mod 2.5K+
P: 2,545
If all of that works for you then fine - but it can be done just as well with bound forms, using the Before Update event of the form to trigger data validation and record timestamping, with cancellation of the update if the validation fails.

Validation of individual fields can also be triggered from the After Update events of each field (bound or unbound).

Using unbound forms means replicating much of what Access already does - including populating each record onto the form in the first place. As mentioned, if it works for you then that's just fine - but you have a lot of items to maintain by taking this approach.

-Stewart
Aug 2 '09 #16

100+
P: 171
Thanks Stewart Ross Inverness,

For consistency I just prefer all the forms to be unbound. In some situations I may be appending more than 1 table, so if those can be catered by a bound table, then definatley I will try that next time.


Thank You
Aug 3 '09 #17

Post your reply

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