There is on relational field between the two tables.
Typo in the above. Did you mean to say -
There is no relational field between the two tables.
Or are you saying the tables are related?
At any rate, here is one option.
Make the DeviceType control on your form an *unbound* text box with a
control source of;
=Iif(nz([AssetNo],0)=0, "",DLookup("[DeviceType]", "[Assets table]",
"[AssetNo] = " & txtAssetNo))
The above assumes that the AssetNo field is an integer. If it is text then it
would be like;
=Iif(nz([AssetNo],"")="", "",DLookup("[DeviceType]", "[Assets table]",
"[AssetNo] = """ & txtAssetNo & """"))
for clarity, the end of that statement is - " " " & txtAssetNo & " " " "
(three quotes, then four quotes). There could be some typos that need to be
fixed, as I'm just writing this in the e-mail editor.
Basically, if there was nothing in the AssetNo, then the DeviceType text box
would be blank, otherwise the DeviceType text box would *display* (not store)
the associated DeviceType. I doubt "management" will know the difference.
--
_________
Sean Bailey
Johnny said:
Absolutely agree with your first comment. The form is a standard form. The
AssetNo contol on the form has been changed back and forth from a text box to
a combo box in my attempts at coming to a solution. Right now I have
returned the form to it's original state which is a text box. The AssetNo is
the PK of the Assets table, but the FK in the Helpdesk table is "HelpDeskNo".
There is on relational field between the two tables.
--
Johnny
:
I am going under the guidance of management to do it this way. Yes, there
would be redundant information in 2 tables by pulling DeviceType and AssetNo
from the Asset table and have it go to the HelpDesk table after entering the
data into the form. However, this is the what they are wanting to happen.
Then "management" doesn't know much about relational database design.
If DeviceType is a descriptive field in the Assets table, it should not be
stored redundantly in another table.
There are a few ways you could approach this (DLookup, Column property
of a combo box, etc.) but some more info may be helpful, i.e.
What type of form are you using? (standard form, continuous, etc.)
Is the AssetNo control on your form a text box or combo box?
Is AssetNo the PK of the Assets table and is there an associated FK
in the Helpdesk table?
--
_________
Sean Bailey
:
I am going under the guidance of management to do it this way. Yes, there
would be redundant information in 2 tables by pulling DeviceType and AssetNo
from the Asset table and have it go to the HelpDesk table after entering the
data into the form. However, this is the what they are wanting to happen.
The form previously only contained the AssetNo which I would look up from a
button leading to a query to search for the AssetNo based on the recipient
requesting assistance. They now would like to be able to associate a field
called DeviceType. The DeviceType field would only have data in it if the
user has requested a hardware assistance, where each hardware in the building
was previously given an AssetNo to go by. If it's a software issue, for
instance, there would be no AssetNo entered and no matching DeviceType entry.
I'm just not sure how to get the DeviceType to autopopulate the type of
hardware that is associated to the entry of an AssetNo.
Does this help? If not, please let me know. Thanks.
--
Johnny
:
You have two tables: helpdesk table and Assets table. The Assets table has
the AssetNo and DeviceType stored. Why would you want to store the same
data again in the helpdesk table? Maybe you need to re-think your table
design. If you want to refer to the Assets table from the helpdesk table,
then add a field to the helpdesk table that holds the primary key of the
Assets table. Please tell us what you are trying to accomplish.
Damon
This should be fairly simple, but as always I make it hard to do. I have
two
control source fields that are bound to a helpdesk table. The field names
are AssetNo and DeviceType. I need the DeviceType field to autopopulate
based on the AssetNo that matches it. I created a qry to list the AssetNo
(###) and matching DeviceType (PC, Monitor, Laptop, etc.), but cannot
figure
out how to incorporate all of this. The AssetNo and DeviceType are
actually
coming from a separate table called Assets. Maybe I should do some coding
instead, but not positive or confident on the code.
Any assistance would be greatly appreciated.
