[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-27
12
Medium Priority
?
1,136 Views
Last Modified: 2012-08-28
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
Comment
Question by:FrankBabz
  • 6
  • 5
12 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38338025
Give this a whirl...

Me.Parent!Refresh
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38338030
try adding this line of code after you update the main form table


me.parent.requery
0
 

Author Comment

by:FrankBabz
ID: 38338490
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 120

Expert Comment

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

post the codes that calls the "msgbox"
0
 

Author Comment

by:FrankBabz
ID: 38338766
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38338787
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
 

Author Comment

by:FrankBabz
ID: 38339039
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38339126
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
 

Author Comment

by:FrankBabz
ID: 38340894
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38340946
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
 

Author Closing Comment

by:FrankBabz
ID: 38341153
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38341187
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question