Non-bound control on bound-form

[DetRich]

Hello,

I have a form which displays info about certificates installed on a server.
It is bound to a certificates table. I need to display data from another
field in a text box. This is the environment for that server (think Dev,
Prod, test, etc.). This comes from the main server table. I would like to
have the correct environment displayed when the hostname changes. So, I
think that I need code behind the hostname_OnChange event. I need to
populate the environment text box with the results of the following query:

Select Environment from tblServers
Where tblServer.Hostname = Me.Hostname.


My 2nd idea is to set the control source for the Environment text box using
a DLookup:

DLookup ("Environment", "tblServers", "tblServers.hostname = Me.Hostname")

Can anyone tell me if either of these is a valid solution? Which is better?

For the 2nd option, is it better to use a DLookup, or actual SQL, like this:
Select environment
From tblServers
Where tblServers.hostname = Me.hostname

Thanks,
Rich

 

[Jeff Boyce]

If by "valid" you mean "it works", what happens when you try these?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DetRich]

By "valid", I mean is this a workable solution.
It does not work now, but is either option actually possible?

 

[Klatuu]

How about using a query as the form's record source that joins tblServers on
HostName? Then you can bind the control to that field.

 

[DetRich]

I've tried, but it won't work.

I am using the following query:

Select environment
FROM tblServers
WHERE tblServers.hostname = Me.hostname

I have named this query, qryEnvironmentByHostname and set the control source
to:
=qryenvironmentbyhostname

When I run the form, the text box displays "#Name?"

I've also tried using the DLookup as the current source.

 

[Klatuu]

This will not work at all:

Select environment
FROM tblServers
WHERE tblServers.hostname = Me.hostname

You can do it like this:

=Nz(DLookup("[environment]", "tblServers", "hostname = """ & Me.hostname &
""""), "Not Found")

 

[DetRich]

Thanks for the reply Dave.
Why is the NZ needed, and at the end, why is "Not found" needed?

I thought NZ was used when a value being returned might be zero?

Thanks,
Rich

This will not work at all:

Select environment
FROM tblServers
WHERE tblServers.hostname = Me.hostname

You can do it like this:

=Nz(DLookup("[environment]", "tblServers", "hostname = """ & Me.hostname &
""""), "Not Found")



--
Dave Hargis, Microsoft Access MVP

I've tried, but it won't work.

I am using the following query:

Select environment
FROM tblServers
WHERE tblServers.hostname = Me.hostname

I have named this query, qryEnvironmentByHostname and set the control source
to:
=qryenvironmentbyhostname

When I run the form, the text box displays "#Name?"

I've also tried using the DLookup as the current source.

 

[DetRich]

Hello,

Klatuu's suggestion worked. When adding the his suggested code,

=Nz(DLookup("[environment]", "tblServers", "hostname = """ & Me.hostname &
""""), "Not Found")

Adding this for the forms On_Current event, the environment is displayed as
expected.

Thanks very much Klatuu. I'd still like to understand why the "NZ" and
"NotFound" are needed.

Issue is resolved.

DetRich

This will not work at all:

Select environment
FROM tblServers
WHERE tblServers.hostname = Me.hostname

You can do it like this:

=Nz(DLookup("[environment]", "tblServers", "hostname = """ & Me.hostname &
""""), "Not Found")



--
Dave Hargis, Microsoft Access MVP

I've tried, but it won't work.

I am using the following query:

Select environment
FROM tblServers
WHERE tblServers.hostname = Me.hostname

I have named this query, qryEnvironmentByHostname and set the control source
to:
=qryenvironmentbyhostname

When I run the form, the text box displays "#Name?"

I've also tried using the DLookup as the current source.

 

[Klatuu]

The Nz function is not absolutely necessary. In this case it is to show a
way to handle situations where you do not get a match. Domain Aggragate
functions will return Null when there is no match and you always need to plan
for that eventuality.

The Nz function does not look for a zero value, it is used to return an
alternate value when the value tested is Null. The default return, if no
alternate value is specified, is zero; however, you can return any value you
want. You will want to return the same data type as a valid value.

If you use a Variant variable to receive the value, you can just allow a
Null to be returned and test for a Null value after the DLookup.
--
Dave Hargis, Microsoft Access MVP

Thanks for the reply Dave.
Why is the NZ needed, and at the end, why is "Not found" needed?

I thought NZ was used when a value being returned might be zero?

Thanks,
Rich

This will not work at all:

Select environment
FROM tblServers
WHERE tblServers.hostname = Me.hostname

You can do it like this:

=Nz(DLookup("[environment]", "tblServers", "hostname = """ & Me.hostname &
""""), "Not Found")



--
Dave Hargis, Microsoft Access MVP

I've tried, but it won't work.

I am using the following query:

Select environment
FROM tblServers
WHERE tblServers.hostname = Me.hostname

I have named this query, qryEnvironmentByHostname and set the control source
to:
=qryenvironmentbyhostname

When I run the form, the text box displays "#Name?"

I've also tried using the DLookup as the current source.


:

How about using a query as the form's record source that joins tblServers on
HostName? Then you can bind the control to that field.
--
Dave Hargis, Microsoft Access MVP


:

By "valid", I mean is this a workable solution.
It does not work now, but is either option actually possible?



:

If by "valid" you mean "it works", what happens when you try these?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

I have a form which displays info about certificates installed on a
server.
It is bound to a certificates table. I need to display data from another
field in a text box. This is the environment for that server (think Dev,
Prod, test, etc.). This comes from the main server table. I would like
to
have the correct environment displayed when the hostname changes. So, I
think that I need code behind the hostname_OnChange event. I need to
populate the environment text box with the results of the following query:

Select Environment from tblServers
Where tblServer.Hostname = Me.Hostname.


My 2nd idea is to set the control source for the Environment text box
using
a DLookup:

DLookup ("Environment", "tblServers", "tblServers.hostname = Me.Hostname")

Can anyone tell me if either of these is a valid solution? Which is
better?

For the 2nd option, is it better to use a DLookup, or actual SQL, like
this:
Select environment
From tblServers
Where tblServers.hostname = Me.hostname

Thanks,
Rich