DLookup (Or ELookup)?

I'm designing an Inventory Program. I have an Inventory Table
(tbl_InventoryListing). The Program is driven by Part Numbers. I have a
lookup table (tlu_PartNumbers). I need to design a "Receiving" form that
will add a new record to tbl_InventoryListing table. I've set up a query
containing both tables, linked by the Part Number.

SELECT tbl_InventoryListing.PartNo, tlu_PartNumbers.Nomenclature,
tbl_InventoryListing.NIIN, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.ReceivedDate, tbl_InventoryListing.ReceiptDoc,
tbl_InventoryListing.UnitOfIssue, tbl_InventoryListing.CageCode,
tbl_InventoryListing.SupplySource, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Sponsor,
tbl_InventoryListing.Program, tbl_InventoryListing.Division,
tbl_InventoryListing.Purpose, tbl_InventoryListing.Building,
tbl_InventoryListing.Location, tbl_InventoryListing.WarehouseNo,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.NHA
FROM tlu_PartNumbers RIGHT JOIN tbl_InventoryListing ON
tlu_PartNumbers.PartNo = tbl_InventoryListing.PartNo;


I believe I need to change the select statement to "SELECT
tlu_PartNumbers.PartNo, tlu_PartNumbers.Nomenclature, tlu_PartNumbers.NIIN,
tlu_PartNumbers.CageCode, tlu_PartNumbers.SupplySource," since that
information is fairly stagnant, but for the new record being added to
tbl_InventoryListing, the "CageCode", and "SupplySource" may change from what
is stored in the tlu_PartNumbers table.

I need the Part Number field to store the part number selected from a
drop-down list (from tlu_PartNumbers), and I need the NIIN, Cage Code and
Supply Source fields automatically populated from tlu_PartNumbers, and saved
as part of the record, though I'd like to set that up as a drop-down (both
fields come from tlu_CageCodes). I think I need to use DLookup for that, and
I do understand the syntax for DLookup (I think),

=DLookup(“[NIIN]�,�sqry_Receiving2�,�[PartNo] = “ & Me.PartNo)

I'm just not sure where to use it. Do I replace the "fields" that I need
populated with text boxes? If I do that, will the information be saved in
those fields when the record is saved?

tbl_InventoryListing currently has over 23,000 records, and tlu_PartNumbers
has over 4,000 records.

Please help!

Firstly, what do you mean by 'fairly stagnant'? If the rows in
tbl_InventoryListing will always without exception reflect the current values
of those columns in tlu_PartNumbers then you don't need those fields (other
than the foreign key PartNo column) in the former. If a row in
tbl_InventoryListing might reflect a value which differs from the current
value in tlu_PartNumbers, even if only once, then you must also have that
column in tbl_InventoryListing.

Assuming that its only the NIIN, CageCode, and SupplySource columns whose
values may differ from the current values in tlu_PartNumbers and the
Nomenclature value will always be that in tlu_PartNumbers, then you will need
the first three columns also in tbl_InventoryListing, but not the last.
There is really little point in basing the form on a query which joins the
tables; you can base in on one on the tbl_InventoryListing table alone, e.g

SELECT *
FROM tbl_InventoryListing
ORDER BY PartNo;

The fact that you used a RIGHT JOIN in your query worries me a little as
there should be no need to use one when joining these tables as there should
be no row in tbl_InventoryListing which does not have a matching row (by
PartNo) in tlu_PartNumbers, and in the relationship between these tables you
should enforce referential integrity to ensure this. If by any chance you
find that you can't do this then it means that you have unmatched rows in
tbl_InventoryListing, which will either need deleting (if they are redundant
to your business needs of course) or additional rows will need to be added to
tlu_PartNumbers. If you need to do either of these we can guide you through
the process of creating the necessary 'action' query.

To populate the NIIN, Cage Code and Supply Source controls in the form when
you select a part number you could use the DLookup function in the PartNo
combo box's AferUpdate event procedure (the sample Northwind database does
this with unit costs in the orders Subform), but another way is to include
the NIIN, Cage Code and Supply Source columns in the combo box's RowSource as
hidden columns. The Nomenclature column can also be included, so the
RowSource property would be:

SELECT PartNo, NIIN, CageCode, SupplySource, Nomenclature
FROM tlu_PartNumbers
ORDER BY PartNo;

Set the combo box's other properties up like this:

BoundColum: 1
ColumnCount: 5
ColumnWidths: 8cm;0cm;0cm;0cm;0cm

If your units of measurement are imperial rather than metric Access will
automatically convert the dimensions in the last one. The first dimension is
fairly arbitrary so long as its at least as wide as the control. Setting the
others to zero hides the other columns.

You can then populate three text box's bound to the NIIN, CageCode and
SupplySource columns with code in the PartNo combo box's AfterUpdate event
procedure like this:

Dim ctrl as Control

