Requery subform from another form

[mandy.wuest]

I have a main form (frmManuscripts) with a subform (sfrmAuthors). If I
want to add an author to the subform then I click a button on the main
form which opens a new form (frmAddAuthor). When frmAddAuthor closes I
requery the subform.

Forms!frmManuscripts!sfrmAuthors.Form.Requery

This works just fine unless I want to add an author that is not
already in the table.

To do this I open frmAddAuthor and then click a button that opens
frmAddNewAuthor. When I close frmAddNewAuthor the new author shows up
in the combo box on frmAddAuthor; but when frmAddAuthor is closed
sfrmAuthors shows a blank entry. If I close frmManuscripts and open it
again the entry is no longer blank, it contains the correct
information.

I have tried to requery just about everything but I either get error
messages or the same result. Any suggests?

Thanks,
Mandz

 

[Marshall Barton]

I have a main form (frmManuscripts) with a subform (sfrmAuthors). If I
want to add an author to the subform then I click a button on the main
form which opens a new form (frmAddAuthor). When frmAddAuthor closes I
requery the subform.

Forms!frmManuscripts!sfrmAuthors.Form.Requery

This works just fine unless I want to add an author that is not
already in the table.

To do this I open frmAddAuthor and then click a button that opens
frmAddNewAuthor. When I close frmAddNewAuthor the new author shows up
in the combo box on frmAddAuthor; but when frmAddAuthor is closed
sfrmAuthors shows a blank entry. If I close frmManuscripts and open it
again the entry is no longer blank, it contains the correct
information.

Did you make sure the new record was saved before using
requery? If not, add Me.Dirty = False before the
Requery.

It would be much safer if the add authors form is opened in
dialog mode. Then you can put the Requery in the button's
code instead of in the add authors form.

 

[mandy.wuest]

I have a main form (frmManuscripts) with a subform (sfrmAuthors). If I
want to add an author to the subform then I click a button on the main
form which opens a new form (frmAddAuthor). When frmAddAuthor closes I
requery the subform.

This works just fine unless I want to add an author that is not
already in the table.

To do this I open frmAddAuthor and then click a button that opens
frmAddNewAuthor. When I close frmAddNewAuthor the new author shows up
in the combo box on frmAddAuthor; but when frmAddAuthor is closed
sfrmAuthors shows a blank entry. If I close frmManuscripts and open it
again the entry is no longer blank, it contains the correct
information.

Did you make sure the new record was saved before using
requery?  If not, add   Me.Dirty = False  before the
Requery.

It would be much safer if the add authors form is opened in
dialog mode.  Then you can put the Requery in the button's
code instead of in the add authors form.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Here is my code.

Private Sub cmdAddAuthor_Click()
On Error GoTo Err_cmdAddAuthor_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddAuthor"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

Forms!frmManuscripts!sfrmAuthors.Form.Requery

Exit_cmdAddAuthor_Click:
Exit Sub

Err_cmdAddAuthor_Click:
MsgBox Err.Description
Resume Exit_cmdAddAuthor_Click

End Sub

As for the new record being saved here is the code. This is my first
database and not sure if this qualifies as saved.


Private Sub Form_Close()
On Error GoTo Err_Form_Close

Dim db As Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim ID As Variant

'Opens the table named tblCPCRCIDAuthor
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From tblCPCRCIDAuthor")

ID = Forms!frmManuscript!txtCPCRCID 'Brings in the CPCRCID of
frmManuscript

'This prevents records from being written if the record on the main
form is deleted.
If IsNull(ID) Then
Exit Sub
End If

If Me.txtAuthor1 <> "" Then 'Prevents the program from writing blank
records
rs.AddNew
rs!Author = Me.txtAuthor1
rs!CPCRCID = ID
rs!Cleared = Me.ckbCleared
rs.Update
Else
Exit Sub
End If

'closes the connection with the database and recordset
rs.Close
db.Close


Exit_Form_Close:
Exit Sub

Err_Form_Close:
MsgBox Err.Description
Resume Exit_Form_Close
End Sub

Thank you for your time and suggestions
Mandz

 

[Marshall Barton]

Here is my code.

Private Sub cmdAddAuthor_Click()
On Error GoTo Err_cmdAddAuthor_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddAuthor"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

Forms!frmManuscripts!sfrmAuthors.Form.Requery

Exit_cmdAddAuthor_Click:
Exit Sub

Err_cmdAddAuthor_Click:
MsgBox Err.Description
Resume Exit_cmdAddAuthor_Click

End Sub

As for the new record being saved here is the code. This is my first
database and not sure if this qualifies as saved.


Private Sub Form_Close()
On Error GoTo Err_Form_Close

Dim db As Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim ID As Variant

'Opens the table named tblCPCRCIDAuthor
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From tblCPCRCIDAuthor")

ID = Forms!frmManuscript!txtCPCRCID 'Brings in the CPCRCID of
frmManuscript

