forms are not updated 1

[Dophia]

Hi Everyone: I have a problem with a form, from which you click a button to open another form and subform, based on a similar query, but of the same tables.

The first form lists all the records from the tables. Since you cannot have a continuous form list with a subform, I then have a button to open the next form, which is a main form and subform, based on a similar query of the two tables, but joined differently.

When I close the form with the subform, and return to the other form, the other form does not show the updated information from the form and subform. I have tried Requery, Refresh, etc.

What is wrong?

Sophia

 

[dhookom]

Are you opening "form with the subform" in acDialog mode? What is your code that opens the form and then does the requery?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Dophia . . .

Are you saying you'd perfer a mainform with [blue]continuous subform[/blue], and that subform also has a [blue]continuous subform[/blue]?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Dophia]

Hi Everyone: This is the button code to open the other form (FrmWeb) which is a form and subform.

Main Form name = FrmWeb2
Other Main Form name = FrmWeb
subform on FrmWeb = SubfrmWeb


Private Sub OpenWebForm_Click()
On Error GoTo Err_OpenWebForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmWeb"

stLinkCriteria = "[Pound_Sheet_No]=" & "'" & Me![Pound_Sheet_No] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenWebForm_Click:
Exit Sub

Err_OpenWebForm_Click:
Msgbox Err.Description
Resume Exit_OpenWebForm_Click

End Sub

When I close the FrmWeb (other form and subform) to return to the main form (FrmWeb2), this is the close button code:

Private Sub Close_Click()
On Error GoTo Err_Close_Click
If Me.Dirty Then Me.Dirty = False 'Save the current record
Me.Requery
DoCmd.Close

Exit_Close_Click:
Exit Sub

Err_Close_Click:
Msgbox Err.Description
Resume Exit_Close_Click

End Sub

 

[Dophia]

Aceman:

What I am trying to achieve is to have a form which shows a record and also a subform for that record. So, I cannot do that, I have a form which shows the records and a button to open the form and subform. The first table exists, but the second table (which has a one to one relationship) is to be created on the subform.

Sophia

 

[dhookom]

When you close FrmWeb, its record will be saved. Try something like:

Private Sub OpenWebForm_Click()
On Error GoTo Err_OpenWebForm_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FrmWeb"
    
    stLinkCriteria = "[Pound_Sheet_No]=" & "'" & Me![Pound_Sheet_No] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    Me.Requery

Exit_OpenWebForm_Click:
    Exit Sub

Err_OpenWebForm_Click:
    Msgbox Err.Description
    Resume Exit_OpenWebForm_Click
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Dophia . . .

I'm trying very hard to prevent you from needing the 2nd form.

[blue]The first form lists all the records from the tables. [purple]Since you cannot have a continuous form list with a subform[/purple], ...[/blue]

Who or what ... got you to believe that? ... Too many times, if you go by access you'll never get there!

So ... its a [blue]continuous view[/blue] mainform with a [blue]continuous view[/blue] subform your after? ... Yes?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Dophia]

Aceman: How can you have a continous form with a subform?

Sophia

 

[MajP]

http://www.4shared.com/file/94409998/8cabb825/SubForms.html

Like this.

 

[Dophia]

Duane: The other form and subform (FrmWeb and SubfrmWeb)are working fine, but the problem is when I close the form to return to FrmWeb2. The FrmWeb2 does not show the updated data.

Sophia

 

[MajP]

That is an example of a Continous form with a synched, single form and a synched continous form. But you can do a true continous datasheet view with a subdatasheet view.

 

[dhookom]

Dophia,
I think I understood your issue. Did you try might suggestion? If not, why not? If so, what didn't work?

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Duane: Yes I did try it. But I think I may be putting it in the wrong place since it sort of works. From your suggestion, it seems that you want me to put it on the form that opens FrmWeb and subfrmWeb. But, when I close that form, I want the FrmWeb2 to show the updated data.

The data on the FrmWeb2 is only shown if I click on that record. But i want it to show up automatically, without having to click on the record. A new problem is that when I open the FrmWeb to edit it again, I get an error message "Write conflict".

Sophia

 

[dhookom]

If you use acDialog, your code will stop executing until the opened form closes. This allows the other form to update records in the tables then the current form is requeried and should be able to see the changes made in the opened form/subform.

Try get rid of these lines of code:

If Me.Dirty Then Me.Dirty = False 'Save the current record
   Me.Requery

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Dophia . . .

[blue]dhookom[/blue] and I have been hitting you from two different sides. Out of respect for [blue]dhookom[/blue], I've just been waiting (don't wanna twist any current confusion). I'll jump in when things appear settled ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Dophia]

Thank you all for the support. I had to go out of town for a week or so.....I'll look into this and get back to you.

Thanks MajP for the sample db. That example is very helpful.


Sophia

 

[MajP]

In Access 2007 you can do this with a Split Form.

http://office.microsoft.com/en-us/access/HA100759941033.aspx

The Access subform wizard actually causes severe limitations on a subform. The wizard make it seem that a subform can only link to a field in a main form. In fact you can link a subform to an unbound control, you can link to a field or control in another subform, and you link multiple continous forms on a main form.

Personally in all the databases I have made, I have never used a nested subform. I find them confusing and just ugly. I find synched subforms far clearer.