Re: Passing An SQL Query to recordset?


"Dave@Yacc" <DaveYacc@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BC3D6EFC-126D-40AE-AF19-6B3144FD05E9@xxxxxxxxxxxxxxxx
> righty having made progress i'm stuck again! I dont know why but i'm doing
> seems to be a rare example, Im finding little answers in any literature I
> have or any examples dotted about on the net!
>
> basically i have this...
>
> Set lst = Me!lstContacts
>
> While Not m_rs.EOF
> With lst
> .RowSourceType = "Value List"
> .RowSource = m_rs("ContactTitle") & " " & m_rs("ContactForename") & " "
> & m_rs("ContactSurname")
> m_rs.MoveNext
> End With
> Wend

Why don't you use a DataList? Add the Micrisoft DataList to your project,
then add one to your form in place of the listbox

You will need to concatenate the name fragments into one calculated field in
the SQL statement to make this trick fly. Not sure what db engine you're
using, but this would work if it was SQL Server (your mileage may vary.)

SELECT ContactTitle + ' ' + ContactForename + ' ' + ContactSurname AS
FullName FROM yourtable WHERE [your criteria, etc...]

[then open your recordset, and then...]

Set lstContacts.DataSource = m_rs
lstContacts.ListField = "FullName"
lstContacts.ReFill

The result should be a list filled from a recordset.


-Mark


> Ofcourse this only sends the last record in the reportset to the list box,
> i
> need to make it iterate through to return and print to the list box all
> the
> records not just one! I'm stumped, tried various methods but all kicked
> off,
> and im having to use rowsource because I dont have listbox.additem,
> listbox.clear etc annoying!
>
> again any advice appreciated!
> "Dave@Yacc" wrote:
>
>> I just realised i was looking at it from totally the wrong angle! The
>> code i
>> mentioned looking at basically does exactly what you described, which
>> will
>> infact do the job, i think fatigue is getting the better off me, thanks
>> for
>> the reply think im sorted!
>>
>> "Stephen Howe" wrote:
>>
>> > > i have a drop down list which selects and business and retrieves the
>> > > business ID,
>> > > i then have a query which recieves the id and retrieves all records
>> > > from a
>> > > contacts table that have the same ID, (basically all the people that
>> > > we
>> > can
>> > > get in touch with from a particular business)
>> >
>> > Good. Wish everyone here would post an outline of the problem they are
>> > trying to solve.
>> >
>> > > I need to know if it's therefore possible to send the results of this
>> > query
>> > > to a recordset in ADO?
>> >
>> > No. Basically you can setup a Command object which returns a recordset.
>> > The Command object will have 1 parameter which is the ID and
>> > CommandText
>> > will be set to the SQL query. Everytime you wish to get the records for
>> > a
>> > new ID, you feed the Command object's parameter a new ID, call Execute
>> > and
>> > the Recordset returned will be for that ID.
>> >
>> > The Recordset is likely to be Server-sided, ForwardOnly and ReadOnly.
>> > If you want a Recordset with different properties, you can do it, but
>> > the
>> > mechanics are slightly different.
>> > You can still reuse the Recordset object mind you.
>> >
>> > Is this what you want?
>> >
>> > Stephen Howe
>> >
>> >
>> >


.

Relevant Pages

  • Re: insert Q
    ... many sql statements on the page). ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ...
    (microsoft.public.inetserver.asp.db)
  • Re: insert Q
    ... >> conn.execute SQL ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ...
    (microsoft.public.inetserver.asp.db)
  • RE: Trouble Retrieving Recordset from Stored Procedure
    ... connect to your SQL server. ... Dim rs As New ADODB.Recordset ... you can retrieve records without the command object. ... I have since been told how to retrieve the recordset by fixing my current ...
    (microsoft.public.access.modulesdaovba)
  • RE: Trouble Retrieving Recordset from Stored Procedure
    ... connect to your SQL server. ... Dim rs As New ADODB.Recordset ... you can retrieve records without the command object. ... I have since been told how to retrieve the recordset by fixing my current ...
    (microsoft.public.access.modulesdaovba)
  • Re: GetRows Method
    ... I told you that i dont want your help. ... I simply wanted intellgent answers from intellgent people. ... >> It moves the data from the recordset to the aVarray, ... > process data. ...
    (microsoft.public.vb.database.ado)
Loading