Recordset problem DAO - HELP! 
Author Message
 Recordset problem DAO - HELP!

I have the following code:

  SQLStr = "select client, sum(amt) as SumAmt from glacct inner join journl"
  SQLStr = SQLStr & " on journl.glacct = glacct.glacct"
  SQLStr = SQLStr & " where ((date <= #"
  SQLStr = SQLStr & CStr(gblEndDate) & "#"
  SQLStr = SQLStr & " and (journl.posted is null or journl.posted = '' or
journl.posted = ' '))"
  SQLStr = SQLStr & " and (glacct.[special processing] = '6'))"
  SQLStr = SQLStr & " group by client"

  Set rsPost1099Set = CurrentDb.OpenRecordset(SQLStr)
  If rsPost1099Set.RecordCount = 0 Then
     MsgBox "No records selected for the date/1099 specified." & vbCrLf & _
            "Select a different range or check that you " & vbCrLf & _
            "have not already posted for this period."
     SelectJournal1099Records = False
     Exit Function
  End If

  rsPost1099Set.MoveLast
  rsPost1099Set.MoveFirst

  temp = rsPost1099Set!CLIENT
  temp = rsPost1099Set!sumamt

Record count is 1 (expected in this case), but the Client and SumAmt fields
are null (not expected).

1)  How do I list (perhaps in debug mode) the fields in a recordset?

2) How do I view the values in the fields (in debug mode in this case while
testing)?

I'm hoping that I don't have to create 20 temporary tables to hold what I
would put in recordsets.

Thanks.



Sat, 16 Mar 2002 03:00:00 GMT  
 Recordset problem DAO - HELP!
I'm not entirely sure what you're asking, but in the Debug window, while
your Recordset object is in scope, all its properties including its fields
and their properties (including Value) are available in the Locals pane if
it's "Dimmed" locally.  If it's not local you can add it to the Watch pane
and get at it that way.

--

Quote:

>I have the following code:

>  SQLStr = "select client, sum(amt) as SumAmt from glacct inner join
journl"
>  SQLStr = SQLStr & " on journl.glacct = glacct.glacct"
>  SQLStr = SQLStr & " where ((date <= #"
>  SQLStr = SQLStr & CStr(gblEndDate) & "#"
>  SQLStr = SQLStr & " and (journl.posted is null or journl.posted = '' or
>journl.posted = ' '))"
>  SQLStr = SQLStr & " and (glacct.[special processing] = '6'))"
>  SQLStr = SQLStr & " group by client"

>  Set rsPost1099Set = CurrentDb.OpenRecordset(SQLStr)
>  If rsPost1099Set.RecordCount = 0 Then
>     MsgBox "No records selected for the date/1099 specified." & vbCrLf & _
>            "Select a different range or check that you " & vbCrLf & _
>            "have not already posted for this period."
>     SelectJournal1099Records = False
>     Exit Function
>  End If

>  rsPost1099Set.MoveLast
>  rsPost1099Set.MoveFirst

>  temp = rsPost1099Set!CLIENT
>  temp = rsPost1099Set!sumamt

>Record count is 1 (expected in this case), but the Client and SumAmt fields
>are null (not expected).

>1)  How do I list (perhaps in debug mode) the fields in a recordset?

>2) How do I view the values in the fields (in debug mode in this case while
>testing)?

>I'm hoping that I don't have to create 20 temporary tables to hold what I
>would put in recordsets.

>Thanks.



Sat, 16 Mar 2002 03:00:00 GMT  
 Recordset problem DAO - HELP!
I can see the property, "recordcount" and correctly see the value in it (1).
But I can't see the fields of the recordset at all.  In either the "Watches"
or "Immediate" windows.  I am single stepping through the code at the time
so the scope is valid.  (For instance, the recordcount shows "1").  When I
try to assign the values of the fields to a temporary variable (temp in this
case), I just get a Null as the value of temp.  I tried this with the bang
and with temp = rsPost1099Set.Fields("Client").

I don't know how I can use any of the values in the recordset as I can't
access any of them.  I can't see the fields in the recordset (in case the
name is different from the one listed in the select statement which the
summary was when I modeled it in a query).

Obviously I'm missing something and it probably is something simple and
basic.  I"ve been trying to get this all day.  Thanks for responding.

Quote:

>I'm not entirely sure what you're asking, but in the Debug window, while
>your Recordset object is in scope, all its properties including its fields
>and their properties (including Value) are available in the Locals pane if
>it's "Dimmed" locally.  If it's not local you can add it to the Watch pane
>and get at it that way.
>>I have the following code:

