Creating a populated field

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Im building a form and in the subform I want a "description" field to be
populated when I enter a "Part Number" in the field before it. I have a
separate table listing part numbers and their descriptions. It's been a
while and my memory is a bit rusty. Im working in Access 2000.

Thanx for your help
Tom
 
Im building a form and in the subform I want a "description" field to be
populated when I enter a "Part Number" in the field before it. I have a
separate table listing part numbers and their descriptions. It's been a
while and my memory is a bit rusty. Im working in Access 2000.

Thanx for your help
Tom
Click to expand...

You could put a dlookup( ) in the afterUpdate event of the Part Number
textbox/comboBox.

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")
 
Guess I should have been clearer.. I want the "Description" in the
description filed while keeping the part Number in the "part Number" field

OldPro said:
Im building a form and in the subform I want a "description" field to be
populated when I enter a "Part Number" in the field before it. I have a
separate table listing part numbers and their descriptions. It's been a
while and my memory is a bit rusty. Im working in Access 2000.

Thanx for your help
Tom
Click to expand...

You could put a dlookup( ) in the afterUpdate event of the Part Number
textbox/comboBox.

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")
Click to expand...
 
Guess I should have been clearer.. I want the "Description" in the
description filed while keeping the part Number in the "part Number" field



You could put a dlookup( ) in the afterUpdate event of the Part Number
textbox/comboBox.
Click to expand...
txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That shouldn't change anything. Once the textbox has the description,
and if the textbox is linked to the field, then when the record is
updated, the description in the textbox will be saved to the field.
 
Tom said:
Guess I should have been clearer.. I want the "Description" in the
description filed while keeping the part Number in the "part Number" field

OldPro said:
Im building a form and in the subform I want a "description" field to
be
populated when I enter a "Part Number" in the field before it. I have
a
separate table listing part numbers and their descriptions. It's been
a
while and my memory is a bit rusty. Im working in Access 2000.

Thanx for your help
Tom
Click to expand...

You could put a dlookup( ) in the afterUpdate event of the Part Number
textbox/comboBox.

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")
Click to expand...
Click to expand...

OldPro's solution will work, so long as you pass the arguments in the right
order. Instead of:

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")

use this:

txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")
 
Guess I should have been clearer.. I want the "Description" in the
description filed while keeping the part Number in the "part Number" field
Click to expand...

Well... no, you almost certainly don't.

Relational databases use the Grandmother's Pantry Principle: "A place - ONE
place! - for everything, everything in its place". The description field
should exist in the Parts table *AND NOPLACE ELSE*; storing it redundantly in
your other table wastes space and (more importantly) leaves you vulnerable to
data errors. If you have multiple copies of the description, any of these
copies could be edited, leaving you with multiple descriptions for the same
part!

Just store the Part Number, and *DISPLAY* the description as needed.

John W. Vinson [MVP]
 
Well... I've entered the code in the After update in the parts filed. When I
enter a part nmbr I get a message telling me the macro does not exist or
hasnt been saved.

Not sure what I should be doing here..

Stuart McCall said:
Tom said:
Guess I should have been clearer.. I want the "Description" in the
description filed while keeping the part Number in the "part Number" field

OldPro said:
Im building a form and in the subform I want a "description" field to
be
populated when I enter a "Part Number" in the field before it. I have
a
separate table listing part numbers and their descriptions. It's been
a
while and my memory is a bit rusty. Im working in Access 2000.

Thanx for your help
Tom

You could put a dlookup( ) in the afterUpdate event of the Part Number
textbox/comboBox.

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")
Click to expand...
Click to expand...

OldPro's solution will work, so long as you pass the arguments in the right
order. Instead of:

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")

use this:

txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")
Click to expand...
 
Actually, he will need to remove the added space between the single and
double quotes before and after the reference to txtPartNo as well.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Stuart McCall said:
Tom said:
Guess I should have been clearer.. I want the "Description" in the
description filed while keeping the part Number in the "part Number" field

OldPro said:
Im building a form and in the subform I want a "description" field to
be
populated when I enter a "Part Number" in the field before it. I have
a
separate table listing part numbers and their descriptions. It's been
a
while and my memory is a bit rusty. Im working in Access 2000.

Thanx for your help
Tom

You could put a dlookup( ) in the afterUpdate event of the Part Number
textbox/comboBox.

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")
Click to expand...
Click to expand...

OldPro's solution will work, so long as you pass the arguments in the right
order. Instead of:

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")

