Simple Append Query Not Working

[iheartvba]

Hi,
I have a simple append query which takes data from a form and appends it into a table.

INSERT INTO tblSmsSent ( MobileNumber, ClientName, TimeSent, AppointmentTime, AppointmentDate )
SELECT [Forms]![frmSMS]![tbMobile] AS Expr1, [Forms]![frmSMS]![tbFName] AS Expr2, Now() AS Expr3, [Forms]![frmSMS]![tbTime] AS Expr4, [Forms]![frmSMS]![tbDate] AS Expr5
FROM tblSmsSent;

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 !

[ChipR]

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:

Private Sub cmdAddRecord_Click()
On Error GoTo ErrorHandler
  
  Dim strSQL as String

  strSQL = "INSERT INTO tblSmsSent " _
         & "(MobileNumber, ClientName, TimeSent, AppointmentTime, AppointmentDate) " _
         & "VALUES (" _
         & tbMobile & ", " _
         & tbFName & ", #" _
         & Now() & "#, #" _
         & tbTime & "#, #" _
         & tbDate & "#);"

  'MsgBox strSQL     'For testing

  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL, 0
  DoCmd.SetWarnings True

ExitCode:
  Exit Sub

ErrorHandler:
  MsgBox "Error adding record. " & vbCrLf & "Error Number: " & Err.Number & vbCrLf & " Description: " & Err.Description
  Resume ExitCode

End Sub

[iheartvba]

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

[ChipR]

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.

[iheartvba]

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

[FishVal]

....

INSERT INTO tblSmsSent ( MobileNumber, ClientName, TimeSent, AppointmentTime, AppointmentDate )
SELECT [Forms]![frmSMS]![tbMobile] AS Expr1, [Forms]![frmSMS]![tbFName] AS Expr2, Now() AS Expr3, [Forms]![frmSMS]![tbTime] AS Expr4, [Forms]![frmSMS]![tbDate] AS Expr5
FROM tblSmsSent;

....

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

[iheartvba]

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

[FishVal]

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

[iheartvba]

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!

[FishVal]

: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?

[iheartvba]

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

[Stewart Ross]

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

[iheartvba]

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.

[Stewart Ross]

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

[iheartvba]

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