tlwolf
asked on
How to get a VBA code to find a subform
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
I'd use code like this:
private sub test()
dim rs as dao.recordset
dim fld as dao.field
set rs = forms("InvoiceFormName").C ontrols("S ubformCont rolName"). Form.Recor dset
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
private sub test()
dim rs as dao.recordset
dim fld as dao.field
set rs = forms("InvoiceFormName").C
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
hi tlwolf,
this might be usefull anytime working with subforms :
http://www.mvps.org/access/forms/frm0031.htm
cheers
Ricky
this might be usefull anytime working with subforms :
http://www.mvps.org/access/forms/frm0031.htm
cheers
Ricky
ASKER
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 .Connectio n
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:=adOpenForwardO nly, LockType:=adLockPessimisti c
rstInvoiceDetails.Open Source:="InvoiceDetails", ActiveConnection:=cnnMSF, _
CursorType:=adOpenForwardO nly, LockType:=adLockPessimisti c
'assign values to controls on the form
Description = Forms![Invoice].[InvoiceDe tails]![De scription]
Quantity = Forms![Invoice].[InvoiceDe tails]![Qu antity]
Stock = Forms![Invoice].[InvoiceDe tails]![St ock]
'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
Public Function UpdateInventory()
'declare variables and assign address to object variables
Dim cnnMSF As ADODB.Connection
Set cnnMSF = Application.CurrentProject
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:=adOpenForwardO
rstInvoiceDetails.Open Source:="InvoiceDetails", ActiveConnection:=cnnMSF, _
CursorType:=adOpenForwardO
'assign values to controls on the form
Description = Forms![Invoice].[InvoiceDe
Quantity = Forms![Invoice].[InvoiceDe
Stock = Forms![Invoice].[InvoiceDe
'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
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
me.frmSub.form!fieldX
where frmSub is name of your subform, fieldX name of field