How to reference data without using Dlookup()

[HunterEngineeringCoop]

Hi all,

Is there an easy way to reference a value in a table if you know the value of the primary key and want to return a value in a different field for that record.

For example, I have a table that has 2 fields. One field is a time period in string form, i.e. 3 months, 1 year, etc., and the second field is that same time period represented in number of days, i.e. 90, 365. Is there a way to look up the numeric representation if you have the text representation without using the DLookup function.



So if my time period is saved as "3 months", could I do something like tblCalibrationCyles.[CalCycleTxt].[CalCycleNum], where [CalCycleTxt] is a field in one table and [CalCycleNum] is a field in the look up table, to return the value "90."

I have seen something similar to this before, but I didn't understand how it worked. Any help would be greatly appreciated.

Thanks,
Coop

[jzwp11]

One way would be with a combo box, but you may have to alter your tables slightly. You would use the combo box on a form, not at the table level.

For your table that holds the time periods (i.e. your lookup table)

tblPeriods
-pkPeriodID primary key, autonumber
-txtPeriod (a text field to hold the descriptive name of the period)
-longPeriod (a long number field to hold the # of days)


Your other table would then reference the pkPeriodID as a foreign key. Let's just say we have an equipment table and each piece of equipment must be calibrated at a certain frequency (i.e. your period)

tblEquipment
-pkEquipID primary key, autonumber
-txtEquipmentName
-fkPeriodID foreign key to tblPeriods indicating the calibration period


In a form based on tblEquipment, you would then use a combo box bound to fkPeriodID but whose row source is supplied by tblPeriods. You would set the column widths for all but the txtPeriod to zero which would then display the descriptive period text. The bound column should be the pkPeriodID so that it correlates to the fkPeriodID field.

[NTC]

jwz's advice is fundamentally correct; all I add is that the best way kind of depends on the user experience / form layout that is taking place.

but essentially a combobox of that table can call that value for you. so one selects 3 month - and that 90 value will be there to be useable. what is not clear to me is whether this is placed down in the detail of the form within a record as an unbound control or pre set up as a lookup field within the table itself - or even in the header of the form...... but definitely if a user is in front of the screen to make event occur to select 3 month - then you don't need a dlookup function.....

[HunterEngineeringCoop]

Okay so I basically store the pkPeriodsID value in my equipment table. So if I create a report based off the equipment table how I would get the longPeriod field value to display instead of the pkPeriodsID value?

[NTC]

if you are using a combobox - that box's record source can contain all the fields of its record (even if the end user doesn't see them).

the box has a 'bound' value - but those other values can be called from elsewhere ie:
=me.ComboboxName.column(2)

(any number can be used that is appropriate)

as a sanity check - put in an unbound textbox with its control source having that syntax.....then when you select and reselect different values in the combobox you'll see the value in the unbound text box change too.....one can then use this value as you need.

Hope this helps.

[jzwp11]

NTC's advice on the form should work, but you mention a report in your most recent post. Did you actually mean form? If you really did mean report, you can create a query that brings the related tables together and then select the fields you want. You would then base your report on the query rather than the table.

[HunterEngineeringCoop]

Yes, a report is what I'm trying to create. I'm currently using the DLookup() function to do what I want, but it is a very ugly piece of code:

=DLookUp("CalCycleTimeNum","tblCycleTime","CalCycl eTimeTxt ='" & [CalibrationCycle] & "'")+DLookUp("[MaxOfDate]","qryLastCalibrationDates","Item = '" & [CalibrationItemName] & "' ")

I was trying to find a way to simplify this. Using a query as the recordsource for my report would probably be better way to handle this piece of data.

Also, the info about the combo box will come in handy as well. I didn't realize that you can access data in columns other than the bound column.

Thanks for all your help!

[jzwp11]

You're welcome. If you need help constructing the query, just post back.