Passing Value or Dlookup, which is more effcient

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

i have a main form which has the ID and Type of the Product. i have a service
report form that is linked through the ID. is it more effcient to do a
dlookup for the type of product on the service report, or is it better to
pass it through an open args.

the service report table doesnt store the product type information, it uses
it to show or hide certain fields that relate to a table that keeps
information related to a certain type of product. thus the reason for knowing
on the service report form the type of product.

thanks...
 
Dawn,

DLookup is pretty slow, so you should only use it as a last resort.

The best method would be to create a join query that combines the
fields from both of your tables and use that as the RecordSource for
your report.

Hope this helps,

Peter De Baets
Peter's Software - Microsoft Access Tools for Developers
http://www.peterssoftware.com
 
the service report is actually a form, not a "report" object. it is a record
of the service performed for a customers product.
 
Is the service report form opened by the main form or both forms open at the
same time? Is the service report form a subform of the main form?
 
it is opened by a button on the main form called new service report. the only
connecting data is the productid. the service reports are "attached" to the
product the service tech fixed.

there is a third table involved that is directly related to the product
table. that table stores "running hours". this table is updated through the
service reports and i need to make sure that the service report "knows" what
type of product it is before showing the fields for the running hours.

basically what i really want to know is which way of getting the product
type is more efficient? a dlookup or opening args? i have it all working,
just looking to see what the "experts" know to be more efficient.
 
The better method is to use the Where argument of the OpenForm method when
opening the service report form.

Docmd.OpenForm "[service report form]", , , [ProductID] = " &
Me.txtProductID

[ProductID] should actually be the name of the field in the service report
form's recordset that identifies the product. Me.txtProductID should be the
name of the control on the main form that identifies the product. the code
above assumes it is a numeric field. If it is a text field, you would use
this syntax:

Docmd.OpenForm "[service report form]", , , [ProductID] = """ &
Me.txtProductID & """"
 
Hello Klatuu

thanks, but, been there dun dat.

opening the "subform" service report is not the issue.

there are four tables involved.

products which has the ProductID and ProductTypeID

blocks which has the ProductID, BlockID and BlockSerial

running hours which has ProductID, BlockID, RHDate, HoursATDate,
ServiceReportID, IssueID

service reports which has ProductID, ServiceReportID, ServiceDate, and all
the fields for inputting what was done on the service report

on the service report form, there is a combo box that allows the user to
choose which block that they are going to put running hours for on the
service report. this combo looks at the product and then finds the blocks
that are related to it. this works fine.

on the service report form there is a text box where the user enters the
running hours related to the service report, the product and the particular
block they chose. this value along with the blockID and servicereportID is
then sql'ed into the appropriate spot in the running hour table against the
current product. this works perfectly.

all i would like to know is:

which is better for getting a value into a form control, dlookup, or using
the form open args?

on my main form i have a list box that gives a list of all the service
reports regardless of what product is being shown on the same form. when a
user double clicks this list box this code fires:

Private Sub lstServiceReport_DblClick(Cancel As Integer)
On Error GoTo Err_lstServiceReport_DblClick
Dim stDocName As String
Dim stLinkCriteria As String
Dim intArgs As Integer

stDocName = "frmManageServiceReports"
intArgs = Me.lstServiceReport.Column(1)

stLinkCriteria = "[ServiceReportID]=" & Me![lstServiceReport]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acWindowNormal, intArgs

Exit_lstServiceReport_DblClick:
Exit Sub

Err_lstServiceReport_DblClick:
MsgBox Err.Description
Resume Exit_lstServiceReport_DblClick
End Sub

when the form loads this code fires:

Private Sub Form_Load()
On Error GoTo Err_Form_Load

'make the numbers and stuff show right
Me.SerialNumber.Requery

Me.cboSRStatusID.Requery
Me.cbSRBlockID.Requery

Me.txttblRunningHours = Null

Me.cbSRBlockID.Visible = False
Me.txttblRunningHours.Visible = False

Dim findSRRunningHours As Single
Dim strProductType As String
Dim frmopenArgs As Integer

