Look up based on Query results 1

[netrusher]

I have and unbound field on my form (Method10) that I am currently populating from a quey. What I want to do
is have this field populated based on the criteria of the query that does a lookup from a table. Example:

If Field (Pull_Seque) of Query (MainEntryQuerySelect) = 001 I then want to do a look up to Table
(ReplenishMethodTbl) (PullSeqCode) Field and populate (Method10) on the Form from the (Method) Field in the
(ReplenishMethodTbl). How do I type the code? I have tried and I cannot solve this!

 

[GingerR]

Ok, I've read this four times and still can't get it straight. Please detail your table structure, what you are wanting, and what you have tried but doesn't work. Your post is very confusing.

Also, why are you populating an unbound field? Just for display purposes? When is this field getting populated? When a user clicks a button? Or selects something from a combo box or something? Is it performing some type of calculation for the user? Please explain. Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[netrusher]

Thanks for your assistance.

I am populating an unbound field for display purposes.
When a part number is selected from a comboxBox AfterUpdate a query is ran. The code below is used to
populate the Unbound Fields.

Me.PartNumber10 = DLookup("PartNumber", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.PrimarySupplier10 = DLookup("PrimarySupplier", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.OPERATION10 = DLookup("Operation", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.FrozenUsage10 = DLookup("FROZEN_USA", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.KBQty10 = DLookup("PROD_KB_QT", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.FrozenKbQty10 = DLookup("KB_QUANTIT", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.CONTAINER10 = DLookup("CONTAINER_", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.PiecesPer10 = DLookup("PIECES_PER", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.NumberContainers10 = DLookup("NUMBER_OF", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")
Me.PullStep10 = DLookup("SURROGATE_", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null")

I have another unbound field on the form I want to populate
from the query by going to another table to lookup the info. Example

If the Pull_Seque field in the query is 001 I want to populate the PullSeqCode field on the form with the value
from the table. I have two columns in the table. If the criteria from the query is 001 I want look up the 001
in the table and have the field on the form populate with Bin.

Table

PullSeqCode Method
001 Bin
002 Bin2
003 Bin3

I hope this helps make sense.

 

[GingerR]

So essentially, you are looking up data in a table which depends on looking up data in your query, right? Embedded dlookups.

Me.Method10 = DLookup("Method","PullSeqCodeTable","PullSeqCode = '" & dlookup("Pull_Seque", "MainEntryQrySelect", "Surrogate_ = 1 or Surrogate_ is null") & "'")

substitute in your table name for "PullSeqCodeTable"

So if you parse it out, it's

dlookup("Method","TableName","PullSeqCode = 'X'")

where X = dlookup of the query. ok?

I used single quotes cause it looks like your Seq Code is not numeric.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[netrusher]

Thanks GingerR