ADODB Recordset Nightmare

[djt2009]

Hello,
Can anyone help me out with my problem.
The following code, that i have aquired, finds a record in a table based on the information put into a text box, then outputs the" last name" value from that row of the table.

Private Sub cmdFindRecord_Click()
Dim adoRst As ADODB.Recordset
Dim txtEmpID As TextBox
Dim txtLastName As TextBox
Dim lngValue As Long
Set adoRst = New ADODB.Recordset
adoRst.ActiveConnection = CurrentProject.Connection
adoRst.Open "Employees", , adOpenDynamic, adLockOptimistic
lngValue = Me.txtEmpID.Value
adoRst.Find ("EmployeeID = " & lngValue)
Me.txtLastName.Value = adoRst("LastName")
adoRst.Close
Set adoRst = Nothing
End Sub

I am trying to adapt this code to work after update of a combo box containing a drop down of all my equipments, and produce the serialnumber that represents that equipment.

The combo box is sourced from the column that i am asking the code to find through - is this an issue?

My table has no primary key defined...it is a list of equipment and serial numbers - is this an issue?

The following is my attempt.

Private Sub cboEquipmentType_AfterUpdate()
Dim adoRst As ADODB.Recordset
Dim cboEquipmentType As ComboBox
Dim txtSN As TextBox
Dim strEqpt As String
Set adoRst = New ADODB.Recordset
adoRst.ActiveConnection = CurrentProject.Connection
adoRst.Open "tbl_SN", , adOpenDynamic, adLockOptimistic '
strqpt = Me.cboEquipmentType.Value
adoRst.Find ("[EquipmentDescription] = " & strEqpt)
Me.txtSN.Value = adoRst("SN")
adoRst.Close
Set adoRst = Nothing
End Sub

The code stalls at the highlighted line and brings the following error.

run time error 3001
arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I am at a complete dead end with this problem, and very much a beginner with vba. Any help either with my code or a solution to what im trying to achieve would be greatly appretiated.

Cheers
Dan

 

[Banana]

To answer your question at hand:

If EquipmentDescription is a text field, then it needs to be delimited:

adoRst.Find ("[EquipmentDescription] = [color=red]'[/color]" & strEqpt[color=red] & "'"[/color])

That said, this recordset is for a combobox and for a form? I see no benefit of using ADO here, especially that you're using Access and not a ODBC connection, and you would actually get better performance for less effort using DAO. Your combobox can then use a parameterized DAO query to automatically fetch only equipments based on a customer without so much effort.

 

[djt2009]

banana!
Thankyou for your rapid response!

I do not really understand all that you mentioned as alternative methods for dealing with my problem, although I had read somewhere that DAO was older and not as favoured as ADO.
I have just implemented your advice, and it seems to have worked past that line. however it has stalled on the line underneath with the following error.

run time error 2113
the value you entered isn't valid for this field.

 

[boblarson]

Any reason why you don't just use a simple DLookup, which would replace all of that code with:

Me.txtSN = DLookup("[SN]", "tbl_SN", ("[EquipmentDescription] = '" & strEqpt & "'")

 

[djt2009]

boblarson,banana many thanks... job done

 

[Banana]

Glad you got it fixed.

Just to answer the side question, though:

Yes, DAO is older and it was once slanted to be deprecated in favor of ADO, but Microsoft has reversed its position on this. For example (going by my memroy; could be wrong!):

Access 1997: DAO was the default
Access 2000: ADO was the default
Access 2002-2003: DAO is default but ADO is enabled as well.
Access 2007: DAO* remains the default, ADO is no longer checked by default.

*Note that in 2007 and later, Jet, the database engine behind Access is now renamed ACE, and DAO is now ACEDAO (I think?!?).

But more importantly, we don't have to use either one or other; I've had used both concurrently in some of my project. However, it usually works out this way: DAO for everything Jet, ADO for ODBC linked tables.

ADO does offer some cool features that isn't possible with DAO, but as you've discovered requires much more coding and deeper understanding of the object model to be utilized effectively. With DAO and Jet Expression Service, as Bob shown you, you can just do this in one line of code.

HTH.

 

[dfenton]

DAO for everything Jet, ADO for ODBC linked tables.

I would disagree with that. You use ADO when you want to bypass Jet entirely, so ODBC has nothing to do with it, since ODBC is a Jet data source (i.e., Jet is talking to the ODBC driver which talks to the ODBC data source).

Thus, OLEDB is really useful only for databases that provide their own OLEDB driver. This would include MS SQL Server and Oracle, but not, for instance, MySQL.

And using OLEDB means you're doing things unbound, mostly.

And, BTW, if you use the right parent objects, you don't need a reference to ADO in order to use it. The CurrentProject object provides an interface to ADO, just as the Access Application object provides an interface to DAO functionality like CurrentDB, even when the DAO reference is not selected.

 

[Banana]

David, your description is indeed far more precise and accurate than what I had asserted. In fact, I do use ODBC as exposed via Jet & DAO far more often than I do with OLEDB/ADO. I usually only use ADO when there's a specific functionality that Jet/DAO doesn't support (e.g. binding to a stored procedure result to a form, for instance). That occurs quite rarely.

Even so, using ADO with a ODBC source makes far more sense than ADO with Jet source. Not to say that there is no good reason to do so, but I've yet found a case where I needed something that DAO couldn't do it.

You are correct that we can use references to Access object such as CurrentDb or CurrentProject without needing a reference; I frequently use With...End With block to save myself the trouble of dimming a variable that'd just be pointing to an object in Access's space. However, given the nature of ADO model, I'd be surprised if one could actually build a project without requiring a new recordset object, a new command object or anything like that, and even if it could be worked around, it would almost definitely involve more coding.