Re: Recordset problem
From: AlexD (anonymous_at_discussions.microsoft.com)
Date: 04/10/04
- Next message: Kate: "Connection between MDB and SQL Server"
- Previous message: TC: "Re: msaccess.exe remains in task manager; which vars cause?"
- In reply to: Dirk Goldgar: "Re: Recordset problem"
- Next in thread: Dirk Goldgar: "Re: Recordset problem"
- Reply: Dirk Goldgar: "Re: Recordset problem"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 9 Apr 2004 23:37:33 -0700
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-----
>"AlexD" <anonymous@discussions.microsoft.com> wrote in
message
>news:1a44401c41dc9$63d7de20$a401280a@phx.gbl
>> 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)
>
>
>.
>
- Next message: Kate: "Connection between MDB and SQL Server"
- Previous message: TC: "Re: msaccess.exe remains in task manager; which vars cause?"
- In reply to: Dirk Goldgar: "Re: Recordset problem"
- Next in thread: Dirk Goldgar: "Re: Recordset problem"
- Reply: Dirk Goldgar: "Re: Recordset problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
