Multiple results from query

A

anders.vestnes

Hey.

I'm working on a code where I am going to pick out a unknowed number of
strings from a database. In the code below I am geting onley one result
in the "rst!BomItemId" thoo it should hawe been a unknowned number
(more than 10). could some one help me with how you are extracting
multiple reults from a VBA select query?


Function TotalPrice()

Dim BomItemId As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ComponentOrderJobId As String
Dim buffer As String

ComponentOrderJobId =
Forms![frmComponentOrderProject]![ComponentOrderJobId]

strSQL = "SELECT [BomItemID] FROM [TblBomItems] " & _
"WHERE [ComponentOrderJobID] = " & _
ComponentOrderJobId & " ;"

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

buffer = rst!BomItemId

cnn.Close

'Then some more code....

End Function
 
G

Guest

Your assumption could be wrong on the number of records to be returned. For a
quick and simple test, run the following as a query. Put in a proper value in
the Where clause and see what happens.

SELECT [BomItemID]
FROM [TblBomItems]
WHERE [ComponentOrderJobID] = [put proper value here] ;
 
A

anders.vestnes

Hey Jerry

Thanks for your feedback. I will try to be a bit more specific
concerning my question.
In my question I wrote:
SELECT [BomItemID] FROM [TblBomItems] WHERE .....

I have putted the same statement in to the ordinary SQL builder in
Access and got 125 results that should be correct for one of the
situations.

I tried to write
"SELECT max([BomItemID]) FROM [TblBomItems] WHERE ....."
And got the last record.

So I have written
"SELECT min([BomItemID]) FROM [TblBomItems] WHERE ....."
Then I naturally get the first record.

I have also tried with the DISTINCT attribute to get out all results.
It did not work. My assumption is then that I need to do something else
to get all the results out.

When writing neither max or min I get the same result as when writing
min.


Jerry Whittle skrev:
Your assumption could be wrong on the number of records to be returned. For a
quick and simple test, run the following as a query. Put in a proper value in
the Where clause and see what happens.

SELECT [BomItemID]
FROM [TblBomItems]
WHERE [ComponentOrderJobID] = [put proper value here] ;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
Hey.

I'm working on a code where I am going to pick out a unknowed number of
strings from a database. In the code below I am geting onley one result
in the "rst!BomItemId" thoo it should hawe been a unknowned number
(more than 10). could some one help me with how you are extracting
multiple reults from a VBA select query?


Function TotalPrice()

Dim BomItemId As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ComponentOrderJobId As String
Dim buffer As String

ComponentOrderJobId =
Forms![frmComponentOrderProject]![ComponentOrderJobId]

strSQL = "SELECT [BomItemID] FROM [TblBomItems] " & _
"WHERE [ComponentOrderJobID] = " & _
ComponentOrderJobId & " ;"

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

buffer = rst!BomItemId

cnn.Close

'Then some more code....

End Function
Click to expand...
Click to expand...
 
G

Guest

Last or First mean do NOT mean the same as Min and Max. That Max returned the
Last record is either a coincidence or you have done a very good job of
incrementing the BomItemID field.

DISTINCT gets ride of duplicates so if you have more than one record with
the same BomItemID, you will see that record only once.

If you have only the same data in BomItemID OR your Where clause narrows
down the BomItemID to only one, Min and Max will only return the same record.

You need to try it again with the specific thing that you are looking for in
the Where clause.
WHERE [ComponentOrderJobID] = [put proper value here]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
Hey Jerry

Thanks for your feedback. I will try to be a bit more specific
concerning my question.
In my question I wrote:
SELECT [BomItemID] FROM [TblBomItems] WHERE .....

I have putted the same statement in to the ordinary SQL builder in
Access and got 125 results that should be correct for one of the
situations.

I tried to write
"SELECT max([BomItemID]) FROM [TblBomItems] WHERE ....."
And got the last record.

So I have written
"SELECT min([BomItemID]) FROM [TblBomItems] WHERE ....."
Then I naturally get the first record.

I have also tried with the DISTINCT attribute to get out all results.
It did not work. My assumption is then that I need to do something else
to get all the results out.

When writing neither max or min I get the same result as when writing
min.


Jerry Whittle skrev:
Your assumption could be wrong on the number of records to be returned. For a
quick and simple test, run the following as a query. Put in a proper value in
the Where clause and see what happens.

SELECT [BomItemID]
FROM [TblBomItems]
WHERE [ComponentOrderJobID] = [put proper value here] ;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
Hey.

I'm working on a code where I am going to pick out a unknowed number of
strings from a database. In the code below I am geting onley one result
in the "rst!BomItemId" thoo it should hawe been a unknowned number
(more than 10). could some one help me with how you are extracting
multiple reults from a VBA select query?


Function TotalPrice()

Dim BomItemId As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ComponentOrderJobId As String
Dim buffer As String

ComponentOrderJobId =
Forms![frmComponentOrderProject]![ComponentOrderJobId]

strSQL = "SELECT [BomItemID] FROM [TblBomItems] " & _
"WHERE [ComponentOrderJobID] = " & _
ComponentOrderJobId & " ;"

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

buffer = rst!BomItemId

cnn.Close

'Then some more code....

End Function
Click to expand...
Click to expand...
Click to expand...
 
A

anders.vestnes

Thanks again Jerry ;)

If anyone out there would know anything about the ADODB.Recordset and
why it appears to only be able to hold on to one result I would be very
happy to here from you. I replete my question in short text:

I have a SQL statement like this:

