Combo Box Code

[Old Music Lover]

This is how I used to do a combox in prior releases of MSAccess. I am on
Access 2000 but have done no VBA coding on it. This is the way it continues
to be described in the docmentation but I can't get it to work.

I have a combo box with the following "after update" code. I get a Type
Mismatch message on the

Set MeClone = Me.RecordsetClone statement.

Any clues as to what I have done wrong? It also does not recognize the
NoMatch on the MeClone.

Private Sub NAME_SELECT_Change()
Dim SearchValue As String
Dim MeClone As Recordset
Set MeClone = Me.RecordsetClone
Let SearchValue = Me![NAME_SELECT].Value
MeClone.Find "[EMPLID] = " & SearchValue
If Not MeClone.NoMatch Then
Me.Bookmark = MeClone.Bookmark
End If
MeClone.Close
End Sub

 

[Allen Browne]

Access 2000 includes a reference to the ADO library instead of the DAO
library, and both have a Recordset object.

To fix the problem:
1. From the code window, choose References on the Tools menu.

2. Check the box beside:
Microsoft DAO 3.6
Uncheck the box beside Microsoft ActiveX ... if you don't need that one.

3. Change the 2nd line of your procedure to:
Dim MeClone As DAO.Recordset

For more information on references in the different versions of Access, see:
Solving Problems with Library References
at:
http://allenbrowne.com/ser-38.html

 

[Old Music Lover]

Thanx a million. I will try this tomorrow at work--

If I were to code this in MSAccess 2000 using ADO instead of DAO, how would
the code look. The online documentation I have still shows the DAO way. I
am open to learning the new.

Access 2000 includes a reference to the ADO library instead of the DAO
library, and both have a Recordset object.

To fix the problem:
1. From the code window, choose References on the Tools menu.

2. Check the box beside:
Microsoft DAO 3.6
Uncheck the box beside Microsoft ActiveX ... if you don't need that one.

3. Change the 2nd line of your procedure to:
Dim MeClone As DAO.Recordset

For more information on references in the different versions of Access,
see:
Solving Problems with Library References
at:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is how I used to do a combox in prior releases of MSAccess. I am on
Access 2000 but have done no VBA coding on it. This is the way it
continues to be described in the docmentation but I can't get it to work.

I have a combo box with the following "after update" code. I get a Type
Mismatch message on the

Set MeClone = Me.RecordsetClone statement.

Any clues as to what I have done wrong? It also does not recognize the
NoMatch on the MeClone.

Private Sub NAME_SELECT_Change()
Dim SearchValue As String
Dim MeClone As Recordset
Set MeClone = Me.RecordsetClone
Let SearchValue = Me![NAME_SELECT].Value
MeClone.Find "[EMPLID] = " & SearchValue
If Not MeClone.NoMatch Then
Me.Bookmark = MeClone.Bookmark
End If
MeClone.Close
End Sub

 

[Allen Browne]

DAO is the native Access library.

It is what you get when you bind a form to a table in your mdb.
It is what Access uses when you design a table or run a query from the
database window.

