Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 2nd, 2006, 12:27 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Fields object syntax in VBA query

I am new to ADO and trying to place a recordset field name in a vba query correctly. This works outside the query statement using double quotes with Debug.print and I get correct results. But inside the query statement shown below, I get an Error Invalid use of Syntax with Double or Single quotes. Everything works after the equal sign and up to the where statement.


.Open "SELECT Uparm, Aparm FROM WIST WHERE (Fields('Uparm').Value
= " & Me.Uparm & ")"

What is the correct syntax for this when placed after the WHERE statement? Documented text says it should be as it is typed with single quotes when in a query statement as above but it does not work. Fields('Uparm').Value



Debug.Print Fields["Uparm"].Value

I get the correct data when I use Debug.Print

Any help would be appreciated.


StepD



 
Old March 2nd, 2006, 12:41 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

The tricky part is trying to distiniguish between VBA and SQL.

Try this...

.Open "SELECT Uparm, Aparm FROM WIST WHERE Uparm = " & Me.Uparm

if Uparm is not numeric you may need to add quotes

... WHERE Uparm = '" & Me.Uparm & "'"

Hope that helps,

Mike

Mike
EchoVue.com
 
Old March 2nd, 2006, 01:27 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Tried it. I got "No value given for one or more required parameters"

Debug shows the right side as having a value but the left side no value. In my SELECT statement the 1st Uparm is from the table. the Uparm after the WHERE statement is referring to the recordset which is why I believe Uparm alone will not work.

Thanks for your response. I would be interested in any future solutions you may have.

StepD.

 
Old March 2nd, 2006, 01:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi StepD,

And don't forget about DateTime fields:

Code:
Sub Test()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String

    Dim strCriteria As String
    Dim intCriteria As Integer
    Dim dtmCriteria As Date

    strCriteria = "Text1"    ' TextField
    intCriteria = 2          ' NumberField
    dtmCriteria = #1/2/2006# ' DateTimeField

    Set cnn = CurrentProject.Connection

    strSQL = "SELECT * FROM tblRecords WHERE TextField = '" & strCriteria & "'"
    ' strSQL = "SELECT * FROM tblRecords WHERE NumberField = " & intCriteria
    ' strSQL = "SELECT * FROM tblRecords WHERE DateTimeField = #" & dtmCriteria & "#"

    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

    Debug.Print rst.GetString(adClipString, , ";")

End Sub
Also, ADO recordset object don't carry table schema information (keeps 'em light weight), so you can't get at a field's (or column's) Name through the Fields collection, only the field's Value.

All of the following are identical, returning the Value property (not the Name) of the EmployeeID field in the rstEmployees recordset object (assuming EmployeeID is the first field in the recordset).

The fields collection is 0-based.

rstEmployees.Fields("EmployeeID").Value
rstEmployees.Fields(0).Value
rstEmployees(0).Value
rstEmployees("EmployeeID").Value
rstEmployees!EmployeeID.Value
rstEmployees.Fields("EmployeeID")
rstEmployees.Fields(0)
rstEmployees(0)
rstEmployees("EmployeeID")
rstEmployees!EmployeeID

To get at the field's column name in the recordset's underlying table schema, you need to use either the OpenSchema method of a Connection object, or an ADOX Catalog object and its collections. See:

http://p2p.wrox.com/topic.asp?TOPIC_ID=39779

HTH,

Bob


 
Old March 2nd, 2006, 02:05 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

All correct, BUT none of those listed (tried them all) will work when placing them after the WHERE statement of a query. btw, I am looking for the field value, not the name.

Thanks Bob. All responses are welcome. Any addition help is ALWAYS appreciated.

StepD

 
Old March 2nd, 2006, 02:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

You can't reference the Fields collection of a recordset object in the recordset objects Open method anyway, 'cause the recordset object hasn't been allocated yet (hence, the Fields collection hasn't been iinitialized yet).

Bob

 
Old March 2nd, 2006, 02:51 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

It is not a question of if it's getting initialized, It's a question of If I can get all fields by not specify the WHERE why cant I get a specific field.


Thanks.


 
Old March 2nd, 2006, 02:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

You could, however, reference the fields collection of an open recordset in the command argument used to open a second recorset, like:

Set rst2 = New ADODB.Recordset
strSQL2 = "Select * From tblRecords WHERE NumberField = " & rst.Fields("NumberField").Value
rst2.Open strSQL2, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

Bob

 
Old March 2nd, 2006, 03:04 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Mike was right with:

"SELECT Uparm, Aparm FROM WIST WHERE Uparm = " & Me.Uparm

Thats a perfectly valid SQL statement. Your error message is indicating that Me.Uparm isn't providing a value. What is Me.Uparm? Is it the name of a control on your form? The Me syntax suggests that you're trying to pull a value from a contol on your form. So your SQL should be:

"SELECT Uparm, Aparm FROM WIST WHERE Uparm = " & Me.TheNameOfYour Control

Bob




 
Old March 2nd, 2006, 03:19 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

For example, if your control is a textbox bound to a text field in your table, use:

strSQL = "SELECT * FROM tblRecords WHERE TextField = '" & Me.txtTextField & "'"
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

If your control is a textbox bound to a number field in your table, use:

strSQL = "SELECT * FROM tblRecords WHERE NumberField = " & Me.txtNumberField
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

If your control is a textbox bound to a DateTime field in your table, use:

strSQL = "SELECT * FROM tblRecords WHERE DateTimeField = #" & Me.txtDateTimeField & "#"
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

Bob






Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Syntax in VBA? Bill_L Excel VBA 0 September 11th, 2008 08:47 AM
Vba Consolidate Syntax grouth Excel VBA 3 September 29th, 2006 07:37 PM
Using VBA to define query fields??? mmcdonal Access VBA 0 November 24th, 2004 03:45 PM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
help with VBA syntax jacqui Excel VBA 0 January 12th, 2004 02:08 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.