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 !

[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

[Stewart Ross]

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

[iheartvba]

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