Get Current (1 Viewer)

mizzourob · Sep 13, 2018

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...

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 · Sep 13, 2018

You don't have a FROM clause.

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

isladogs · Sep 13, 2018

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

Code:

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

mizzourob · Sep 14, 2018

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 · Sep 14, 2018

mizzourob said:
When I tried adding that...

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

mizzourob · Sep 14, 2018

MarkK said:
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

Code:

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 · Sep 14, 2018

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 · Sep 14, 2018

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

pbaldy · Sep 14, 2018

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

Jeffr.Lipton · Sep 14, 2018

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

Code:

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 · Sep 14, 2018

It already had single quotes around it.

Jeffr.Lipton · Sep 14, 2018

pbaldy said:
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 · Sep 14, 2018

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

mizzourob · Sep 17, 2018

Re: Get Current [Solved]

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

Code:

Set Recordset = CurrentDb.OpenRecordset(SQLstring, dbOpenDynaset)

Full code is now

Code:

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 · Sep 17, 2018

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

Jeffr.Lipton · Sep 17, 2018

pbaldy said:
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 · Sep 17, 2018

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

Get Current (1 Viewer)

mizzourob

New member

pbaldy

Wino Moderator

isladogs

MVP / VIP

mizzourob

New member

MarkK

bit cruncher

mizzourob

New member

pbaldy

Wino Moderator

mizzourob

New member

pbaldy

Wino Moderator

Jeffr.Lipton

Registered User.

pbaldy

Wino Moderator

Jeffr.Lipton

Registered User.

pbaldy

Wino Moderator

mizzourob

New member

pbaldy

Wino Moderator

Jeffr.Lipton

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread