Set value of one control based on value of another

  • Thread starter Thread starter Andi
  • Start date Start date
A

Andi

If anyone could help me I would sure be appreciative. I
am creating a form and have set the data source to all of
the fields in one of the talbes in my DB. There are fifty
fields that each allow input of an inventory number.
There will be a control attached to each one of these
fields on my form for data input. I would like another
control for each that reflects the description attached
to the item number entered in the first of the two
controls. I would like to have this description looked
up from another of the tables in my DB that contains two
fields in each record: Description and ItemNumber. I
have created the first control bound to one of the item
number fields in the "PickList" table. I have created a
second unbound control and am trying to use a DLookup
function to look up the field's value (Description) from
the Item table.

Am I going about this in the correct way?

If so, Is there a problem with the syntax of my DLookup:

=DLookUp("[Description]","ItemNumbers","[ItemNumber]=
[Forms!FrmPickList!CntItem1]")

If I try adding the ItemNumber table to the data source
for my form, I end up with a blank form when I go to
record view. I am assuming this means I have a faulty
relationship or none specified where I should. So, I
removed that table from the SQL statement and now I just
end up with nothing in the Description control.

I am a beginner with Access and figure I am missing
something basic, but have found no help in several books
and/or postings on DLookup syntax.

Can anyone help?

Thanks in advance,

Andi
 
Hi Andi

Have you set the description controls datasource to your DLookup function
If so, I would consider maybe issuing a requery command when the PickList field changes (myPickListField.OnChange event) or even putting the DLookup function in this event

Private Sub myPickListField1_Change(

'Update description contro
Me.myDescription1.Requer

End Su

myPickListField1 is a textbox control, and myDescription1 is also a textbox

This means that everytime the PickList field changes, it will requery your db to update the description field

Let me know how it goes

Hope this helps
Tra

----- Andi wrote: ----

If anyone could help me I would sure be appreciative. I
am creating a form and have set the data source to all of
the fields in one of the talbes in my DB. There are fifty
fields that each allow input of an inventory number.
There will be a control attached to each one of these
fields on my form for data input. I would like another
control for each that reflects the description attached
to the item number entered in the first of the two
controls. I would like to have this description looked
up from another of the tables in my DB that contains two
fields in each record: Description and ItemNumber. I
have created the first control bound to one of the item
number fields in the "PickList" table. I have created a
second unbound control and am trying to use a DLookup
function to look up the field's value (Description) from
the Item table

Am I going about this in the correct way

If so, Is there a problem with the syntax of my DLookup

=DLookUp("[Description]","ItemNumbers","[ItemNumber]
[Forms!FrmPickList!CntItem1]")

If I try adding the ItemNumber table to the data source
for my form, I end up with a blank form when I go to
record view. I am assuming this means I have a faulty
relationship or none specified where I should. So, I
removed that table from the SQL statement and now I just
end up with nothing in the Description control

I am a beginner with Access and figure I am missing
something basic, but have found no help in several books
and/or postings on DLookup syntax

Can anyone help

Thanks in advance

And
 
Oops, found a problem

I wouldn't suggest using the OnChange event (now that I have tested - doh), better to use the OnLostFocus event which will retrieve the description once you leave the control

hth
Tra
 
Andi said:
If anyone could help me I would sure be appreciative. I
am creating a form and have set the data source to all of
the fields in one of the talbes in my DB. There are fifty
fields that each allow input of an inventory number.
There will be a control attached to each one of these
fields on my form for data input. I would like another
control for each that reflects the description attached
to the item number entered in the first of the two
controls. I would like to have this description looked
up from another of the tables in my DB that contains two
fields in each record: Description and ItemNumber. I
have created the first control bound to one of the item
number fields in the "PickList" table. I have created a
second unbound control and am trying to use a DLookup
function to look up the field's value (Description) from
the Item table.

Am I going about this in the correct way?

If so, Is there a problem with the syntax of my DLookup:

=DLookUp("[Description]","ItemNumbers","[ItemNumber]=
[Forms!FrmPickList!CntItem1]")

If I try adding the ItemNumber table to the data source
for my form, I end up with a blank form when I go to
record view. I am assuming this means I have a faulty
relationship or none specified where I should. So, I
removed that table from the SQL statement and now I just
end up with nothing in the Description control.

I am a beginner with Access and figure I am missing
something basic, but have found no help in several books
and/or postings on DLookup syntax.

Can anyone help?

Thanks in advance,

Andi
Click to expand...
Try this.

=DLookUp("[Description]","ItemNumbers","[ItemNumber]="
& [Forms!FrmPickList!CntItem1] & ")"

Having the control Forms!FrmPickList!CntItem1 within quotes
makes it part of the overall string and does not allow VBA
to access the variable data in the control.

Having said that, did you look at the combo box and the list box?
Using these controls vs two text boxes is a better approach.
Also you should normalizing you database design. It will save you
from all kinds of problems in the future.

Ron
 
Tray,

Thank you for the suggestions! Unfortunately, I am still
unable to set the value of the description field on the
basis of the Item1 field. I had originally set the data
source property on the description control to the dlookup
function (you were correct). However, when I do this, the
control gives me an #ERROR mesage. I have since tried
entering the function in the OnEnter and the OnGotFocus
properties and still come up with a blank control. When I
tried putting a macro to SetValue on the Description
Contol to the DLookup function as an AfterUpdate or
LostFocus property of the Item1 control, I get a visual
basic error telling me the SetValue Action failed.

This seems like such a simple thing for Access to do, I
figure I must be missing something obvious. Any other
thoughts?
-----Original Message-----

Hi Andi,

Have you set the description controls datasource to your DLookup function?
If so, I would consider maybe issuing a requery command
Click to expand...
when the PickList field changes (myPickListField.OnChange
event) or even putting the DLookup function in this event.
Private Sub myPickListField1_Change()

'Update description control
Me.myDescription1.Requery

End Sub

myPickListField1 is a textbox control, and myDescription1 is also a textbox.

This means that everytime the PickList field changes, it
Click to expand...
will requery your db to update the description field.
Let me know how it goes.

Hope this helps.
Tray


----- Andi wrote: -----

If anyone could help me I would sure be appreciative. I
am creating a form and have set the data source to all of
the fields in one of the talbes in my DB. There are fifty
fields that each allow input of an inventory number.
There will be a control attached to each one of these
fields on my form for data input. I would like another
control for each that reflects the description attached
to the item number entered in the first of the two
controls. I would like to have this description looked
up from another of the tables in my DB that contains two
fields in each record: Description and ItemNumber. I
have created the first control bound to one of the item
number fields in the "PickList" table. I have created a
second unbound control and am trying to use a DLookup
function to look up the field's value (Description) from
the Item table.

Am I going about this in the correct way?

If so, Is there a problem with the syntax of my DLookup:

=DLookUp("[Description]","ItemNumbers","[ItemNumber]=
[Forms!FrmPickList!CntItem1]")

If I try adding the ItemNumber table to the data source
for my form, I end up with a blank form when I go to
record view. I am assuming this means I have a faulty
relationship or none specified where I should. So, I
removed that table from the SQL statement and now I just
end up with nothing in the Description control.

I am a beginner with Access and figure I am missing
something basic, but have found no help in several books
and/or postings on DLookup syntax.

Can anyone help?

Thanks in advance,

Andi

.
Click to expand...
 
Hey Ron,

Thanks for the suggestion. When I enter the syntax just
as you wrote it, Access tells me the expression is mssing
a closing paranthesis or bracket (doesn't seem to
recognize the one between the " " s). If I remove
the " " around that paranthesis, I get a #NAME error.

I don't want to use a double list or combo box as the
Item # is a bound field to enter that item number in a
pick list table wherease I don't need the description in
the pick list table. The description is already
associated with the item in the ItemNumber table and I
just want it displayed here in the form.

Andi said:
Try this.

=DLookUp("[Description]","ItemNumbers","[ItemNumber]="
& [Forms!FrmPickList!CntItem1] & ")"

Having the control Forms!FrmPickList!CntItem1 within quotes
makes it part of the overall string and does not allow VBA
to access the variable data in the control.

Having said that, did you look at the combo box and the list box?
Using these controls vs two text boxes is a better approach.
Also you should normalizing you database design. It will save you
from all kinds of problems in the future.

Ron

--
Ronald W. Roberts
Roberts Communication
(e-mail address removed)
To reply remove "_at_robcom_dot_com"

.
Click to expand...
 
Back
Top