Control Source in a textbox

[Tom Brown]

Ladies and Gentlemen,

I am trying to create a login screen which uses DLookup in the control
source of a textbox to pull the user location based on the user login
number. I have created a query that pulls the user location from an
underlying table based on the login # which then pulls the correct value to
the textbox. My question concerns using the Data-Control Source to enter
the DLookup value. The reason I use a textbox and not a combo box is
because the login user will be ONLY in one place, and I don't want a list to
come up giving the user the opportunity to pull ANY location from a list. I
guess I am confused about how to have the textbox point to a query using
something OTHER than the Control Source when you need the Control Source to
point to an area in the bound table.

In control source of textbox:
=DLookup("[BU_Name]","[tbl_users]","[Employee_Number] =
Forms![frm_Login_Page]![txt_E_Number]")

This works at pulling the correct value from the tbl-users table, but when
posted the entry does not post to the underlying (tbl_Master_Data) table.

Any help will be greatly appreciated.

Thanks,

Tom Brown

 

[Klatuu]

If you want the text box bound to a field in the form's record source, then
you use the Default Value property rather than the Control Source. Use the
control source for the field you want to bind the text box to.

When you use a function as the control source of a text box, it can only be
an unbound control for display purposes. This is useful when you want to
present a value that is the result of a calculation based on other field in
the recordset.

When you have a bound control, existing records will display the last saved
value. But, you can use the Default Value to load the initial value. That
value will then populate the field in the form's recordset.

It is probably not necessary to use query in the DLookup. The only reason
would be if the field to be returned and the field(s) to filter on are in
different tables. In either case you you use the name of the table or the
query:

=DLookup("[ReturnField]", "TableOrQueryName", "[Criteria Field] = " &
Me.SomeControl)

[ReturnField] is the name of the field you want to retrieve the value from.

TableOrQueryName is the name of the table or query that has the field you
need to locate and return the value.

[Criteria Field] is the name of a field you want to use to filter the return
of the function.

Me.SomeControl is the source of the value you want to match [Criteria Field]
on.

The example above assumes [Criteria Field] is a numeric field. If it is not
a numeric field, it is necessary to encluse the source in the correct
dilimiters.
Text:
"[Criteria Field] = '" & Me.SomeControl & "'")
Date:
"[Criteria Field] = #" & Me.SomeControl & "#")

Note: The = is necessary for use in a Default Value or Control Source
property.
--
Dave Hargis, Microsoft Access MVP

Ladies and Gentlemen,

I am trying to create a login screen which uses DLookup in the control
source of a textbox to pull the user location based on the user login
number. I have created a query that pulls the user location from an
underlying table based on the login # which then pulls the correct value to
the textbox. My question concerns using the Data-Control Source to enter
the DLookup value. The reason I use a textbox and not a combo box is
because the login user will be ONLY in one place, and I don't want a list to
come up giving the user the opportunity to pull ANY location from a list. I
guess I am confused about how to have the textbox point to a query using
something OTHER than the Control Source when you need the Control Source to
point to an area in the bound table.

In control source of textbox:
=DLookup("[BU_Name]","[tbl_users]","[Employee_Number] =
Forms![frm_Login_Page]![txt_E_Number]")

This works at pulling the correct value from the tbl-users table, but when
posted the entry does not post to the underlying (tbl_Master_Data) table.

Any help will be greatly appreciated.

Thanks,

Tom Brown

 

[Tom Brown]

Thanks, Dave

I tried your suggestion to use the default value as the area to bind the
textbox to the Master_Data table. However, when I post the entry, none of
the values that appear in the form post to the table.

This is how the Default Value is shown in the textbox-

=[tbl_Master_Data]![Cashier_Name]

I got this value from the Expression Builder, which came up when I clicked
on the ... box next to the Default Value.
I feel like I am almost there. Please let me know what I am doing wrong.

Thanks,

Tom Brown

If you want the text box bound to a field in the form's record source,
then
you use the Default Value property rather than the Control Source. Use
the
control source for the field you want to bind the text box to.

When you use a function as the control source of a text box, it can only
be
an unbound control for display purposes. This is useful when you want to
present a value that is the result of a calculation based on other field
in
the recordset.

When you have a bound control, existing records will display the last
saved
value. But, you can use the Default Value to load the initial value.
That
value will then populate the field in the form's recordset.

It is probably not necessary to use query in the DLookup. The only reason
would be if the field to be returned and the field(s) to filter on are in
different tables. In either case you you use the name of the table or the
query:

=DLookup("[ReturnField]", "TableOrQueryName", "[Criteria Field] = " &
Me.SomeControl)

[ReturnField] is the name of the field you want to retrieve the value
from.

TableOrQueryName is the name of the table or query that has the field you
need to locate and return the value.

[Criteria Field] is the name of a field you want to use to filter the
return
of the function.

Me.SomeControl is the source of the value you want to match [Criteria
Field]
on.

The example above assumes [Criteria Field] is a numeric field. If it is
not
a numeric field, it is necessary to encluse the source in the correct
dilimiters.
Text:
"[Criteria Field] = '" & Me.SomeControl & "'")
Date:
"[Criteria Field] = #" & Me.SomeControl & "#")

