Requerying a Form when another closes

[Richhol65]

Hi

Hopefully someone can help

I have a main form populated by Dlookups.

When I click on a textbox I open another form which is used to change data in my main table. Once this is closed the main form is still displayed but the amended data isnt reflected in this form.

How do i get the data to change, I have tried requerying the form and the query that is used for the dlookups but I cant get it to work

Any help would be appreciated

Cheers

Rich

 

[Mihail]

Open your form using the acDialog option:

DoCmd.OpenForm "FormName", , , , , acDialog
Me.Requery

 

[Richhol65]

Open your form using the acDialog option:

DoCmd.OpenForm "FormName", , , , , acDialog
Me.Requery

Hi Mihail

I have tried that but when I do that the code that I use to part populate the opening form doesn't work

Here is my code

Private Sub txt10800_Click()
Dim canc As Integer
If IsNull(Me.txt10800) Then
DoCmd.OpenForm "frmSty1", , , , , acDialog
Forms!frmSty1!txtAppDate = Me.txtDate
Forms!frmSty1!txtAppTime = "08:00"
Forms!frmSty1!txtAppDay = Format(Me.txtDate, "dddd")
Else
  canc = MsgBox("Do you want to cancel " & DLookup("StylistName", "Stylist1") & "'s appointment at 08:00am on " & Me.txtDate & " ?" & vbNewLine & vbNewLine, vbYesNo, "Cancel Y/N?")
  Select Case canc
   Case vbYes
   DoCmd.OpenForm "frmCancelAppointment", , , , , acDialog
   Forms!frmCancelAppointment!txtAppID = DLookup("TxtID", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
   Forms!frmCancelAppointment!txtAppDate = DLookup("AppDate", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
   Forms!frmCancelAppointment!txtAppDay = DLookup("AppDay", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
   Forms!frmCancelAppointment!cmbCustName = DLookup("CustName", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
   Forms!frmCancelAppointment!txtAppTime = DLookup("AppStartTime", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
   Forms!frmCancelAppointment!txtService = DLookup("ServiceName", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
   Case vbNo
   Forms!DailyDiary.Form.Requery
   
 End Select
 End If
 End Sub

Any Ideas

Rich

 

[Mihail]

Move the red code under the OPEN event of each form (frmStyl and frmCancelAppintment).
Of course you must rewrite a little bit the code.
Exemple:
Forms!frmSty1!txtAppDate = Me.txtDate
will be:
Me.txtAppDate = Forms!MainFormName!txtDate

Private Sub txt10800_Click()
Dim canc As Integer
    If IsNull(Me.txt10800) Then
        DoCmd.OpenForm "frmSty1", , , , , acDialog
            [COLOR=Red]Forms!frmSty1!txtAppDate = Me.txtDate
            Forms!frmSty1!txtAppTime = "08:00"
            Forms!frmSty1!txtAppDay = Format(Me.txtDate, "dddd")[/COLOR]
    Else
        canc = MsgBox("Do you want to cancel " & DLookup("StylistName", "Stylist1") & "'s appointment at 08:00am on " & Me.txtDate & " ?" & vbNewLine & vbNewLine, vbYesNo, "Cancel Y/N?")
        Select Case canc
            Case vbYes
                DoCmd.OpenForm "frmCancelAppointment", , , , , acDialog
                    [COLOR=Red]Forms!frmCancelAppointment!txtAppID = DLookup("TxtID", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
                    Forms!frmCancelAppointment!txtAppDate = DLookup("AppDate", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
                    Forms!frmCancelAppointment!txtAppDay = DLookup("AppDay", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
                    Forms!frmCancelAppointment!cmbCustName = DLookup("CustName", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
                    Forms!frmCancelAppointment!txtAppTime = DLookup("AppStartTime", "PopulateDailyDiary", "[Textbox] = 'txt10800'")
                    Forms!frmCancelAppointment!txtService = DLookup("ServiceName", "PopulateDailyDiary", "[Textbox] = 'txt10800'")[/COLOR]
            Case vbNo
                Forms!DailyDiary.Form.Requery
        End Select
    End If
End Sub

 

[Richhol65]

Hi Mihail

The problem is that the code will change depending on which textbox is selected so I am currently selecting textbox txt10800 and that fills a time of 08:00 if I selected txt10830 it needs to fill a time of 08:30 and so on

So surely moving the code to the open event wouldnt work as it would need to be different code dependant on the textbox that is clicked

Am I understanding it right

Cheers

Rich

 

[Mihail]

WHAT ?!?!?
And... how many text boxes you have ?
48 ? One for every half of hour ? More ?

No No and NO.
This is not a solution.
Remove all this textboxes and use a single combobox (or a list box) that is filled with times.
Use the CLICK event for this control to open your second form.

So, the statement
Forms!frmSty1!txtAppTime = "08:00"
will become
Me.txtAppTime = Forms!MainFormName!ComboName