Set ctrl = Me.ActiveControl

Me.NIIN = ctrl.Column(1)
Me.CageCode = ctrl.Column(2)
Me.SupplySource = ctrl.Column(3)

The Column property is zero-based, so Column(1) is the second column and so
on.

For the Nomenclature add an unbound text box to the form, with a
ControlSource property of:

=PartNo.Column(4)

Ken Sheridan
Stafford, England

Deb wrote:
I'm designing an Inventory Program. I have an Inventory Table
(tbl_InventoryListing). The Program is driven by Part Numbers. I have a
lookup table (tlu_PartNumbers). I need to design a "Receiving" form that
will add a new record to tbl_InventoryListing table. I've set up a query
containing both tables, linked by the Part Number.

SELECT tbl_InventoryListing.PartNo, tlu_PartNumbers.Nomenclature,
tbl_InventoryListing.NIIN, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.ReceivedDate, tbl_InventoryListing.ReceiptDoc,
tbl_InventoryListing.UnitOfIssue, tbl_InventoryListing.CageCode,
tbl_InventoryListing.SupplySource, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Sponsor,
tbl_InventoryListing.Program, tbl_InventoryListing.Division,
tbl_InventoryListing.Purpose, tbl_InventoryListing.Building,
tbl_InventoryListing.Location, tbl_InventoryListing.WarehouseNo,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.NHA
FROM tlu_PartNumbers RIGHT JOIN tbl_InventoryListing ON
tlu_PartNumbers.PartNo = tbl_InventoryListing.PartNo;

I believe I need to change the select statement to "SELECT
tlu_PartNumbers.PartNo, tlu_PartNumbers.Nomenclature, tlu_PartNumbers.NIIN,
tlu_PartNumbers.CageCode, tlu_PartNumbers.SupplySource," since that
information is fairly stagnant, but for the new record being added to
tbl_InventoryListing, the "CageCode", and "SupplySource" may change from what
is stored in the tlu_PartNumbers table.

I need the Part Number field to store the part number selected from a
drop-down list (from tlu_PartNumbers), and I need the NIIN, Cage Code and
Supply Source fields automatically populated from tlu_PartNumbers, and saved
as part of the record, though I'd like to set that up as a drop-down (both
fields come from tlu_CageCodes). I think I need to use DLookup for that, and
I do understand the syntax for DLookup (I think),

=DLookup(“[NIIN]�,�sqry_Receiving2�,�[PartNo] = “ & Me.PartNo)

I'm just not sure where to use it. Do I replace the "fields" that I need
populated with text boxes? If I do that, will the information be saved in
those fields when the record is saved?

tbl_InventoryListing currently has over 23,000 records, and tlu_PartNumbers
has over 4,000 records.

Please help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1

Ken:

Thank you for responding.

Part Numbers are what drives the database, and referential integrity has
been established between tlu_PartNumbers and tbl_InventoryListing. The
"fairly stagnant" information is the other fields in tlu_PartNumbers. NIIN
should be the same for the part numbers they are assigned to, but have found
a few discrepancies. Cage and Supply Source may change if they change
vendors.

I pulled up Northwinds, and based on the code in the ProductID Combo Box, I
entered the following code:

Private Sub PartNo_AfterUpdate()
On Error GoTo Err_PartNo_AfterUpdate

Dim strFilter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "PartNo = " & Me!PartNo

'Look up Part Number NIIN and assign it to NIIN control.
Me.NIIN = DLookup("NIIN", "tlu_PartNumbers", strFilter)

Exit_PartNo_AfterUpdate:
Exit Sub

Err_PartNo_AfterUpdate
MsgBox Err.Description
Resume Exit_PartNo_AfterUpdate

End Sub

I just used the NIIN so far for testing purposes.

PartNo Combo Box Properties - Row Source contains:
SELECT tlu_PartNumbers.PartNo, tlu_PartNumbers.Nomenclature,
tlu_PartNumbers.UI, tlu_PartNumbers.NIIN, tlu_PartNumbers.Cage,
tlu_PartNumbers.SupplySource FROM tlu_PartNumbers ORDER BY
tlu_PartNumbers.PartNo;

NIIN is still not populating, so obviously I'm still missing something.

Please advise.

Thank you

Is PartNo text data type by any chance? If so its value needs to be wrapped
in quotes characters when building the string expression:

