• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1171
  • Last Modified:

VB Access: How to 'Refresh' main form from the 'subform' of the main form.

In the code of a subform I update the table that is the source of the main form.  In the main form the updated field does not get updated unless I click back to the main form and issue a refresh.   I would like to issue a refresh of the main form from the code in the subform.

Thanks,
Frank
0
FrankBabz
Asked:
FrankBabz
  • 6
  • 5
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl...

Me.Parent!Refresh
0
 
Rey Obrero (Capricorn1)Commented:
try adding this line of code after you update the main form table


me.parent.requery
0
 
FrankBabzAuthor Commented:
I need to explain better....

MainForm  has URL field in its underlying source table.  In the Main Form...
    MainTextbox displays this URL (it is not editable here)

If user want to change this URL he must first open and show a Subform of the MainForm where in the subform there is a
   SubFormTextbox that displays the same URL (it is not editable here either).
  Adjacent to the SubFormText box is a Button "EditURL" that when clicked
     it alllows user to Edit and Test a replacement URL.
   If user is satisfied, and clicks OK, the URL is updated in the Source Table.

My problem is that:
After user clicks OK neither MainTextBox or SubFormTextbox shows the updated URL.

A requery of the MainForm will update both text boxes.  But, I would like this requery to have been done in code after the OK button (above) has been clicked.

You may ask why the Edit URL button is not in the Main Form.  That is because the SubForm is a common Subform used by many other Main Forms.

Thanks for any suggestions or help.....
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Rey Obrero (Capricorn1)Commented:
Where is the OK button ? in a message box?

post the codes that calls the "msgbox"
0
 
FrankBabzAuthor Commented:
Here in the subform is the button click code that calls the Edit/update URL (aka 'ContactURL') and passes the ID of the 'source' record  in Args to the ContactURL form:

Private Sub EditURL_Click()      ' this is located in the subform.
  Dim Args As String
  Args = tbxContactID & ";"
  DoCmd.OpenForm "ContactURL", acNormal, , , acFormAdd, , Args
End Sub

In the ContactURL form the caller can test and reply OK (via a messageBox) to have the URL changed in the 'source' table.  After the 'source' table is updated then "DoCmd.Close"
0
 
Rey Obrero (Capricorn1)Commented:
FrankBabz,

you are giving us informations important to achieve what you wanted to do in "Partial"

<In the ContactURL form the caller can test and reply OK (via a messageBox) to have the URL changed >

post the codes for the "msgbox"
0
 
FrankBabzAuthor Commented:
Sorry not to understand your requests.  Here is all the code in the ContactURL module wherein the OK messagebox is located...
------------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Dim ContactID As Long
Dim ContactNameLNF As String
Dim OldContactURL As String

Private Sub ButTestURL_Click()
  Dim Er As String, UpdMSG As String
  '
  ShowUrlInBrowser Me.tbxContactURL, Er
  If Er <> "" Then
    MsgBox Er  ' Exit
  Else
    MsgBox "You should now see your URL showing in your browser."
    If Me.tbxContactURL = OldContactURL Then MsgBox "You did not change the URL": Exit Sub
    UpdMSG = "You can update..." & ContactNameLNF & "'s" & vbCrLf & _
       "URL from..." & vbCrLf & OldContactURL & vbCrLf & _
       "URL to....." & vbCrLf & Me.tbxContactURL & vbCrLf & _
       "Select OK to update!!  or Select CANCEL"
    If vbOK = MsgBox(UpdMSG, vbOKCancel + vbQuestion, "Update URL?") Then
      ' ...UPDATE URL!!
       Dim SQL As String
       SQL = "UPDATE Contacts SET ContactWebURL='" & Me.tbxContactURL & "' " & _
              "WHERE ContactID=" & ContactID & ";"
      On Error Resume Next
      CurrentDb.Execute SQL, dbFailOnError + dbSeeChanges
      If Err.number > 0 Then
        MsgBox ("Update Error: " & Err.Description): Err.Clear
      Else
        OldContactURL = Me.tbxContactURL
        MsgBox "Contact URL has been updated!"
        DoCmd.Close
      End If
    End If
  End If
End Sub


