Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move.Show data in another field (2000)

    Hi,
    I have a subform on a main form and it has 3 fields, intQty, intQtyReceived, calcQtyBackordered. intQty and intQtyReceived are fields in a table and the calcQtyBackordered is calculated in a qry (intQty-intQtyReceived). What I would like is for what amount is in intQty to automatically be put in intQtyReceived, because 99% of the time we will not have backordered items. Righ now intQtyReceived comes up with default 0 and the user has to type in the qty received. I'm not sure how to do this. I tried putting in the control source of intQtyReceived putting =[intQty] but naming it intQty Received and that seems to work as far as it puts the right amt there, but now my calcQtyBackorderded doesn't calculate right, it was still showing a 1 when the true calculation should be 0.
    Thanks,
    Deb

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

    Re: Move.Show data in another field (2000)

    Try this:

    1. Clear the Default Value property of the intQryReceived field in the table (and of the control on the form bound to this field, if necessary).

    2. Create an After Update event handler of the control bound to intQty:

    Private Sub intQry_AfterUpdate()
    ' Only set intQtyReceived if blank
    If IsNull(Me.intQtyReceived) Then
    Me.intQtyReceived = Me.intQty
    End If
    End Sub

    Substitute the correct names where needed.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move.Show data in another field (2000)

    The only thing that may be wrong with that is the event. In this form the intQty field is already filled in, it will not have to updated. Not sure which event to use.
    Thanks,
    Deb

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

    Re: Move.Show data in another field (2000)

    You could put the same code in the On Current event of the form. Or you could run a one-time update query to set the value of intQtyReceived to that of intQty if it is blank.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move.Show data in another field (2000)

    I put it on the onload even and for some reason there is a zero that keeps appearing in the field, but I've taken out all default values so I changed my code to this and it is working. Thank you. Deb

    Private Sub Form_Load()
    ' Only set intQtyReceived if blank
    If Me.intQtyReceived = 0 Then
    Me.intQtyReceived = Me.intQty
    End If
    End Sub

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

    Re: Move.Show data in another field (2000)

    The On Load event will only work for the first record that is displayed, not as the user moves from record to record.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move.Show data in another field (2000)

    I'm having that same problem with On Open and On Current and Before Update, meaning it works for the first record and that's it. Not sure what else to try.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move.Show data in another field (2000)

    I tried the update query using the OnCurrent event and that seems to be working, I'm going to do some further testing. Thanks for the suggestions.
    Deb

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

    Re: Move.Show data in another field (2000)

    If it works for you, fine, but I don't understand why you would need to run an update query in the On Current event (nor why the code I proposed only worked in the first record - the On Current event fires every time the user moves to another record).

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move.Show data in another field (2000)

    Well, now that you actually say that, I can explain why the On Current didn't "seem" to work. This is a receive parts form where all the user is doing is putting in a receive date for each part. it's a form with a subform the subform is continuous, so I could see 3 records and did not tab to the other records to prove your code correct, I saw all 3 records, the first one was correct but the last 2 weren't when just opening the form. So your code most likely does work I just didn't test correctly.

    Deb

Posting Permissions

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