Note: The = is necessary for use in a Default Value or Control Source
property.
--
Dave Hargis, Microsoft Access MVP

Ladies and Gentlemen,

I am trying to create a login screen which uses DLookup in the control
source of a textbox to pull the user location based on the user login
number. I have created a query that pulls the user location from an
underlying table based on the login # which then pulls the correct value
to
the textbox. My question concerns using the Data-Control Source to enter
the DLookup value. The reason I use a textbox and not a combo box is
because the login user will be ONLY in one place, and I don't want a list
to
come up giving the user the opportunity to pull ANY location from a list.
I
guess I am confused about how to have the textbox point to a query using
something OTHER than the Control Source when you need the Control Source
to
point to an area in the bound table.

In control source of textbox:
=DLookup("[BU_Name]","[tbl_users]","[Employee_Number] =
Forms![frm_Login_Page]![txt_E_Number]")

This works at pulling the correct value from the tbl-users table, but
when
posted the entry does not post to the underlying (tbl_Master_Data) table.

Any help will be greatly appreciated.

Thanks,

Tom Brown

 

[Klatuu]

I think yo misread my post, Tom.
You use the control source to bind the control to the record source field.
You use the default value to populate the text box for new records.
--
Dave Hargis, Microsoft Access MVP

Thanks, Dave

I tried your suggestion to use the default value as the area to bind the
textbox to the Master_Data table. However, when I post the entry, none of
the values that appear in the form post to the table.

This is how the Default Value is shown in the textbox-

=[tbl_Master_Data]![Cashier_Name]

I got this value from the Expression Builder, which came up when I clicked
on the ... box next to the Default Value.
I feel like I am almost there. Please let me know what I am doing wrong.

Thanks,

Tom Brown

If you want the text box bound to a field in the form's record source,
then
you use the Default Value property rather than the Control Source. Use
the
control source for the field you want to bind the text box to.

When you use a function as the control source of a text box, it can only
be
an unbound control for display purposes. This is useful when you want to
present a value that is the result of a calculation based on other field
in
the recordset.

When you have a bound control, existing records will display the last
saved
value. But, you can use the Default Value to load the initial value.
That
value will then populate the field in the form's recordset.

It is probably not necessary to use query in the DLookup. The only reason
would be if the field to be returned and the field(s) to filter on are in
different tables. In either case you you use the name of the table or the
query:

=DLookup("[ReturnField]", "TableOrQueryName", "[Criteria Field] = " &
Me.SomeControl)

[ReturnField] is the name of the field you want to retrieve the value
from.

TableOrQueryName is the name of the table or query that has the field you
need to locate and return the value.

[Criteria Field] is the name of a field you want to use to filter the
return
of the function.

Me.SomeControl is the source of the value you want to match [Criteria
Field]
on.

The example above assumes [Criteria Field] is a numeric field. If it is
not
a numeric field, it is necessary to encluse the source in the correct
dilimiters.
Text:
"[Criteria Field] = '" & Me.SomeControl & "'")
Date:
"[Criteria Field] = #" & Me.SomeControl & "#")

Note: The = is necessary for use in a Default Value or Control Source
property.
--
Dave Hargis, Microsoft Access MVP

Ladies and Gentlemen,

I am trying to create a login screen which uses DLookup in the control
source of a textbox to pull the user location based on the user login
number. I have created a query that pulls the user location from an
underlying table based on the login # which then pulls the correct value
to
the textbox. My question concerns using the Data-Control Source to enter
the DLookup value. The reason I use a textbox and not a combo box is
because the login user will be ONLY in one place, and I don't want a list
to
come up giving the user the opportunity to pull ANY location from a list.
I
guess I am confused about how to have the textbox point to a query using
something OTHER than the Control Source when you need the Control Source
to
point to an area in the bound table.

In control source of textbox:
=DLookup("[BU_Name]","[tbl_users]","[Employee_Number] =
Forms![frm_Login_Page]![txt_E_Number]")

This works at pulling the correct value from the tbl-users table, but
when
posted the entry does not post to the underlying (tbl_Master_Data) table.

Any help will be greatly appreciated.

Thanks,

Tom Brown