Populate a continuous form using ADO

[tmuhammad]

Hi there, I am trying to populate a continuous form using ADO, following is
the function which needs fixing, can someone kindly help me with this.
Thanks much

Private Sub Form_Load()

On Error GoTo err_Form_Load


Dim objRstADO As ADODB.Recordset
Dim sqlstr As String
Dim tZone As String

If PostgreSQLADOCo nnection() Then

tZone = "set timezone to 'GMT'; set datestyle to 'ISO'; " & _
"select version(), case when pg_encoding_to_ char(1) =
'SQL_ASCII' " & _
"then 'UNKNOWN' else getdatabaseenco ding() end"
cPostgreSQL.Exe cute tZone

sqlstr = "Select a.id, a.name, count(*) as count " & _
"FROM tblcustomer a, tblorder b " & _
"WHERE a.order_id=b.id " & _
"GROUP BY 1,2 " & _
"ORDER BY 3 DESC"


Set objRstADO = cPostgreSQL.Exe cute(sqlstr, , adLockReadOnly)

Set Me.Recordset = objRstADO

Do While Not objRstADO.EOF

objRstADO.MoveN ext
Me.txtID = objRstADO("id")
Me.txtName = objRstADO("name ")
Me.txtCount = objRstADO("coun t")
Loop



objRstADO.Close
Set objRstADO = Nothing

Else
MsgBox "Connection error: Could not execute"
End If
Exit Sub

err_Form_Load:
MsgBox Err.Number & " - " & Err.Description , vbOKOnly
End Sub

[MacDermott]

Re: Populate a continuous form using ADO

For one thing, you shouldn't need the loop at the end populating your
controls.
Setting the Recordset property of your form should be sufficient.

Also, you haven't specified what behavior you're getting here, so I don't
know exactly what you want but aren't getting.
But I would be very surprised if this SQL returned a recordset which was
updatable.

HTH
- Turtle

"tmuhammad" <tmuhammad@roge rs.com> wrote in message
news:MY92c.1581 40$Qg7.19864@ne ws04.bloor.is.n et.cable.rogers .com...[color=blue]
> Hi there, I am trying to populate a continuous form using ADO, following[/color]
is[color=blue]
> the function which needs fixing, can someone kindly help me with this.
> Thanks much
>
> Private Sub Form_Load()
>
> On Error GoTo err_Form_Load
>
>
> Dim objRstADO As ADODB.Recordset
> Dim sqlstr As String
> Dim tZone As String
>
> If PostgreSQLADOCo nnection() Then
>
> tZone = "set timezone to 'GMT'; set datestyle to 'ISO'; " & _
> "select version(), case when pg_encoding_to_ char(1) =
> 'SQL_ASCII' " & _
> "then 'UNKNOWN' else getdatabaseenco ding() end"
> cPostgreSQL.Exe cute tZone
>
> sqlstr = "Select a.id, a.name, count(*) as count " & _
> "FROM tblcustomer a, tblorder b " & _
> "WHERE a.order_id=b.id " & _
> "GROUP BY 1,2 " & _
> "ORDER BY 3 DESC"
>
>
> Set objRstADO = cPostgreSQL.Exe cute(sqlstr, , adLockReadOnly)
>
> Set Me.Recordset = objRstADO
>
> Do While Not objRstADO.EOF
>
> objRstADO.MoveN ext
> Me.txtID = objRstADO("id")
> Me.txtName = objRstADO("name ")
> Me.txtCount = objRstADO("coun t")
> Loop
>
>
>
> objRstADO.Close
> Set objRstADO = Nothing
>
> Else
> MsgBox "Connection error: Could not execute"
> End If
> Exit Sub
>
> err_Form_Load:
> MsgBox Err.Number & " - " & Err.Description , vbOKOnly
> End Sub
>
>
>[/color]