'This prevents records from being written if the record on the main
form is deleted.
If IsNull(ID) Then
Exit Sub
End If

If Me.txtAuthor1 <> "" Then 'Prevents the program from writing blank
records
rs.AddNew
rs!Author = Me.txtAuthor1
rs!CPCRCID = ID
rs!Cleared = Me.ckbCleared
rs.Update
Else
Exit Sub
End If

'closes the connection with the database and recordset
rs.Close
db.Close

[snip]

The code for the button looks good, but I don't understand
the need for all that code in the add author form. If the
add author form was bound to tblCPCRCIDAuthor, then it seems
like the only code you would need is
Me.CPCRCID = Forms!frmManuscript!txtCPCRCID

 

[mandy.wuest]

Here is my code.

Private Sub cmdAddAuthor_Click()
On Error GoTo Err_cmdAddAuthor_Click

   Dim stDocName As String
   Dim stLinkCriteria As String

   stDocName = "frmAddAuthor"
   DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

Exit_cmdAddAuthor_Click:
   Exit Sub

Err_cmdAddAuthor_Click:
   MsgBox Err.Description
   Resume Exit_cmdAddAuthor_Click

End Sub

As for the new record being saved here is the code. This is my first
database and not sure if this qualifies as saved.

Private Sub Form_Close()
On Error GoTo Err_Form_Close

Dim db As Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim ID As Variant

'Opens the table named tblCPCRCIDAuthor
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From tblCPCRCIDAuthor")

ID = Forms!frmManuscript!txtCPCRCID   'Brings in the CPCRCID of
frmManuscript

'This prevents records from being written if the record on the main
form is deleted.
If IsNull(ID) Then
Exit Sub
End If

If Me.txtAuthor1 <> "" Then   'Prevents the program from writing blank
records
rs.AddNew
rs!Author = Me.txtAuthor1
rs!CPCRCID = ID
rs!Cleared = Me.ckbCleared
rs.Update
Else
Exit Sub
End If

'closes the connection with the database and recordset
rs.Close
db.Close

[snip]

The code for the button looks good, but I don't understand
the need for all that code in the add author form.  If the
add author form was bound to tblCPCRCIDAuthor, then it seems
like the only code you would need is
        Me.CPCRCID = Forms!frmManuscript!txtCPCRCID

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Each author could write more than one manuscript and manuscripts often
have more than one author. tblCPCRCIDAuthor joins the manuscript ID
with the author ID. So the users choose authors by looking at data
stored in tblContactInfo. None of the controls on the form are
actually bound to tblCPCRCIDAuthor; they are bound to a combo box that
selects information from tblContactInfo. There is probably a better
way to do this but it works.

I guess the part that really confuses me is why I get a blank record.
The subform is obviously refreshing because a blank record will appear
after I have entered an author. It knows there is another record out
there but it doesn't show the data in that record. If I take out the
requery code I don't get any changes in the subform. It obviously has
to be requeried but am I using the requery in the wrong place?

 

[Marshall Barton]

On Aug 20, 9:17 am, Marshall Barton  wrote:
(e-mail address removed) wrote:
I have a main form (frmManuscripts) with a subform (sfrmAuthors). If I
want to add an author to the subform then I click a button on the main
form which opens a new form (frmAddAuthor). When frmAddAuthor closes I
requery the subform.

This works just fine unless I want to add an author that is not
already in the table.

To do this I open frmAddAuthor and then click a button that opens
frmAddNewAuthor. When I close frmAddNewAuthor the new author shows up
in the combo box on frmAddAuthor; but when frmAddAuthor is closed
sfrmAuthors shows a blank entry. If I close frmManuscripts and open it
again the entry is no longer blank, it contains the correct
information.

Did you make sure the new record was saved before using
requery?  If not, add   Me.Dirty = False  before the
Requery.

It would be much safer if the add authors form is opened in
dialog mode.  Then you can put the Requery in the button's
code instead of in the add authors form.

Here is my code.

Private Sub cmdAddAuthor_Click()
On Error GoTo Err_cmdAddAuthor_Click

   Dim stDocName As String
   Dim stLinkCriteria As String

   stDocName = "frmAddAuthor"
   DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

Exit_cmdAddAuthor_Click:
   Exit Sub

Err_cmdAddAuthor_Click:
   MsgBox Err.Description
   Resume Exit_cmdAddAuthor_Click

End Sub

As for the new record being saved here is the code. This is my first
database and not sure if this qualifies as saved.

Private Sub Form_Close()
On Error GoTo Err_Form_Close

Dim db As Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim ID As Variant

'Opens the table named tblCPCRCIDAuthor
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From tblCPCRCIDAuthor")

ID = Forms!frmManuscript!txtCPCRCID   'Brings in the CPCRCID of
frmManuscript

'This prevents records from being written if the record on the main
form is deleted.
If IsNull(ID) Then
Exit Sub
End If

