Recordset problem

[AlexD]

My query uses data from form's combo boxes (from tab
control) to filter data.
When I'm checking

DCount("*", "qryQuery") = 0 Then ... It's more than 0

But, after

Me.RecordSource = "qryIngred_Cost_R"
Me.Recordset

the recordset is empty. When I open the query during the
Proc from DB is empty too. But, when I open it after the
Proc it's working and I get a result.
I'm using linked tables - may be this is the issue.

 

[Dirk Goldgar]

My query uses data from form's combo boxes (from tab
control) to filter data.
When I'm checking

DCount("*", "qryQuery") = 0 Then ... It's more than 0

But, after

Me.RecordSource = "qryIngred_Cost_R"
Me.Recordset

the recordset is empty. When I open the query during the
Proc from DB is empty too. But, when I open it after the
Proc it's working and I get a result.
I'm using linked tables - may be this is the issue.

Please post the code.

 

[AlexD]

Here is the code, Dirk.

Dim rstRecordset As DAO.Recordset
Dim dbs As Database
Dim strIngrPrice As String

txtTxt.Value = ""

If cboComboBox <> "" Then

Set dbs = OpenDatabase("Database.mdb")

If DCount("*", "qryQuery") = 0 Then

Msg = MsgBox("...", vbCritical, "No data")
rstRecordset.Close
dbs.Close
Exit Sub
Else

Me.RecordSource = "qryQuery"
Set rstRecordset = Me.Recordset

strStr = rstRecordset![Cost]
txtTxt.Value = strIngrPrice
rstRecordset.Close
dbs.Close

End If

End If

 

[Dirk Goldgar]

Here is the code, Dirk.

Dim rstRecordset As DAO.Recordset
Dim dbs As Database
Dim strIngrPrice As String

txtTxt.Value = ""

If cboComboBox <> "" Then

Set dbs = OpenDatabase("Database.mdb")

If DCount("*", "qryQuery") = 0 Then

Msg = MsgBox("...", vbCritical, "No data")
rstRecordset.Close
dbs.Close
Exit Sub
Else

Me.RecordSource = "qryQuery"
Set rstRecordset = Me.Recordset

strStr = rstRecordset![Cost]
txtTxt.Value = strIngrPrice
rstRecordset.Close
dbs.Close

End If

End If

Is that your real code, quoted exactly? I find it hard to believe your
real code has all those generic names: txtTxt, cboComboBox,
Database.mdb, qryQuery, strStr. Please, when you are asked to post your
code for remote debugging, unless there are real reasons of privacy and
security to argue against it, post the *real* code, not code that you've
modified just for the post. If you delete irrelevancies in the interest
of brevity, that's okay, but include a note that you've deleted
something.

There are lots of things wrong, or at least potentially wrong, with the
code you posted, but it's hard to be sure where the real problems lie
because I don't think that's your real code. Here are some possible
problem areas:

(1)

txtTxt.Value = ""

I take it this is a text box. Is it unbound or bound to a text field?
If not, the assignment will fail. If so, you may still want to use

txtTxt.Value = Null

to clear it, rather than assigning a zero-length string. In general,
Null is used in Access for unknown values, while the zero-length string
is used only for text values that are known not to exist. Note,
however, that this is unlikely to be the source of whatever error you
are experiencing.


(2)

If cboComboBox <> "" Then

Unless cboComboBox is bound to a text field that may contain a
zero-length string, this test will never be true. At a guess, you
should be testing like this:

If IsNull(cboComboBox) Then

(3)

Set dbs = OpenDatabase("Database.mdb")

Am I guessing right that "Database.mdb" is actually the current
database, the one in which this code is running? If so, you don't need
to use OpenDatabase to open it; it's already open. If you need it,
just use the CurrentDb function to get a reference to a copy of the
active database:

Set dbs = CurrentDb

HOWEVER, I don't see any reason in the posted code for you to be using a
database object at all. Quite likely this line should go away
altogether.

(4)

Set dbs = OpenDatabase("Database.mdb")

If DCount("*", "qryQuery") = 0 Then

