Help with SQL statement in VBA

[oli82]

Hello,

New to adding SQL script in VBA so looking for your wisdom.

In this database people make requests for a piece of data, is the data they request has already been requested I am looking for a popup to tell the enterer that this has already been requested and the status of the request.


The script is as follows:

_____________________________________________________

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim rsCount As Integer
Dim SQLtext As String

SQLtext = "SELECT T_Requests.Figure_ID, T_Requests.Purpose, T_Requests.State" & _
"FROM T_Data INNER JOIN T_Requests ON T_Data.Figure_ID = T_Requests.Figure_ID" & _
"WHERE (((T_Requests.Figure_ID) = Forms!T_Data!Figure_ID) And ((T_Requests.Purpose) = Forms!T_Requests!Purpose))" & _
"GROUP BY T_Requests.Figure_ID, T_Requests.Purpose, T_Requests.State"

Set db = CurrentDb
Set rs = db.OpenRecordset(queryNameOrSQL)
rs.MoveLast

rsCount = rs.RecordCount

If rsCount = 1 Then

MsgBox "Record already exists.", vbInformation, "System Error"

___________________________________________________________


Within the message box I would also like to include the purpose of the returned row.

The main issue I currently have is that the SQL text is not recognised as an object.

Sorry its so long but thankyou v much for help in advance,

Oliver

 

[qdogfball]

Hmm..

somewhere in the code you will need to run the SQL

Set rs = db.OpenRecordset(queryNameOrSQL)

Should this be ?

Set rs = db.OpenRecordset(SQLText)

Because I do not see where you actually run the query of your SQLtext.

A place to start..

 

[oli82]

Yes, you are right this part is wrong, I have changed to this and I still get the error message cannot find object.

Thanks for the help.

Oliver

 

[chergh]

Your missing spaces in your sql string:

SQLtext = "SELECT T_Requests.Figure_ID, T_Requests.Purpose, T_Requests.State " & _
"FROM T_Data INNER JOIN T_Requests ON T_Data.Figure_ID = T_Requests.Figure_ID " & _
"WHERE (((T_Requests.Figure_ID) = Forms!T_Data!Figure_ID) And ((T_Requests.Purpose) = Forms!T_Requests!Purpose)) " & _
"GROUP BY T_Requests.Figure_ID, T_Requests.Purpose, T_Requests.State"

 

[chergh]

To include the purpose:

msgbox(rs!Purpose)

 

[oli82]

Thanks will give it a go in a bit, when you say spaces I have to have a space after " like " select ?

Cheers,

Oliver

 

[chergh]

Your SQL:

SQLtext = "SELECT T_Requests.Figure_ID, T_Requests.Purpose, T_Requests.State" & _
"FROM T_Data INNER JOIN T_Requests ON T_Data.Figure_ID = T_Requests.Figure_ID"

If you were to output in a message box or something you would see:

SELECT T_Requests.Figure_ID, T_Requests.Purpose, T_Requests.StateFROM T_Data INNER JOIN T_Requests ON T_Data.Figure_ID = T_Requests.Figure_ID

My SQL

"SELECT T_Requests.Figure_ID, T_Requests.Purpose, T_Requests.State " & _
"FROM T_Data INNER JOIN T_Requests ON T_Data.Figure_ID = T_Requests.Figure_ID " & _

When output to a message box would look like:

SELECT T_Requests.Figure_ID, T_Requests.Purpose, T_Requests.State FROM T_Data INNER JOIN T_Requests ON T_Data.Figure_ID = T_Requests.Figure_ID

As you can see in your SQL there is no space before the "FROM" clause

 

[oli82]

thanks, thats clear to me now.

 

[Peter Bellamy]

You have proabably cracked it by now but you could put a Debug.print SQLtext line under it and then look at how it is being read in the Immediate window