If Me.txtAuthor1 <> "" Then   'Prevents the program from writing blank
records
rs.AddNew
rs!Author = Me.txtAuthor1
rs!CPCRCID = ID
rs!Cleared = Me.ckbCleared
rs.Update
Else
Exit Sub
End If

'closes the connection with the database and recordset
rs.Close
db.Close

[snip]

Each author could write more than one manuscript and manuscripts often
have more than one author. tblCPCRCIDAuthor joins the manuscript ID
with the author ID. So the users choose authors by looking at data
stored in tblContactInfo. None of the controls on the form are
actually bound to tblCPCRCIDAuthor; they are bound to a combo box that
selects information from tblContactInfo. There is probably a better
way to do this but it works.

I guess the part that really confuses me is why I get a blank record.
The subform is obviously refreshing because a blank record will appear
after I have entered an author. It knows there is another record out
there but it doesn't show the data in that record. If I take out the
requery code I don't get any changes in the subform. It obviously has
to be requeried but am I using the requery in the wrong place?

I still don't think you need all that code. I looks to me
as if the code is operating on the wrong table and seriously
confusing everything.

If tblCPCRCIDAuthor is the junction table between the
Manuscripts and Contacts tables, then the frmAddAuthor form
should be bound to the Contacts table/query and would
require no code at all.

Once the author has been added to the Contacts table, you
can add the author to a manuscript in the sfrmAuthors
subform simply by selecting an author from the combo box
with Contacts as its row source.

 

[mandy.wuest]

(e-mail address removed) wrote:
On Aug 20, 9:17 am, Marshall Barton  wrote:
(e-mail address removed) wrote:
I have a main form (frmManuscripts) with a subform (sfrmAuthors). If I
want to add an author to the subform then I click a button on the main
form which opens a new form (frmAddAuthor). When frmAddAuthor closes I
requery the subform.
Forms!frmManuscripts!sfrmAuthors.Form.Requery
This works just fine unless I want to add an author that is not
already in the table.
To do this I open frmAddAuthor and then click a button that opens
frmAddNewAuthor. When I close frmAddNewAuthor the new author showsup
in the combo box on frmAddAuthor; but when frmAddAuthor is closed
sfrmAuthors shows a blank entry. If I close frmManuscripts and open it
again the entry is no longer blank, it contains the correct
information.
Did you make sure the new record was saved before using
requery?  If not, add   Me.Dirty = False  before the
Requery.
It would be much safer if the add authors form is opened in
dialog mode.  Then you can put the Requery in the button's
code instead of in the add authors form.
Here is my code.
Private Sub cmdAddAuthor_Click()
On Error GoTo Err_cmdAddAuthor_Click
   Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "frmAddAuthor"
   DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
Forms!frmManuscripts!sfrmAuthors.Form.Requery
Exit_cmdAddAuthor_Click:
   Exit Sub
Err_cmdAddAuthor_Click:
   MsgBox Err.Description
   Resume Exit_cmdAddAuthor_Click
End Sub
As for the new record being saved here is the code. This is my first
database and not sure if this qualifies as saved.
Private Sub Form_Close()
On Error GoTo Err_Form_Close
Dim db As Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim ID As Variant
'Opens the table named tblCPCRCIDAuthor
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From tblCPCRCIDAuthor")
ID = Forms!frmManuscript!txtCPCRCID   'Brings in the CPCRCID of
frmManuscript
'This prevents records from being written if the record on the main
form is deleted.
If IsNull(ID) Then
Exit Sub
End If
If Me.txtAuthor1 <> "" Then   'Prevents the program from writing blank
records
rs.AddNew
rs!Author = Me.txtAuthor1
rs!CPCRCID = ID
rs!Cleared = Me.ckbCleared
rs.Update
Else
Exit Sub
End If
'closes the connection with the database and recordset
rs.Close
db.Close
[snip]

Each author could write more than one manuscript and manuscripts often
have more than one author. tblCPCRCIDAuthor joins the manuscript ID
with the author ID.  So the users choose authors by looking at data
stored in tblContactInfo. None of the controls on the form are
actually bound to tblCPCRCIDAuthor; they are bound to a combo box that
selects information from tblContactInfo. There is probably a better
way to do this but it works.

I guess the part that really confuses me is why I get a blank record.
The subform is obviously refreshing because a blank record will appear
after I have entered an author. It knows there is another record out
there but it doesn't show the data in that record. If I take out the
requery code I don't get any changes in the subform. It obviously has
to be requeried but am I using the requery in the wrong place?

I still don't think you need all that code.  I looks to me
as if the code is operating on the wrong table and seriously
confusing everything.

If tblCPCRCIDAuthor is the junction table between the
Manuscripts and Contacts tables, then the frmAddAuthor form
should be bound to the Contacts table/query and would
require no code at all.

Once the author has been added to the Contacts table, you
can add the author to a manuscript in the sfrmAuthors
subform simply by selecting an author from the combo box
with Contacts as its row source.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Perhaps I have gotten in over my head. I think I will try to redo my
forms as you suggested.
Thank you so much for your help

Mandz