>>  SQLStr = "select client, sum(amt) as SumAmt from glacct inner join
>journl"
>>  SQLStr = SQLStr & " on journl.glacct = glacct.glacct"
>>  SQLStr = SQLStr & " where ((date <= #"
>>  SQLStr = SQLStr & CStr(gblEndDate) & "#"
>>  SQLStr = SQLStr & " and (journl.posted is null or journl.posted = '' or
>>journl.posted = ' '))"
>>  SQLStr = SQLStr & " and (glacct.[special processing] = '6'))"
>>  SQLStr = SQLStr & " group by client"

>>  Set rsPost1099Set = CurrentDb.OpenRecordset(SQLStr)
>>  If rsPost1099Set.RecordCount = 0 Then
>>     MsgBox "No records selected for the date/1099 specified." & vbCrLf &
_
>>            "Select a different range or check that you " & vbCrLf & _
>>            "have not already posted for this period."
>>     SelectJournal1099Records = False
>>     Exit Function
>>  End If

>>  rsPost1099Set.MoveLast
>>  rsPost1099Set.MoveFirst

>>  temp = rsPost1099Set!CLIENT
>>  temp = rsPost1099Set!sumamt

>>Record count is 1 (expected in this case), but the Client and SumAmt
fields
>>are null (not expected).

>>1)  How do I list (perhaps in debug mode) the fields in a recordset?

>>2) How do I view the values in the fields (in debug mode in this case
while
>>testing)?

>>I'm hoping that I don't have to create 20 temporary tables to hold what I
>>would put in recordsets.

>>Thanks.



Sat, 16 Mar 2002 03:00:00 GMT  
 Recordset problem DAO - HELP!
Hello -

If your original query is :

"select client, sum(amt) as SumAmt from ...."

Then your recordset only has two fields, which would be referred to as
rstpost1099set!client AND rstpost1099set!SumAmt

It doesn't matter what you have in the WHERE clause or the rest of your
query - if you SELECT two fields, you get two fields. Let me know if this
isn't answering your question. Good luck!

-Jennifer Alden.


Quote:
> I have the following code:

>   SQLStr = "select client, sum(amt) as SumAmt from glacct inner join
journl"
>   SQLStr = SQLStr & " on journl.glacct = glacct.glacct"
>   SQLStr = SQLStr & " where ((date <= #"
>   SQLStr = SQLStr & CStr(gblEndDate) & "#"
>   SQLStr = SQLStr & " and (journl.posted is null or journl.posted = '' or
> journl.posted = ' '))"
>   SQLStr = SQLStr & " and (glacct.[special processing] = '6'))"
>   SQLStr = SQLStr & " group by client"

>   Set rsPost1099Set = CurrentDb.OpenRecordset(SQLStr)
>   If rsPost1099Set.RecordCount = 0 Then
>      MsgBox "No records selected for the date/1099 specified." & vbCrLf &
_
>             "Select a different range or check that you " & vbCrLf & _
>             "have not already posted for this period."
>      SelectJournal1099Records = False
>      Exit Function
>   End If

>   rsPost1099Set.MoveLast
>   rsPost1099Set.MoveFirst

>   temp = rsPost1099Set!CLIENT
>   temp = rsPost1099Set!sumamt

> Record count is 1 (expected in this case), but the Client and SumAmt
fields
> are null (not expected).

> 1)  How do I list (perhaps in debug mode) the fields in a recordset?

> 2) How do I view the values in the fields (in debug mode in this case
while
> testing)?

> I'm hoping that I don't have to create 20 temporary tables to hold what I
> would put in recordsets.

> Thanks.



Sat, 16 Mar 2002 03:00:00 GMT  
 Recordset problem DAO - HELP!
The problem I have is that I can't access the two fields at all.  The
assignment statement sets TEMP to Null in both cases.  (It's only there so I
can see that I can access the fields in the recordset.  Is there an open or
some other thing that I have to do first?  The problem is probably very
simple and I just don't see it.  I can't find any examples of assigning
values in Access 2000 Developer's Handbook, Access Programming Windows for
Dummies or the other book I'm reading on this.


Sat, 16 Mar 2002 03:00:00 GMT  
 Recordset problem DAO - HELP!
Am I right in concluding that, whichever of the Watch or Immediate windows
your recordset object is displayed in, it doesn't contain a collection named
"Fields", which in turn contains two Items representing the fields in the
recordset?  That's what I would expect you to get, but if you don't, there
must be something wrong with your query.  Try creating a new (temporary)
query in the query builder, going into SQL view, and pasting your SQL into
it.  Switch back to Design view and see if it looks good, then switch to
Datasheet view and see what you get.

--

Dirk Goldgar
(to reply via email, remove NOSPAM from address)

Quote:

>I can see the property, "recordcount" and correctly see the value in it
(1).
>But I can't see the fields of the recordset at all.  In either the
"Watches"
>or "Immediate" windows.  I am single stepping through the code at the time
>so the scope is valid.  (For instance, the recordcount shows "1").  When I
>try to assign the values of the fields to a temporary variable (temp in
this
>case), I just get a Null as the value of temp.  I tried this with the bang
>and with temp = rsPost1099Set.Fields("Client").

>I don't know how I can use any of the values in the recordset as I can't
>access any of them.  I can't see the fields in the recordset (in case the
>name is different from the one listed in the select statement which the
>summary was when I modeled it in a query).

