ADO assist

[Peter Lux]

I'm trying to create a sales ticket (on a vb form) and fetching results from
an Access database. When I try to get the results from the recordset, it
doesn't look right. I've re-imported the data with "trim(<fieldname>)" to
make sure that I'm not picking up extra characters. First, here's the data
and how it should look
<the ticket_desc below looks fine>
121
CREEK TWP.
RR4 BOX 334
CLINTON, IL 61727

Here's the code:

Dim cAcc As New ADODB.Connection
Dim rsAcc As New ADODB.Recordset

Private Sub pbPrint_Click()
Dim Line1, Line2, Line3 As String
Dim v1, v2, v3 As String

Set rsAcc = cAcc.Execute(" Select ticket_desc from co_locn")
'This is fine.. it looks good
tbCoLocnAddr.Text = rsAcc(0)
Set rsAcc = cAcc.Execute(" Select a.ticket_nbr, b.name, b.address, b.city,
b.state, b.zip, b.cust_id from Sale a, Customer b where a.ticket_nbr=121 and
" & _
"a.cust_id=b.cust_id and a.address_seq_num = b.address_seq_num")
tbTicket.Text = rsAcc(0)
Line1 = rsAcc.Fields(1)

It didn't matter whether I had rsAcc(1) or rsAcc.Fields(1), line 1 (in
debug) ended up:
CREEK TWP. F RR4 BOX 334
It's the name AND the address..can you not reuse the same recordset or do
you have to 'reset' it before using it again?

[K Dales]

Use rsAcc.Close to close the first recordset before running the 2nd query.
--
- K Dales


"Peter Lux" wrote:

> I'm trying to create a sales ticket (on a vb form) and fetching results from
> an Access database. When I try to get the results from the recordset, it
> doesn't look right. I've re-imported the data with "trim(<fieldname>)" to
> make sure that I'm not picking up extra characters. First, here's the data
> and how it should look
> <the ticket_desc below looks fine>
> 121
> CREEK TWP.
> RR4 BOX 334
> CLINTON, IL 61727
>
> Here's the code:
>
> Dim cAcc As New ADODB.Connection
> Dim rsAcc As New ADODB.Recordset
>
> Private Sub pbPrint_Click()
> Dim Line1, Line2, Line3 As String
> Dim v1, v2, v3 As String
>
> Set rsAcc = cAcc.Execute(" Select ticket_desc from co_locn")
> 'This is fine.. it looks good
> tbCoLocnAddr.Text = rsAcc(0)
> Set rsAcc = cAcc.Execute(" Select a.ticket_nbr, b.name, b.address, b.city,
> b.state, b.zip, b.cust_id from Sale a, Customer b where a.ticket_nbr=121 and
> " & _
> "a.cust_id=b.cust_id and a.address_seq_num = b.address_seq_num")
> tbTicket.Text = rsAcc(0)
> Line1 = rsAcc.Fields(1)
>
> It didn't matter whether I had rsAcc(1) or rsAcc.Fields(1), line 1 (in
> debug) ended up:
> CREEK TWP. F RR4 BOX 334
> It's the name AND the address..can you not reuse the same recordset or do
> you have to 'reset' it before using it again?
>
>
>
>
>
>

[Peter Lux]

Tried that same result.
I tried a different ticket and I still get that weird 'F' showing up. If I
look at rcAcc(2) it comes back (realising that this is a different ticket)
DO NOT MAIL < URBANA
and has this '<' character in there. I went back thru my immediates and
found what it looked like on this record:
RR4 BOX 334 < CLINTON
Same '<' what's up with that???


