Dlookup question (1 Viewer)

[gguy]

I am working on a database to track aerial applicators as they move from company to company in the course of a growing season.

My main table (company table) and form has company information and the subform has the pilot information (pilot table).

As the pilots changes companies as they move north through the season, I want to enter the new company ID they are changing to, using a combo box in the pilot sub form. The combo box has a look up of the company ID from the company table. Based on the new company ID selected in the combo box I want to run a dlookup in the on change event to set the company ID in the pilot table to the selected company ID.

[comp_ID) = DLookup("[comp_ID]", "Company", "[Comp_ID] = forms!pilot_info![Comp_ID]")

This dlookup acts like it works but does not change the Pilot table company ID.

Tracking is done in a change table that is written at the same time.

Hope this is clear enough. Thanks for any help. G

 

[PNGBill]

Couldn't you just have the Table field in a Subform Text Box Control so when you select the New company, it is actually making the change in the table then and there.

 

[G37Sam]

Your syntax is off. Try:

me!comp_ID = DLookup("[comp_ID]", "Company", "[Comp_ID] = " & forms!pilot_info![Comp_ID])

 

[Galaxiom]

Your syntax is off. Try:

me!comp_ID = DLookup("[comp_ID]", "Company", "[Comp_ID] = " & forms!pilot_info![Comp_ID])

Nothing tragically wrong with the original syntax except perhaps a typo with the parens instead of the right bracket. Yes it should include Me! too but that won't usually stop it working because Access will assume it. (Access can make really dumb assumptions sometimes so do include it.)

The original expression uses a direct reference to the Comp_ID control. The difference of the later post is that the value is concatenated into the argument of the DLookUp.

However both versions are equally pointless because they retreive the Comp_ID that is equal to the value in the combo (the same value used in the condition).

Simply bind the combobox to the company field of the recordsource.

However I would do it in a subform like PNGBill suggested and make a new record for each change. You wouldn't need a separate change table as the entire history would be there in the related table.