Aternative To DLookup 1

[TimTDP]

In Access 2000 I have a form with 2 fields, Quantity and Mass
A third unbound control "TotalMass" = Quantity * Mass.
Because Mass can be in various denominations, i.e Gram, Kg, Ton, I have a function that converts the mass to a standard unit (Kg) which I use in a query to provide the answer. I then use the Dlookup function as the Control Source for TotalMass.

It works well but I am worried that the database will become slow once the number of records increases. Is there a more efficient method?

 

[fneily]

You don't state how many records you have now. Do you mean actual speed or perceived speeed? See:
thread702-1477224

Since the conversion is a simple arithmetic operation, that should go pretty quick. That's what chips are built for.

You could always set up a test database with test data and run it to see if there is any time delay.

 

[MajP]

I do not think it really is going to be an issue, but if you wanted you could do a couple of things.

1)
Build a conversion table
tblConversion
unit
convValue

example
lbs .454
gm .001
metric ton 1000
short ton 907.1

Now you can link your tables by "unit" and use sql to do the calculations right in your query for the form.

2) You already have a function to do the conversion, why not just carry that out to do the total Mass?

Public Function totalMass(sngQuantity As Single, strUnits As String) As Single
  totalMass = sngQuantity * getKG(strUnits)
End Function
Public Function getKG(strUnits As String) As Single
  Select Case strUnits
    Case "gm"
      getKG = 0.001
    Case "lbs"
      getKG = 0.454
    Case "metric Ton"
      getKG = 1000
    Case "short Ton"
      getKG = 907.1
    'Add more unit conversions...
    Case Else
      MsgBox "Units not supported"
  End Select
End Function

on the unbound control
=totalMass([Quantity],[Mass])