DLookup (Or ELookup)?

[Deb]

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!

 

[Deb]

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

 

[Deb]

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

 

[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