strFilter = "PartNo = """ & Me!PartNo & """"

A pair of contiguous quotes characters within a literal string delimited by
quotes is interpreted as a literal quotes character.

If you use the alternative method I suggested of referencing a combo box
control's columns then the data type is immaterial.

I'm still not absolutely clear about your logical model. When you say that
you 'have found a few discrepancies' between the NIIN values and the PartNo
values, are these legitimate variations, or are they mistakes? If the latter,
and the PartNo value should always determine the NIIN value, you should not
be assigning the value from the tlu_PartNumbers table to a column in the
tbl_InventoryListing, and deleting the column form the latter would bring
everything back into line as there would only be one NIIN value per part
number, the one in the tlu_PartNumbers table.

Ken Sheridan
Stafford, England

Deb wrote:
Ken:

Thank you for responding.

Part Numbers are what drives the database, and referential integrity has
been established between tlu_PartNumbers and tbl_InventoryListing. The
"fairly stagnant" information is the other fields in tlu_PartNumbers. NIIN
should be the same for the part numbers they are assigned to, but have found
a few discrepancies. Cage and Supply Source may change if they change
vendors.

I pulled up Northwinds, and based on the code in the ProductID Combo Box, I
entered the following code:

Private Sub PartNo_AfterUpdate()
On Error GoTo Err_PartNo_AfterUpdate

Dim strFilter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "PartNo = " & Me!PartNo

'Look up Part Number NIIN and assign it to NIIN control.
Me.NIIN = DLookup("NIIN", "tlu_PartNumbers", strFilter)

Exit_PartNo_AfterUpdate:
Exit Sub

Err_PartNo_AfterUpdate
MsgBox Err.Description
Resume Exit_PartNo_AfterUpdate

End Sub

I just used the NIIN so far for testing purposes.

PartNo Combo Box Properties - Row Source contains:
SELECT tlu_PartNumbers.PartNo, tlu_PartNumbers.Nomenclature,
tlu_PartNumbers.UI, tlu_PartNumbers.NIIN, tlu_PartNumbers.Cage,
tlu_PartNumbers.SupplySource FROM tlu_PartNumbers ORDER BY
tlu_PartNumbers.PartNo;

NIIN is still not populating, so obviously I'm still missing something.

Please advise.

Thank you

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1

Hi Ken:

The Part Number is text (as are most of the fields). If I use the
alternative method, referencing combo box control columns, will the data be
saved in the tbl_InventoryListing record? I was under the impression that it
would display, but not be saved.

The discrepancies in NIIN numbers may very well be mistakes. Out of 23,000
records, I only ran across one instance where there were two different NIIN
numbers for the same part number. I'll try to get a reading on this tomorrow.

I'm going to try adding the quotation marks as you described and see if that
works.

Many thanks,

Deb

Eureka!!!! Thank you very much!!!

Of course that generated another issue, but I have a more pressing problem
right now. Will start a new thread.

THANK YOU -- Deb

"KenSheridan via AccessMonster.com" wrote:

Is PartNo text data type by any chance? If so its value needs to be wrapped
in quotes characters when building the string expression:

strFilter = "PartNo = """ & Me!PartNo & """"

A pair of contiguous quotes characters within a literal string delimited by
quotes is interpreted as a literal quotes character.

If you use the alternative method I suggested of referencing a combo box
control's columns then the data type is immaterial.

I'm still not absolutely clear about your logical model. When you say that
you 'have found a few discrepancies' between the NIIN values and the PartNo
values, are these legitimate variations, or are they mistakes? If the latter,
and the PartNo value should always determine the NIIN value, you should not
be assigning the value from the tlu_PartNumbers table to a column in the
tbl_InventoryListing, and deleting the column form the latter would bring
everything back into line as there would only be one NIIN value per part
number, the one in the tlu_PartNumbers table.

Ken Sheridan
Stafford, England

Deb wrote:
Ken:

Thank you for responding.

Part Numbers are what drives the database, and referential integrity has
been established between tlu_PartNumbers and tbl_InventoryListing. The
"fairly stagnant" information is the other fields in tlu_PartNumbers. NIIN
should be the same for the part numbers they are assigned to, but have found
a few discrepancies. Cage and Supply Source may change if they change
vendors.

I pulled up Northwinds, and based on the code in the ProductID Combo Box, I
entered the following code:

Private Sub PartNo_AfterUpdate()
On Error GoTo Err_PartNo_AfterUpdate

Dim strFilter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "PartNo = " & Me!PartNo

'Look up Part Number NIIN and assign it to NIIN control.
Me.NIIN = DLookup("NIIN", "tlu_PartNumbers", strFilter)

Exit_PartNo_AfterUpdate:
Exit Sub

Err_PartNo_AfterUpdate
MsgBox Err.Description
Resume Exit_PartNo_AfterUpdate

End Sub

I just used the NIIN so far for testing purposes.

PartNo Combo Box Properties - Row Source contains:
SELECT tlu_PartNumbers.PartNo, tlu_PartNumbers.Nomenclature,
tlu_PartNumbers.UI, tlu_PartNumbers.NIIN, tlu_PartNumbers.Cage,
tlu_PartNumbers.SupplySource FROM tlu_PartNumbers ORDER BY
tlu_PartNumbers.PartNo;

NIIN is still not populating, so obviously I'm still missing something.

Please advise.

Thank you

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1