Form Recordset

[jurgen]

Hi everyone,

I'm having problems with displaying the records from a recordset that I've binded to a form.

I set the recordset to the form using:
Me.RecordSet= RS
( RS is an ADODB.Recordset )
( Event is a button_click )

This works perfect, I've got the recordset and attached.
The form then displays the exact number of rowselectors that are in the recordset but no fields or content are shown in my datasheet.

How can I display the content of the fields and the available fields in the recordset.

Can someone help me please ?

Jurgen

 

[ZmrAbdulla]

Something like this to be used to bind the fields

Me.FieldName = rs("FieldName")

Example

Me.FirstName = rs("FirstName")
Me.MiddleName = rs("MiddleName")
Me.LastName = rs("LastName")

HTH

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[jurgen]

Hi,

Tried this one but it doesn't work because of the fact that i've always different fieldnames, because i want to make the form as a datasheet displaying the content, so in content 1 there is a firstname but the content 2 hasn't.

Any other ideas

 

[ZmrAbdulla]

If I understand correctly you want to populate a datasheet with diffrent recordsets (and one at a time).. right?


Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[jurgen]

Yes that's it.

 

[ZmrAbdulla]

I've never tried this... I don't know if it is possible.. It must be...
You are using different queries then you can assign the value with each query
like
choice1: query1 and fileds depends to query1
choice2: query2 and fileds depends to query2
Query1

Me.Field1 = rs("FirstName")
Me.Field2 = rs("MiddleName")
Me.Field3 = rs("LastName")

Query2

Me.Field1 = rs1("FirstName")
Me.Field2 = rs1("Company")
Me.Field3 = rs1("Telephone")

Note: Form and controls (textbox,combobox etc) must be unbound

BTW
IF you use ListView(Activex) then it is very easy.You have more control on that...
HTH


Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[jurgen]

That's what the result should be, i've tried the listview but it's not the way to go unfortunally.

 

[ZmrAbdulla]

Did you try like what I said query1 and query2 ?

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[jurgen]

I'm going to try it but for the record, the query is in fact a stored procedure on oracle that i call and receive a cursor back that i put into a recordset. And that recordset is the result i want to show on a form in a datasheet

Here's the code i use for the stored procedure tot put it into a recordset, this works now dsiplaying the data ...

Dim Conn As String
Dim QSQL As String
Dim cn As ADODB.Connection
Dim CPw2 As ADODB.Command
Dim rst As ADODB.Recordset
    
Conn = "DSN=" & AppGetDSN & ";User ID=" &    SecCurrentUserGetUserToken.mbLogin & ";Password=" & SecCurrentUserGetUserToken.mbPassword & " ;"
    
    Set cn = New ADODB.Connection
       With cn
           .ConnectionString = Conn
           .CursorLocation = adUseClient
           .Open
       End With

    QSQL = "TRAIN.SP_ALLOCATIONS.TradeListing"

    Set CPw2 = New ADODB.Command
    With CPw2
        Set .ActiveConnection = cn
        .CommandText = QSQL
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("param1",   adDBDate, adParamInput, , "01/11/2005")
        Set rst = .Execute
    End With
    
    'rst.Close
        
    cn.Close
    Set cn = Nothing

 

[ZmrAbdulla]

I am totally illiterate on ORACLE db.
Is this code to display the records? or appending to the table? I can see somethig like
[tt]
.Parameters.Append .CreateParameter("param1", adDBDate, adParamInput, , "01/11/2005")
Set rst = .Execute
[/tt]
I am confused now..

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[jurgen]

This code works fine. The item you recall is to add parameters to the call of the storedprocedure on oracle. SO has nothing to do with the problem.

THe result of this code is a recordset: rst .This recordset contains all data that has to be displayed on the form ...

Jurgen

 

[ZmrAbdulla]

I am sorry...My knowledge on this ends here..
and here is a sample to populate a form with SQL and ADODB may help you...
[tt]
Private Sub cboLastName1_AfterUpdate()
On Error Resume Next

Dim rs As ADODB.Recordset
Dim strSQL As String

cboLastName1.SetFocus
If cboLastName1.Value > 0 Then
strSQL = "SELECT * FROM tblTable WHERE ID = " & cboLastName1.Value

Set rs = CreateObject("ADODB.Recordset")
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open strSQL, CurrentProject.Connection

If rs.State = 1 Then
If Not rs.BOF Then
Me.Prefix = rs("Prefix")
Me.FirstName = rs("FirstName")
Me.MiddleName = rs("MiddleName")
Me.LastName = rs("LastName")
End If
rs.Close
End If
Set rs = Nothing
End If

End Sub
[/tt]

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[PHV]

In the Original Post (OP), what about replacing this:
Me.RecordSet= RS
By this ?
Set Me.RecordSet = RS

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jurgen]

That's what I've in my code and works fine, but i m not seeing any records on my form
Set me.recordset = rs works fine.

The thing i'm trying to find out how can i display this recordset on a form ...

Hope you can bare with me ...

 

[PHV]

Take a look here:

http://support.microsoft.com/default.aspx?SCID=kb;en;281998

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jurgen]

Yep, tried it but same result, i'm using Access 2000, i would like tot use 2002/2003 then it worked, but migration is no option here ...