code: TestIfSubform, How to Create a General Module, Access Basics
---
Hi Kelly,
"insert the code in the design view of the subform"
the code needs to be put in the module behind the form. To see this
module, from the menu in design view, choose -->
View, Code
after inserting the code and debugging, it will be saved automatically
when you save the form. You can click the diskette icon in the code
view or the form view to save the form
"invalid reference to the Parent Property"
the code will only work when the form is a subform on the parent form.
You can incorporate a test to see if the subform is opened by itself or
as a subform and only do the find if it is a subform:
put this into a general module
'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number > 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~
and incorporate this function into your code...
'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()
'save current record if changes were made
If me.dirty then me.dirty = false
If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if
'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if
'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if
'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"
'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
else
msgbox "Part Number " & me.PartNum & " was not found" _
,, "Error locating part"
End If
End Function
'~~~~~~~~~~~~~~~`
How to Create a General Module
1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in
once the code is in the module sheet, do
Debug,Compile from the menu
if there are no syntax/reference errors, nothing will appear to happen
-- this is good
Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.
In addition to the VBA chapters I sent you, here is another good
reference to prepare yourself for programming with Access:
Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*