Get Current

[mizzourob]

I have a table named "General" which contains fields "CONTRACT" and "EXTENSION" I am trying to find the last entered EXTENSION when viewing any of the CONTRACT record value in the table using some VBA in a on click event button in a form that uses the General table as it's record source. The EXTENSION value is in the third column in the General data table (array element 2).

But I keep getting a 3075 error code. Here is my code...

Dim SQLstring As String
SQLstring = "SELECT * WHERE General.[CONTRACT] = '" & Me.CONTRACT& "' ORDER BY EXTENSION DESC ;"
MsgBox SQLstring
Recordset = CurrentDb.OpenRecordset(SQLstring)
MsgBox "EXTENSION = " & Recordset.Fields(2)

The first message box generates what appears to be a correct SQL string but the second message box fails to appear and I only get the 3075 error. Any thoughts would be great!

 

[pbaldy]

You don't have a FROM clause.

I'd also use the TOP predicate so only the desired record is returned.

 

[isladogs]

Your sql statement is incorrect as it doesn't include the table the data is from.
The first part should be

SELECT * FROM General WHERE Contract = .....

 

[mizzourob]

Thanks for the tip on adding the From clause. When I tried adding that I end up with a run-time error 3131 Syntax error in 'From' Clause. Still not sure what I'm missing...

 

[MarkK]

When I tried adding that...

Pro tip: We can't troubleshoot your SQL if you don't show it to us.
Mark

 

[mizzourob]

Pro tip: We can't troubleshoot your SQL if you don't show it to us.
Mark

If I modify my code with the FROM clause as suggested and as shown below error 3131 pops up

Dim SQLstring As String
SQLstring = "SELECT * FROM General WHERE General.[CONTRACT] = '" & Me.CONTRACT& "' ORDER BY EXTENSION DESC ;"
MsgBox SQLstring
Recordset = CurrentDb.OpenRecordset(SQLstring)
MsgBox "EXTENSION = " & Recordset.Fields(2)

 

[pbaldy]

This will let you push out the finished SQL string so you can post it here:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, there should be a space between these: "Me.CONTRACT&"

 

[mizzourob]

I wonder if the issue has to do with the table name being GENERAL which is a reserved word?

 

[pbaldy]

If so, bracketing it should work around the problem: [General]

 

[Jeffr.Lipton]

Also, if Me.Contract is text, you need a quote character around it:

Dim Quot_Char as String
Quot_Char = chr(34)
SQLstring = "SELECT * FROM [General] WHERE [General].[CONTRACT] = '" & Quot_Char & Me.CONTRACT & Quot_Char & "' ORDER BY EXTENSION DESC ;"

 

[pbaldy]

It already had single quotes around it.

 

[Jeffr.Lipton]

It already had single quotes around it.

You're right! I've had problems using single quotes from time to time (sometimes ACCESS seems to demand a double quote! :banghead, so I've given up and just use a quote character. I've just found it's less likely to lead to problems.

 

[pbaldy]

The singles should work unless the text has one in it, like O'Shea. You're safer with the double.

 

[mizzourob]

Re: Get Current [Solved]

To finally solve this, I had to alter my OpenRecordset command by appended dbOpenDynaset to it.

Set Recordset = CurrentDb.OpenRecordset(SQLstring, dbOpenDynaset)

Full code is now

Dim SQLstring As String
SQLstring = "SELECT * FROM General WHERE General.[CONTRACT] = '" & Me.CONTRACT& "' ORDER BY EXTENSION DESC ;"
MsgBox SQLstring
Set Recordset = CurrentDb.OpenRecordset(SQLstring, dbOpenDynaset)
MsgBox "EXTENSION = " & Recordset.Fields(2)

 

[pbaldy]

I wouldn't expect the absence of that to cause the syntax error you were getting, but glad you got it sorted.

 

[Jeffr.Lipton]

The singles should work unless the text has one in it, like O'Shea. You're safer with the double.

Now that I can link to URLs, here's a good discussion on single vs double quotes by a certain Mr Baldy: http://www.baldyweb.com/BuildSQL.htm

 

[pbaldy]

That Baldy guy is an idiot, but luckily the FAQ was written by Jason.