SELECT [BomItemID] FROM [TblBomItems]
WHERE [ComponentOrderJobID] =
Forms![frmComponentOrderProject]![ComponentOrderJobId];

When executed in the Access Query builder it gives multiple results.
When executed in VBA (see full code earlier in string), it gives only
one result. (the first one of the many coming from the Query builder
test)

I have checked inside the ADODB.Recordset with the Add Watch
"thingy" to verify that it only contain one result when I step run
the code.

Jerry Whittle skrev:
Last or First mean do NOT mean the same as Min and Max. That Max returned the
Last record is either a coincidence or you have done a very good job of
incrementing the BomItemID field.

DISTINCT gets ride of duplicates so if you have more than one record with
the same BomItemID, you will see that record only once.

If you have only the same data in BomItemID OR your Where clause narrows
down the BomItemID to only one, Min and Max will only return the same record.

You need to try it again with the specific thing that you are looking for in
the Where clause.
WHERE [ComponentOrderJobID] = [put proper value here]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
Hey Jerry

Thanks for your feedback. I will try to be a bit more specific
concerning my question.
In my question I wrote:
SELECT [BomItemID] FROM [TblBomItems] WHERE .....

I have putted the same statement in to the ordinary SQL builder in
Access and got 125 results that should be correct for one of the
situations.

I tried to write
"SELECT max([BomItemID]) FROM [TblBomItems] WHERE ....."
And got the last record.

So I have written
"SELECT min([BomItemID]) FROM [TblBomItems] WHERE ....."
Then I naturally get the first record.

I have also tried with the DISTINCT attribute to get out all results.
It did not work. My assumption is then that I need to do something else
to get all the results out.

When writing neither max or min I get the same result as when writing
min.


Jerry Whittle skrev:
Your assumption could be wrong on the number of records to be returned. For a
quick and simple test, run the following as a query. Put in a proper value in
the Where clause and see what happens.

SELECT [BomItemID]
FROM [TblBomItems]
WHERE [ComponentOrderJobID] = [put proper value here] ;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hey.

I'm working on a code where I am going to pick out a unknowed number of
strings from a database. In the code below I am geting onley one result
in the "rst!BomItemId" thoo it should hawe been a unknowned number
(more than 10). could some one help me with how you are extracting
multiple reults from a VBA select query?


Function TotalPrice()

Dim BomItemId As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ComponentOrderJobId As String
Dim buffer As String

ComponentOrderJobId =
Forms![frmComponentOrderProject]![ComponentOrderJobId]

strSQL = "SELECT [BomItemID] FROM [TblBomItems] " & _
"WHERE [ComponentOrderJobID] = " & _
ComponentOrderJobId & " ;"

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

buffer = rst!BomItemId

cnn.Close

'Then some more code....

End Function
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

PMFBI

your rst might contain more than
one record, but when you perform
the following statement

buffer = rst!BomItemId

it is only going to pick up
the BomItemId from *one record*.

try this

Function TotalPrice()

Dim BomItemId As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ComponentOrderJobId As String
Dim buffer As String

ComponentOrderJobId =
Forms![frmComponentOrderProject]![ComponentOrderJobId]

strSQL = "SELECT [BomItemID] FROM [TblBomItems] " & _
"WHERE [ComponentOrderJobID] = " & _
ComponentOrderJobId & " ;"

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
do while NOT rst.EOF
buffer = buffer & rst!BomItemId & vbCrLf
rst.MoveNext
loop
Debug.Print buffer
cnn.Close


Again, apologies for butting in
(especially if I misunderstood)
 
A

Anders

Thanks a lot Gary!!

That worked perfectly :)

Gary Walter skrev:
PMFBI

your rst might contain more than
one record, but when you perform
the following statement

buffer = rst!BomItemId

it is only going to pick up
the BomItemId from *one record*.

try this

Function TotalPrice()

Dim BomItemId As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ComponentOrderJobId As String
Dim buffer As String

ComponentOrderJobId =
Forms![frmComponentOrderProject]![ComponentOrderJobId]

strSQL = "SELECT [BomItemID] FROM [TblBomItems] " & _
"WHERE [ComponentOrderJobID] = " & _
ComponentOrderJobId & " ;"

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
do while NOT rst.EOF
buffer = buffer & rst!BomItemId & vbCrLf
rst.MoveNext
loop
Debug.Print buffer
cnn.Close


Again, apologies for butting in
(especially if I misunderstood)

I'm working on a code where I am going to pick out a unknowed number of
strings from a database. In the code below I am geting onley one result
in the "rst!BomItemId" thoo it should hawe been a unknowned number
(more than 10). could some one help me with how you are extracting
multiple reults from a VBA select query?


Function TotalPrice()

Dim BomItemId As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ComponentOrderJobId As String
Dim buffer As String

ComponentOrderJobId =
Forms![frmComponentOrderProject]![ComponentOrderJobId]

strSQL = "SELECT [BomItemID] FROM [TblBomItems] " & _
"WHERE [ComponentOrderJobID] = " & _
ComponentOrderJobId & " ;"

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

buffer = rst!BomItemId

cnn.Close

'Then some more code....

End Function
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count number of Records Query always returns -1 10
Access Access 2003 VBA Open Recordset 1
ADO syntax problem 2
Error when calling function from query 2
Read query records 3
transfer the name of combobox to module 4
OWC (office web component ) chart , stacked XY scatter 0
Access 2003 and SQL Server 2005 and DAO recordset 1

Top