query

[Rpettis31]

How would I create a query that would pull a value from a table based on
another field on a form. I have a combo, box that has a list of all the
purchase orders in a table. One of the fields is a planning cell which is a
number that relates to a table that list the factory name related to the the
planning cell.

So when a po is selected from the combo box, then the details are placed in
the appropriate text boxes however the Factory name displays the number I
would like the event to include a query that will list the factory name
instead of the number.

 

[Michel Walsh]

The easiest way could be to write a query that will join the two implied
tables through their factory number and to base the form on this new query:
you will then get the name, through automatic lookup with the query, as soon
as you specify the factory number in a new record of the first table.


Another possibility it to use

DLookup("FactoryName", "ReferenceTableNameHere", "FactoryID=" &
FactoryIDYouWantTheName )

returning the said name.


Vanderghast, Access MVP

 

[Rpettis31]

I am getting a syntax error with this code. Basically a combo box selects
the PO
Then the data from the columns is assigned. However, the factory id is
listed in the factory name and I want to take that value and place the
factory name there.
I have read that DLookups are bad have never used them and I am not familiar
with joining tables.

Case "PO"
Me.TxtVendor = Me.cmbxOrderNumber.Column(11)
Me.txtLine = Me.cmbxOrderNumber.Column(1)
Me.txtItemDescription = Me.cmbxOrderNumber.Column(3)
Me.txtSpecificationNumber = Me.cmbxOrderNumber.Column(4)
Me.txtStdCost = Me.cmbxOrderNumber.Column(5)
Me.txtBuyer = Me.cmbxOrderNumber.Column(7)
Me.txtFactoryName = Me.cmbxOrderNumber.Column(8)
Me.cboItemNumber = Me.cmbxOrderNumber.Column(2)
Me.cboItemNumber.ColumnWidths = "1in"
Dlookup("FactoryName", "tblApprvSuppliers","FactoryID=" & me.txtFactoryName)

 

[Michel Walsh]

You need to convert the factoryID into its name, so it is probably
me.FactoryID rather than me.txtFactoryName in the line:


Dlookup("FactoryName", "tblApprvSuppliers","FactoryID=" &
me.txtFactoryName)



Also, if you already have the FactoryName, you would not have any need to
find it, no? Or I haven't understand your "query that will list the factory
name
instead of the number."


Vanderghast, Access MVP