query table

[ryan.fitzpatrick3]

I have a table that has item numbers and item descriptions, within it
there are thousands of item numbers. I have a form where I type in the
item # into a text box, is there away for the description of that item
to populate in the item description text box? Thanks in advance.

ryan

 

[fredg]

I have a table that has item numbers and item descriptions, within it
there are thousands of item numbers. I have a form where I type in the
item # into a text box, is there away for the description of that item
to populate in the item description text box? Thanks in advance.

ryan

You just wish to display the description, not edit it?
[ItemNumber] is a Number datatype field, not Text?
There are several ways. Here is a simple one:

Use an Unbound text control.
Set it's control source to:
=DLookUp("[ItemDescription]","TableName","[ItemNumber] = " &
Me.[txtControlName])

 

[ryan.fitzpatrick3]

Thanks.

In the table I have itemname and itemdescription in tblcomponent.

=DLookUp("[ItemDescription]","TableName","[ItemNumber] = " & Me.
[txtControlName])

so it should look like

=DLookUp("[ItemDescription]","tblcomponent","[ItemNumber] = " & Me.
[txtControlName])

what goes in txtcontrolname? The unbound tbox name where the
itemdescription is supposed to go is

tboxItemDescription

To be clear the 1st text box itemname, once item number is entered in

the textbox, "itemdescription" will populate the item description
correct?

Ryan

I have a table that has item numbers and item descriptions, within it
there are thousands of item numbers. I have a form where I type in the
item # into a text box, is there away for the description of that item
to populate in the item description text box? Thanks in advance.

ryan

You just wish to display the description, not edit it?
[ItemNumber] is a Number datatype field, not Text?
There are several ways. Here is a simple one:

Use an Unbound text control.
Set it's control source to:
=DLookUp("[ItemDescription]","TableName","[ItemNumber] = " &
Me.[txtControlName])

 

[fredg]

Thanks.

In the table I have itemname and itemdescription in tblcomponent.

=DLookUp("[ItemDescription]","TableName","[ItemNumber] = " & Me.
[txtControlName])

so it should look like

=DLookUp("[ItemDescription]","tblcomponent","[ItemNumber] = " & Me.
[txtControlName])

what goes in txtcontrolname? The unbound tbox name where the
itemdescription is supposed to go is

tboxItemDescription

To be clear the 1st text box itemname, once item number is entered in

the textbox, "itemdescription" will populate the item description
correct?

Ryan

I have a table that has item numbers and item descriptions, within it
there are thousands of item numbers. I have a form where I type in the
item # into a text box, is there away for the description of that item
to populate in the item description text box? Thanks in advance.

ryan

You just wish to display the description, not edit it?
[ItemNumber] is a Number datatype field, not Text?
There are several ways. Here is a simple one:

Use an Unbound text control.
Set it's control source to:
=DLookUp("[ItemDescription]","TableName","[ItemNumber] = " &
Me.[txtControlName])

This is from your original message:

I have a form where I type in the item # into a text box<

Replace [txtControlName] with whatever the actual control name is of
that unbound control on your form into which you enter the wanted
ItemNumber.

 

[NetworkTrade]

or just use a combobox sourced on a query that has those two fields.... the
item number can be the first column that one selects/types...

then hang an unbound text box next to it: =ComboBox.Column(1)

assuming the first column is 0

any time you make a change in the combobox to a new Id - the unbound textbox
will show the description....

 

[ryan.fitzpatrick3]

This is what I have

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
me.SupplierNumber)

name is the itemdescription field,
tblSSIMSVendors is the table
ITEMNUM is itemnumber
SupplierNumber is the control source of the textbox where I input the
item code.

It shows up as a #NAME.

Thanks.

In the table I have itemname and itemdescription in tblcomponent.

=DLookUp("[ItemDescription]","TableName","[ItemNumber] = " & Me.
[txtControlName])

so it should look like

=DLookUp("[ItemDescription]","tblcomponent","[ItemNumber] = " & Me.
[txtControlName])

what goes in txtcontrolname? The unbound tbox name where the
itemdescription is supposed to go is

To be clear the 1st text box itemname, once item number is entered in

the textbox, "itemdescription" will populate the item description
correct?

Ryan

I have a table that has item numbers and item descriptions, within it
there are thousands of item numbers. I have a form where I type in the
item # into a text box, is there away for the description of that item
to populate in the item description text box? Thanks in advance.
ryan
You just wish to display the description, not edit it?
[ItemNumber] is a Number datatype field, not Text?
There are several ways. Here is a simple one:
Use an Unbound text control.
Set it's control source to:
=DLookUp("[ItemDescription]","TableName","[ItemNumber] = " &
Me.[txtControlName])

This is from your original message:

I have a form where I type in the item # into a text box<

Replace [txtControlName] with whatever the actual control name is of
that unbound control on your form into which you enter the wanted
ItemNumber.

 

[fredg]

On Thu, 10 Apr 2008 16:04:05 -0700 (PDT),

This is what I have

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
me.SupplierNumber)

name is the itemdescription field,
tblSSIMSVendors is the table
ITEMNUM is itemnumber
SupplierNumber is the control source of the textbox where I input the
item code.

It shows up as a #NAME.

Ryan,
I beg your pardon. I did suggest you use an unbound text control to
place the DLookUp in, but then I gave you the syntax to use as though
you were writing VBA code (using Me.[txtControlName]).
My goof.
The Me. keyword can be used when writing code. It is NOT recognized by
the Access Jet engine when used in an Access control source.
So here is the correct syntax, using your above field and control
names:

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

You must make sure the name of this control is NOT the same as the
name of any field used in the above expression.

Now I see an additional problem with you using "Name" as the name of
your Item Description field.

"Name" is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article

286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

So change the name of the [Name] field to something else, i.e.
[ItemName], and make sure that the control's name is not the same as
any of the fields used in it's control source and all should be well.

 

[ryan.fitzpatrick3]

Thanks Fred for your time and help.

Now I make this an unbound text box the text box where I input a item
number? If I want that number to go into a table will it still go in
there?

Instead of #NAME I get #ERROR with this code
=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

This is what I have

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
me.SupplierNumber)

name is the itemdescription field,
tblSSIMSVendors is the table
ITEMNUM is itemnumber
SupplierNumber is the control source of the textbox where I input the
item code.

It shows up as a #NAME.

Ryan,
I beg your pardon. I did suggest you use an unbound text control to
place the DLookUp in, but then I gave you the syntax to use as though
you were writing VBA code (using Me.[txtControlName]).
My goof.
The Me. keyword can be used when writing code. It is NOT recognized by
the Access Jet engine when used in an Access control source.
So here is the correct syntax, using your above field and control
names:

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

You must make sure the name of this control is NOT the same as the
name of any field used in the above expression.

Now I see an additional problem with you using "Name" as the name of
your Item Description field.

"Name" is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article

286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html

So change the name of the [Name] field to something else, i.e.
[ItemName], and make sure that the control's name is not the same as
any of the fields used in it's control source and all should be well.

 

[fredg]

On Fri, 11 Apr 2008 08:59:12 -0700 (PDT),

Thanks Fred for your time and help.

Now I make this an unbound text box the text box where I input a item
number? If I want that number to go into a table will it still go in
there?

Instead of #NAME I get #ERROR with this code
=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

This is what I have

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
me.SupplierNumber)

name is the itemdescription field,
tblSSIMSVendors is the table
ITEMNUM is itemnumber
SupplierNumber is the control source of the textbox where I input the
item code.

It shows up as a #NAME.

Ryan,
I beg your pardon. I did suggest you use an unbound text control to
place the DLookUp in, but then I gave you the syntax to use as though
you were writing VBA code (using Me.[txtControlName]).
My goof.
The Me. keyword can be used when writing code. It is NOT recognized by
the Access Jet engine when used in an Access control source.
So here is the correct syntax, using your above field and control
names:

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

You must make sure the name of this control is NOT the same as the
name of any field used in the above expression.

Now I see an additional problem with you using "Name" as the name of
your Item Description field.

"Name" is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article

286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html

So change the name of the [Name] field to something else, i.e.
[ItemName], and make sure that the control's name is not the same as
any of the fields used in it's control source and all should be well.

Ryan,
It's not the [SupplierNumber] control that has to be unbound it's the
control into which you have written this DLookUp expression, the one
in which you wish to display the [VENDNAME].

Let's assume you have a record with a [VENDNUM] value of 5.
(Change the 5 to whatever a known valid VendNum number is.)

If you change the DLOOkUp to:

=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = 5")

and make sure the name of this control in neither "VendName" nor
"VendNum", do you get the proper VendName for that value?
You should.
Then go back and re-write the expression to:
=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

where [SupplierNumber] is the name of the control on the form which
displays the correct VendNum. [SupplierNumber] can be bound to a field
in the table which displays the VendNum value, or it can be unbound,
in which case you have to manually enter the number to be looked up.

 

[ryan.fitzpatrick3]