Msg = MsgBox("...", vbCritical, "No data")
rstRecordset.Close
dbs.Close
Exit Sub

You don't need to open the database object dbs before calling DCount,
since that function uses its own database object internally.

At this point you haven't opened or set the recordset object
rstRecordset, so attempting to Close it will raise an error.

If you don't open the database object at this point (since you don't
need it in this branch of the logic) you shouldn't close it.

In the code as far as you've posted it, the Exit Sub statement is
unnecessary at this point. Since you haven't posted the complete code,
I can't tell whether you need it or not.

(5)

Me.RecordSource = "qryQuery"
Set rstRecordset = Me.Recordset

strStr = rstRecordset![Cost]
txtTxt.Value = strIngrPrice
rstRecordset.Close
dbs.Close

Your earlier description makes it sound like this is where you're findin
g a problem. I can't be sure what the problem is, though, until the
code is cleaned up. There's no declaration for "strStr", so I don't
know whether this is supposed to be a renaming of strIngrPrice or some
other variable. Are you trying to pick up the value from
rstRecordset![Cost] and put it in txtTxt? If so, why don't you just
bind that text box to the Cost field in the first place, since you're
making that query be the form's recordsource? Whatever you're doing
here, you're plainly going the long way around. But until I understand
what you're really trying to accomplish, I can't tell you how to fix it.

One thing I can tell you for sure: you must *not* close the form's
recordset! You didn't open it, and you shouldn't be closing it. So the
line ...

rstRecordset.Close

.... should be deleted. And if as I suspect you don't need a database
object at all, this line, too ...

dbs.Close

.... should go.

That's about all I can tell right now, and I don't know if any of it is
going to solve your most immediate problem. If you'll tell me what
you're trying to accomplish, post your real code, and tell me what event
is executing it, I may be able to give better advice.

 

[AlexD]

Thank you very much, Dirk.
I really appreciate your explanation. You are right I was
trying to simplify the code by changing names and confused
many things.

