Update subform field from pop-up form

Y

yator

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
 
J

Jeanette Cunningham

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 said:
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
Click to expand...
 
Y

yator

Jeanette,
Thanks for the response.

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

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?
Click to expand...
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

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 said:
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?
Click to expand...
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
Y

yator

Thanks again Jeanette, but what code on my Close command button would make
the frm_encounter_lookup invisible?

Jeanette Cunningham said:
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?
Click to expand...
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Me.Visible = False

Private Sub cmdClose_Click()
Me.Visible = False
End Sub

Jeanette Cunningham

yator said:
Thanks again Jeanette, but what code on my Close command button would make
the frm_encounter_lookup invisible?

Jeanette Cunningham said:
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?
Click to expand...
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?



:

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


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
Click to expand...
Click to expand...
Click to expand...
 
Y

yator

This works perfect! One last question: frm_encounter_lookup is a continuous
form, if I hide the Record Selectors on the form, what code coul dI use to
make my cmdClose_Click() button Select the record and make the pop-up form
invisible?
thanks!

Jeanette Cunningham said:
Me.Visible = False

Private Sub cmdClose_Click()
Me.Visible = False
End Sub

Jeanette Cunningham

yator said:
Thanks again Jeanette, but what code on my Close command button would make
the frm_encounter_lookup invisible?

Jeanette Cunningham said:
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?



:

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


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
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

In that case I suggest that you replace the continuous form with a combo
box or listbox on a single form. This way the user can easily understand how
to select the record they want and your code will be able to pick up the
value the user selected.
Note: the cmdClose button only makes the form invisible.
Back on your main form the code reads the selected record from the combo or
list box from the open but hidden form.

Jeanette Cunningham

yator said:
This works perfect! One last question: frm_encounter_lookup is a
continuous
form, if I hide the Record Selectors on the form, what code coul dI use to
make my cmdClose_Click() button Select the record and make the pop-up form
invisible?
thanks!

Jeanette Cunningham said:
Me.Visible = False

Private Sub cmdClose_Click()
Me.Visible = False
End Sub

Jeanette Cunningham

yator said:
Thanks again Jeanette, but what code on my Close command button would
make
the frm_encounter_lookup invisible?

:

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?



:

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


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
Click to expand...
Click to expand...
Click to expand...
 
Y

yator

I see your point. Thanks for all your help!!!

Jeanette Cunningham said:
In that case I suggest that you replace the continuous form with a combo
box or listbox on a single form. This way the user can easily understand how
to select the record they want and your code will be able to pick up the
value the user selected.
Note: the cmdClose button only makes the form invisible.
Back on your main form the code reads the selected record from the combo or
list box from the open but hidden form.

Jeanette Cunningham

yator said:
This works perfect! One last question: frm_encounter_lookup is a
continuous
form, if I hide the Record Selectors on the form, what code coul dI use to
make my cmdClose_Click() button Select the record and make the pop-up form
invisible?
thanks!

Jeanette Cunningham said:
Me.Visible = False

Private Sub cmdClose_Click()
Me.Visible = False
End Sub

Jeanette Cunningham

Thanks again Jeanette, but what code on my Close command button would
make
the frm_encounter_lookup invisible?

:

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?



:

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


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add Record to Subform using a Listbox 4
Pop-up form that creates record that links 1:1 to main form 0
Data Entry Form and Subform problems 5
Problems adding new record from form / subform 6
Populating a "pop-up" form from a subForm 2
Update subform field 1
refresh an open form using records from another open form? 26
How to link form and subform w/ Combo 7

Top