Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    Kent
    Posts
    2

    Red face Unanswered: Mixing Subforms and VBA

    Heres a problem for u!

    Microsoft give Access two abilities, that i need to interact in order to make my program work!!!

    These are forms being able to contain subforms, and the use of VBA to manipulate objects in a form, however, i cannot seem to write a Visual Basic for applications that can manipulate data in a object, that is contained in a subform.

    I have been told that it is better to manipulate the data when it is in a query, but to do this, i will need to re-make the majority of my program.

    And before you ask, i am new to Access, and i am not that good at VBA, yet!!!

    This is what i originally tried out, the old 'address' to an object in a form, but with the name of the subform included(but typical access gave me a nice error message):

    Forms![frm New Issues].[subfrm reader].loannumber

    (and dont say anything about the spaces, i only found out about the so called 10 commandments of access at www.mvps.com/access.index.html a week ago!!!!)

    PLEASE HELP!!!!

    thanks

    sab

  2. #2
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168
    This annoyed me for ages. It's not very obvious. Here you go:

    Forms![MainFormName].[SubformName].Form.[ControlName].Value = "Whatever"

    Hope this helps.

    P.s. I use spaces in my names sometimes. It rarely matters. I bet I get loads of replies slating me now. Maybe someone should set up a poll to see how many people do use spaces.
    J.

  3. #3
    Join Date
    Feb 2003
    Location
    Kent
    Posts
    2
    Thank Mate!

    I will try that!!! And i agree, i think we should start up a conversation about using spaces!!!

    thanks again
    sab

  4. #4
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    There's one tripwire if you don't create the subform with the access wizard:

    The name of of the subform control you place on your form may be different from the name of its SourceObject Property (which is the name of the form you want to display within). You have to use the name of the control, not the name of the form.

    Sometimes you can omit the .form! part, but it should be safe to use it always.

  5. #5
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Re: Mixing Subforms and VBA

    Hi posting two of my Functins in The project which interact with MAIN FORM and SUBFORM. This is perfect example of VBA code. this Function is placed in Module and is called from Subform.
    Take A look at it , hope this gives u an idea of how VBA is and how to Ineract with Controls and Data flowing from Form and Subform.

    'Function to Transfer Ripped Inventory according to Work Orders
    Public Function transferWOItemRipped(itemNo As String, QtyTransfer4 As Long) As Long
    If ErrorTrapping = True Then
    On Error GoTo transferWOItemRipped_err_handler
    End If
    Dim SQLSTmt, itemNum As String
    Dim curdb As Database
    Dim formname, Form As Form
    Dim forthInv, ThirdInvCheck, thirdInvTransfer As Recordset
    Dim RemoveInventory, Clear As Boolean
    Dim Cost As Currency
    Set curdb = CurrentDb()
    Set Form = Forms![yworkOrder2].Form
    Set formname = Forms![yworkOrder2]![zworkOrderLineItem].Form
    If Right$(itemNo, 6) = "MFJPCB" Then
    Call transferMFJPCB(itemNo, QtyTransfer4)
    Exit Function
    ElseIf Left$(itemNo, 5) = "FJPCM" Then
    Call transferFJPCM(itemNo, QtyTransfer4)
    Exit Function
    Else
    SQLSTmt = "select * from [Inventory] where [Item Number]= '" & Form![Item Number] & "'"
    Set forthInv = curdb.OpenRecordset(SQLSTmt, dbOpenDynaset)
    If forthInv.EOF = True Then
    MsgBox "Item Number Does Not Exist In Inventory Table" & vbCrLf & "Please Enter The Item In Inventroy Table" & vbCrLf & "Or Make Sure You Have Entered Right Item Number", vbCritical, "Check Ripped Inventory"
    Exit Function
    Else
    SQLSTmt = "select * from [Inventory] where [Item Number] = '" & itemNo & "' And [Category] ='RIPPED'"
    Set ThirdInvCheck = curdb.OpenRecordset(SQLSTmt, DB_OPEN_DYNASET)
    If ThirdInvCheck.EOF = True Then
    MsgBox "Item Number Does Not Exist In Inventory Table" & vbCrLf & "Please Enter The Item In Inventroy Table" & vbCrLf & "Or Make Sure You Have Entered Right Item Number", vbCritical, "Check Ripped Inventory"
    Exit Function
    Else
    If (ThirdInvCheck![Quantity In Stock] < QtyTransfer4) Then
    response = MsgBox(" You Dont Have Enough Inventory In Ripped Stock , Would You Like To Check In Planned? ", vbYesNo)
    If response = ID_YES Then
    DoCmd.GoToRecord , , acNewRec
    Exit Function
    End If
    End If
    If ((ThirdInvCheck![Quantity In Stock] > QtyTransfer4) Or (ThirdInvCheck![Quantity In Stock] = QtyTransfer4)) Then
    Call RemoveInventoryExca(itemNo, RemoveInventory)
    itemNum = itemNo
    RecalcItemsandCost (itemNum)
    Cost = ThirdInvCheck![Cost]
    If ((formname![Quantity to Transfer] = 0) Or (formname![Quantity to Transfer] = "")) Then
    forname![Quantity to Transfer] = formname![Quantity Reqd]
    End If
    SQLSTmt = "Insert into [Inventory Products] ([Item Number],[Date Received],[Cost],[Quantity In Stock],[Quantity Received]) Values ('" & Form![Item Number] & "','" & Date & "','" & Cost & "','" & QtyTransfer4 & "','" & QtyTransfer4 & "')"
    curdb.Execute (SQLSTmt)
    itemNum = Form![Item Number]
    RecalcItemsandCostExca (itemNum)
    MsgBox "Quantity" & " " & QtyTransfer4 & " " & "Has Been Transfered" & vbCrLf & "Please Remeber That You Can Manually Adjust Inventory Yourself" & vbCrLf & "Under Purchase tab, Click On Manage Inventory,Then Click on InStock" & vbCrLf & "And Click On Adjust Inventory", vbOK, "Inventory Transfer"
    End If
    End If
    End If
    End If
    forthInv.Close
    ThirdInvCheck.Close
    curdb.Close
    Set forthInv = Nothing
    Set ThirdInvCheck = Nothing
    Set curdb = Nothing
    Exit Function
    transferWOItemRipped_err_handler:
    DispError "Transfering Ripped Work Order Item", "transferWOItemRipped"
    Exit Function
    End Function]


    ---------------------------------------------------------------------------
    'Functorn to Transfer Inventory according to Work Orders

    Public Function transferWOItemPlanned(itemNo As String, QtyTransfer4 As Long) As Long
    If ErrorTrapping = True Then
    On Error GoTo transferWOItemPlanned_err_handler
    End If
    Dim curdb As Database
    Dim formname, Form As Form
    Dim SQLSTmt, secondItem, ItemNoRipped As String
    Dim itemNum As String
    Dim QtyReqd As Long
    Dim RemoveInventory As Boolean
    Dim thirdInv, SecondInvTransfer As Recordset
    Set curdb = CurrentDb()
    Set formname = Forms![yworkOrder2]![zworkOrderLineItem].Form
    ItemNoRipped = formname![Item Transfer]
    SQLSTmt = "select * from [Inventory] where [Item Number]= '" & ItemNoRipped & "'"
    Set thirdInv = curdb.OpenRecordset(SQLSTmt, dbOpenDynaset)
    If thirdInv.EOF = True Then
    MsgBox "Item Number Does Not Exist In Inventory Table", vbCritical, "Transfering Inventory"
    Exit Function
    Else
    SQLSTmt = "select * from [Inventory] where [Item Number] = '" & itemNo & "' And [Category] ='PLANNED'"
    Set SecondInvTransfer = curdb.OpenRecordset(SQLSTmt, DB_OPEN_DYNASET)
    If SecondInvTransfer.EOF = True Then
    MsgBox "Item Number" & itemNo & "Does Not Exist In Inventory Table", vbCritical, "Transfering Inventory"
    Exit Function
    Else
    If (SecondInvTransfer![Quantity In Stock] < QtyTransfer4) Then
    MsgBox " You Do Not Have Enough Inventory In Planned Stock" & vbCrLf & "Please Place A Work Order for This Item Number", vbCritical, "Transfer Inventory"
    Exit Function
    ElseIf ((SecondInvTransfer![Quantity In Stock] > QtyTransfer4) Or (SecondInvTransfer![Quantity In Stock] = QtyTransfer4)) Then
    Call RemoveInventoryExca(itemNo, RemoveInventory)
    itemNum = itemNo
    RecalcItemsandCostExca (itemNum)
    Call ConvertQtyToLF(ItemNoRipped, QtyTransfer4)
    Cost = thirdInv![Cost]
    SQLSTmt = "Insert into [Inventory Products] ([Item Number],[Date Received],[Cost],[Quantity In Stock],[Quantity Received]) Values ('" & itemNo & "','" & Date & "','" & Cost & "','" & formname![Quantity to Produce] & "','" & formname![Quantity to Produce] & "')"
    curdb.Execute (SQLSTmt)
    itemNum = ItemNoRipped
    RecalcItemsandCostExca (itemNum)
    QtyReqd = formname![Quantity Reqd]
    Call ConvertQtyReqdToLF(QtyReqd)
    End If
    End If
    End If
    thirdInv.Close
    SecondInvTransfer.Close
    curdb.Close
    Set thirdInv = Nothing
    Set SecondInvTransfer = Nothing
    Set curdb = Nothing
    Exit Function
    transferWOItemPlanned_err_handler:
    DispError " Transfering Planned Work Order", "transferWOItemPlanned"
    Exit Function
    End Function

Posting Permissions

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