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]

@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

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