Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Betekom, Belgium
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dlookup between equal subforms (access2002)

    As a novice I could use some help with the next thing. (tried the recordset approach but that was totally wrong)
    On a masterform frmorder are two subforms (on equal level) subformulier qry tblorderlijn and subformulier qry actuastock
    when filling out the orderdetails and more specific the number of articles is would like to see the actual stock situation of that article.
    Actual stock is to be found in the query.
    I would like the second subform positioned on the just filled out article/item so it is easier to check for the actual stock stituation

    Somehow this isn't the right approach but i cannot figure out why.

    Dim rs
    rs = DLookup(" Forms![frmorder]![subformulier qry tblorderlijn].[Form].[omschrijving]", _
    "qry actuastock", _
    "forms![frmorder]![subformulier qry actuastock].[Form].[Omschrijving]='" _
    & Forms![frmorder]![subformulier qry tblorderlijn].[Form].[Omschrijving] & "'")


    As a novice I need to learn lot so please try to keep it one step at the time.
    Thank you,
    Jan

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

    Re: dlookup between equal subforms (access2002)

    From your description, I'd say the "subformulier qry actuastock" subform should be linked to the "subformulier qry tblorderlijn" subform, but probably not through the "Omschrijving" field. Don't you have a product ID field?

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Betekom, Belgium
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dlookup between equal subforms (access2002)

    Hans,

    that was fast <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    Yes I do have a itemfield but than the subform query of the stock does not give me the exact situation.
    table
    orderlijn = lijnnr - orderid - itemid - aantal (the same item can appear more than once)
    item = itemid - omschrijving - stock(purchased) - minimum stock

    query based on orderlijn & items
    Actua Stock: [Stock]-[Som Van Aantal]
    Som Van Aantal: Aantal --- tblOrderlijn --- Som
    Stock --- tblItems --- Group By
    Omschrijving --- tblItems --- Group By
    ItemID --- tblOrderlijn --- Group By
    MinStock --- tblItems --- Group By
    to order: IIf([Actua Stock]<[MinStock];"Reorder " & [MinStock]-[Actua Stock];"***") --- Expressie


    I don't know if I am clear enough but :
    all the items are shown on the orderlijn
    next to it on the same stucturelevel I intend to show the actual stock situation of all the items
    (prefer to limit them to the ones used in the orderlijn)

    in the query I do not put the orderid as it would not give me the actual stock of the item

    Does this help you further on.
    I am trying to combine saleorders and stock situation but I do need to learn a lot about access. I am doing this for fun would you believe this.

    Thanks for the assistance,
    jan

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

    Re: dlookup between equal subforms (access2002)

    You could try the following:

    Add the following procedure to the code module of the orderlijn subform:

    Private Sub SeekItem()
    ' Find the record that matches the current item
    Dim rst As Object

    If Not IsNull(Me.ItemID) Then
    Set rst = Me.Parent.[subformulier qry actuastock].Form.Recordset.Clone
    rst.FindFirst "ItemID = " & Me.ItemID
    If Not rst.EOF Then
    Me.Parent.[subformulier qry actuastock].Form.Bookmark = rst.Bookmark
    End If
    End If

    Set rst = Nothing
    End Sub

    Call this from the On Current event of the orderlijn subform and from the After Update event of the control bound to ItemID:

    Private Sub Form_Current()
    ' Ignore synchronization problems when the form is opened
    On Error Resume Next
    Call SeekItem
    End Sub

    Private Sub ItemID_AfterUpdate()
    Call SeekItem
    End Sub

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Location
    Betekom, Belgium
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dlookup between equal subforms (access2002)

    Hans,

    this works great. Now i am experimenting with your code because it seems that in my present situation I cannot update the orderlijn.
    Yes the allowdataentry was set to false but when turned on to true suddenly the grouped orders dissappear. I think its the combination with the unbound combobox that does it. So experimenting with you code on the unbound combo and a query in the subform get me a partly result.
    The query is being steered according the value in the combobox. Only have to deal with the table to get a totally new order in and showing its number in the combo.

    to be continued

Posting Permissions

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