Unlinked Tables 2

[tlove161]

I am building a form which will be used to enter simple item descriptions. I have a question about two of the fields. When the [ItemNumber] field is entered on the form I want to automatically fill in the [ItemDescription] field on the form. The item descriptions are contained in an unrelated table.

For example, item number 20xxx is a "40' Box Car Single Door". 20xxx is in the [ItemSeries] field in the unrelated table. Item numbers in this series could range from 20001 to 20999, but they would all be 40' Box Car Single Door".

What do I need to put in the [ItemDescription] field on the form in order to accomplish this? I am stumped.

 

[hsotb]

This would go in the Control Source.

=[ItemDescription].Column(1)

 

[tlove161]

hsotb-

What does the .Column(1) part mean? Thanks

 

[jksmi]

Hi,

I've made a couple of assumptions, your numbers will start from 1001 and you won't store this part of the description in the items table (as its already normalised).

The relationship between the 2 tables as I see it is: the integer of (item number divided by 1000) is equal to the integer of (item series divided by 1000), i.e. Int([ITEM_NUMBER]/1000 = Int([ITEM_SERIES]/1000).

So you can now create a look up query along the lines of...

SELECT ITEMS.ITEM_NUMBER, ITEM_DESC.DESCRIPTION
FROM ITEM_DESC, ITEMS
WHERE Int([ITEMS]![ITEM_NUMBER]/1000))=Int([ITEM_DESC]![ITEM_SERIES]/1000)
GROUP BY ITEMS.ITEM_NUMBER, ITEM_DESC.DESCRIPTION;

In your form you can set the control source to look up DESCRIPTION from this query using a dlookup, i.e. in control source

=DLookup("DESCRIPTION","LU_ITEM_DESC","[ITEM_NUMBER] = " & txtItemNumber)

alternatively you could go straight in with the dlookup to your item description table, in which case the dlookup would look something like...

]=DLookup("DESCRIPTION","ITEM_DESC","[ITEM_SERIES] = " & Int(CLng(txtItemNumber)/1000))

but the look up query will allways come in handy for reports, other forms, etc...

HTH, Jamie
FAQ219-2884

 

[jksmi]

Oh, I guess I did make a further assumption - that you had normalised you item description table so that there was only 1 record for every 1000 items (i.e. item series 20000 is 40' Box Car Single Door, item series 20000 to 20999 do not exist - next item series is 30000)

HTH, Jamie
FAQ219-2884

 

[tlove161]

Jamie-

That is correct about the item description table. It is normalized. Thank you for the help.

 

[tlove161]

Jamie-

I have two questions for you. I am using the DLookup function but I have to hit F9 to get it to populate the Description field on the form.

It is also not populating the table after F9. Here is the function:

=DLookUp("Description","tblProdItemCodes","Series = " & Form!ItemNumber)

What do I need to do so I do not have to hit F9 to populate the field on the form and to populate the Description field on the table. Thanks

 

[PHV]

Have you tried to call the ReCalc method of your form ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

To add to PHV's suggestion, what is "Form!ItemNumber" referring too?

One usually uses either only the control name (if the control resides in the same form) or a fully qualified reference:

[tt]..."Series = " & [ItemNumber])[/tt]

or

[tt]..."Series = " & Forms![frmYourFormName]![ItemNumber])[/tt]

Roy-Vidar