The thing is I'm trying to create a form for data entries
with combo and text boxes. 1st combo box - when a user
click it and select some product, the items associated
with the product in the 1st combo box will appear in the
2nd one . When the user select a item in the 2d combo box,
the price associated with the item in the 2d combo box
should appear in the text box. (There will be other text
boxes for manual qty entries.)
Everything is working fine (I appreciate the tip about
CurrentDB. It'll help me very much in many cases.) For the
combo boxes I'm using RecordSource in properties such as
Select ... WHERE forms!frmForm!cboComboBox and it's
working. But, I've stuck with appearence a price in the
text box. There is no RecordSource in the text box's
properties. This is why I'm trying to use Recordset. The
query itself with the combo box value in WHERE statement
form!frmForm!cboComboBox is working fine but recordset
with VB is empty I don't know why.
I'm writing from my home computer. So, I'll use these
simple names

Proc cboComboBox_Click
Dim rstRecordset As DAO.Recordset
Dim dbs As Database
Dim strPrice As String

txtTextBox.Value = "" ' May be you are right I'll check it

If cboComboBox <> "" Then ' It means there should be some
item in the combo box to have a price in the text box.

Set dbs = OpenDatabase("Database.mdb") 'I'll change it to
CurrentDB

If DCount("*", "qryQuery") = 0 Then

Msg = MsgBox("...", vbCritical, "No data")
' the close rst and db of course shouldn't be here

Exit Sub

Else

Me.RecordSource = "qryQuery"
Set rstRecordset = Me.Recordset ' the problem is here,
it's empty despite the query itself is working if I run it
when I go out from the Proc. But, when I run the query
from DB during the Proc it's empty.
'In addition, I checked the recordset with RecordCount.
It's equal 0.

rstPrice = rstRecordset![Price] ' Of course it'll be with
the mistake "no records"

txtTextBox.Value = rstPrice
rstRecordset.Close
dbs.Close

End If
End If

End Proc

I think there is something very simple but I cannot see it.

Thanks,

Alex

-----Original Message-----

Here is the code, Dirk.

Dim rstRecordset As DAO.Recordset
Dim dbs As Database
Dim strIngrPrice As String

txtTxt.Value = ""

If cboComboBox <> "" Then

Set dbs = OpenDatabase("Database.mdb")

If DCount("*", "qryQuery") = 0 Then

Msg = MsgBox("...", vbCritical, "No data")
rstRecordset.Close
dbs.Close
Exit Sub
Else

Me.RecordSource = "qryQuery"
Set rstRecordset = Me.Recordset

strStr = rstRecordset![Cost]
txtTxt.Value = strIngrPrice
rstRecordset.Close
dbs.Close

End If

End If

Is that your real code, quoted exactly? I find it hard to believe your
real code has all those generic names: txtTxt, cboComboBox,
Database.mdb, qryQuery, strStr. Please, when you are asked to post your
code for remote debugging, unless there are real reasons of privacy and
security to argue against it, post the *real* code, not code that you've
modified just for the post. If you delete irrelevancies in the interest
of brevity, that's okay, but include a note that you've deleted
something.

There are lots of things wrong, or at least potentially wrong, with the
code you posted, but it's hard to be sure where the real problems lie
because I don't think that's your real code. Here are some possible
problem areas:

(1)

txtTxt.Value = ""

I take it this is a text box. Is it unbound or bound to a text field?
If not, the assignment will fail. If so, you may still want to use

txtTxt.Value = Null

to clear it, rather than assigning a zero-length string. In general,
Null is used in Access for unknown values, while the zero- length string
is used only for text values that are known not to exist. Note,
however, that this is unlikely to be the source of whatever error you
are experiencing.


(2)

If cboComboBox <> "" Then

Unless cboComboBox is bound to a text field that may contain a
zero-length string, this test will never be true. At a guess, you
should be testing like this:

If IsNull(cboComboBox) Then

(3)

Set dbs = OpenDatabase("Database.mdb")

Am I guessing right that "Database.mdb" is actually the current
database, the one in which this code is running? If so, you don't need
to use OpenDatabase to open it; it's already open. If you need it,
just use the CurrentDb function to get a reference to a copy of the
active database:

Set dbs = CurrentDb

HOWEVER, I don't see any reason in the posted code for you to be using a
database object at all. Quite likely this line should go away

altogether.

(4)
Set dbs = OpenDatabase("Database.mdb")

If DCount("*", "qryQuery") = 0 Then

Msg = MsgBox("...", vbCritical, "No data")
rstRecordset.Close
dbs.Close
Exit Sub

You don't need to open the database object dbs before calling DCount,
since that function uses its own database object internally.

At this point you haven't opened or set the recordset object
rstRecordset, so attempting to Close it will raise an error.

If you don't open the database object at this point (since you don't
need it in this branch of the logic) you shouldn't close it.

In the code as far as you've posted it, the Exit Sub statement is
unnecessary at this point. Since you haven't posted the complete code,
I can't tell whether you need it or not.

(5)

Me.RecordSource = "qryQuery"
Set rstRecordset = Me.Recordset

strStr = rstRecordset![Cost]
txtTxt.Value = strIngrPrice
rstRecordset.Close
dbs.Close

Your earlier description makes it sound like this is where you're findin
g a problem. I can't be sure what the problem is, though, until the
code is cleaned up. There's no declaration for "strStr", so I don't
know whether this is supposed to be a renaming of strIngrPrice or some
other variable. Are you trying to pick up the value from
rstRecordset![Cost] and put it in txtTxt? If so, why don't you just
bind that text box to the Cost field in the first place, since you're
making that query be the form's recordsource? Whatever you're doing
here, you're plainly going the long way around. But until I understand
what you're really trying to accomplish, I can't tell you how to fix it.

One thing I can tell you for sure: you must *not* close the form's
recordset! You didn't open it, and you shouldn't be closing it. So the
line ...

rstRecordset.Close

.... should be deleted. And if as I suspect you don't need a database
object at all, this line, too ...

dbs.Close

.... should go.

That's about all I can tell right now, and I don't know if any of it is
going to solve your most immediate problem. If you'll tell me what
you're trying to accomplish, post your real code, and tell me what event
is executing it, I may be able to give better advice.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Thank you very much, Dirk.
I really appreciate your explanation. You are right I was
trying to simplify the code by changing names and confused
many things.