I was reading on dlookups, the vendnum is not an autoID but the
actually vendor number, does this matter?

This is what I have

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
me.SupplierNumber)

name is the itemdescription field,
tblSSIMSVendors is the table
ITEMNUM is itemnumber
SupplierNumber is the control source of the textbox where I input the
item code.

It shows up as a #NAME.

Ryan,
I beg your pardon. I did suggest you use an unbound text control to
place the DLookUp in, but then I gave you the syntax to use as though
you were writing VBA code (using Me.[txtControlName]).
My goof.
The Me. keyword can be used when writing code. It is NOT recognized by
the Access Jet engine when used in an Access control source.
So here is the correct syntax, using your above field and control
names:

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

You must make sure the name of this control is NOT the same as the
name of any field used in the above expression.

Now I see an additional problem with you using "Name" as the name of
your Item Description field.

"Name" is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article

286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html

So change the name of the [Name] field to something else, i.e.
[ItemName], and make sure that the control's name is not the same as
any of the fields used in it's control source and all should be well.

 

[ryan.fitzpatrick3]

I got it Thanks a million!! I think it didn't work because in the
table where I copied the information over from a query, the VENDNUM
was in text I switched to number. Sorry about that, I didn't realize
that until right now.

Thanks Fred for your time and help.

Now I make this an unbound text box the text box where I input a item
number? If I want that number to go into a table will it still go in
there?

Instead of #NAME I get #ERROR with this code
=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

@safeway.com wrote:
This is what I have
=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
me.SupplierNumber)
name is the itemdescription field,
tblSSIMSVendors is the table
ITEMNUM is itemnumber
SupplierNumber is the control source of the textbox where I input the
item code.
It shows up as a #NAME.
Ryan,
I beg your pardon. I did suggest you use an unbound text control to
place the DLookUp in, but then I gave you the syntax to use as though
you were writing VBA code (using Me.[txtControlName]).
My goof.
The Me. keyword can be used when writing code. It is NOT recognized by
the Access Jet engine when used in an Access control source.
So here is the correct syntax, using your above field and control
names:
=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])
You must make sure the name of this control is NOT the same as the
name of any field used in the above expression.
Now I see an additional problem with you using "Name" as the name of
your Item Description field.
"Name" is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
For an even more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html
So change the name of the [Name] field to something else, i.e.
[ItemName], and make sure that the control's name is not the same as
any of the fields used in it's control source and all should be well.

Ryan,
It's not the [SupplierNumber] control that has to be unbound it's the
control into which you have written this DLookUp expression, the one
in which you wish to display the [VENDNAME].

Let's assume you have a record with a [VENDNUM] value of 5.
(Change the 5 to whatever a known valid VendNum number is.)

If you change the DLOOkUp to:

=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = 5")

and make sure the name of this control in neither "VendName" nor
"VendNum", do you get the proper VendName for that value?
You should.
Then go back and re-write the expression to:
=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

where [SupplierNumber] is the name of the control on the form which
displays the correct VendNum. [SupplierNumber] can be bound to a field
in the table which displays the VendNum value, or it can be unbound,
in which case you have to manually enter the number to be looked up.

 

[ryan.fitzpatrick3]

Last question, i think, now that this works! On a new record it shows
#error because there is nothing in the text box where you input a
number, do I make the code like this to make it where the #error
doesn't show on new records?

=NZ(DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber]) ,0)

or is there an iserror function like in excel?

Thanks Fred for your time and help.

Now I make this an unbound text box the text box where I input a item
number? If I want that number to go into a table will it still go in
there?

Instead of #NAME I get #ERROR with this code
=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

@safeway.com wrote:
This is what I have
=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
me.SupplierNumber)
name is the itemdescription field,
tblSSIMSVendors is the table
ITEMNUM is itemnumber
SupplierNumber is the control source of the textbox where I input the
item code.
It shows up as a #NAME.
Ryan,
I beg your pardon. I did suggest you use an unbound text control to
place the DLookUp in, but then I gave you the syntax to use as though
you were writing VBA code (using Me.[txtControlName]).
My goof.
The Me. keyword can be used when writing code. It is NOT recognized by
the Access Jet engine when used in an Access control source.
So here is the correct syntax, using your above field and control
names:
=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])
You must make sure the name of this control is NOT the same as the
name of any field used in the above expression.
Now I see an additional problem with you using "Name" as the name of
your Item Description field.
"Name" is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
For an even more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html
So change the name of the [Name] field to something else, i.e.
[ItemName], and make sure that the control's name is not the same as
any of the fields used in it's control source and all should be well.

