dlookfuntion not doing anything.. 1

[wshm]

Below coding is suppose to get value "FYMO" where "txtBtxDate" is between Bdate and Edate.
I set this in afterevent property of txtBtxDate but it's not doing anything.. is something wrong?

Private Sub txtBtxDate_AfterUpdate()

txtFYMO = DLookup("[FYMO]", "tbl_FYMO", "[Bdate] < #" & _
txtBtxDate & "# AND [Edate] = #" & txtBtxDate & "#")

Me.txtFYMO.Requery

End Sub

 

[zevw]

try this first!

Private Sub txtBtxDate_AfterUpdate()
Dim rslt

  rslt = DLookup("[FYMO]", "tbl_FYMO", "[Bdate] < #" & _
            txtBtxDate & "# AND [Edate] = #" & txtBtxDate & "#")
      
    Msgbox rslt
 
    Me.txtFYMO = rslt

End Sub

is msgbox coming up with a value?

If so then your field txtFYMO should be populated with the new value.

 

[wshm]

i'm getting an error msg "Run-Time error '94', Invalid use of Null.

and

msgbox rslt is highlighted..

 

[zevw]

I Think this will work.

Private Sub txtBtxDate_AfterUpdate()
Dim rslt

  rslt = DLookup("[FYMO]", "tbl_FYMO", "[Bdate] < #" & _
            me.txtBtxDate & "# AND [Edate] = #" & me.txtBtxDate & "#")
      
    Msgbox NZ(rslt, "Empty")
 
    Me.txtFYMO = rslt

End Sub

If you get the message "Empty", that means that there is no record matching the criteria of the DLookup.

 

[wshm]

thank you for your fast response zevw. for some reason, im getting the msg (empty) but no event is occuring. txtFYMO is still null..

 

[wshm]

just saw the last part of your message. that's very strange because there's a match criteria...

 

[wshm]

sigh... what am i thinking..


rslt = DLookup("[FYMO]", "tbl_FYMO", "[Bdate] < #" & _
me.txtBtxDate & "# AND [Edate] > #" & me.txtBtxDate & "#")

i changed the = to > and it's working now. Thx and happy holidays!!

 

[zevw]

You can also change it to >=