Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Instant DlookUP Requery (A2K)

    I had this working in the past, but as things happen, it ain't working no more.

    Here are the details

    Parent Form: frm Inventory - 01
    Cheers,
    Andy

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instant DlookUP Requery (A2K)

    Andy,
    Have you tried anything along the lines of .requery or .refresh? I'm sure one of those would work sometime after your record is added (perhaps on current, or on the click of a button?) Or how about On Exit of the text box where you enter the part number?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instant DlookUP Requery (A2K)

    Hi Jeremy,
    Thanks for looking in. A couple of points. To add injury to insult at this point in time, when I open the form, the field in question displays #Error, and the info line at the bottom of the screen displays "Calculating.." and the whole form is pretty well hung up.
    I did try the requery of that field when I was exiting the first field I went into and that didn't work. However, until I get past this #Error thing, I won.t be able to test accurately. It's obviously something in my DLookUp formula.
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instant DlookUP Requery (A2K)

    Andy,
    Have you changed computers, or moved the database? I believe you can get the #Error message from missing references as well. When your form opens, is there a current record? Or do you have to select a record first? Also, looking at your formula, if your records aren't inserted at the end of the list with same part number, next order (if that makes sense) and then re-numbered, "stepping" to the previous record may become painful. Can you post a small copy?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instant DlookUP Requery (A2K)

    Hi Jeremy,

    This is my step-by-step resonse

    Have you changed computers, or moved the database?
    Nope!

    I believe you can get the #Error message from missing references as well.
    I've checked my references numerous times, but obviously I've missed something because I think I've been staring at it for too long.

    When your form opens, is there a current record?
    I open up on the first record

    Or do you have to select a record first?
    Selecting will come later when this first part works correctly

    Also, looking at your formula, if your records aren't inserted at the end of the list with same part number, next order (if that makes sense) and then re-numbered, "stepping" to the previous record may become painful.
    As far as I can see they're being inserted at the end, but
    Cheers,
    Andy

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Instant DlookUP Requery (A2K)

    What I would do is to introduce a OnCurrent event for the subform and populate the text variable as follows:
    <pre>Private Sub Form_Current()
    If Not IsNull([Inv_Rcvd_SysKey]) Then
    txtPrevNewCost = DLookup("[WB_Rcvd_NewCost]", "qry Inventory - Received", _
    "[Inv_Rcvd_SysKey]=" & [Inv_Rcvd_SysKey] - 1)
    End If
    End Sub
    </pre>


    And I would put the following line in the subforms BeforeUpdate event:
    <pre> txtPrevNewCost = txtKalc_NewCost
    </pre>


  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instant DlookUP Requery (A2K)

    Hi Patt,

    Thanks for helping out. I cut and pasted the following after putting in the appropriarte carriage returns

    Private Sub Form_Current()
    If Not IsNull([Inv_Rcvd_SysKey]) Then
    txtPrevNewCost = DLookup("[WB_Rcvd_NewCost]", "qry Inventory - Received", "[Inv_Rcvd_SysKey]=" & [Inv_Rcvd_SysKey] - 1)
    End If
    End Sub

    And got a runtime error '-214735256780020009 You can't assign an object a value to this object message.

    I also replaced my: txtPrevNewCost = txtKalc_NewCost with your txtPrevNewCost = txtKalc_NewCost which looked identical to me.

    I guess I must have missed something in the transaltion.
    Cheers,
    Andy

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Instant DlookUP Requery (A2K)

    >>I also replaced my: txtPrevNewCost = txtKalc_NewCost with your txtPrevNewCost = txtKalc_NewCost which looked identical to me.<<

    I cannot find where you had that instruction.

    With respect to the runtime error did you blank the Control Source of the control txtPrevNewCost ?

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instant DlookUP Requery (A2K)

    Hi Patt,

    I'm obviously losing it in my frustration.

    In the sub form before update I originally had the following:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    [WB_Rcvd_CdnValu] = txtKalc_CdnValue
    [WB_Rcvd_USValu] = txtKalc_USValue
    [WB_Rcvd_ExRate] = txtCurrentRate
    [WB_Rcvd_NewCost] = txtKalc_NewCost
    [Inv_Rcvd_Modified] = Now
    End Sub

    I misunderstood you to say that you wanted me to replace

    [WB_Rcvd_NewCost] = txtKalc_NewCost

    with

    txtPrevNewCost = txtKalc_NewCost

    Sorry about that.

    However, I now have in the Before Update of the subform, the following:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    [WB_Rcvd_CdnValu] = txtKalc_CdnValue
    [WB_Rcvd_USValu] = txtKalc_USValue
    [WB_Rcvd_ExRate] = txtCurrentRate
    [WB_Rcvd_NewCost] = txtKalc_NewCost
    [Inv_Rcvd_Modified] = Now
    txtPrevNewCost = txtKalc_NewCost
    End Sub

    And I still have:

    Private Sub Form_Current()
    If Not IsNull([Inv_Rcvd_SysKey]) Then
    txtPrevNewCost = DLookup("[WB_Rcvd_NewCost]", "qry Inventory - Received", _
    "[Inv_Rcvd_SysKey]=" & [Inv_Rcvd_SysKey] - 1)
    End If
    End Sub

    And I still get: runtime error '-214735256780020009

    I'm not quite sure what you are referring to with:

    With respect to the runtime error did you blank the Control Source of the control txtPrevNewCost ?

    My apologies for late, weekend dullness.
    Cheers,
    Andy

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Instant DlookUP Requery (A2K)

    The name of the main form in your DLookup expression is incorrect. It is not
    <big>frm Inventory - 01

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instant DlookUP Requery (A2K)

    Hans,
    you are a sharp-eyed individual aren't you. Thank you. That got in there because I have a bad/good habit of cutting and pasting my code into Word in order to see it more clearly and in conjunction with other code. Unfortunately, as you found in my code, when I copy what I think is a new, improved version, Word will elongate or whatever a standard hyphen into a longer what's its' name.

    However, based on the inclusion of Patt's suggestions, and as you can see in the attached, I still have a problem.
    Cheers,
    Andy

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Instant DlookUP Requery (A2K)

    If you want to use Pat's code, you must clear the control source of the text box. You cannot assign a value to a calculated control.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Instant DlookUP Requery (A2K)

    I have just got back from shopping, have you solved the problem yet. Hans explained more clearly that you had to clear the Control Source of the control and not leave the =Dlookup.... in there.

  14. #14
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instant DlookUP Requery (A2K)

    Patt & Hans,

    I thank you both, clearing the control solved the problem and all appears to be working well. I think I'll leave it alone tonight before I do any serious damage. Again, thanks kindly,
    Cheers,
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •