Copy field from one form to another.

[juancarlos]

Hello!

I have two tables, one is the "Cases" which has a ton of fields, including:
Policy, Name, Date Opened, Date Closed, Correspondent, etc. No duplicates allowed.

The other table is "Phone Calls", which has, among others:
Policy, Who Called, Relationship, Date/Time, Who Took The Call, Message, etc.

They're linked by the Policy field, so any Case could have several phone calls.

The correspondents use a form that displays the Case information neatly. When anyone gets a call, they check this form to see if there's a Case open, if there is, they click a button within this form that opens another form to log the phone call. What I want to do is take the current Case displayed on their screen (the one they just found) and have it populate the corresponding field in the Phone Call form. Currently, they get a blank form and they must make sure to enter the correct policy number so that the call gets matched.

I thought maybe a macro that would, upon clicking a button, copy the Policy field, open the form, paste the policy number into the Policy field of the new form might be a solution...but I haven't figured out a way to implement that. Or there may be a much simpler way of doing this.

Any insight will be greatly appreciated.

-JC

 

[theDBguy]

Hi JC. Did you say the user clicks a button to open the call list form? If so, then you already have a place to put the code to add the policy number. Can you post the code behind the button they click to open the call form? Thanks.

 

[juancarlos]

Thanks!

here's the code:

Private Sub Touchpoint_Click()
On Error GoTo Err_Touchpoint_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Phone Calls Log"
    
    stLinkCriteria = "[Policy]=" & "'" & Me![CORREO INTERNACIONAL.Policy] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Touchpoint_Click:
    Exit Sub
Err_Touchpoint_Click:
    MsgBox Err.Description
    Resume Exit_Touchpoint_Click
    
End Sub

Correo Internacional is the name of the Cases table (it's a legacy thing).

 

[theDBguy]

Hi. Try replacing this part of the code with the following:

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.Policy

Then, in the Open Event of the Call Log form, enter the following code:

If Not IsNull(Me.OpenArgs) Then
    Me.Policy.DefaultValue = Me.OpenArgs
End If

 

[MajP]

Private Sub Touchpoint_Click()
On Error GoTo Err_Touchpoint_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Phone Calls Log"
    
    stLinkCriteria = "[Policy]=" & "'" & Me![CORREO INTERNACIONAL.Policy] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
   [B] 'the code keeps running so you can manipulate the called form here
    Forms(stDocName).CaseFieldName = Me.CaseFieldName[/B]
Exit_Touchpoint_Click:
    Exit Sub
Err_Touchpoint_Click:
    MsgBox Err.Description
    Resume Exit_Touchpoint_Click
    
End Sub

 

[juancarlos]

TheDBguy: It's giving me a compile error: "method or data member not found" and it highlights Me.Policy

 

[theDBguy]

On which code, the first or the second one?

 

[juancarlos]

The first one:

Private Sub Touchpoint_Click()
On Error GoTo Err_Touchpoint_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Phone Calls Log"
    
    stLinkCriteria = "[Policy]=" & "'" & Me![CORREO INTERNACIONAL.Policy] & "'"
'   DoCmd.OpenForm stDocName, , , stLinkCriteria
   DoCmd.OpenForm stDocName, , , stLinkCriteria, , , [U]Me.Policy[/U]
  

Exit_Touchpoint_Click:
    Exit Sub
Err_Touchpoint_Click:
    MsgBox Err.Description
    Resume Exit_Touchpoint_Click
    
End Sub

 

[theDBguy]

Okay, please replace it with the whole coreo...policy.

 

[juancarlos]

I...should've seen that. Why does it want me to type the whole thing? is it because the field is in two tables? Would it have been better to call one "Policy" and another "Policy Number" or just something different?

Thanks, by the way, works like a charm. The minions are gonna love it.

-JC

 

[theDBguy]

Hi JC. Congratulations! Glad to hear you got it to work. Good luck with your project.