Re: Update subform field from pop-up form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

If there is not a textbox or combo for EventNo on the popup, then change the
code to:

Private Sub cmdEncounter_Click()
On Error GoTo Err_cmdEncounter_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_encounter_lookup"
stLinkCriteria = "[MRN]=" & "'" & Me![frmRelayMR] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

With Forms!frm_encounter_lookup
If Not IsNull(.Account) Then
Me.Account = .Account
End If
End With
DoCmd.Close acForm, "frm_encounter_lookup"

Exit_cmdEncounter_Click:
Exit Sub
Err_cmdEncounter_Click:
MsgBox Err.Description
Resume Exit_cmdEncounter_Click
End Sub

Jeanette Cunningham










"Jeanette Cunningham" <nnn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OlTA0rfXIHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
the code all goes in the event for cmdEncounter_Click as shown below.

Private Sub cmdEncounter_Click()
On Error GoTo Err_cmdEncounter_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_encounter_lookup"
stLinkCriteria = "[MRN]=" & "'" & Me![frmRelayMR] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

With Forms!frm_encounter_lookup
If Not IsNull(.Account) Then
Me.Account = .Account
End If
If Not IsNull(.EventNo) Then
Me.EventNo = .EventNo
End If
End With
DoCmd.Close acForm, "frm_encounter_lookup"

Exit_cmdEncounter_Click:
Exit Sub
Err_cmdEncounter_Click:
MsgBox Err.Description
Resume Exit_cmdEncounter_Click
End Sub


3. Also, I would like to have one command button to select the record on
the
pop-up form, close the pop-up and change the Account field on
FrmSubEvent, is
that possible?
You need a Close button on frm_encounter_lookup that makes this form
invisible when it is clicked.
You need a way for the user to enter the account no or choose it, before
they click the Close button.
Where does the value for EventNo come from - I assume not from
frm_encounter_lookup - but your code showed that you also need that value.

Jeanette Cunningham

Where would I place the code on FrmSubEvent ? I have tried in the On Got
Focus and On Activate events of the Subform FrmSubEvent with no luck. So
to
review:

1. I have the following code on a command button on the subform
FrmSubEvent
to open the pop-up form as acDialog.

Private Sub cmdEncounter_Click()
On Error GoTo Err_cmdEncounter_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_encounter_lookup"
stLinkCriteria = "[MRN]=" & "'" & Me![frmRelayMR] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

Exit_cmdEncounter_Click:
Exit Sub
Err_cmdEncounter_Click:
MsgBox Err.Description
Resume Exit_cmdEncounter_Click
End Sub

2. Per your suggestion I have added the following to FrmSubEvent

Private Sub Form_GotFocus()
With Forms!frm_encounter_lookup
If Not IsNull(.Account) Then
Me.Account = .Account
End If
If Not IsNull(.EventNo) Then
Me.EventNo = .EventNo
End If
End With
DoCmd.Close acForm, "frm_encounter_lookup"
End Sub

3. Also, I would like to have one command button to select the record on
the
pop-up form, close the pop-up and change the Account field on
FrmSubEvent, is
that possible?



"Jeanette Cunningham" wrote:

That's probably because the current record in the subform has not yet
been
saved to disk.
If you let the user select the value on FrmLookupVisit, then when they
click
the close button on FrmLookupVisit, instead of closing the form, you
just
hide it, your subform FrmSubEvent can read the value from FrmLookupVisit
and
only then close the popup.

So put code to open the popup in acDialog mode.
After user clicks the close button,
code on FrmSubEvent checks that values were entered on the popup,
and if it finds values, it gets the values for the controls for its
currently open, unsaved record.

Here is an example of the type of code to use:
-------------------------
DoCmd.OpenForm "FrmLookupVisit", , , , , acDialog
'after user clicks close, FrmLookupVisit becomes invisible and the code
back
here on FrmSubEvent continues
With Forms!FrmLookupVisit
If Not IsNull(.txtAccountOnThePopup) Then
Me.txtAccount = .txtAccountOnThePopup
End If
If Not IsNull(.txtEventNoOnThePopup) Then
Me.txtEventNo = .txtEventNoOnThePopup
End If
End With
DoCmd.Close acForm, "FrmLookupVisit"
--------------------------
change the control names to the names on your forms.

Jeanette Cunningham


"yator" <yator@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A58F39F0-B157-45F0-9039-53B443805232@xxxxxxxxxxxxxxxx
I have a Form with 2 subforms. I need the ability to change the Child
Field
on the second subform to a value found in a pop-up form. I have the
following
structure:

FrmMain
Primary Key: ClientNo

FrmSubVisit
Record Source: q_frm_sub_visits_active
Field 1: Account
Field 2: AccDate
Link Child Fields: ClientNo
Link Master Fields: ClientNo
Default View: Continuous

FrmSubEvent
Record Source: q_frm_sub_events
Field 1: Account
Field 2: EventNo
Link Child Fields: Account
Link Master Fields: txtRelayAccount (text relay on FrmMain from
frmSubVisit)
Default View: Single Form

The "Account" on frmSubEvent may need to be changed to a previous
inactive
Account. A command button opens pop-up form FrmLookupVisit to find all
accounts related to the current ClientNo.

FrmLookupVisit
Record Source: tbl_visits
Field 1: Account
Field 2: EventNo
Default View: Continuous

A Command Button on the pop-up form should select a record and change
"Account" on frmSubEvent, and close the pop-up. I have tried the
following
code on the On Click property of the VisitSelect button, but receive
the
error:
"You can't add or change a record because a related record is required
in
table "tbl_Visits".
I am able to change the field manually with no problem.

Private Sub cboVisitSelect_Click()
Dim db As DAO.Database
On Error GoTo Err_cboVisitSelect_Click

Dim SQL As String
Set db = CurrentDb()

SQL = "UPDATE tbl_event " & _
"SET tbl_event.Account = '" & Me![Account] & "' " & _
" WHERE EventNo = " & Me![EventNo]

db.Execute SQL, dbFailOnError

Exit_cboVisitSelect_Click:
Exit Sub

Err_cboVisitSelect_Click:
MsgBox Err.Description
Resume Exit_cboVisitSelect_Click

End Sub









.

Quantcast