DLookUp to write to new table

[Johnny]

Could someone take a look at my code and see what I am missing. My quest is
to have the data I retrieve through my DLookUp code to also write this
information to the table my form is based on. The information I am pulling
in is a device type from an asset table and putting it into a helpdesk table
based on a helpdesk form.

See what you think:
=IIf(nz([Asset Tag No],0)=0,"",DLookUp("[Device Type]","[Assets]","[Asset
Tag Number] = " & [Asset_Tag_No]))

Thanks much.

 

[Beetle]

We covered this in the previous thread, but to reiterate, you should not
be attempting to store the Device Type redundantly in the Helpdesk table.
Just display it in an unbound control on your form, which is what that
IIf statement should do, provided all the field names etc. are correct.

 

[Johnny]

No need to reiterate, I do not disagree with you. However, I basically do
not have a job any longer if I cannot make this work the way my manager has
told me to do it, which is write to another table. Those were his words, by
the way. This is why I was posting this farther, because he was not totally
happy with my results. Thanks anyway.
--
Johnny

We covered this in the previous thread, but to reiterate, you should not
be attempting to store the Device Type redundantly in the Helpdesk table.
Just display it in an unbound control on your form, which is what that
IIf statement should do, provided all the field names etc. are correct.
--
_________

Sean Bailey

Could someone take a look at my code and see what I am missing. My quest is
to have the data I retrieve through my DLookUp code to also write this
information to the table my form is based on. The information I am pulling
in is a device type from an asset table and putting it into a helpdesk table
based on a helpdesk form.

See what you think:
=IIf(nz([Asset Tag No],0)=0,"",DLookUp("[Device Type]","[Assets]","[Asset
Tag Number] = " & [Asset_Tag_No]))

Thanks much.

 

[Beetle]

Well, in that case we'll just overlook the whole "best practices" thing.
What you'll need to do is use a *bound* text box for the DeviceType.
make it's control source the DeviceType field in the helpdesk table.

Then in the After Update event of the AssetNo control you would put code
like:

If(nz(Me![Asset Tag No],0)<>0, Then
Me![txtDeviceType] =DLookUp("[Device Type]","[Assets]","[Asset
Tag Number] = " & [Asset_Tag_No]))
Else Me![DeviceType] = ""
End If

You may also want to add the same code to your forms Curreent event.


BTW - tell your boss I said he doesn't know what he's talking about.
(just kidding...................sort of <g>)

--
_________

Sean Bailey

No need to reiterate, I do not disagree with you. However, I basically do
not have a job any longer if I cannot make this work the way my manager has
told me to do it, which is write to another table. Those were his words, by
the way. This is why I was posting this farther, because he was not totally
happy with my results. Thanks anyway.
--
Johnny

We covered this in the previous thread, but to reiterate, you should not
be attempting to store the Device Type redundantly in the Helpdesk table.
Just display it in an unbound control on your form, which is what that
IIf statement should do, provided all the field names etc. are correct.
--
_________

Sean Bailey

Could someone take a look at my code and see what I am missing. My quest is
to have the data I retrieve through my DLookUp code to also write this
information to the table my form is based on. The information I am pulling
in is a device type from an asset table and putting it into a helpdesk table
based on a helpdesk form.

See what you think:
=IIf(nz([Asset Tag No],0)=0,"",DLookUp("[Device Type]","[Assets]","[Asset
Tag Number] = " & [Asset_Tag_No]))

Thanks much.

 

[Johnny]

Hi Beetle,

Thanks for trying to so hard to help bail me out of this situation. I know
it's just a test they are putting me through. Anyway, I set up the code
exactly as you have listed below in both the After update of the AssetNo and
the On Current Event of the form, plus changed the control source of the
Device Type. Getting an error on the "Else" command of "Compile Error: Else
without If". I tried many things with the If location around the Else, but
nothing works.

Your idea seems perfectly logical to me, however, you are dealing with a
complete retard in this area - I mean me. I've not done much coding at all,
then was just thrown into this.

Thanks again for your assistance and not giving up on me.
--
Johnny

Well, in that case we'll just overlook the whole "best practices" thing.
What you'll need to do is use a *bound* text box for the DeviceType.
make it's control source the DeviceType field in the helpdesk table.

Then in the After Update event of the AssetNo control you would put code
like:

If(nz(Me![Asset Tag No],0)<>0, Then
Me![txtDeviceType] =DLookUp("[Device Type]","[Assets]","[Asset
Tag Number] = " & [Asset_Tag_No]))
Else Me![DeviceType] = ""
End If

You may also want to add the same code to your forms Curreent event.


BTW - tell your boss I said he doesn't know what he's talking about.
(just kidding...................sort of <g>)

--
_________

Sean Bailey

No need to reiterate, I do not disagree with you. However, I basically do
not have a job any longer if I cannot make this work the way my manager has
told me to do it, which is write to another table. Those were his words, by
the way. This is why I was posting this farther, because he was not totally
happy with my results. Thanks anyway.
--
Johnny

We covered this in the previous thread, but to reiterate, you should not
be attempting to store the Device Type redundantly in the Helpdesk table.
Just display it in an unbound control on your form, which is what that
IIf statement should do, provided all the field names etc. are correct.
--
_________

Sean Bailey


:

Could someone take a look at my code and see what I am missing. My quest is
to have the data I retrieve through my DLookUp code to also write this
information to the table my form is based on. The information I am pulling
in is a device type from an asset table and putting it into a helpdesk table
based on a helpdesk form.

See what you think:
=IIf(nz([Asset Tag No],0)=0,"",DLookUp("[Device Type]","[Assets]","[Asset
Tag Number] = " & [Asset_Tag_No]))

Thanks much.

 

[Johnny]

Thank you, someone conceded to good advice.
--
Johnny

We covered this in the previous thread, but to reiterate, you should not
be attempting to store the Device Type redundantly in the Helpdesk table.
Just display it in an unbound control on your form, which is what that
IIf statement should do, provided all the field names etc. are correct.
--
_________

Sean Bailey

Could someone take a look at my code and see what I am missing. My quest is
to have the data I retrieve through my DLookUp code to also write this
information to the table my form is based on. The information I am pulling
in is a device type from an asset table and putting it into a helpdesk table
based on a helpdesk form.

See what you think:
=IIf(nz([Asset Tag No],0)=0,"",DLookUp("[Device Type]","[Assets]","[Asset
Tag Number] = " & [Asset_Tag_No]))

Thanks much.