none
help with DLookUp and DMax

    Question

  • I am trying to set the control source for a field based on a combo box selection. the control source I am trying to call is not in the record source because adding that table would make my form non-updateable. So to get the correct data I am trying to use the DLookUp function with a DMax in the criteria section. I'm pretty new at using these functions so any help on why I am not getting the value I would like (currently the field remains blank) would be great. Here is the code that I am using:

    Me.Field1.ControlSource = "=DLookUp('no','Revision',[date_revision]=DMax('date_revision', 'Revision', 'pnpid = FORMS!InstrumentIndex!pnpid'))"

    Thanks for the help

    Nick

    Wednesday, September 12, 2012 4:21 PM

All replies

  • ...
    Me.Field1.ControlSource = "=DLookUp('no','Revision',[date_revision]=DMax('date_revision', 'Revision', 'pnpid = FORMS!InstrumentIndex!pnpid'))"

    I think you have to surround the third parameter by double quotes. Try this:

    Me!Field1.ControlSource = "=DLookUp('no', 'Revision', " & Chr$(34) & "date_revision=DMax('date_revision', 'Revision', 'pnpid = Forms!InstrumentIndex!pnpid')" & Chr$(34) & ")"

    cu
    Karl
    ******************
    Access-FAQ (German/Italian): http://www.donkarl.com

    Wednesday, September 12, 2012 6:15 PM
  • Thank you,

    That syntax returned the value of 0 for each record which is not a value in any of the records for that field in the table being called. What could be causing this?

    Thanks again,

    Nick

    Wednesday, September 12, 2012 6:26 PM
  • nickjohnson wrote:
    >
    > That syntax returned the value of 0 for each record which is not a
    > value in any of the records for that field in the table being
    > called. What could be causing this?
     
    Check what you get when you insert the expression into the control
    source in design view. There it should work this way (1 line):
     
    =DLookUp("no", "Revision", "date_revision=DMax('date_revision',
     'Revision', 'pnpid = Forms!InstrumentIndex!pnpid')")
     
    If you still geht 0 then try if DMax on it's own gives the expected result:
     
    =DMax("date_revision", "Revision", "pnpid = Forms!InstrumentIndex!pnpid")
     
    --
    cu
    Karl
    *********
    Access-FAQ (German/Italian): http://www.donkarl.com
     
    Wednesday, September 12, 2012 6:42 PM
  • Thank you,

    The DMax function on it's own works just fine, using the code directly in the control source in design view still returns a value of 0. Does this mean that the way I have my equation set causing this error?

    Thanks again

    Nick

    Wednesday, September 12, 2012 6:57 PM
  • Me.Field1.ControlSource = "=DLookUp('no','Revision',[date_revision]=DMax('date_revision', 'Revision', 'pnpid = FORMS!InstrumentIndex!pnpid'))"

    Hi Nick,

    Me.Field1.ControlSource binds the control to a field in the recordset. I have my doubts that DLookUp returns that field name.

    Or do you want to assign the result of DLookUp to Me.Field1? In that case use:

    Me.Field1 = "=DLookUp('no','Revision',[date_revision]=DMax('date_revision', 'Revision', 'pnpid = FORMS!InstrumentIndex!pnpid'))"

    Imb.

    Wednesday, September 12, 2012 9:37 PM
  • Remove the = inside the string.  (untested)

    Me.Field1.ControlSource = "DLookUp('no','Revision',[date_revision]=DMax('date_revision', 'Revision', 'pnpid = FORMS!InstrumentIndex!pnpid'))"

    Wednesday, September 12, 2012 10:02 PM
  • Thank you for the suggestions,

    Removing the control source and just setting the field to the DLookUp function did not yield the results that I am looking for. Also removing the the = in the string did not display any value. Is there another way to do this without using the DLookUp function?

    thanks for all the help

    Nick

    Thursday, September 13, 2012 4:22 PM
  • I will generaly create the control as an unbound control if the control source is not in the record source. I would then use vba to update the desired table.

    As an example:

    On the OnChange event of the combo box:

    Dim varValue as variant
    varValue = Dlookup("","","")
    Me.UnboundControl = varValue
    docmd.RunSQL"UPDATE TableName Set ColumnName = " & varValue & " WHERE (set your crieteria)..."

    HTH

    Imran

    Thursday, September 13, 2012 7:39 PM
  • Maybe it is preferable to simply leave the field control source set to the expression and issue a Me.Recalc when you need to re-actualize the value.
    Sunday, September 16, 2012 11:59 PM