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

SQL Statement not Inserting Into Table

P: 15
Hi All,

Please advise. i'm loosing sleep over this form :(.

The statement was working perfectly a little while ago but i don't know what happened. I didn't modify any fields in the table.

The statement is executed from the btn_requestForCircular and is suppose to insert into the Requests for Circulars table. The table has a primary key which is shared amongst other tables called RequestID. When the form loads. The RequestID is passed onto the From and Dimmed publically as GlobalRequestID.

The statement is:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.     strSQL = "INSERT INTO [tbl_RequestsForCirculars] (RequestID, DateofRequest, ReqDistributionDate, ReqImplementationDate, ImpactOperations, ImpactNational, ImpactedCentres, CircularSize, CRNumber, JobNumber, Attachments, UpdateType, FormsIncluded, Translation, DraftReceived, DraftSentForReview, ConditionalSignOff, FrenchTranslationCompleted, PrintJobCompleted, ScheduledDistribution, ScheduledImplementation, UrgentROC, Status)"
  3.     strSQL = strSQL & " VALUES ('" & GlobalRequestID & "', '" & txt_requestDate.Value & "', '" & txt_requestDistribution.Value & "', '" & txt_requestImplementation.Value & "', '" & impactOP & "', '" & impactN & "', '" & impactC & "', '" & cSize & "', '" & CRNumber & "', '" & JobNumber & "', '" & Attachments & "', '" & UpdateType & "', '" & FormsIncluded & "', '" & Translation & "', '" & txt_draftReceived.Value & "',"
  4.     strSQL = strSQL & " '" & txt_draftSent.Value & "', '" & txt_conditionalSignoff.Value & "', '" & translationdate & "', '" & txt_printCompleted.Value & "', '" & txt_scheduledDistribution.Value & "', '" & txt_scheduledImplementation.Value & "', '" & urgent & "', '" & "Open" & "')"
  5. DoCmd.SetWarnings False
  6. DoCmd.RunSQL strSQL

Please help. I've re written this statement several times and no luck.

Regards.
Dec 7 '06 #1
Share this Question
Share on Google+
4 Replies


P: 15
Folks!

I think i foudn the problem, but not the solution.

The string only picks up the following:
"INSERT INTO [tbl_RequestsForCirculars] (RequestID, DateofRequest, ReqDistributionDate, ReqImplementationDate, ImpactOperations, ImpactNational, ImpactedCentres, CircularSize, CRNumber, JobNumber, Attachments, UpdateType, FormsIncluded, Translation, Da"

So it doesn't pick up the complete string... and i don't understand why, since i've dimmed it as String :|.
Dec 7 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Global strings may have a character limit. I'm not sure, I'll check.

However, are you passing the string into a text field at any stage as this would restrict it to 255 characters.

Mary
Dec 7 '06 #3

P: 15
Thanks Mary,

I ended up using an Insert statement to place the primary key in the Table and then used the Update statement to update the remaining fields. I had to break the entire statement into 1 insert and 4 updates to get it to work.

Thanks for the reply.

Regards.
Dec 7 '06 #4

NeoPa
Expert Mod 15k+
P: 31,656
I'm with Mary on this one.
I've used much longer strings than 256 chars without problem.
Certainly the code as posted should work. You haven't tried it using a macro instead?
RunSQL Method
The RunSQL method carries out the RunSQL action in Visual Basic.

expression.RunSQL(SQLStatement, UseTransaction)
Remarks
The maximum length of the sqlstatement argument is 32,768 characters (unlike the SQL Statement action argument in the Macro window, whose maximum length is 256 characters).
What happens if you insert the line
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strSQL
before your DoCmd.RunSQL line?
Dec 7 '06 #5

Post your reply

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