Subform not updating Table with dlookup (1 Viewer)

[John thomas]

Hi have a subform called Newordersid and a table called NewOrdersID
I use Dlookup to populate the sub form on afterchange event of a combo box
It does this but does not populate its table
I use dlookup for otherfields in the subform and table and they all work
I have checked the spelling and used copy and paste to make sure i have it right but it will not work .
I must be doing something wrong but just cant spot it
Any Idears

 

[jdraw]

Please show readers some of the related code.

 

[John thomas]

Hi Jdraw
this is the dlookup code
Private Sub ProCombo_AfterUpdate()
SoldAtPrice = DLookup("[Sell at price]", "Products", "IDProduct= " & [ProductID])
Product = DLookup("[Product]", "Products", "IDProduct= " & [ProductID])
Supplier = DLookup("suppliersfk", "Products", "IDProduct= " & [ProductID])
VatAtVatSum = DLookup("[VatAtVatSum]", "Products", "IDProduct= " & [ProductID])
InvoiceTotallIncludesVatAt = DLookup("[InvoiceTotallIncludesVatAt]", "Products", "IDProduct= " & [ProductID])
quantity = 1
End Sub

I have just gone back deleted the fields and reentered but same problem

it updates the subform but not the sub form table

 

[jdraw]

The DLookups are selecting data from table Products.
What exactly are
SoldAtPrice
Product
Supplier
VatAtVatSum
InvoiceTotallIncludesVatAt?

I would expect an Sql Update query to actually update the Table NewOrdersID.

In plain English, are you trying to update (or Insert) a record in the Products table???
Please tell us.

 

[John thomas]

Hi

sold at price ,Product ect are all fields in the products table
when the product is selected via the combo box i want these fields plus vatatvatsum and InvoiceTotallIncludesvatAt to be automatically selected fro the products tabe and enterd into the new orders Table
cant understand why it enters them into the subform but the subform deos not enter them inti0 the Orderdetails Table
Thanks

 

[jdraw]

I think you have to be explicit.

strSQL ="INSERT into tblNewOrder SET " _
& "SoldAtPrice = " & DLookup("[Sell at price]", "Products", "IDProduct= " & [ProductID]) & ", Product = " & DLookup("[Product]", "Products", "IDProduct= " & [ProductID])...
all fields including Quantity....

Then
Currentdb.Execute strSQL, dbFailOnError

Or dim some variables
then assign your Dlookups to the variables, then an explicit Insert into to create the new record in your table.

 

[John thomas]

Ok Thanks for your help

 

[Galaxiom]

Private Sub ProCombo_AfterUpdate()
SoldAtPrice = DLookup("[Sell at price]", "Products", "IDProduct= " & [ProductID])
Product = DLookup("[Product]", "Products", "IDProduct= " & [ProductID])
Supplier = DLookup("suppliersfk", "Products", "IDProduct= " & [ProductID])
VatAtVatSum = DLookup("[VatAtVatSum]", "Products", "IDProduct= " & [ProductID])
InvoiceTotallIncludesVatAt = DLookup("[InvoiceTotallIncludesVatAt]", "Products", "IDProduct= " & [ProductID])
quantity = 1
End Sub

That code is incredibly inefficient. Each one of those DLookups is like running a separate query.

Typically the subform would simply have the table (or a query on the table) as its RecordSource with the subformcontrol's MasterLinkFields Property as the ProductID combobox and the ChildLinkFields as the ProductID field.

This links the subform ProductID to the combobox and requires no code at all.