Assigning a value to a field based on other fields value

[Joe]

I have a form in datasheet view with 2 fields; Part # and Part
Description. The Part # field is bound to a table called Temp (which
is bound to the form). This field is setup in the table to be a combo
box value that looks in my Parts table so users can select the part #
from a drop down list.

What I would like to do is have the Part Description field auto
populate with the corresponding description when the Part # is
selected on the form (which is datasheet view, if that matters)

I have tried to set the default value to a SQL statement and tried to
run VB docmd.runsql (x) code on the After Update event of the Part #
field to no avail.

Any thoughts on how I can get this to work?

Thanks

 

[Jeff Boyce]

It isn't clear whether you are trying to get a copy of the "description"
added into a second table, or if you are trying to see the parts'
descriptions in your form, after selecting a part.

If the latter, that's quite do-able. If the former, stop now!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joe]

It isn't clear whether you are trying to get a copy of the "description"
added into a second table, or if you are trying to see the parts'
descriptions in your form, after selecting a part.

If the latter, that's quite do-able. If the former, stop now!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Just trying to make Descriptions visible on the form, no need to save
the value.

Solution?

Thanks

 

[Jeff Boyce]

In the AfterUpdate event of the combobox, add something like:

Me!txtDescription = Me!cboYourCombobox.Column(n)\

where "n" is the zero-based count of the column from your combobox's source
that holds the description.

NOTE1: the query you use to 'feed' the combobox will have to include the
description you want to use
NOTE2: using a datasheet view might cause more problems than it solves.
You can use a continuous form and make it look a lot like a datasheet, but
consider, if your users "see a spreadsheet", won't they expect it to behave
like one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joe]

In the AfterUpdate event of the combobox, add something like:

Me!txtDescription = Me!cboYourCombobox.Column(n)\

where "n" is the zero-based count of the column from your combobox's source
that holds the description.

NOTE1: the query you use to 'feed' the combobox will have to include the
description you want to use
NOTE2: using a datasheet view might cause more problems than it solves.
You can use a continuous form and make it look a lot like a datasheet, but
consider, if your users "see a spreadsheet", won't they expect it to behave
like one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

That works but as you mentioned "sing a datasheet view might cause
more problems than it solves". In particular each new row wants to
use the most recent associated Description that is associated so that
each row ends up displaying the exact same description.

Using a continuous form does not seem like the right solution as only
1 record is visible at a time. The form I am working on is actually a
subform that is intended to show multiple rows.

Not sure if it is possible to get the description to vary from one row
to the next. This is not critical but would be very helpful. What do
you think? Give it up? Or worth trying a different approach?

Thanks again.!

 

[John W. Vinson]

Using a continuous form does not seem like the right solution as only
1 record is visible at a time. The form I am working on is actually a
subform that is intended to show multiple rows.

Ummm...

The whole POINT of continuous forms is that they show multiple records at one
time.

You may need to open the form you're using as the subform and shrink it down
to one row tall (or as short vertically as required for your data). You will
then see as many rows as will fit into the Subform Control on the mainform.

 

[Joe]

Ummm...

The whole POINT of continuous forms is that they show multiple records at one
time.

You may need to open the form you're using as the subform and shrink it down
to one row tall (or as short vertically as required for your data). You will
then see as many rows as will fit into the Subform Control on the mainform.

The continuous form doesn't seem to work as it continually cycles
through the same record. The datasheet view always tabs down to a new
record. My Sub Forms "Cycle" property is set to "All Records".
Perhaps the issue is that there are no records to cycle through as I
am using the sub form to add new values?

 

[John W. Vinson]

The continuous form doesn't seem to work as it continually cycles
through the same record. The datasheet view always tabs down to a new
record. My Sub Forms "Cycle" property is set to "All Records".
Perhaps the issue is that there are no records to cycle through as I
am using the sub form to add new values?

Is the form's Data Entry property set to Yes? If so set it to No.

If not, what is the Recordsource property of the form?

Obviously if there are no records to cycle through then you'll... see no
records. I'm not sure what you're saying here.

 

[Joe]

Is the form's Data Entry property set to Yes? If so set it to No.

If not, what is the Recordsource property of the form?

Obviously if there are no records to cycle through then you'll... see no
records. I'm not sure what you're saying here.

John

I tried the continuous form and set the Data Entry property to No but
still experienced the same issue, which is, once you select a Part #
on one row of data (continuous form or datasheet view) Access repeast
the most recently selected part # to display in the Description field
regardless of what other values are stored in the subsequent record
field.

To work around this what I did is to change the Part # field combo box
query to display he Part Description as field #1 and Part # as field #
2 and set the field to be bound to column #2. This way the user can
type in descriptions but save the Part #.

There may be a better way but this will suffice, thanks for your
assistance.

Joe