Learner - SQL or DLookup?

[epicmove]

Hi Guys,
Just created the following routine, wondered if I have gone about it correctly?

In my database (of IT Equipment), each Equipment_Type_Table has a relating Specification Table. The name of the Specification form is stored in the Equipment_Type_Table.

When a new product is entered on the system, if it is a PC (Equipment_Type = "PC") then they need to enter the details in the SpecificationPC Form.
Likewise for a Printer it would be the SpecificationPrinter Form.

The code below (executed by button onclick), looks at what is entered as the equipment type on the Products Form, finds the name of the specification form relating to that Equipment_Type and opens that form.

All the specification tables are child tables of the Products table.

Private Sub openspec_Click()

On Error GoTo Err_openspec_Click

Dim stDocName As String
Dim currtype As String
Dim stLinkCriteria As String
'equipment type detail of product model
currtype = Me![Equipment_Type_Detail]
'looks for the table name relating to the equipment type value
stDocName = DLookup("[Specification_Table_Name]", "TblEquipmentTypeDetail", "[Equipment_Type_Detail_Name] =" & currtype)

stLinkCriteria = "[Hardware_Detail_ID]=" & Me![Product_Hardware_ID]
'opens the relevant specification form for that equipment type
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_openspec_Click:
Exit Sub

Err_openspec_Click:
MsgBox Err.Description
Resume Exit_openspec_Click

End Sub

Is it possible to use SQL within the code instead of a DLookup?
I would eventually like to display the spec table in a subform of the products form so presume I can just change the code to:

subform.controlsource = stDocName (and change the values in the equip type to the table names?)

Any help would be much appreciated.
BF

 

[llkhoutx]

SQL is much faster that DLookup.

Try

stDocName = DLookup("[Specification_Table_Name]", "TblEquipmentTypeDetail", "[Equipment_Type_Detail_Name] =" & currtype)

Dim db as dao.database
dim rs as dao.recordset
set db=currentdb
set rs=db.openrecordset("TblEquipmentTypeDetail",dbopensnapshot)
rs.findfirst "[Equipment_Type_Detail_Name] =" & currtype
if rs.nomatch then
msgbox "Record doesn't exist"
'add appropriate code for no record
'?????
end if
stDocName= rs!Specification_Table_Name

etc.

Be sure to add "Microsoft DAP 3.6 Object Library" to your list of references.