In my workflow, I have the user create a new member via a command box in the parent form. Next, in order to maintain data integrity, I immediately have the user create an associated grower record, that links one to one to the parent member record. My code below accomplishes both of these tasks. I use the openargs to pass the newly created BUSINESS.MEMBER_ID (an auto number in the parent table) to the load event of the child form.
After the load event is finished, I want to open the form to the same record that was just created. And although my statement hold the values of the newly created GROWER.MEMBER_ID just created, when I call the form to open to that particular record, only a blank form shows up and no record....!!!
If someone can see what I'm doing wrong in my code, please......help.............CementCarver
(Section One..... Create new Member)
------------------------------------
Sub CREATE_NEW_MEMBER_Click()
'Sub AddMember(rst1BUSINESS As Recordset)
Dim rst1 As DAO.Recordset, IntMax_Mem As Integer
Dim Business_lnk As Integer
Set db = CurrentDb
Set rst1BUSINESS = db.OpenRecordset("BUSINESS")
Dim strName As String
strName = InputBox("Please Enter New OGVG Member Name?", "CREATE NEW OGVG MEMBER", "")
'MsgBox "You Entered " & strName
With rst1BUSINESS
.AddNew
![BUSINESS_NAME] = strName
Business_lnk = !MEMBER_ID
.Update
.Bookmark = .LastModified
'Debug.Print rst1("MEMBER_ID")
rst1BUSINESS.Close
End With
Me![Combo126].Requery
DoCmd.Requery
Me.Refresh
DoCmd.GoToControl "BUSINESS_NAME"
DoCmd.FindRecord strName, , True, , True, , True
DoCmd.OpenForm "BUSINESS_MAIN", , , , , , strName
End Sub
-----------------------------------------------------
(Section Two, Form Load Event)
Private Sub Form_Load()
Dim intOpenArgs As Integer
'take incoming openargs to set form load passing BUSINESS.MEMBER_ID
intOpenArgs = Forms!frm_CREATE_FULLGROWER.OpenArgs
DoCmd.OpenForm "frm_CREATE_FULLGROWER", , , , , , intOpenArgs
End Sub
---------------------------------------------------------
(Section Three - Create the GROWER record and open the form based on this new record)
Private Sub Create_Grower_Record_Click()
Dim rstGrower As Recordset
Dim rst1BUSINESS As Recordset
Dim strBUSINESS_NAME As String
Dim IntFarm_num As Integer
Dim IntMax_Mem As Integer
Dim IntBusMax As Integer
If Me.Check_Grower = True Then
MsgBox "Grower Check Box has been selected"
ElseIf Me.Check_Marketer = True Then
MsgBox "Marketer Check Box has been selected"
ElseIf Me.Check_Packer = True Then
MsgBox "Packer Check Box has been selected"
End If
Set db = CurrentDb
Set rstGrower = db.OpenRecordset("FULLGROWER")
strBUSINESS_NAME = [Forms]![BUSINESS_MAIN]![BUSINESS_NAME]
AddGrower rstGrower, strBUSINESS_NAME
End Sub
Public Sub AddGrower(rstGrower As Recordset, _
strBUSINESS_NAME As String)
Dim Grower_lnk As Integer
IntFarm_num = DMax("FARM_NO", "FULLGROWER")
IntFarm_num = IntFarm_num + 1
' Insert grower record
With rstGrower
.AddNew
![BUSINESS NAME] = strBUSINESS_NAME
![Farm_No] = IntFarm_num
Grower_lnk = Forms!BUSINESS_MAIN!MEMBER_ID
!MEMBER_ID = Grower_lnk
.Update
.Bookmark = .LastModified
rstGrower.Close
End With
DoCmd.Requery
Me.Refresh
'Pass arguments to form load event
DoCmd.OpenForm "frm_CREATE_FULLGROWER", , , , , , Grower_lnk
'Open the form based on where clause
sWHERE = "[MEMBER_ID] = " & Grower_lnk
-----PROBLEM LINE BELOW------------when I track the value in a watch for that variable, the correct values are being carried,
but the form only open on a blank record.....!!!!!!!
DoCmd.OpenForm "frm_CREATE_FULLGROWER", acNormal, , sWHERE 'newly created record FULLGROWER.MEMBER_ID
End Sub