use this:

txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")
Click to expand...
 
Well... I've entered the code in the After update in the parts filed. When I
enter a part nmbr I get a message telling me the macro does not exist or
hasnt been saved.

Not sure what I should be doing here..
Click to expand...

Don't use the AfterUpdate property of the table.field, use the
AfterUpdate property of the textbox that is linked to the field(on the
form).
 
txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")

This is the code I'm using. It is in the "after Update" on the "partNmbr"
box on the form.

Now I'm getting "The expression After Update that you entered as the event
property setting produced the following error: The object doesn't contain
the automation object 'textDescription'

Dale Fye said:
Actually, he will need to remove the added space between the single and
double quotes before and after the reference to txtPartNo as well.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Stuart McCall said:
Tom said:
Guess I should have been clearer.. I want the "Description" in the
description filed while keeping the part Number in the "part Number" field

:

Im building a form and in the subform I want a "description" field to
be
populated when I enter a "Part Number" in the field before it. I have
a
separate table listing part numbers and their descriptions. It's been
a
while and my memory is a bit rusty. Im working in Access 2000.

Thanx for your help
Tom

You could put a dlookup( ) in the afterUpdate event of the Part Number
textbox/comboBox.

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")
Click to expand...

OldPro's solution will work, so long as you pass the arguments in the right
order. Instead of:

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")

use this:

txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")
Click to expand...
Click to expand...
 
Tom,

You need to replace "txtDescription" with the name of the control where you
want to display the parts description. Although I agree with John Vinson,
this should be an unbound control, so that you are not writing the same part
description in multiple places in your database.

Assuming that your PartNo field is a text field, then there are actually
three extra spaces in the DLOOKUP function below. I will copy what you are
using now, and right below it I will but an X in the spaces that needs to be
deleted. Compare the two and correct your code accordingly.

txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")

txtDescription = dlookup("Description", "tblParts", "[PartNo] = 'X" &
txtPartNo & "X'X")

HTH
Dale

Tom said:
txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")

This is the code I'm using. It is in the "after Update" on the "partNmbr"
box on the form.

Now I'm getting "The expression After Update that you entered as the
event
property setting produced the following error: The object doesn't contain
the automation object 'textDescription'

Dale Fye said:
Actually, he will need to remove the added space between the single and
double quotes before and after the reference to txtPartNo as well.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Stuart McCall said:
Guess I should have been clearer.. I want the "Description" in the
description filed while keeping the part Number in the "part Number"
field

:

Im building a form and in the subform I want a "description" field
to
be
populated when I enter a "Part Number" in the field before it. I
have
a
separate table listing part numbers and their descriptions. It's
been
a
while and my memory is a bit rusty. Im working in Access 2000.

Thanx for your help
Tom

You could put a dlookup( ) in the afterUpdate event of the Part
Number
textbox/comboBox.

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")

OldPro's solution will work, so long as you pass the arguments in the
right
order. Instead of:

txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")

use this:

txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")
Click to expand...
Click to expand...
Click to expand...
 
txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")

This is the code I'm using. It is in the "after Update" on the "partNmbr"
box on the form.

Now I'm getting "The expression After Update that you entered as the event
property setting produced the following error: The object doesn't contain
the automation object 'textDescription'



Dale Fye said:
Actually, he will need to remove the added space between the single and
double quotes before and after the reference to txtPartNo as well.
Click to expand...
Dale
Click to expand...
Email address is not valid.
Please reply to newsgroup only.
Click to expand...
Guess I should have been clearer.. I want the "Description" in the
description filed while keeping the part Number in the "part Number" field
:
Im building a form and in the subform I want a "description" field to
be
populated when I enter a "Part Number" in the field before it. I have
a
separate table listing part numbers and their descriptions. It's been
a
while and my memory is a bit rusty. Im working in Access 2000.
Thanx for your help
Tom
You could put a dlookup( ) in the afterUpdate event of the Part Number
textbox/comboBox.
txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")
OldPro's solution will work, so long as you pass the arguments in the right
order. Instead of:
txtDescription = dlookup( "tblParts","Description","[PartNo] = ' " &
txtPartNo & " ' ")
use this:
txtDescription = dlookup("Description", "tblParts", "[PartNo] = ' " &
txtPartNo & " ' ")- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

It's probably a spelling error; change "txtDescription" to the name of
your textbox that displays the description.
 
Back
Top