Wrong Number of Arguments or invalid property assignment

[sgtSortor]

I Am trying to insert data into a table --- some of the fields are coming from a different form (frmHold)



DoCmd.RunSQL "INSERT INTO tblReportResults1 (AppID, DocumentID, ReportType, RptDate, ProjOff, ProjContactPerson,) VALUES (" & Forms!frmHold.AppIDHold, Forms!frmHold.DocumentIDHold, Me.ReportType, Me.RptDate, Me.ProjOff, Me.ProjContactPerson

I've verified that all of the fields are in the table and there is good data to be inserted.

Please Hellp

 

[ajetrumpet]

where does the error occur? Additionally, take out the last comma:

DoCmd.RunSQL "INSERT INTO tblReportResults1 (AppID, DocumentID, ReportType, 
   RptDate, ProjOff, ProjContactPerson[color=red][b][size=4][u],[/size][/color][/u][/b])

 

[pbaldy]

Additionally, each of the form references must be concatenated separately from the commas and such. In other words, only form references should be outside the quotes; everything else must be inside. On top of that, text values must be surrounded by single quotes, dates by #. Plus you missed the closing parentheses.

 

[ajetrumpet]

only form references should be outside the quotes; everything else must be inside.

Paul, this is not true for me, and I don't think it's a rule....because this works (if the control value is of text format):

"WHERE field = forms!form!control"

If the control value is of number format, the above code does not work, and you have to use your method:

"WHERE field = " & forms!form!control

can you explain this? Basically, any form control value that is compatible with the string data type can be included inside the quotation marks surrounding the entire statement....

 

[pbaldy]

Well, I'll stand partially corrected here. I forgot that RunSQL can correctly interpret form references. I always use the Execute method now, which can not, but is more efficient. That said, the OP is using "Me." for some of the references, which is only valid inside VBA and I'll be very surprised if the query will work with them inside the quotes. They either need to be changed to full form references, or concatenated into the string.

 

[ajetrumpet]

Well, I'll stand partially corrected here. I forgot that RunSQL can correctly interpret form references. I always use the Execute method now, which can not, but is more efficient. That said, the OP is using "Me." for some of the references, which is only valid inside VBA and I'll be very surprised if the query will work with them inside the quotes. They either need to be changed to full form references, or concatenated into the string.

Paul,

this subject REALLY needs a FAQ. I don't think people have a clue about the differences between using all of these in VBA (and I don't either!):

* me.
* me!
* forms()
* forms!form!control
* "forms!form!control"

Maybe the "Sarg" will have some answers...

 

[sgtSortor]

Okay, this one works now,

strInsert = "INSERT INTO tblReportResults1 ([AppID], [DocumentID], [ReportType], [RptDate], [ProjOff], [ProjContactPerson]) VALUES (forms!frmHold.AppIDHold, forms!frmHold.DocumentIDHold, forms!frmHold.ReportTypeHold, forms!frmHold.RptDateHold, forms!frmHold.ProjOffHold, forms!frmHold.ProjContactPersonHold)"
DoCmd.RunSQL strInsert

But now my second insert statement is pulling data from a table and one variable to insert into another table...

strInsert2 = "INSERT INTO tblReportResults2 ( RptResID, SectionID, QuestID, DocumentID ) SELECT ltblReportForms_QnA.SectionID, ltblReportForms_QnA.QuestionID, ltblReportForms_QnA.DocumentID FROM ltblReportForms_QnA WHERE (((ltblReportForms_QnA.DocumentID)=[forms]![frmHold].[DocumentIDHold]));"
DoCmd.RunSQL strInsert2


-RptResID - is a variable called vRptResID and I don't know where to place that in the insert statement.

 

[sgtSortor]

Well I figured out what I was doing.......

strInsert2 = "INSERT INTO tblReportResults2 ( RptResID, DocumentID, SectionID, QuestID, QuestType )SELECT tblReportResults1.RptResID, ltblReportForms_QnA.DocumentID, ltblReportForms_QnA.SectionID, ltblReportForms_QnA.QuestionID, ltblReportForms_QnA.QuestType FROM tblReportResults1, ltblReportForms_QnA WHERE (((ltblReportForms_QnA.DocumentID)=[forms]![frmHold].[DocumentIDHold]));"


Works exactly as I needed it too.