DLookup (Or ELookup)?

  • Thread starter Thread starter Deb
  • Start date Start date
D

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!
 
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
 
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
 
Back
Top