How to pass a query field result to a form field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form (Mileage) that tracks my mileage using two fields (From and
To). I have a table (Distances) that lists the mileage between all possible
From's and To's. I have created a query that pulls the current values in the
From and To fields of my form (Mileage) to locate in the Distances table the
mileage appropriate to the specific From and To of the Distances table. Once
my query has selected the correct mileage I do not know how to pass the value
from the query back to my form to be placed in the underlying table. Does
anyone know how to do this or have a better way of doing the same thing? I am
working in ACCESS 2003
 
Use the AfterUpdate event of both your From and To boxes to lookup the
distance in the table and assign it to the field on your form.

This kind of thing:

Private Sub LocationFrom_AfterUpdate()
Dim strWhere As String

If Not (IsNull(Me.[LocationFrom]) or IsNull(Me.[LocationTo])) Then
strWhere = "([LocationFrom] = """ & Me.[LocationFrom]) & _
""") AND (LocationTo = """ & Me.[LocationTo]) & """)"
Me.[Distance] = DLookup("Distance", "Distances", strWhere)
End If
End Sub

Private Sub LocationTo_AfterUpdate()
Call LocationFrom_AfterUpdate
End Sub

Notes:
=====
1. Adjust the code to match your actual field names. Note that "FROM" is a
reserved word in SQL, so not a good name for a field. I've therefore
suggested LocationFrom and LocationTo as field names.

2. If the LocationFrom and LocationTo field are Number fields (not Text
fields), lose the extra quotes, i.e.:
strWhere = "([LocationFrom] = " & Me.[LocationFrom]) & _
") AND (LocationTo = " & Me.[LocationTo]) & ")"

3. Calling the one procedure from the other is easier to write and maintain
that duplicating the code.
 
Back
Top