ADO problem?? urgent

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

Hi,
I have an unbound form and am using a combo box with the
after update event to populate the form. Also running
similar code for add new, update, delete. All works
perfectly as I want it in Access 2000, nice and fast, no
problems at all. The problem is I have to build it in
Access 2000 and run it in Access 97. When I convert to 97
The problems start. The error I'm getting points
to "currentproject.connection" something about an
undecleared variable. (there could be other problems but
I haven't got past this one yet so I don't know)
Any help greatly appreciated.

Private Sub cboSupSch_AfterUpdate()
Dim cnnlocal As New ADODB.Connection
Dim rstcurr As New ADODB.Recordset
Dim fldcurr As ADODB.Field
Set cnnlocal = CurrentProject.Connection
rstcurr.Open "select * from tblcontact where contactids
= " & Forms!frmcontact!cboSupSch & ";", cnnlocal, _
adOpenKeyset, adLockPessimistic
With rstcurr
Me.txtContactIDS = !ContactIDS
Me.cboSalutation = !SalutationID
Me.txtFirstNm = !FirstNm
Me.txtLastNm = !LastNm
Me.txtAddr1 = !Addr1
Me.txtAddr2 = !Addr2
Me.cboSurb = !SurbID
Me.txtCity = !City
Me.txtPhone1 = !Phone1
Me.txtPhone1Extn = !Phone1Extn
Me.txtPhone2 = !Phone2
Me.txtPhone2Extn = !Phone2Extn
Me.txtFax = !Fax
Me.txtMobile = !Mobile
Me.txtEmail = !Email
Me.txtNotes = !Notes
End With
'clean up
rstcurr.Close
Set rstcurr = Nothing
End Sub
 
You're making this way too hard for yourself.
Include all the fields you need to display from the table in the query
behind the combobox.
Be sure to set your ColumnCount and ColumnWidths properties
appropriately; you can set ColumnWidth to 0 for any field you don't want to
display in the dropdown list of the combobox.

Now you have two options:
If you need to COPY these values to the respective fields, you can use
code like this:
Me.txtContactIDS = cboSupSch.Column(1)
for each value you need to copy. Remember that the Column() collection
is 0-based.

On the other hand, if you're not going to allow your users to edit those
values, you can set the ControlSource of txtContactIDS to
= cboSubSch.Column(1)
The textbox will populate automatically whenever you select a value in the
combobox, without any further code.
But your users will not be able to edit it.

HTH
- Turtle
 
I know there are much easier ways but I'm not convinced
they are better. I'll try DAO and see how it goes.
 
Back
Top