frmopenArgs = Me.OpenArgs
strProductType = DLookup("ProductTypeID", "tblProductList", "ProductID =
" & frmopenArgs)

If strProductType = "Compressor" Then

'turn on the block id and running hours fields
Me.cbSRBlockID.Visible = True
Me.txttblRunningHours.Visible = True
Me.cbSRBlockID.Requery

'check to see if it is a new service report
If Not IsNull(Me.ServiceReportID) Then
'check to see if there is a block already selected
If IsNull(Me.cbSRBlockID.Value) Then
Me.cbSRBlockID.Value = DLookup("BlockID", "subtblBlockList",
"ProductID =" & Me.ProductID)
' msgbox ("combo value is " & Me.cbSRBlockID.Value)
End If
'check to see if there is a value to show
findSRRunningHours = Nz(DLookup("HoursAtDate",
"subtblRunningHours", "ServiceReportID = " & Me.ServiceReportID & " And
BlockID =" & Me.cbSRBlockID), -1)
' msgbox ("find running hours is " & findRunningHours)
'if there is show it
Me.txttblRunningHours = findSRRunningHours
End If
End If

Me.ServiceReportDate.SetFocus

'turn off the credit card line till needed
Me.Visa.Enabled = False
Me.MC.Enabled = False
Me.Other.Enabled = False
Me.CardNumber.Enabled = False
Me.ExpiryDateMonth.Enabled = False
Me.ExpiryDateYear.Enabled = False

Exit_Form_Load:
Exit Sub

Err_Form_Load:
If Err = 2448 Then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description & "
happened in the Load Event"
End If
Resume Exit_Form_Load
End Sub

there is also a button that allows the user to create a new service report
for the currently viewed product, that code looks like this:

Private Sub cmdAddNewServiceReport_Click()
On Error GoTo Err_cmdAddNewServiceReport_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim intArgs As Integer

stDocName = "frmManageServiceReports"
intArgs = Me.subProductID

stLinkCriteria = "[ProductID]=" & Me![txtProduct]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , intArgs

Exit_cmdAddNewServiceReport_Click:
Exit Sub

Err_cmdAddNewServiceReport_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewServiceReport_Click
End Sub

you will notice that in both the double click of the listbox or the use of
the new button causes an argument to be passed in the loading of the form. it
is essential for the combo box and text field to be told to show or not
because some product types dont have blocks and running hours.

appreciate any insights.

Klatuu said:
The better method is to use the Where argument of the OpenForm method when
opening the service report form.

Docmd.OpenForm "[service report form]", , , [ProductID] = " &
Me.txtProductID

[ProductID] should actually be the name of the field in the service report
form's recordset that identifies the product. Me.txtProductID should be the
name of the control on the main form that identifies the product. the code
above assumes it is a numeric field. If it is a text field, you would use
this syntax:

Docmd.OpenForm "[service report form]", , , [ProductID] = """ &
Me.txtProductID & """"

--
Dave Hargis, Microsoft Access MVP


DawnTreader said:
it is opened by a button on the main form called new service report. the only
connecting data is the productid. the service reports are "attached" to the
product the service tech fixed.

there is a third table involved that is directly related to the product
table. that table stores "running hours". this table is updated through the
service reports and i need to make sure that the service report "knows" what
type of product it is before showing the fields for the running hours.

basically what i really want to know is which way of getting the product
type is more efficient? a dlookup or opening args? i have it all working,
just looking to see what the "experts" know to be more efficient.
 
I wouldn't say one is superior to the other. It mostly depends on other
things happening. My suggested method was based on your asking about the
Openargs, but you additional information made it more clear what you are
doing. In this case, I would think the OpenArgs would do nicely.
--
Dave Hargis, Microsoft Access MVP


DawnTreader said:
Hello Klatuu

thanks, but, been there dun dat.

opening the "subform" service report is not the issue.

there are four tables involved.

products which has the ProductID and ProductTypeID

blocks which has the ProductID, BlockID and BlockSerial

running hours which has ProductID, BlockID, RHDate, HoursATDate,
ServiceReportID, IssueID

service reports which has ProductID, ServiceReportID, ServiceDate, and all
the fields for inputting what was done on the service report

on the service report form, there is a combo box that allows the user to
choose which block that they are going to put running hours for on the
service report. this combo looks at the product and then finds the blocks
that are related to it. this works fine.

on the service report form there is a text box where the user enters the
running hours related to the service report, the product and the particular
block they chose. this value along with the blockID and servicereportID is
then sql'ed into the appropriate spot in the running hour table against the
current product. this works perfectly.

all i would like to know is:

which is better for getting a value into a form control, dlookup, or using
the form open args?

on my main form i have a list box that gives a list of all the service
reports regardless of what product is being shown on the same form. when a
user double clicks this list box this code fires:

Private Sub lstServiceReport_DblClick(Cancel As Integer)
On Error GoTo Err_lstServiceReport_DblClick
Dim stDocName As String
Dim stLinkCriteria As String
Dim intArgs As Integer

stDocName = "frmManageServiceReports"
intArgs = Me.lstServiceReport.Column(1)

stLinkCriteria = "[ServiceReportID]=" & Me![lstServiceReport]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acWindowNormal, intArgs

Exit_lstServiceReport_DblClick:
Exit Sub

Err_lstServiceReport_DblClick:
MsgBox Err.Description
Resume Exit_lstServiceReport_DblClick
End Sub

when the form loads this code fires:

Private Sub Form_Load()
On Error GoTo Err_Form_Load

'make the numbers and stuff show right
Me.SerialNumber.Requery

Me.cboSRStatusID.Requery
Me.cbSRBlockID.Requery

Me.txttblRunningHours = Null

Me.cbSRBlockID.Visible = False
Me.txttblRunningHours.Visible = False

Dim findSRRunningHours As Single
Dim strProductType As String
Dim frmopenArgs As Integer

frmopenArgs = Me.OpenArgs
strProductType = DLookup("ProductTypeID", "tblProductList", "ProductID =
" & frmopenArgs)

If strProductType = "Compressor" Then

'turn on the block id and running hours fields
Me.cbSRBlockID.Visible = True
Me.txttblRunningHours.Visible = True
Me.cbSRBlockID.Requery

'check to see if it is a new service report
If Not IsNull(Me.ServiceReportID) Then
'check to see if there is a block already selected
If IsNull(Me.cbSRBlockID.Value) Then
Me.cbSRBlockID.Value = DLookup("BlockID", "subtblBlockList",
"ProductID =" & Me.ProductID)
' msgbox ("combo value is " & Me.cbSRBlockID.Value)
End If
'check to see if there is a value to show
findSRRunningHours = Nz(DLookup("HoursAtDate",
"subtblRunningHours", "ServiceReportID = " & Me.ServiceReportID & " And
BlockID =" & Me.cbSRBlockID), -1)
' msgbox ("find running hours is " & findRunningHours)
'if there is show it
Me.txttblRunningHours = findSRRunningHours
End If
End If

Me.ServiceReportDate.SetFocus

'turn off the credit card line till needed
Me.Visa.Enabled = False
Me.MC.Enabled = False
Me.Other.Enabled = False
Me.CardNumber.Enabled = False
Me.ExpiryDateMonth.Enabled = False
Me.ExpiryDateYear.Enabled = False

Exit_Form_Load:
Exit Sub

Err_Form_Load:
If Err = 2448 Then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description & "
happened in the Load Event"
End If
Resume Exit_Form_Load
End Sub

there is also a button that allows the user to create a new service report
for the currently viewed product, that code looks like this:

Private Sub cmdAddNewServiceReport_Click()
On Error GoTo Err_cmdAddNewServiceReport_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim intArgs As Integer

stDocName = "frmManageServiceReports"
intArgs = Me.subProductID

stLinkCriteria = "[ProductID]=" & Me![txtProduct]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , intArgs

Exit_cmdAddNewServiceReport_Click:
Exit Sub

Err_cmdAddNewServiceReport_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewServiceReport_Click
End Sub

you will notice that in both the double click of the listbox or the use of
the new button causes an argument to be passed in the loading of the form. it
is essential for the combo box and text field to be told to show or not
because some product types dont have blocks and running hours.

appreciate any insights.

Klatuu said:
The better method is to use the Where argument of the OpenForm method when
opening the service report form.

Docmd.OpenForm "[service report form]", , , [ProductID] = " &
Me.txtProductID

[ProductID] should actually be the name of the field in the service report
form's recordset that identifies the product. Me.txtProductID should be the
name of the control on the main form that identifies the product. the code
above assumes it is a numeric field. If it is a text field, you would use
this syntax:

Docmd.OpenForm "[service report form]", , , [ProductID] = """ &
Me.txtProductID & """"

--
Dave Hargis, Microsoft Access MVP


DawnTreader said:
it is opened by a button on the main form called new service report. the only
connecting data is the productid. the service reports are "attached" to the
product the service tech fixed.

there is a third table involved that is directly related to the product
table. that table stores "running hours". this table is updated through the
service reports and i need to make sure that the service report "knows" what
type of product it is before showing the fields for the running hours.

basically what i really want to know is which way of getting the product
type is more efficient? a dlookup or opening args? i have it all working,
just looking to see what the "experts" know to be more efficient.

:

Is the service report form opened by the main form or both forms open at the
same time? Is the service report form a subform of the main form?

--
Dave Hargis, Microsoft Access MVP


:

the service report is actually a form, not a "report" object. it is a record
of the service performed for a customers product.

:

Dawn,

DLookup is pretty slow, so you should only use it as a last resort.

The best method would be to create a join query that combines the
fields from both of your tables and use that as the RecordSource for
your report.

Hope this helps,

Peter De Baets
Peter's Software - Microsoft Access Tools for Developers
http://www.peterssoftware.com
 
Thanks. :)

Klatuu said:
I wouldn't say one is superior to the other. It mostly depends on other
things happening. My suggested method was based on your asking about the
Openargs, but you additional information made it more clear what you are
doing. In this case, I would think the OpenArgs would do nicely.
--
Dave Hargis, Microsoft Access MVP


DawnTreader said:
Hello Klatuu

thanks, but, been there dun dat.

opening the "subform" service report is not the issue.

there are four tables involved.

products which has the ProductID and ProductTypeID

blocks which has the ProductID, BlockID and BlockSerial

running hours which has ProductID, BlockID, RHDate, HoursATDate,
ServiceReportID, IssueID

service reports which has ProductID, ServiceReportID, ServiceDate, and all
the fields for inputting what was done on the service report

on the service report form, there is a combo box that allows the user to
choose which block that they are going to put running hours for on the
service report. this combo looks at the product and then finds the blocks
that are related to it. this works fine.

on the service report form there is a text box where the user enters the
running hours related to the service report, the product and the particular
block they chose. this value along with the blockID and servicereportID is
then sql'ed into the appropriate spot in the running hour table against the
current product. this works perfectly.

all i would like to know is:

which is better for getting a value into a form control, dlookup, or using
the form open args?

on my main form i have a list box that gives a list of all the service
reports regardless of what product is being shown on the same form. when a
user double clicks this list box this code fires:

Private Sub lstServiceReport_DblClick(Cancel As Integer)
On Error GoTo Err_lstServiceReport_DblClick
Dim stDocName As String
Dim stLinkCriteria As String
Dim intArgs As Integer

stDocName = "frmManageServiceReports"
intArgs = Me.lstServiceReport.Column(1)

stLinkCriteria = "[ServiceReportID]=" & Me![lstServiceReport]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acWindowNormal, intArgs

Exit_lstServiceReport_DblClick:
Exit Sub

Err_lstServiceReport_DblClick:
MsgBox Err.Description
Resume Exit_lstServiceReport_DblClick
End Sub

when the form loads this code fires:

Private Sub Form_Load()
On Error GoTo Err_Form_Load

'make the numbers and stuff show right
Me.SerialNumber.Requery

Me.cboSRStatusID.Requery
Me.cbSRBlockID.Requery

Me.txttblRunningHours = Null

Me.cbSRBlockID.Visible = False
Me.txttblRunningHours.Visible = False

Dim findSRRunningHours As Single
Dim strProductType As String
Dim frmopenArgs As Integer

frmopenArgs = Me.OpenArgs
strProductType = DLookup("ProductTypeID", "tblProductList", "ProductID =
" & frmopenArgs)

If strProductType = "Compressor" Then

'turn on the block id and running hours fields
Me.cbSRBlockID.Visible = True
Me.txttblRunningHours.Visible = True
Me.cbSRBlockID.Requery

'check to see if it is a new service report
If Not IsNull(Me.ServiceReportID) Then
'check to see if there is a block already selected
If IsNull(Me.cbSRBlockID.Value) Then
Me.cbSRBlockID.Value = DLookup("BlockID", "subtblBlockList",
"ProductID =" & Me.ProductID)
' msgbox ("combo value is " & Me.cbSRBlockID.Value)
End If
'check to see if there is a value to show
findSRRunningHours = Nz(DLookup("HoursAtDate",
"subtblRunningHours", "ServiceReportID = " & Me.ServiceReportID & " And
BlockID =" & Me.cbSRBlockID), -1)
' msgbox ("find running hours is " & findRunningHours)
'if there is show it
Me.txttblRunningHours = findSRRunningHours
End If
End If

Me.ServiceReportDate.SetFocus

'turn off the credit card line till needed
Me.Visa.Enabled = False
Me.MC.Enabled = False
Me.Other.Enabled = False
Me.CardNumber.Enabled = False
Me.ExpiryDateMonth.Enabled = False
Me.ExpiryDateYear.Enabled = False

Exit_Form_Load:
Exit Sub

Err_Form_Load:
If Err = 2448 Then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description & "
happened in the Load Event"
End If
Resume Exit_Form_Load
End Sub

there is also a button that allows the user to create a new service report
for the currently viewed product, that code looks like this:

Private Sub cmdAddNewServiceReport_Click()
On Error GoTo Err_cmdAddNewServiceReport_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim intArgs As Integer

stDocName = "frmManageServiceReports"
intArgs = Me.subProductID

stLinkCriteria = "[ProductID]=" & Me![txtProduct]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , intArgs

Exit_cmdAddNewServiceReport_Click:
Exit Sub

Err_cmdAddNewServiceReport_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewServiceReport_Click
End Sub

you will notice that in both the double click of the listbox or the use of
the new button causes an argument to be passed in the loading of the form. it
is essential for the combo box and text field to be told to show or not
because some product types dont have blocks and running hours.

appreciate any insights.

Klatuu said:
The better method is to use the Where argument of the OpenForm method when
opening the service report form.

Docmd.OpenForm "[service report form]", , , [ProductID] = " &
Me.txtProductID

[ProductID] should actually be the name of the field in the service report
form's recordset that identifies the product. Me.txtProductID should be the
name of the control on the main form that identifies the product. the code
above assumes it is a numeric field. If it is a text field, you would use
this syntax:

Docmd.OpenForm "[service report form]", , , [ProductID] = """ &
Me.txtProductID & """"

--
Dave Hargis, Microsoft Access MVP


:

it is opened by a button on the main form called new service report. the only
connecting data is the productid. the service reports are "attached" to the
product the service tech fixed.

there is a third table involved that is directly related to the product
table. that table stores "running hours". this table is updated through the
service reports and i need to make sure that the service report "knows" what
type of product it is before showing the fields for the running hours.

basically what i really want to know is which way of getting the product
type is more efficient? a dlookup or opening args? i have it all working,
just looking to see what the "experts" know to be more efficient.

:

Is the service report form opened by the main form or both forms open at the
same time? Is the service report form a subform of the main form?

--
Dave Hargis, Microsoft Access MVP


:

the service report is actually a form, not a "report" object. it is a record
of the service performed for a customers product.

:

Dawn,

DLookup is pretty slow, so you should only use it as a last resort.

The best method would be to create a join query that combines the
fields from both of your tables and use that as the RecordSource for
your report.

Hope this helps,

Peter De Baets
Peter's Software - Microsoft Access Tools for Developers
http://www.peterssoftware.com
 
Back
Top