Using dlookup to populate textbox

[Guest]

Tables: tblEmployee; tblFireDept
Form: frmPersAction (Record Source = tblEmployee)
Combo Box: cboDept (query on tblFireDept)
Textboxes: txtFireChief; txtDeptPhone

What I would like to do is populate the textboxes based on the selection in
the combo box. I'm using 'dlookup', however, either nothing happens when a
selection is made (textboxes not populated) or I get a Run-time error 3075
(or some other number).

I've tried putting the following dlookup statement in the control source of
the combo box as well as in the after update to no avail. I figure if I
could get one of them to work, I'm doing good.
=DLookup("[FireChief]", "[tblFireDept]", "[DeptName]= ' "& cboDept&"'")

Is this possible or should I take another route?
Any suggestions would be greatly appreciated.

 

[Gary Miller]

While it is possible, a drawback to your approach is that
while this method may display the Chief and Phone when you
navigate to an existing record, there will be no trigger to
give those controls new data just by selecting the
Department without using code to requery the form after your
selection.

There is another easy approach that can be used. Create and
save a small 2 field single form view form based on your
tblFireDept that is just big enough to display the Chief and
the Phone. In the format section set the form not to use
scroll bars or navigation buttons. Now in your frmPersAction
use the control wizard to drop a Subform control on your
form adjascent to your cboDept. Using the wizard select the
new form and choose the FireDept fields from both tables as
the master and child linking fields. Resize the subform and
arrange it as needed. Click on the subform containing
control and under the data properties set it to Locked=Yes
so no one can inadvertantly change the Chief or number. Now
the subform will automatically populate the subform with the
info from the FireDept table.

This is only one of several approaches that can be used.
Another good one as long as there is only one record per
department is to take advantage of the relationship between
the two tables and base your form on a query that includes
both tables. You can then add any fields that you want from
the Dept table to the query and then just add those as
controls on your form as if they are part of the Pers
record. As above, when a Dept is selected, the other Dept
info that you have added will automatically appear. Do make
sure and lock those controls as well.

BTW - Best design practices would have you use an Autonumber
Deptartment ID field that you use as the Primary key instead
of the name. You will have a bit of a problem as is if
departments change names or merge together. With an ID you
just change the name as it is only used for display
purposes.

--
Gary Miller
Sisters, OR



message

Tables: tblEmployee; tblFireDept
Form: frmPersAction (Record Source = tblEmployee)
Combo Box: cboDept (query on tblFireDept)
Textboxes: txtFireChief; txtDeptPhone

What I would like to do is populate the textboxes based on
the selection in
the combo box. I'm using 'dlookup', however, either
nothing happens when a
selection is made (textboxes not populated) or I get a
Run-time error 3075
(or some other number).

I've tried putting the following dlookup statement in the
control source of
the combo box as well as in the after update to no avail.
I figure if I
could get one of them to work, I'm doing good.
=DLookup("[FireChief]", "[tblFireDept]", "[DeptName]= ' "&
cboDept&"'")

Is this possible or should I take another route?
Any suggestions would be greatly appreciated.

 

[Guest]

Thanks Gary. I tried your second suggestion and based the form on a query
that included both tables. Works perfectly. Thanks again.

While it is possible, a drawback to your approach is that
while this method may display the Chief and Phone when you
navigate to an existing record, there will be no trigger to
give those controls new data just by selecting the
Department without using code to requery the form after your
selection.

There is another easy approach that can be used. Create and
save a small 2 field single form view form based on your
tblFireDept that is just big enough to display the Chief and
the Phone. In the format section set the form not to use
scroll bars or navigation buttons. Now in your frmPersAction
use the control wizard to drop a Subform control on your
form adjascent to your cboDept. Using the wizard select the
new form and choose the FireDept fields from both tables as
the master and child linking fields. Resize the subform and
arrange it as needed. Click on the subform containing
control and under the data properties set it to Locked=Yes
so no one can inadvertantly change the Chief or number. Now
the subform will automatically populate the subform with the
info from the FireDept table.

This is only one of several approaches that can be used.
Another good one as long as there is only one record per
department is to take advantage of the relationship between
the two tables and base your form on a query that includes
both tables. You can then add any fields that you want from
the Dept table to the query and then just add those as
controls on your form as if they are part of the Pers
record. As above, when a Dept is selected, the other Dept
info that you have added will automatically appear. Do make
sure and lock those controls as well.

BTW - Best design practices would have you use an Autonumber
Deptartment ID field that you use as the Primary key instead
of the name. You will have a bit of a problem as is if
departments change names or merge together. With an ID you
just change the name as it is only used for display
purposes.

--
Gary Miller
Sisters, OR



message

Tables: tblEmployee; tblFireDept
Form: frmPersAction (Record Source = tblEmployee)
Combo Box: cboDept (query on tblFireDept)
Textboxes: txtFireChief; txtDeptPhone

What I would like to do is populate the textboxes based on
the selection in
the combo box. I'm using 'dlookup', however, either
nothing happens when a
selection is made (textboxes not populated) or I get a
Run-time error 3075
(or some other number).

I've tried putting the following dlookup statement in the
control source of
the combo box as well as in the after update to no avail.
I figure if I
could get one of them to work, I'm doing good.
=DLookup("[FireChief]", "[tblFireDept]", "[DeptName]= ' "&
cboDept&"'")

Is this possible or should I take another route?
Any suggestions would be greatly appreciated.