Solved

How to get a VBA code to find a subform

Posted on 2001-07-16
6
585 Views
Last Modified: 2008-02-01
I have an Access db with an Invoice form that also had a detail subform.  The Invoice is an order form and the detail subform lists: quantity, description item ordered, and the inventory in stock for that item.  I have a cmd button that activates a module that is to update the inventory.  It is to find the description item ordered in the inventory table and subtract the quantity ordered from the stock.  I cannot get the code to find the subform no matter how I type it.  How do you code VBA in order to find fields in a subform?
0
Comment
Question by:tlwolf
6 Comments
 
LVL 19

Expert Comment

by:frankytee
Comment Utility
try the following:

me.frmSub.form!fieldX

where frmSub is name of your subform, fieldX name of field
0
 
LVL 1

Accepted Solution

by:
ravl earned 300 total points
Comment Utility
To access a field on the sub-form from an event in the main form the format is:
Forms![MainFormName].[SubFormName]![SubFormFieldName]

So if your main form is called frmOrder and your sub-form is called frmDetail, then to reference the Quantity field in the sub-form you would type:
Forms![frmOrder].[frmDetail]![Quantity]
0
 
LVL 19

Expert Comment

by:frankytee
Comment Utility
my code assumes you are trying to reference the subform from your main form.

me.frmSub.form!fieldX

if your name of your subform or field has spaces then you would have to enclose them in [ ] square brackets as ravl stated.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:hotbudare
Comment Utility
I'd use code like this:

private sub test()
dim rs as dao.recordset
dim fld as dao.field

    set rs = forms("InvoiceFormName").Controls("SubformControlName").Form.Recordset
    do while not rs.eof
        for each fld in rs.fields
            debug.print fld.name & ":" & fld.value
        next fld
        rs.move.next
    loop
    set fld=nothing
    rs.close
    set rs=nothing
end  sub

HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela

0
 
LVL 12

Expert Comment

by:Paurths
Comment Utility
hi tlwolf,

this might be usefull anytime working with subforms :

http://www.mvps.org/access/forms/frm0031.htm

cheers
Ricky
0
 

Author Comment

by:tlwolf
Comment Utility
Here is my code.  It works properly except it will not move to the next record in the subform.  Can you make suggestions to help me complete this task?

Public Function UpdateInventory()

'declare variables and assign address to object variables

Dim cnnMSF As ADODB.Connection
Set cnnMSF = Application.CurrentProject.Connection

Dim Description As String, Quantity As Long, Stock As Long
Dim rstInventory As ADODB.Recordset, Invoice As Form
   
Set rstInventory = New ADODB.Recordset
Set rstInvoiceDetails = New ADODB.Recordset
Set Invoice = Application.Forms("Invoice")

'open recordset Inventory & InvoiceDetails, Invoice should be open already
rstInventory.Open Source:="Inventory", ActiveConnection:=cnnMSF, _
    CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic
rstInvoiceDetails.Open Source:="InvoiceDetails", ActiveConnection:=cnnMSF, _
    CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic
   
'assign values to controls on the form
Description = Forms![Invoice].[InvoiceDetails]![Description]
Quantity = Forms![Invoice].[InvoiceDetails]![Quantity]
Stock = Forms![Invoice].[InvoiceDetails]![Stock]

'search for the Description in Description field
rstInventory.Find criteria:="Description = '" & Description & "'"

'calculate Stock remaining after invoice purchase
Do Until rstInvoiceDetails.EOF
    If Stock >= Quantity Then
        rstInventory.Fields("Stock").Value = Stock - Quantity
    End If
    'move record pointer to the next record
    rstInvoiceDetails.MoveNext
Loop

'save changes to record
rstInventory.Update

'close the recordset
rstInventory.Close
Set rstInventory = Nothing        'disassociate object variable from object

'close the recordset
rstInvoiceDetails.Close
Set rstInvoiceDetails = Nothing        'disassociate object variable from object

End Function
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now