combo box to update two fields after update (1 Viewer)

jemar707311 · Aug 17, 2001

My problem, any suggestions appreciated:

I have a table, tblFiscalYears, 3 fields: FiscalYear, FYBeginDate, FYEndDate.

I have a search form that lets users establish criteria for a query against another table. On the search form, I have a combo box,cmboFY, that is populated by tblFiscalYears and after update I want it to populate two other fields, FYBeginDate and FYEndDate that are used for criteria.

HOW???? I fooled with If Then, Select Case, however, I don't want to establish the criteria in code, I want the selection to look at the table.

Why I am doing this may help......the end users don't want to user their brain and type in the begin/end dates of the fiscal years.

Make sense anyone???? please help.....this forum has saved me twice now. thanks.

jatfill · Aug 17, 2001

On the AfterUpdate property of the combobox, use the DLookUp command to pull the start and end dates from the table and write them into the two other fields. This would still be done through code, but it would look at the table's data as you eluded to:

Forms![FormName]![FYBeginDate].Value = DLookUp("[FYBeginDate]", "tblFiscalYears", "[FiscalYear] = " & Me.FiscalYear)

Forms![FormName]![FYEndDate].Value = DLookUp("[FYEndDate]", "tblFiscalYears", "[FiscalYear] = " & Me.FiscalYear)

[This message has been edited by jatfill (edited 08-17-2001).]

shacket · Aug 17, 2001

If I am following you correctly, when you choose the FiscalYear from cmboFY, you want two text boxes to display the FYBeginDate and the FYEndDate for the FY displayed in the combo. Is that right?

If so, put the following in the AfterUpdate for cmboFY:

Me.txtFYBeginDate = cmboFY.Column(1)
Me.txtFYBeginDate = cmboFY.Column(2)

Notes:
-you must have the three fields in the combo box
-column counts are 0-based, so Column(0) is the first column, Column(1) is the second and so on

jemar707311 · Aug 17, 2001

THANKS JATFILL. WORKED GREAT.

Rich · Aug 17, 2001

Why use DLookUp to return the value of fields that are already in the source table of your combo box. Probably the worst way to do it.

shacket · Aug 19, 2001

Exactly Rich. Both will work, but you want to have Access only do the work once, which it does when it populates the Combo Box. To use the DLookUp function in each place makes Access look the information up 3 times (once for the Combo box, once each for the statements). If the data is already in the combo box, it is more efficient to use the Column statements I mentioned in previous post.

jatfill · Aug 20, 2001

that's an excellent tip... I have one additional question about this, however.

Do the 2/3rd columns in question have to be displayed on the listbox in order for the reference to function, or can they be "hidden" from the user...?

AlanS · Aug 20, 2001

List box / combo box columns may be referenced whether or not they are hidden. In most cases, you will want to hide all columns (except the one the user selects a value from) by setting their widths to 0.

jemar707311 · Aug 24, 2001

Thanks all......I switched it to reference the combo box. Learned two things this time!!!
'Tis my quote I always use at an interview.......Woodrow Wilson: 'I not only use all the brains I have, but all I can borrow.'

Thanks for the brains.

combo box to update two fields after update (1 Viewer)

jemar707311

Registered User.

jatfill

Registered User.

shacket

Registered User.

jemar707311

Registered User.

Rich

Guest

shacket

Registered User.

jatfill

Registered User.

AlanS

Registered User.

jemar707311

Registered User.

Similar threads

Users who are viewing this thread