Need to get data from table into form

[MMSMSD]

Hi,
I'm trying to do something that should be fairly simple but seems to be eluding me. I have a table which I update with a simple form that merely chooses or allows me to dump in data and then save the record. It's a mileage form. With one exception, the mileage is always consistant between several locations, so I made a separate table that lists:

StartingPoint
EndingPoint
Mileage

I'm trying to get the original form to pick the correct mileage based on what I put in for starting and ending points on the form. I was able to make a separate query (triggered by 'on lose focus') that showed me it was finding the correct record but do not know how to get the resulting mileage back into the original form. I tried setting the default value as the MILEAGE field of the query but it seems to grabs the default before I've had a chance to select my start and end points. I tried doing a requery when I get to that field in the form, but it just runs the query and doesn't actually put the info in. I tried to do a SETVALUE but get an error about 'not supporting Automation'. I'm running out of creative ideas.

So, I need the MILEAGE from either the original table of possible results or from the special query I made to go into the CALCMILEAGE field I made in the form. Any help would be appreciated. Thanks.

Michael

 

[Remou]

Have you considered DlookUp?
[tt]=DlookUp("Mileage","tblMileages","StartingPoint='" & Me.txtStartingPoint & "' AND EndingPoint='" & Me.txtEndingPoint & "'"[/tt]
Or there abouts.

 

[MMSMSD]

Remou,
I'm trying your suggestion but running into problems. You'll have to pardon my ignorance, as I'm learning Access as I go along. I tried the following:

So that I could track what happens, I set a macro to run ON ENTER in the field (TVDIS_MILES) of the form (frm_DailyAdd). The macro contains a single entry:

SET VALUE

Item - [Forms]![TVDIS_MILES]
Expression - DLookUp("[TVDIS_MILES]","[tbl_Mileage]","[TVDIS_STARTID] = Forms![Combo26] & [TVDIS_ENDID] = Forms![Combo21]")

Combo26 is the combo box on the same form, where I choose starting point. Combo21 is for the destination.

When I run this, I get the following error:

An Error occured while referencing this object
You tried to run a Visual Basic procedurethat improperly references a property or method of an object

When I enter a destination and lose focus, the separate query shows that it can find the proper record. I'm probably just mangling the syntax in some way. Thanks for the help.

Michael

 

[Remou]

You may not need the macro stuff. It is worth trying setting the Control Source to:
[tt]= DLookUp("[TVDIS_MILES]","[tbl_Mileage]","[TVDIS_STARTID] =" & Forms![Combo26] & " And [TVDIS_ENDID] =" & Forms![Combo21])[/tt]
This is assuming that both start and end ID fields are numeric.

There is a FAQ, DLookup Function Examples, faq705-4978,
which you may wish to read.
D Count Problem, thread705-1207225 has some notes, too.

 

[MMSMSD]

Actually, the start and ending points are 2 letter designations, like FC, AC, OT, etc. Since this is a form that will ultimately add a record to a table, I wanted to keep the Control Source as the field in that table. That was my reasoning for putting into an event section, so that I could trigger the lookup when needed (ie after I choose the starting and ending locations from the combo boxes).

When I put your reply in the macro, I got a TYPE MISMATCH error. I'm hoping it's because the IDs are text. The mileage is type DOUBLE for both fields. I took a look at the two references you gave and tried making a few changes to the syntax but it just made it worse. What should the syntax be if the start and ending IDs are letters? Thanks.

 

[Remou]

You need single quotes:
[tt]= DLookUp("[TVDIS_MILES]","[tbl_Mileage]","[TVDIS_STARTID] ='" & Forms![Combo26] & "' And [TVDIS_ENDID] ='" & Forms![Combo21] & "'")[/tt]

 

[MMSMSD]

Remou,

After countless tries, I finally figured out the proper syntax:

=DLookUp("[TVDIS_MILES]","[tbl_Mileage]","[TVDIS_STARTID] ='" & [Combo26] & "' And [TVDIS_ENDID] ='" & [Combo21] & "'")

Seems it didn't like [FORMS]! being in there. Go figure. I left it as a MACRO, triggered with ON ENTER, because I want the database to do the calculation but I want to be able to overwrite if necessary.

I still have a few more hurdles before this thing is fully functional but this was a big one for me. Thanks for your help!