Field based on another field in a record

[musesshadow]

Alright, this one's hard for me to get my head around.

I have two tables:

1. Estimate, containing;
Hardware
Comment
& other non important stuff

2. Budget, containing;
Hardware
Comment
& other non important stuff

When I'm entering a new record in Budget, I want its "Comment" field to be limited to valid options from Estimate where the Hardware types are the same.

i.e.
In Estimate I have:
Hardware-----Comment-----...
Desktop------D530...
Desktop------DC5000...

If I enter "Desktop" under Hardware for the Budget Record, when I go to select a Comment, I want only D530 and DC5000 to be shown.

This sounds like a dynamic by record query, but I have no idea how to do it.

 

[josephwalter]

If I understand right, you're working on a data entry form that is bound to the Budget table. If so, here's my suggestion:

Say you have a form named frmBudget bound to Budget table, with textbox txtHardware bound to Hardware field and combo box cboComment bound to Comments field.

Make the Record Source of cboComment a query something like this: Select Comment from Estimate Where Estimate.Hardware = Forms!frmBudget.txtHardware

Be sure to perform a cboComment.Requery in the txtHardware's AfterUpdate event, as well as in the OnCurrent event of the form.

I hope this makes sense. I'd be happy to clarify any points.

 

[musesshadow]

Yeah, I was having a brain fart kind of day. You know, those days where your deliniation between your table and your form is very fuzzy? Thanks for your help any way though.