locked
Dlookup function not working RRS feed

  • Question

  • Hello,

    I created a split form in which by entering an order number (text field) I use a Dlookup function to populate the "item number" field and others. when I did it in a single item form it worked but when I'm trying to do it now on the split form it doesn't. I've tried different ways and still no luck. In the Immediate screen if i test the the expression and the domain part It returns a value but when I try to define the criteria it returns null. I appreciate the help

    Here is the code...



    Private Sub order_AfterUpdate()

    item_no = DLookup("item_no", "dbo_sfordfil_sql Query", "ord_no='" & order & "'")

    End Sub

    Wednesday, February 29, 2012 10:09 PM

All replies

  • Is your "order_no" a Numeric value?

    In that case you should the criteria without the single quotes:

    Private Sub order_AfterUpdate() 
    
    item_no = DLookup("item_no", "dbo_sfordfil_sql Query", "ord_no=" & order)
     
    End Sub
    


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Wednesday, February 29, 2012 10:21 PM
  • Your code indicates that ord_no is a text field. If it is a number field, use

        Me.item_no = DLookup("item_no", "dbo_sfordfil_sql Query", "ord_no=" & Me.order)


    Regards, Hans Vogelaar

    Wednesday, February 29, 2012 10:22 PM
    • Is item_no a field you are trying to display on the form?  
    • Why are you executing a dlookup in an AfterUpdate event?

     I think there there may be something you have not mentioned that may be helpful in understanding the problem better.  

    Have you used debug and the intermdiate window to:

    1. Check value of "order" in the event, it may not be what you expect when the code executes, it might be null or "" and not an order number.
    2. Have you checked the event is executing when you expect. (e.g. Once for each row to be processed)


    PG A bit of experimentation by trial and error often helps.

    Wednesday, February 29, 2012 10:37 PM
  • Are you not introducing redundancy here?  If item_no and other values are determined by ord_no you only need the former as a foreign key in the form's underlying table.  The values from the other table(s) can be shown by basing the form on a query in which those tables are joined to the referencing table on ord_no.  By redundantly duplicating the values in the referencing table you are leaving it open to update anomalies.

    Ken Sheridan, Stafford, England

    Wednesday, February 29, 2012 11:52 PM
  • The "order_no" is a text
    Wednesday, February 29, 2012 11:56 PM
  • You might not need DLookup to populate the other fields.

    When creating a Query for example you would be able to populate those fields.


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Thursday, March 1, 2012 12:08 AM
  • I'm not an expert at this my vba is very limited.

    I did try the following though...

    item_no = DLookup("item_no", "dbo_sfordfil_sql Query") .....this returned a random value and populated the item_no field after I updated the order field.

    also...

    item_no = DLookup("item_no", "dbo_sfordfil_sql Query", "order='" & "00009163" & "'")....this actually worked and returned the correct item_no that I needed. But I needed to be based on what ever the value of the "order" field is.

    How can I do it through a query instead of dlookup?

    Thursday, March 1, 2012 12:21 AM
  • I believe the advantage of working with a Split Form is that you can work with the same Datasource.

    From below thread:

    http://office.microsoft.com/en-us/access-help/create-a-split-form-HA010075994.aspx

    The two views are connected to the same data source and are synchronized with each other at all times. Selecting a field in one part of the form selects the same field in the other part of the form. You can add, edit, or delete data from either part (provided the record source is updateable and you have not configured the form to prevent these actions).

    Do you need data from another Table which is not related to the Split Form Datasource?


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Thursday, March 1, 2012 12:47 AM
  • How can I do it through a query instead of dlookup?

    The point about using a query is that you would 'pull' the values into control rather than 'pushing' the values into controls as you are attempting.  Whether the former is a valid approach or not depends on what 'functionally determines' the values.  The easiest way to explain this is to look at an example from the sample Northwind database which has the following query as the RecordSource of its order details form's subform:

    SELECT [Order Details].*,
    CCur([Quantity]*[Unit Price]*(1-[Discount])) AS [Extended Price],
    [Order Details Status].[Status Name]
    FROM [Order Details] LEFT JOIN [Order Details Status]
    ON [Order Details].[Status ID] = [Order Details Status].[Status ID];

    You'll see here that the Status Name value for each order detail record is pulled into the query's result set by joining the Order Details table to the Order Details Status table on Status ID and is shown in a bound control in the subform.  The control's Locked property is True (Yes) so it can't be edited.  This means that the value is always determined by the value of Status ID in the Order Details table.  If the Order Details table also included an Order Details Status column this would introduce redundancy and the possibility of update anaomalies as it would be possible to change the value of the Order Details Status column in the Order Details table so that it was inconsistent with the value for the same Status ID in the Order Details Status table.

    The Order Details table also includes a Product ID column which references the Products table.  In this case the product name is pulled into the subform not via joining the tables in a query, but via a combo box bound to the Product ID column, but the principle is the same, it's pilled in not pushed into a column in the table.  Notice, however, that the Products table has a Unit Price column, but the Order Details table also has a Unit price column, and in this case the value is pushed into a bound control in the subform by code in the Product ID combo box's AfterUpdate event procedure.  If you look at the code you'll see it it's a little more complex than you might expect, but essentially it's just wrapping up a DLookup function call in some fancy functions.

    So why are the product and status names pulled in, but the unit price pushed into a column in the Order Details Table?  Why cannot this column also be omitted and the Unit Price also be pulled in?  The answer is that the unit price of a product will change over time, but you want the price recorded in each order record to be the one in force when the order was made.  If it was pulled in, then as prices change over time the prices recorded in each order would also change, which would be wrong.  So the Unit Price column n the Products table is functionally determined by the primary key of that table, whereas the Price column in the Order Details table is functionally determined by the primary key of that table.  There is therefore no redundancy and no risk of update anomalies.

    What you have to decide, therefore, is whether the item-no and other values you are currently trying to 'push' into columns in the form's underlying table should really be 'pulled' in, in the same way as the status and product names are in the Northwind example, or whether they should be 'pushed' in, in the same way as the unit price in the Northwind example.  The question to ask is whether the value of one or the other can legitimately change independently of the other, as with unit price, or must always reflect the other, as with the product and status names.  Only if the former is true should you 'push' the value into a column in the form's underlying table.


    Ken Sheridan, Stafford, England

    Thursday, March 1, 2012 1:21 PM
  • If the value order comes from a control on a form, try:

    item_no=DLookup("item_no", "dbo_sfordfil_sql Query", "ord_no= FORMS!formNameHere!order")

    Note that you mentionned that order is numerical but in an example that you supplied, you start it with multiple zeros. That seems to indicate that order is ALPHA-numerical. In any cases, using the syntaxt  FORMS!formName!ControlName does not require that you specify delimiters and also handle nulls (and date format sequence, in case of dates, but that is not the case here).

    Thursday, March 1, 2012 9:57 PM