>Obviously I'm missing something and it probably is something simple and
>basic.  I"ve been trying to get this all day.  Thanks for responding.


>>I'm not entirely sure what you're asking, but in the Debug window, while
>>your Recordset object is in scope, all its properties including its fields
>>and their properties (including Value) are available in the Locals pane if
>>it's "Dimmed" locally.  If it's not local you can add it to the Watch pane
>>and get at it that way.

>>>I have the following code:

>>>  SQLStr = "select client, sum(amt) as SumAmt from glacct inner join
>>journl"
>>>  SQLStr = SQLStr & " on journl.glacct = glacct.glacct"
>>>  SQLStr = SQLStr & " where ((date <= #"
>>>  SQLStr = SQLStr & CStr(gblEndDate) & "#"
>>>  SQLStr = SQLStr & " and (journl.posted is null or journl.posted = '' or
>>>journl.posted = ' '))"
>>>  SQLStr = SQLStr & " and (glacct.[special processing] = '6'))"
>>>  SQLStr = SQLStr & " group by client"

>>>  Set rsPost1099Set = CurrentDb.OpenRecordset(SQLStr)
>>>  If rsPost1099Set.RecordCount = 0 Then
>>>     MsgBox "No records selected for the date/1099 specified." & vbCrLf &
>_
>>>            "Select a different range or check that you " & vbCrLf & _
>>>            "have not already posted for this period."
>>>     SelectJournal1099Records = False
>>>     Exit Function
>>>  End If

>>>  rsPost1099Set.MoveLast
>>>  rsPost1099Set.MoveFirst

>>>  temp = rsPost1099Set!CLIENT
>>>  temp = rsPost1099Set!sumamt

>>>Record count is 1 (expected in this case), but the Client and SumAmt
>fields
>>>are null (not expected).

>>>1)  How do I list (perhaps in debug mode) the fields in a recordset?

>>>2) How do I view the values in the fields (in debug mode in this case
>while
>>>testing)?

>>>I'm hoping that I don't have to create 20 temporary tables to hold what I
>>>would put in recordsets.

>>>Thanks.



Sun, 17 Mar 2002 03:00:00 GMT  
 Recordset problem DAO - HELP!
Thank you.  You got me pointed in the right direction.  I was assuming I was
doing something wrong (not a bad assumption) when it turned out to be the
data that was goofy.


Sun, 17 Mar 2002 03:00:00 GMT  
 Recordset problem DAO - HELP!
Looks like that by now, you've got this under control. Just wanted to
point out that for complex SQL, your best bet is to get it working in
the query designer, and then move it over to VBA, if that's where you
want it to end up.

Also, there are several examples of modifying and working with data,
both in ADO and DAO, in the A2KDH Volume I. See Chapter 6 for
information on modifying data using ADO, and Appendix C for information
on using DAO. There's examples there, I promise! <g> -- Ken



Quote:
> The problem I have is that I can't access the two fields at all.  The
> assignment statement sets TEMP to Null in both cases.  (It's only
there so I
> can see that I can access the fields in the recordset.  Is there an
open or
> some other thing that I have to do first?  The problem is probably
very
> simple and I just don't see it.  I can't find any examples of
assigning
> values in Access 2000 Developer's Handbook, Access Programming
Windows for
> Dummies or the other book I'm reading on this.

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 18 Mar 2002 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Problems assigning a DAO-recordset to a data control - Need help

2. MS Access 2000 DAO Recordset Problem

3. Problem With DAO Recordset Table Update

4. DAO recordset - problem deleting records

5. Problem with recordSet.update using DAO and ODBC Direct

6. VB 4.0, Access 95, DAO: Recordset.Edit / Update problem

7. VB 4.0, Access 7, DAO: Recordset.Edit / Update problem

8. Transactions - DAO and Recordset Problems

9. dao - go to recordset problem...

10. Assigning a Dao.Recordset to a Datacontrol With VB6 SP4 and Dao.360

11. problem with DAO 3.60, ODBCDirect, trying to open a recordset (dynaset)

12. DAO SQL recordset problem

 

 
Powered by phpBB® Forum Software