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 PostgreSQLADOConnection() Then

tZone = "set timezone to 'GMT'; set datestyle to 'ISO'; " & _
"select version(), case when pg_encoding_to_char(1) =
'SQL_ASCII' " & _
"then 'UNKNOWN' else getdatabaseencoding() end"
cPostgreSQL.Execute 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.Execute(sqlstr, , adLockReadOnly)

Set Me.Recordset = objRstADO

Do While Not objRstADO.EOF

objRstADO.MoveNext
Me.txtID = objRstADO("id")
Me.txtName = objRstADO("name")
Me.txtCount = objRstADO("count")
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]

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" <tm*******@rogers.com> wrote in message
news:MY********************@news04.bloor.is.net.ca ble.rogers.com...

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 PostgreSQLADOConnection() Then

tZone = "set timezone to 'GMT'; set datestyle to 'ISO'; " & _
"select version(), case when pg_encoding_to_char(1) =
'SQL_ASCII' " & _
"then 'UNKNOWN' else getdatabaseencoding() end"
cPostgreSQL.Execute 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.Execute(sqlstr, , adLockReadOnly)

Set Me.Recordset = objRstADO

Do While Not objRstADO.EOF

objRstADO.MoveNext
Me.txtID = objRstADO("id")
Me.txtName = objRstADO("name")
Me.txtCount = objRstADO("count")
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