Results 1 to 8 of 8

Thread: Subform

  1. #1
    Join Date
    Sep 2009
    Posts
    23

    Unanswered: Subform

    I have a subform in datasheet view. I want to put a condition if the user moves to another record.
    Example:
    The cursor is by default in the first record.And if the user moves to the second record, I want to display a message.
    How shall I do this? Thx.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    if the user moves to the second record...
    In the main form or in the subform?

    In both cases you can use the Current event procedure and call a message box there:
    Code:
    Private Sub Form_Current()
    
        MsgBox "Something..."
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Sep 2009
    Posts
    23
    "if the user moves to the second record... " (I am referring to the subform)
    The main form is invoice master and the subform is invoice detail.In my invoice detail, when the user select the inventory,I want to display the available quantity which is in another table(Invoice Detail table and Inventory table are related).
    I was trying to relate the detail subform to another subform (which is the inventory)but I was not successful.
    Let's say I have two records in Invoice Detail.The pointer/maybe cursor is pointing to the first row by default. If the user change it to the second row,I want to perform the dlookup function.I tried putting the code ON CURRENT in invoice detail subform,but when I open the main form,the code is being read.Right now,I put the dlookup funtion in inventory after update and setfocus which is working fine.But what i want to do is if the row is selected.
    Any suggestions pls...
    PS. Im new in Ms Access and reading the forums here helps me alot.Thnx
    Last edited by aldwin890; 09-11-09 at 13:47.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The germane question, I think, is what are you doing with DLookUp() when moving to the second or subsequent record that doesn't need to be done on the first record of the subform?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If what you want to do is to prevent the code from executing when opening the form, here is a solution:

    Code:
    Sub Form_Current()
    
        Static AlreadyOpen as Boolean
    
        If AlreadyOpen = False Then
            AlreadyOpen = True
        Else
            ' Put here the code you want to execute
            ' on the Current event.
        End If
    
    End Sub
    But the main question that remains is: why do you have to perform a DLookUp on the second record?
    Have a nice day!

  6. #6
    Join Date
    Sep 2009
    Posts
    23
    Thnx for your help.It works perfect now.Actually its not only in the second record,dlookup function performs everytime the user changes from one record to another.When the main form is loaded,it already perfom the dlookup in the first row to fetch the default value.
    Last edited by aldwin890; 09-12-09 at 11:53.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad you got it working, but the question that is bugging Sinndho and myself is what are you doing that doesn't need to be done on the first record? It's unusual to have to do something when moving onto any record except the first one.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Sep 2009
    Posts
    23
    Pls refer attached snapshot of the invoice form.
    As you can see the main form has textboxes QtyOnHand,UnitsPending and Units Available.In the datasheet,there is DeptID and PartID columns in which both of the fields are unique identification of the Inventory.I have a table TInventory that has DeptID,PartID,QtyOnHand,UnitsPending,UnitsAvailabl e field.Everytime the record in datasheet is selected,I want to show to the textbox the QtyOnHand,UnitsPending,UnitsAvailable of the particular inventory,this is where I used the Dlookup.
    In the very first load of the main form,I want to have a default value of the textboxes.This is the code:
    Private Sub Form_Load()
    QtyOnHand = DLookup("QtyOnHand", "TWhouseInv", _
    "DeptID = '" & Me.cboDeptID & _
    "' AND PartID = '" & Me.cboPartID & "'")
    UnitsPending = DLookup("UnitsPending", "TWhouseInv", _
    "DeptID = '" & Me.cboDeptID & _
    "' AND PartID = '" & Me.cboPartID & "'")
    UnitsAvailable = DLookup("UnitsAvailable", "TWhouseInv", _
    "DeptID = '" & Me.cboDeptID & _
    "' AND PartID = '" & Me.cboPartID & "'")
    End Sub

    If the user adds/edit the record,the code in the "Private Sub Form_Load()" is executed which is also executed if user will change from one record to another.

    And this is the code if user change from one record to another.
    Private Sub Form_Current()
    'display QtyOnHand,UnitsPending,QtyAvailable in main form
    Static AlreadyOpen As Boolean

    If AlreadyOpen = False Then
    AlreadyOpen = True
    Else
    QtyOnHand = DLookup("QtyOnHand", "TWhouseInv", _
    "DeptID = '" & Me.cboDeptID & _
    "' AND PartID = '" & Me.cboPartID & "'")
    UnitsPending = DLookup("UnitsPending", "TWhouseInv", _
    "DeptID = '" & Me.cboDeptID & _
    "' AND PartID = '" & Me.cboPartID & "'")
    UnitsAvailable = DLookup("UnitsAvailable", "TWhouseInv", _
    "DeptID = '" & Me.cboDeptID & _
    "' AND PartID = '" & Me.cboPartID & "'")
    End If
    End Sub

    GUYS once again thank you.I've been trying to solve this problem for 2 days and I got it running with Sinndho's help.CHEERS!!!
    Attached Files Attached Files

Posting Permissions

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