Private Sub Form_Load()
  Dim Args As String
  Args = GetPartData(Nz(Me.OpenArgs, ""), ";")
  If Args = "" Then Args = "468"   ' 881 Greystone
  If Args = "" Then MsgBox "Contact ID not provided by caller.": Exit Sub
  ContactID = Args
  ''''
  ContactNameLNF = Nz(DLookup("ContactNameLNF", "Contacts", "ContactID=" & ContactID), "")
  OldContactURL = Nz(DLookup("ContactWEBurl", "Contacts", "ContactID=" & ContactID), "")
  ''''
  tbxContactNameLNF = ContactNameLNF
  tbxContactURL = OldContactURL
  '
  If ContactNameLNF = "" Then
    MsgBox ("The contact ID '" & ContactID & "' has no contact name, or Contact ID is invalid")
    DoCmd.Close  ' Close out
  End If
End Sub


Private Sub butClose_Click()
  DoCmd.Close
End Sub
0
 
Rey Obrero (Capricorn1)Commented:
try this changes




Private Sub ButTestURL_Click()
  Dim Er As String, UpdMSG As String
  '
  ShowUrlInBrowser Me.tbxContactURL, Er
  If Er <> "" Then
    MsgBox Er  ' Exit
  Else
    MsgBox "You should now see your URL showing in your browser."
    If Me.tbxContactURL = OldContactURL Then MsgBox "You did not change the URL": Exit Sub
    UpdMSG = "You can update..." & ContactNameLNF & "'s" & vbCrLf & _
       "URL from..." & vbCrLf & OldContactURL & vbCrLf & _
       "URL to....." & vbCrLf & Me.tbxContactURL & vbCrLf & _
       "Select OK to update!!  or Select CANCEL"
    If vbOK = MsgBox(UpdMSG, vbOKCancel + vbQuestion, "Update URL?") Then
      ' ...UPDATE URL!!
       Dim SQL As String
       SQL = "UPDATE Contacts SET ContactWebURL='" & Me.tbxContactURL & "' " & _
              "WHERE ContactID=" & ContactID & ";"
      On Error Resume Next
      CurrentDb.Execute SQL, dbFailOnError + dbSeeChanges
      If Err.number > 0 Then
        MsgBox ("Update Error: " & Err.Description): Err.Clear
      Else
        OldContactURL = Me.tbxContactURL
        MsgBox "Contact URL has been updated!"

'ADDED CODES            
            Forms!NameOfTheMainForm.Requery
            Forms!NameOfTheMainForm!NameOfTheSubformControl.Form.Requery

            
        DoCmd.Close
      End If
    End If
  End If
End Sub
0
 
FrankBabzAuthor Commented:
Capricorn1:

The Subform is common to many mainforms making 'NameOfTheMainForm' different in each case and requiring a way to either retrieve the main formname or passing main formname in the Args.

As an alternative....I have changed/tried my open form code as follows :

Private Sub EditURL_Click()
  Dim Args As String
  Args = tbxContactID & ";"
  DoCmd.OpenForm "ContactURL", acNormal, , , , acDialog, Args
  Me.Parent.Requery
End Sub

and it appears (so far) to work.  Please advise if you think this is a good or bad solution.  

Thank you for your assistance...
0
 
Rey Obrero (Capricorn1)Commented:
that is a good solution indeed..
but you still need to have a way to know that the edit actually occurred, before you make a requery. ( if that is relevant to your application)

post back if you need the verification that the edit actually was done.
0
 
FrankBabzAuthor Commented:
Thank's for your help Capricorn1....

If you have a better way (than I proposed) to verify an update took place, I would like to know your suggestion.

Also, if you have the time, I do wonder if the sub has any way to retrieve the name of the MainForm so that it does not have to be passed in Args.
0
 
Rey Obrero (Capricorn1)Commented:
to get the name of the main form from the subform code


me.parent.name


<If you have a better way (than I proposed) to verify an update took place, I would like to know your suggestion.>

create a public variable in regular module
Public blnUpdated as boolean

if the update actually happened, set the blnUpdated = true from the ContactURL codes


then change your code like this



Private Sub EditURL_Click()
  Dim Args As String
  Args = tbxContactID & ";"
  DoCmd.OpenForm "ContactURL", acNormal, , , , acDialog, Args

if blnUpdated then
  Me.Parent.Requery
end if

End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now