"K Dales" <[email protected]> wrote in message
news:[email protected]...
> Use rsAcc.Close to close the first recordset before running the 2nd query.
> --
> - K Dales
>
>
> "Peter Lux" wrote:
>
>> I'm trying to create a sales ticket (on a vb form) and fetching results
>> from
>> an Access database. When I try to get the results from the recordset, it
>> doesn't look right. I've re-imported the data with "trim(<fieldname>)" to
>> make sure that I'm not picking up extra characters. First, here's the
>> data
>> and how it should look
>> <the ticket_desc below looks fine>
>> 121
>> CREEK TWP.
>> RR4 BOX 334
>> CLINTON, IL 61727
>>
>> Here's the code:
>>
>> Dim cAcc As New ADODB.Connection
>> Dim rsAcc As New ADODB.Recordset
>>
>> Private Sub pbPrint_Click()
>> Dim Line1, Line2, Line3 As String
>> Dim v1, v2, v3 As String
>>
>> Set rsAcc = cAcc.Execute(" Select ticket_desc from co_locn")
>> 'This is fine.. it looks good
>> tbCoLocnAddr.Text = rsAcc(0)
>> Set rsAcc = cAcc.Execute(" Select a.ticket_nbr, b.name, b.address,
>> b.city,
>> b.state, b.zip, b.cust_id from Sale a, Customer b where a.ticket_nbr=121
>> and
>> " & _
>> "a.cust_id=b.cust_id and a.address_seq_num = b.address_seq_num")
>> tbTicket.Text = rsAcc(0)
>> Line1 = rsAcc.Fields(1)
>>
>> It didn't matter whether I had rsAcc(1) or rsAcc.Fields(1), line 1 (in
>> debug) ended up:
>> CREEK TWP. F RR4 BOX 334
>> It's the name AND the address..can you not reuse the same recordset or do
>> you have to 'reset' it before using it again?
>>
>>
>>
>>
>>
>>

[Jim Thomlinson]

Not having your database to play with or such it is hard to say but you may
want to try setting the recordest to nothing before using it again. Closing
it does not delete the recordset...

set rsAcc = nothing
--
HTH...

Jim Thomlinson


"Peter Lux" wrote:

> Tried that same result.
> I tried a different ticket and I still get that weird 'F' showing up. If I
> look at rcAcc(2) it comes back (realising that this is a different ticket)
> DO NOT MAIL < URBANA
> and has this '<' character in there. I went back thru my immediates and
> found what it looked like on this record:
> RR4 BOX 334 < CLINTON
> Same '<' what's up with that???
>
>
> "K Dales" <[email protected]> wrote in message
> news:[email protected]...
> > Use rsAcc.Close to close the first recordset before running the 2nd query.
> > --
> > - K Dales
> >
> >
> > "Peter Lux" wrote:
> >
> >> I'm trying to create a sales ticket (on a vb form) and fetching results
> >> from
> >> an Access database. When I try to get the results from the recordset, it
> >> doesn't look right. I've re-imported the data with "trim(<fieldname>)" to
> >> make sure that I'm not picking up extra characters. First, here's the
> >> data
> >> and how it should look
> >> <the ticket_desc below looks fine>
> >> 121
> >> CREEK TWP.
> >> RR4 BOX 334
> >> CLINTON, IL 61727
> >>
> >> Here's the code:
> >>
> >> Dim cAcc As New ADODB.Connection
> >> Dim rsAcc As New ADODB.Recordset
> >>
> >> Private Sub pbPrint_Click()
> >> Dim Line1, Line2, Line3 As String
> >> Dim v1, v2, v3 As String
> >>
> >> Set rsAcc = cAcc.Execute(" Select ticket_desc from co_locn")
> >> 'This is fine.. it looks good
> >> tbCoLocnAddr.Text = rsAcc(0)
> >> Set rsAcc = cAcc.Execute(" Select a.ticket_nbr, b.name, b.address,
> >> b.city,
> >> b.state, b.zip, b.cust_id from Sale a, Customer b where a.ticket_nbr=121
> >> and
> >> " & _
> >> "a.cust_id=b.cust_id and a.address_seq_num = b.address_seq_num")
> >> tbTicket.Text = rsAcc(0)
> >> Line1 = rsAcc.Fields(1)
> >>
> >> It didn't matter whether I had rsAcc(1) or rsAcc.Fields(1), line 1 (in
> >> debug) ended up:
> >> CREEK TWP. F RR4 BOX 334
> >> It's the name AND the address..can you not reuse the same recordset or do
> >> you have to 'reset' it before using it again?
> >>
> >>
> >>
> >>
> >>
> >>
>
>
>