Look up field

[Guest]

Hello,

I am trying to do something by using the most simple technique possible.

I have two tables in the database. I have an input form which populates
table one.
On this input form there is a client code to be inputed. Table two has the
list of client codes and their text decription.

How could one make the long text decription come up on the input form after
having input the client code? This is the only field used from table two.

I would prefer not to use queries. In excell we would hae used the lookup
function?

Any suggestions?

Thanks

Speedy

 

[EeOr]

You can use the Dlookup function in Access for this. Just put this code
into an unbound textbox:

=DLookUp("[description]","table2","[client code] = " & [client code]) -- the
first client code is the one from table 2 the second one is the one on your
form incase they are named different.

Hth

Jon

 

[Guest]

Hi Speedy,

How long is the text for "the long text decription"? The reason I ask is
that if it is not too long (like say 50 characters max.), then you can create
a combo box that allows your user to pick this text directly. The client code
would be stored in the child table, but your user would not be forced to
remember (possibly) obscure client codes.

If this is the case, have a combo box with a row source that includes the
following SQL Statement. The Row Source property is found on the Data tab of
the properties dialog, when you have the combo box selected:

SELECT [ClientCode], [TextDesc] FROM [TableName] ORDER BY [TextDesc]

where ClientCode is the name of your Client Code field, TextDesc is the name
of your field with the lengthy text description, and TableName is the name of
your table. Note: The square brackets are required only if you have used
special characters, such as spaces, hyphens, etc., or reserved words. The
bound column should be the default = 1. Also on the Data tab, the Control
Source needs to be the foreign key field from the first table, where you will
store ClientCode. The data type for this field must be the same, with the
same field size, as the ClientCode in your Table2. If you have used an
Autonumber in your Table2, then specify Number / Long Integer in the foreign
key field.

On the Format tab of the combo box properties dialog, set the column count
to 2, and column widths to 0"; 2" (or however wide is required to view the
text).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Yes Combo box will work here. Length < 50.

Thaks,

SPeedy

Hi Speedy,

How long is the text for "the long text decription"? The reason I ask is
that if it is not too long (like say 50 characters max.), then you can create
a combo box that allows your user to pick this text directly. The client code
would be stored in the child table, but your user would not be forced to
remember (possibly) obscure client codes.

If this is the case, have a combo box with a row source that includes the
following SQL Statement. The Row Source property is found on the Data tab of
the properties dialog, when you have the combo box selected:

SELECT [ClientCode], [TextDesc] FROM [TableName] ORDER BY [TextDesc]

where ClientCode is the name of your Client Code field, TextDesc is the name
of your field with the lengthy text description, and TableName is the name of
your table. Note: The square brackets are required only if you have used
special characters, such as spaces, hyphens, etc., or reserved words. The
bound column should be the default = 1. Also on the Data tab, the Control
Source needs to be the foreign key field from the first table, where you will
store ClientCode. The data type for this field must be the same, with the
same field size, as the ClientCode in your Table2. If you have used an
Autonumber in your Table2, then specify Number / Long Integer in the foreign
key field.

On the Format tab of the combo box properties dialog, set the column count
to 2, and column widths to 0"; 2" (or however wide is required to view the
text).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hello,

I am trying to do something by using the most simple technique possible.

I have two tables in the database. I have an input form which populates
table one.
On this input form there is a client code to be inputed. Table two has the
list of client codes and their text decription.

How could one make the long text decription come up on the input form after
having input the client code? This is the only field used from table two.

I would prefer not to use queries. In excell we would hae used the lookup
function?

Any suggestions?

Thanks

Speedy

 

[Guest]

Thanks.

You can use the Dlookup function in Access for this. Just put this code
into an unbound textbox:

=DLookUp("[description]","table2","[client code] = " & [client code]) -- the
first client code is the one from table 2 the second one is the one on your
form incase they are named different.

Hth

Jon

Hello,

I am trying to do something by using the most simple technique possible.

I have two tables in the database. I have an input form which populates
table one.
On this input form there is a client code to be inputed. Table two has
the
list of client codes and their text decription.

How could one make the long text decription come up on the input form
after
having input the client code? This is the only field used from table two.

I would prefer not to use queries. In excell we would hae used the lookup
function?

Any suggestions?

Thanks

Speedy

 

[John W. Vinson]

I would prefer not to use queries. In excell we would hae used the lookup
function?

Just a comment:

Queries are as essential to the productive use of Access as expressions in
cells are to productive use of Excel.

If you avoid using queries, you'll lose 90% of the benefit of using Access.
Get to know and use queries; they'll open a whole new way of thinking about
data for you.

John W. Vinson [MVP]