Save Value of Field and use it once form closed

[willbo987]

Hi there,

Thanks for any help you can offer,

I have a MainForm.

Using a button on the MainForm i open an PopUp form which I enter values.

I want to save the value of a field (text), close the PopUp form.
Then use the Saved value in the MainForm to find a record.

So far I have this but not working:

TempVars.Add "company", Me.CompanyName.Value
If Me.Dirty = True Then
Me.Undo
End If
DoCmd.Close
DoCmd.OpenForm "MainForm"
DoCmd.Requery
DoCmd.FindRecord Forms![MainForm]![CompanyName] = TempVars("company")
DoCmd.GoToControl "Combo48"

 

[MarkK]

What I very commonly do is put this routine on a form, so other objects can get the form to find it's own record . . .

Public Sub GoToID(RecordID as Long)
   With Me.RecordsetClone
      .FindFirst "RecordID = " & RecordID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

. . . and obviously you need to replace "RecordID" with the actual name of the primary key for the table in question. But once the form exposes that method, then other consumers can call that method with ease, like (modifying the code you posted) . . .

const FN as string = "MainForm"

[COLOR="Green"]'TempVars.Add "company", Me.CompanyName.Value
'If Me.Dirty = True Then
'Me.Undo
'End If
[/COLOR]DoCmd.OpenForm FN
With Forms(FN)
   .GoToID Me.CompanyName.Value[COLOR="Green"] 'here we call our new custom method[/COLOR]
   .Combo48.SetFocus
End With
DoCmd.Close acForm, Me.Name
[COLOR="Green"]'DoCmd.Requery
'DoCmd.FindRecord Forms![MainForm]![CompanyName] = TempVars("company")
'DoCmd.GoToControl "Combo48"[/COLOR]

Hope this helps,

 

[willbo987]

Ok so i am a bit of a newbie...

I have put this vba code on the main form.
BTW the main form is called frmDCPCConsortiumMembers.

Public Sub GoToID(CompanyName As Integer)
With Me.RecordsetClone
.FindFirst "CompanyName = " & CompanyName
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

On the PopUp form (called frmAddDCPCConsortiumMember) I have this.
It is called on a BeforeUpdate combo field:

Private Sub CompanyName_BeforeUpdate(Cancel As Integer)
Const FN As Integer = "frmDCPCConsortiumMembers"
TempVars.Add "company", Me.CompanyName.Value
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblDCPCConsortiumMembers SET Archived = 0 WHERE CompanyName = " & Me!CompanyName & ""
DoCmd.SetWarnings True
If Me.Dirty = True Then
Me.Undo
End If
With Forms(FN)
.GoToID Me.CompanyName.Value
.Combo48.SetFocus
End With
DoCmd.Close acForm, Me.Name
DoCmd.Requery
End Sub

I am getting a mismatch code

 

[willbo987]

Ok start again,

Made a few mistakes.

On the MainForm I have this:

Public Sub GoToID(CompanyName As Long)
   With Me.RecordsetClone
        .FindFirst "CompanyName = " & CompanyName
        If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

On the Popup Form I have this:

Private Sub CompanyName_BeforeUpdate(Cancel As Integer)
Const FN As String = "frmDCPCConsortiumMembers"
TempVars.Add "company", Me.CompanyName.Value
If Me.Dirty = True Then
Me.Undo
End If
DoCmd.OpenForm FN
With Forms(FN)
.GoToID TempVars("company")
.Combo48.SetFocus
End With
DoCmd.Close acForm, Me.Name
DoCmd.Requery
End Sub

I am not getting mismatch code now the MainForm does not go to a record

 

[willbo987]

SORTED IT!!!!

Made the form requery before going to record !!!!


Thank you THank you Thankyou

 

[MarkK]

Nice job!!!