Dlookup to find value on another form

[burrina]

I want to use dlookup to find a value of a field on another form than the one I am using at the time.



The table where the field that contains the data is named Orders

The field name is TaxRate

The form where the field with the value I need is located on is named AddProducts


something like this; =DLookUp("[TaxRate]", "Orders",[TaxRate] = Form![AddProducts]")

although it does not work, wrong syntax.

I need to lookup the value from the Orders table where it is being stored on my form named AddProducts

[June7]

Don't include variables within quotes. Concatenate variables. Reference to form control is a variable.

Consider the logic of your lookup. The criteria uses TaxRate field. This means you already have to know the tax rate to look for. If you already know the tax rate why look it up? You need the tax rate for product or locality or some other criteria selected on form.

Is the DLookup in VBA or textbox ControlSource?

=DLookUp("[TaxRate]", "Orders", "[ProductID] = " & [ProductID])

There are more efficient methods than DLookup to get this related data.
1. table joins in form RecordSource
2. reference to field in multi-column combobox

[burrina]

My problem is a design issues or just that my brain is not working, maybe both !!!

I have a add products form with a subform on it. The tables being used are Categories and Products. For my main Purchase Order form I use a subform on it that uses the table Orders. So I am trying to find a way to make the subform use the value on the form AddProducts where I set the TaxRate for each Category.
So, AddProducts form with a subform named AddProductsSubform where it shows the categories and products for that category.

Next, my main form which is named TimeCards and it has a subform on it named TimeCardCatAndProdSub and it uses the table Orders for its record source.

[June7]

Don't need DLookup to reference a value in a control on form.

=Forms!AddProducts.TaxRate

[burrina]

Does that form have to be open? That does not work, it will not give me the value of TaxRate on the form AddProducts which is NOT on this form and NOT opened.

[June7]

Yes, form would have to be open. Guess I don't understand what you want to do.

[burrina]

I have 4 tables, 2 for AddProducts form. One named Categories and one named Products. Together they show me all of the products by category.

The other 2 tables are Time and Orders. Orders is used for tracking purchase orders and Time tables is used for tracking job number and the Customers table is used for tracking customer info and finally TShippingInformation which is used for tracking that info.

Here is a few pics of queries used for my forms. I am trying to get the form TimeCatAndProdSub which uses the

[June7]

Don't think you finished description in your post.

If form is open then can reference control on form to retrieve a value.

If the value you want is in table/query then use DLookup.

[burrina]

Ok, then I will definitely have to use DLookup. I want the form TimeAndCatProdSub to lookup the taxrate from the Categories table. Right now, with errors, I can get the form AddProducts to add the TaxRate for the subform AddProductsSubform. I then need this same tax value to be added to the subform TimeAndCatProdSub which has a Tax field that needs updating thru a Dlookup.

On the after update event of the control Cat which is labeled as Category I need it to update the forms Tax value to that of the TaxRate found in the Categories table.

I choose a category that is either Taxable or not, if it is then a TaxRate is set on the form AddProducts which uses the Categories table. It then updates the subform AddProductsSubform.

Here is my update code; It should however use the form AddProducts form instead and use it's TaxRate as the value.

Hope this makes sense:

Private Sub Cat_AfterUpdate()
Me.Prod.RowSource = "SELECT Products.[Product Name], Products.[Unit Price], Products.[Category ID] FROM" & _
" Products WHERE (((Products.[Category ID]) = [Cat]))" & _
" ORDER BY Products.[Product Name];"
Me.Prod = Null
If [Cat] = "LABO" Then ' Non Taxable Category
[% 1A] = 0#
[TaxA] = 0#
Else: [% 1A] = Forms!TimeCards!NameB.Column(7) ' Customers Default Markup
[TaxA] = [TaxA].DefaultValue
End If



End Sub

[June7]

No, doesn't make sense to me. The code is not using DLookup. If it should use AddProducts form why isn't it? What happens when the event runs? Step debug - guidance on debug techniques at bottom of my post.

If you want to provide db for analysis, follow instructions at bottom of my post.

[burrina]

Ok, here is a demo copy. Let me know if this helps.

Thanks,


Dave

[June7]

The main form has TaxYesNoRate field. You want a different tax rate for the subform? I have already given you options for retrieving tax rate. I will try to explain again, if you want the tax rate that is associated with selected category, options:

1. include Categories TaxRate field in category combobox

2. include Categories table in the subform RecordSource

3. DLookup

[burrina]

I finally have the subform TimeAndCatProdSub working as I want. My brain is fried though. Could you please take a look at this code for the form and tell me why this expression is wrong? ExpTax: [ExpTotCost]*[% 1] it shows a slightly higher amount than the real tax amount which should be $4.13

SELECT Orders.[Order ID], Orders.[Customer ID], Orders.TimeID, Orders.OrderDiscount, Orders.Category, Orders.Product, Orders.[% 1], Orders.Price, [Price]*(1+[% 1]) AS ExpPriMarkUp, Orders.Qty, [ExpPriMarkUp]*[Qty] AS ExpTotCost, Orders.Tax, [ExpTotCost]*[% 1] AS ExpTax, ([Tax]=[ExpTotCost]-[% 1])+[Price] AS ExpTotCostNoTx, ([Tax]=[ExpPriMarkUp])+[% 1]*[Price]+[Price] AS ExpTotCostTx, Orders.[Order Date]


Here is the result of all this on my form. See pic.

[June7]

In which textbox is the expression?

Is that SQL part of the RecordSource for the sub form?

It would be nice if the db you provided had a form that opened displaying the same record info in your images. I just discovered that that ShipTo combobox has a RowSource referencing a table/query that isn't even in the db. Also, the Orders record does not have TimeID so it is not even showing up in the subform. I did attempt to fix the Orders record so it looked like the image. I input CategoryID and Qty and TimeID. Now it shows price of 50 and 4.125 TaxAmount on the main form. No tax calculates in the subform record.

There are also aspects of data structure that don't make sense to me. For an example, why is CategoryID and Category in Orders and OrderID in Categories?

My interest in analysing this db is greatly reduced by its disorganized state.

[burrina]

Lots of the krap you see in the db is a result of experimenting. It gets removed when the desired result has been accomplished. The db I sent you is NOT a whole db and it is a part of a much larger db. This is only one module. Since you have lost interest then I simply thank you for your time and will try and figure it out as best as I can.

That SQL is the record source for the subform, yes.The other missing parts of the db were not necessary to troubleshoot db so I left them out. Tried to make it less confusing as possible.

Truthfully, I hesitate to send the completer/partial db since I don't know what will happen to it on this site. Nothing confidential in it, just a LOT of hard work on my part. I also noticed that no one else offers to share there db or else they provide a small sample. I realize that what I have written is not all that but it is a part of me.

Thanks Again ! P.S. If I were dealing with just one person then maybe it would be different, but what prevents someone else from downloading it and keeping it?

Take Care,