DAO is the best library to use if your data is stored in Access tables. It
has been with us now for 12 years. There is no point switching to ADO which
is already dead (replaced by ADO.NET which is completely different from VBA
code.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanx a million. I will try this tomorrow at work--

If I were to code this in MSAccess 2000 using ADO instead of DAO, how
would the code look. The online documentation I have still shows the DAO
way. I am open to learning the new.

Access 2000 includes a reference to the ADO library instead of the DAO
library, and both have a Recordset object.

To fix the problem:
1. From the code window, choose References on the Tools menu.

2. Check the box beside:
Microsoft DAO 3.6
Uncheck the box beside Microsoft ActiveX ... if you don't need that one.

3. Change the 2nd line of your procedure to:
Dim MeClone As DAO.Recordset

For more information on references in the different versions of Access,
see:
Solving Problems with Library References
at:
http://allenbrowne.com/ser-38.html

This is how I used to do a combox in prior releases of MSAccess. I am
on Access 2000 but have done no VBA coding on it. This is the way it
continues to be described in the docmentation but I can't get it to
work.

I have a combo box with the following "after update" code. I get a Type
Mismatch message on the

Set MeClone = Me.RecordsetClone statement.

Any clues as to what I have done wrong? It also does not recognize the
NoMatch on the MeClone.

Private Sub NAME_SELECT_Change()
Dim SearchValue As String
Dim MeClone As Recordset
Set MeClone = Me.RecordsetClone
Let SearchValue = Me![NAME_SELECT].Value
MeClone.Find "[EMPLID] = " & SearchValue
If Not MeClone.NoMatch Then
Me.Bookmark = MeClone.Bookmark
End If
MeClone.Close
End Sub

 

[peregenem]

is no point switching to ADO which
is already dead (replaced by ADO.NET which is completely different from VBA
code.)

Any criteria for ADO being 'dead' would inevitably lead to the
conclusion that DAO is 'longer dead'. It may even be that ADO is very
much alive. ADO has had more recent development than DAO: DAO was not
developed beyond Jet 3.n whereas ADO has been developed for Jet 4.

From MSDN, I get the general impression that DAO was left behind when

ADO came out. Much of the remaining DAO documentation (the bulk having
been removed several years ago) has been stamped with a warning about
the contents' "archived" status, whereas the ADO documentation appears
to be current (though I suspect MS consider it "depreciated").

ADO.NET has replaced ADO/DAO only insofar as .NET has replaced COM,
VB.NET (or possibly C#.NET) has replaced VBA and MSDE has replaced Jet.

I believe the choice between ADO and DAO remains merely 'lifestyle' and
both remain valid with their relative advantages. I would suggest DAO
appeals more to those whose experience pre-date ADO and that ADO would
appeal to newbies if certain vocal members of the 'old school' adopted
a more inclusive/ecumenical attitude. I certainly agree that there may
be few benefits to be gained by switching wholesale between the
technologies other than to exploit particular relative advantages.

 

[David C. Holley]

If ADO & DAO are both still around, why would I ever want to go with the
one that isn't native to JET? (That's a rhetorical question BTW.)

 

[peregenem]

If ADO & DAO are both still around, why would I ever want to go with the
one that isn't native to JET? (That's a rhetorical question BTW.)

If there is more than one SQL product around, why would I want to learn
the data access technology that is native to the depreciated component
Jet?

 

[Old Music Lover]

I didn't mean to start a theoretical discussion. I have a practical problem
and I must meet my users' need. I believe puchasing a big "honking"
MSAccess 2000 book is in my immediate future. (And I thought my days coding
in MSAccess died with the last release!)

The table is an attached Excel Spreadsheet. The code as explained in the
online documentation will not make the combo box do its intended function.
When I change to DAO specific, it trashes the spreadsheet and makes it
unusable.

I have to use an attached spreadsheet. Any suggestions?

 

[peregenem]

The table is an attached Excel Spreadsheet. The code as explained in the
online documentation will not make the combo box do its intended function.
When I change to DAO specific, it trashes the spreadsheet and makes it
unusable.

Are you amending the spreadsheet via an attached table? I've read
several times in these groups that such updating can corrupt an Excel
workbook. It may be a safer to query the workbook direct eg

UPDATE [Excel 8.0;HDR=YES;IMEX=1;DATABASE=C:\Book1.xls];.[Sheet1$]
SET data_col = 'Some value'
WHERE key_col = 42;

I didn't mean to start a theoretical discussion.

Me either I was trying to defend your 'lifestyle choice' to learn
ADO. Apologies for going OT.

I believe puchasing a big "honking"
MSAccess 2000 book is in my immediate future.

Be prepared for a DAO bias, which is entirely understandable and
defendable. A book such as "MSAccess 2000 for the ADO Programmer" may
be too much of a niche

 

[Old Music Lover]

Thanx for the info. I am updating the data in the spreadsheet using a form.
The combo box was intended to let them select the record.

The table is an attached Excel Spreadsheet. The code as explained in the
online documentation will not make the combo box do its intended
function.
When I change to DAO specific, it trashes the spreadsheet and makes it
unusable.

Are you amending the spreadsheet via an attached table? I've read
several times in these groups that such updating can corrupt an Excel
workbook. It may be a safer to query the workbook direct eg

UPDATE [Excel 8.0;HDR=YES;IMEX=1;DATABASE=C:\Book1.xls];.[Sheet1$]
SET data_col = 'Some value'
WHERE key_col = 42;

I didn't mean to start a theoretical discussion.

Me either I was trying to defend your 'lifestyle choice' to learn
ADO. Apologies for going OT.

I believe puchasing a big "honking"
MSAccess 2000 book is in my immediate future.

Be prepared for a DAO bias, which is entirely understandable and
defendable. A book such as "MSAccess 2000 for the ADO Programmer" may
be too much of a niche