The thing is I'm trying to create a form for data entries
with combo and text boxes. 1st combo box - when a user
click it and select some product, the items associated
with the product in the 1st combo box will appear in the
2nd one . When the user select a item in the 2d combo box,
the price associated with the item in the 2d combo box
should appear in the text box. (There will be other text
boxes for manual qty entries.)
Everything is working fine (I appreciate the tip about
CurrentDB. It'll help me very much in many cases.) For the
combo boxes I'm using RecordSource in properties such as
Select ... WHERE forms!frmForm!cboComboBox and it's
working. But, I've stuck with appearence a price in the
text box. There is no RecordSource in the text box's
properties. This is why I'm trying to use Recordset. The
query itself with the combo box value in WHERE statement
form!frmForm!cboComboBox is working fine but recordset
with VB is empty I don't know why.
I'm writing from my home computer. So, I'll use these
simple names

Proc cboComboBox_Click
Dim rstRecordset As DAO.Recordset
Dim dbs As Database
Dim strPrice As String

txtTextBox.Value = "" ' May be you are right I'll check it

If cboComboBox <> "" Then ' It means there should be some
item in the combo box to have a price in the text box.

Set dbs = OpenDatabase("Database.mdb") 'I'll change it to
CurrentDB

If DCount("*", "qryQuery") = 0 Then

Msg = MsgBox("...", vbCritical, "No data")
' the close rst and db of course shouldn't be here

Exit Sub

Else

Me.RecordSource = "qryQuery"
Set rstRecordset = Me.Recordset ' the problem is here,
it's empty despite the query itself is working if I run it
when I go out from the Proc. But, when I run the query
from DB during the Proc it's empty.
'In addition, I checked the recordset with RecordCount.
It's equal 0.

rstPrice = rstRecordset![Price] ' Of course it'll be with
the mistake "no records"

txtTextBox.Value = rstPrice
rstRecordset.Close
dbs.Close

End If
End If

End Proc

I think there is something very simple but I cannot see it.

Thanks,

Alex

I think you are confused about the way some of these properties like
RecordSource and Recordset work. If you're trying to use this form for
data entry, you do not want to go changing its RecordSource; that will
disconnect the form from the table you're trying to update. With regard
to the combo boxes, I think you wrote RecordSource when you meant
RowSource, but if you are successfully getting the second combo box to
be filtered by the first, you've probably actually got that part set up
okay.

However, if I were doing this I would set things up so that I don't have
to do a separate lookup to find the price for the item chosen in the
second combo box. Instead, I'd include that price in the combo box's
RowSource query, and include it as an additional column in the combo
box. This column may be hidden (Column Width set to 0) or left visible
when the combo box's list s dropped down.

Let's suppose your combo boxes are named "cboProduct" and
"cboProductItem", and that the text box you want to put the price into
is named "txtPrice". And let's suppose you set cboProductItem to have
three columns, and set its RowSource to something like this:

SELECT ItemID, ItemDescription, ItemPrice
FROM tblProductItems
WHERE ProductID = [Forms]![YourFormName]![cboProduct];

Now, to make cboProductItems show only the items relevant to the product
chosen in cboProduct, you have this code in the AfterUpdate event of
cboProduct:

'----- start of code for cboProduct -----
Private Sub cboProduct_AfterUpdate()

Me.cboProductItem.Requery
Me.txtPrice = Null ' clear the price -- new product chosen

End Sub
'----- end of code for cboProduct -----

Now, when the user chooses a "product item" from "cboProductItem", you
want to put the priceof that item into txtPrice. Again use the
control's AfterUdate event:

'----- start of code for cboProductItem -----
Private Sub cboProductItem_AfterUpdate()

If Not IsNull(cboProductItem) Then
Me.txtPrice = CCur(Me.cboProductItem.Column(2))
' Note: Column(2) is the third column in the combo list.
End If

End Sub
'----- end of code for cboProductItem -----

And that is that.

 

[AlexD]

Thanks very much, Dirk.
I couldn't even expect that I would get help like this.

Alex

-----Original Message-----

Thank you very much, Dirk.
I really appreciate your explanation. You are right I was
trying to simplify the code by changing names and confused
many things.

The thing is I'm trying to create a form for data entries
with combo and text boxes. 1st combo box - when a user
click it and select some product, the items associated
with the product in the 1st combo box will appear in the
2nd one . When the user select a item in the 2d combo box,
the price associated with the item in the 2d combo box
should appear in the text box. (There will be other text
boxes for manual qty entries.)
Everything is working fine (I appreciate the tip about
CurrentDB. It'll help me very much in many cases.) For the
combo boxes I'm using RecordSource in properties such as
Select ... WHERE forms!frmForm!cboComboBox and it's
working. But, I've stuck with appearence a price in the
text box. There is no RecordSource in the text box's
properties. This is why I'm trying to use Recordset. The
query itself with the combo box value in WHERE statement
form!frmForm!cboComboBox is working fine but recordset
with VB is empty I don't know why.
I'm writing from my home computer. So, I'll use these
simple names

Proc cboComboBox_Click
Dim rstRecordset As DAO.Recordset
Dim dbs As Database
Dim strPrice As String

txtTextBox.Value = "" ' May be you are right I'll check it

If cboComboBox <> "" Then ' It means there should be some
item in the combo box to have a price in the text box.

Set dbs = OpenDatabase("Database.mdb") 'I'll change it to
CurrentDB

If DCount("*", "qryQuery") = 0 Then

Msg = MsgBox("...", vbCritical, "No data")
' the close rst and db of course shouldn't be here

Exit Sub

Else

Me.RecordSource = "qryQuery"
Set rstRecordset = Me.Recordset ' the problem is here,
it's empty despite the query itself is working if I run it
when I go out from the Proc. But, when I run the query
from DB during the Proc it's empty.
'In addition, I checked the recordset with RecordCount.
It's equal 0.

rstPrice = rstRecordset![Price] ' Of course it'll be with
the mistake "no records"

txtTextBox.Value = rstPrice
rstRecordset.Close
dbs.Close

End If
End If

End Proc

I think there is something very simple but I cannot see it.

Thanks,

Alex

I think you are confused about the way some of these properties like
RecordSource and Recordset work. If you're trying to use this form for
data entry, you do not want to go changing its RecordSource; that will
disconnect the form from the table you're trying to update. With regard
to the combo boxes, I think you wrote RecordSource when you meant
RowSource, but if you are successfully getting the second combo box to
be filtered by the first, you've probably actually got that part set up
okay.

However, if I were doing this I would set things up so that I don't have
to do a separate lookup to find the price for the item chosen in the
second combo box. Instead, I'd include that price in the combo box's
RowSource query, and include it as an additional column in the combo
box. This column may be hidden (Column Width set to 0) or left visible
when the combo box's list s dropped down.

Let's suppose your combo boxes are named "cboProduct" and
"cboProductItem", and that the text box you want to put the price into
is named "txtPrice". And let's suppose you set cboProductItem to have
three columns, and set its RowSource to something like this:

SELECT ItemID, ItemDescription, ItemPrice
FROM tblProductItems
WHERE ProductID = [Forms]![YourFormName]![cboProduct];

Now, to make cboProductItems show only the items relevant to the product
chosen in cboProduct, you have this code in the AfterUpdate event of
cboProduct:

'----- start of code for cboProduct -----
Private Sub cboProduct_AfterUpdate()

Me.cboProductItem.Requery
Me.txtPrice = Null ' clear the price -- new product chosen

End Sub
'----- end of code for cboProduct -----

Now, when the user chooses a "product item" from "cboProductItem", you
want to put the priceof that item into txtPrice. Again use the
control's AfterUdate event:

'----- start of code for cboProductItem -----
Private Sub cboProductItem_AfterUpdate()

If Not IsNull(cboProductItem) Then
Me.txtPrice = CCur(Me.cboProductItem.Column(2))
' Note: Column(2) is the third column in the combo list.
End If

End Sub
'----- end of code for cboProductItem -----

And that is that.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.