Ryan,
It's not the [SupplierNumber] control that has to be unbound it's the
control into which you have written this DLookUp expression, the one
in which you wish to display the [VENDNAME].

Let's assume you have a record with a [VENDNUM] value of 5.
(Change the 5 to whatever a known valid VendNum number is.)

If you change the DLOOkUp to:

=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = 5")

and make sure the name of this control in neither "VendName" nor
"VendNum", do you get the proper VendName for that value?
You should.
Then go back and re-write the expression to:
=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

where [SupplierNumber] is the name of the control on the form which
displays the correct VendNum. [SupplierNumber] can be bound to a field
in the table which displays the VendNum value, or it can be unbound,
in which case you have to manually enter the number to be looked up.

 

[ryan.fitzpatrick3]

Nevermind, I figured it out, I put default value 0, and it got rid of
#error. Thanks again

Thanks Fred for your time and help.

Now I make this an unbound text box the text box where I input a item
number? If I want that number to go into a table will it still go in
there?

Instead of #NAME I get #ERROR with this code
=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

@safeway.com wrote:
This is what I have
=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
me.SupplierNumber)
name is the itemdescription field,
tblSSIMSVendors is the table
ITEMNUM is itemnumber
SupplierNumber is the control source of the textbox where I input the
item code.
It shows up as a #NAME.
Ryan,
I beg your pardon. I did suggest you use an unbound text control to
place the DLookUp in, but then I gave you the syntax to use as though
you were writing VBA code (using Me.[txtControlName]).
My goof.
The Me. keyword can be used when writing code. It is NOT recognized by
the Access Jet engine when used in an Access control source.
So here is the correct syntax, using your above field and control
names:
=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])
You must make sure the name of this control is NOT the same as the
name of any field used in the above expression.
Now I see an additional problem with you using "Name" as the name of
your Item Description field.
"Name" is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
For an even more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html
So change the name of the [Name] field to something else, i.e.
[ItemName], and make sure that the control's name is not the same as
any of the fields used in it's control source and all should be well.

Ryan,
It's not the [SupplierNumber] control that has to be unbound it's the
control into which you have written this DLookUp expression, the one
in which you wish to display the [VENDNAME].

Let's assume you have a record with a [VENDNUM] value of 5.
(Change the 5 to whatever a known valid VendNum number is.)

If you change the DLOOkUp to:

=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = 5")

and make sure the name of this control in neither "VendName" nor
"VendNum", do you get the proper VendName for that value?
You should.
Then go back and re-write the expression to:
=DLookUp("[VENDNAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

where [SupplierNumber] is the name of the control on the form which
displays the correct VendNum. [SupplierNumber] can be bound to a field
in the table which displays the VendNum value, or it can be unbound,
in which case you have to manually enter the number to be looked up.

 

[fredg]

On Fri, 11 Apr 2008 09:22:34 -0700 (PDT),

I was reading on dlookups, the vendnum is not an autoID but the
actually vendor number, does this matter?

This is what I have

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
me.SupplierNumber)

name is the itemdescription field,
tblSSIMSVendors is the table
ITEMNUM is itemnumber
SupplierNumber is the control source of the textbox where I input the
item code.

It shows up as a #NAME.

Ryan,
I beg your pardon. I did suggest you use an unbound text control to
place the DLookUp in, but then I gave you the syntax to use as though
you were writing VBA code (using Me.[txtControlName]).
My goof.
The Me. keyword can be used when writing code. It is NOT recognized by
the Access Jet engine when used in an Access control source.
So here is the correct syntax, using your above field and control
names:

=DLookUp("[NAME]","tblSSIMSVendors","[VENDNUM] = " &
[SupplierNumber])

You must make sure the name of this control is NOT the same as the
name of any field used in the above expression.

Now I see an additional problem with you using "Name" as the name of
your Item Description field.

"Name" is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article

286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html

So change the name of the [Name] field to something else, i.e.
[ItemName], and make sure that the control's name is not the same as
any of the fields used in it's control source and all should be well.

A number is a number (as long as the field's DATATYPE is Number or
Currency), so yes AutoNumber and Number are handled the same in this
instance.
You can have a number, i.e. 123 in a text datatype field, but then
it's text and needs to be surrounded by quotes "123" or '123' whenever
you refer to the value in that field. In your usage the criteria would
look like this:
"[VENDNUM] = '" & me.SupplierNumber & "'")
Just for clarity, the above quotes are like this...
"[VENDNUM] = ' " & me.SupplierNumber & " ' ")