SQL Code Problem

[livvie]

What is wrong with this code. It is behind a form in Access 2003

SQL = " SELECT SQLACCESS.tblctwork.job_cpartn AS Partno, SQLACCESS.tblctwork.job_opartn AS CTD, SQLACCESS.tblctwork.job_partdn AS partName , " & _
" SQLACCESS.tblctwork.job_revno AS Rev, SQLACCESS.tblctwork.job_custom AS Customer, SQLACCESS.tblctmist.rec_batch AS Batch , " & _
" SQLACCESS.tblctmist.rec_qnty AS Qty, SQLACCESS.tblctwork.job_id AS JObid, SQLACCESS.tblctpur_h.pur_id AS PoNo , " & _
" SQLACCESS.tblctmist.mistid " & _
" FROM SQLACCESS.tblctpur_d INNER JOIN " & _
" SQLACCESS.tblctwork ON SQLACCESS.tblctpur_d.job_id = SQLACCESS.tblctwork.job_id INNER JOIN " & _
" SQLACCESS.tblctmist INNER JOIN " & _
" SQLACCESS.tblctpur_h ON SQLACCESS.tblctmist.rec_poid = SQLACCESS.tblctpur_h.pur_id ON " & _
" SQLACCESS.tblctpur_d.po_id = SQLACCESS.tblctpur_h.pur_id " & _
" WHERE SQLACCESS.tblctwork.job_id = Forms!frmWorksOrderComplete.form!fsubWorderHead.form!txtjobid "

 

[dtburdick]

You forgot the & in the last line possibly?

" WHERE SQLACCESS.tblctwork.job_id = " & Forms!frmWorksOrderComplete.form!fsubWorderHead.form!txtjobid "

 

[namliam]

You forgot the & in the last line possibly?

" WHERE SQLACCESS.tblctwork.job_id = " & Forms!frmWorksOrderComplete.form!fsubWorderHead.form!txtjobid "

Dont forget to remove the last quote

 

[livvie]

Dont forget to remove the last quote

I'm still getting the runtime error. A RunSQL action requires an argument consisting of an SQL statement.

 

[Mile-O]

DoCmd.RunSQL requires an action query (append, update, delete, etc.) to perform; your query is a selection query.

Why you are doing this in VBA, God only knows, you should just make it as a stored query definition.

 

[livvie]

DoCmd.RunSQL requires an action query (append, update, delete, etc.) to perform; your query is a selection query.

Why you are doing this in VBA, God only knows, you should just make it as a stored query definition.

The only reason I am doing it in VBA is because of the last bit (WHERE) you cant refer to a form in a stored procedure (or can you) and the where condition is going to change each time I pick a new record.

 

[Mile-O]

You cant refer to a form in a stored procedure (or can you)

Yes.

In the criteria in the query design grid just put:

[Forms]![MyForm]![MyControl]

The benefit is your database won't bloat as regularly and you save time by having a stored/saved query which can be run rather than a string which has to be converted, created, and then deleted each time.

 

[livvie]

Yes.

In the criteria in the query design grid just put:

[Forms]![MyForm]![MyControl]

The benefit is your database won't bloat as regularly and you save time by having a stored/saved query which can be run rather than a string which has to be converted, created, and then deleted each time.

This gives me an error 'Data type error in expression'. I am trying to refer to a control on a subform , I am using Access 2003 .adp

 

[Mile-O]

What sort of information do you store in the control you are referencing? Number/Text/Date?

 

[livvie]

What sort of information do you store in the control you are referencing? Number/Text/Date?

It's a Number (int)

 

[Mile-O]

Forms!frmWorksOrderComplete.form!fsubWorderHead.form!txtjobid

You have an extra Form in there.

 

[livvie]

Forms!frmWorksOrderComplete.form!fsubWorderHead.form!txtjobid

You have an extra Form in there.

If I change it to this it allows it but then when I run it cant convert the nvarchar to int.

= N'[Forms]![frmWorksOrderComplete]![form]![fsubWorderHead]![txtjobid]'

 

[Mile-O]

when I run it cant convert the nvarchar to int.

= N'[Forms]![frmWorksOrderComplete]![form]![fsubWorderHead]![txtjobid]'

You've lost me now. WHat's the N' and ' doing there?

 

[livvie]

You've lost me now. WHat's the N' and ' doing there?

Sorry
In SP's in .adp the criteria works slightly differently to the criteria .mdb.
For a string value you put in the '' and it automatically puts in the N. eg
=N'TEXT'
For a numberic value you shouldn't have to put in anything other than the value eg
=123
but when i try using Forms as the criteria I keep getting the 'Data type error in expression' this is why I said earlier that you cant reference forms in an SP as I have tried it before.

 

[Mile-O]

I have no experience with .adp so, what about a function in a module whereby the function references the form?

i.e.

WHERE MyField = MyFunction();

And in the module:

Public Function MyFunction() As Number
    MyFunction = Forms("MyForm").MyControl
End Function

 

[SQL_Hell]

Hi,

you are using SQL server yeah?

Waht you need to do is load the value into a VB variable then refernce that variable in the sql.


Like this:

Dim jobid as integer
jobid = [Forms]![frmWorksOrderComplete]![form]![fsubWorderHead]![txtjobid]

then change your where clause to:

WHERE SQLACCESS.tblctwork.job_id = " & jobid

 

[SQL_Hell]

if you jobid is not an integer then please let me know.

 

[livvie]

I have no experience with .adp so, what about a function in a module whereby the function references the form?

i.e.

WHERE MyField = MyFunction();

And in the module:

Public Function MyFunction() As Number
    MyFunction = Forms("MyForm").MyControl
End Function

sorry to be annoying you but I keep getting an error saying "MyFunction (or whatever I call it)" is not a recognised function name eventhough I have saved the module.

 

[Mile-O]

Have you saved it to a form's module or to a standalone module? It should go in the latter.

 

[livvie]

Have you saved it to a form's module or to a standalone module? It should go in the latter.

In a standalone mod and I have tried referencing the module and the function.
.adp might be more hassle than it is worth