Requery question

[jaad]

Hi All,

I've gone through the post and saw many posts similar to what I am looking to solve but haven't been able to get results still.


Here is the setup:

I have a form named WorkOrderF

On this form there is an Unbound combo box called [GototenantCombo]. This combo box lookup a query[WOSourceQ] (my active work orders) and returns the record that I want to work on.

I use a pop up form [NewWorkOrder] to add other customers to [WOSourceQ]

Once I've added a new customer to new customer shows up into the combo box [GoToTenantCombo], everything good so far.

The problem that I have is with the subform WODDescriptionS F inside WorkOrderF form. It is (master and child linked with the ID of the work orders.

The subform WODDescriptionS F get values from [WODSourceQ] and this is the form that needs to be requery.

HOw I have been managing so far is by clicking RefreshAll command on the ribbon but would love to be able to forgo that step.

Can Anyone help?

PS: Does Access 2010 comes with (refresh all) command? I could use that darn command in about 10 modules. why is something so useful still being ignored by MS?

[nico5038]

For the requery you could use the command "Me.requery " or "Me.subformname .requery" for just the subform.
This line should be added in the code for the AfterUpdate event of the combobox.

When this comand doen't work (Access is stubborn sometimes) I use:

Me.subform.recordsource = Me.subform.recordsource

Which looks funny, but does the trick always.

Nic;o)

[NeoPa]

As Nico says you need to .Requery the form (or all relevant forms). Check out Referring to Items on a Sub-Form for how to refer to one form from another, which will be required if you're doing it from the pop-up form.

PS. Well done for including the names of your objects in the question. So useful, yet often overlooked ;)

[jaad]

One thing I forgot to mention - I already use the AfterUpdate of the combobox with a Macro.

More clearly:

My Parent form is [WorkOrderF] it's record source is query [WOSourceQ]

This form has an Unbound Lookup Combobox [GoToTenantCombo] with record source table [TenantT]. It's AfterUpdate has a macro:

SearchForRecord

Object type: Null
Object name: Null
Record: First
Where Condition: ="[TenantID] = " & Str(Nz(Screen.A ctiveControl,0) )

My parent form has child form(parent&Chi ld Link:[WOID] called [WODDescriptionS F] with record source query [WODSourceQ]

I use a Boolean switch yes/no to determine if the work order is active or not and the criteria for both source queries of the parent and child form.

When I want to introduce a new tenant and work order I use a pop up form[AddNewTenantF] with record source table [WorkOrderT] and a combobox with the following row source:

SELECT   TenantsT.TenantID,
         [FirstName] & " " & [LastName] & "   -  " & [Unit] & " - " & [Building] AS TenantName

FROM     BuildingLT RIGHT JOIN
         (LocationsT RIGHT JOIN
         TenantsT
  ON     LocationsT.LocID=TenantsT.LocID)
  ON     BuildingLT.BuildingID=TenantsT.BuildingID

ORDER BY LocationsT.Unit;

this combobox AfterUpdate is:

Private Sub TenantID_AfterUpdate()
    [Forms]![WorkOrderF]![WODDescriptionSF].[Form].Requery
End Sub

Then I close the popup form and return to my parent form[WorkOrderF] to find the value of the[WorkOrderF].[GoToTenantCombo] combobox to be a random value with random child value as well from [WODDescriptionS F].

I then click RefreshAll button command on the ribbon and may or may not get random values from the [GoToTenantCombo] and the underlining subform.

Once I go to the {GoToTenantComb o] and select a new record that's when everything gets back to normal and everything works.

Cheers

[nico5038]

I would advise to transform your macro's into VBA. Access has an option that enables this.

Next replace the subform requery with:
Me.WODDescripti onS.recordsourc e = Me.WODDescripti onSF.recordsour ce

That should do the trick.

Nic;o)

[jaad]

Unfortunately it didn't work.

This is the VBA Codes that I have on the popup form to add new work orders:

Option Compare Database

Private Sub TenantID_AfterUpdate()
    [Forms]![WorkOrderF]![WODDescriptionSF].[Form].Requery
End Sub
'------------------------------------------------------------
' Form_Load
'
'------------------------------------------------------------
Private Sub Form_Load()
On Error GoTo Form_Load_Err

    DoCmd.GoToRecord , "", acNewRec


Form_Load_Exit:
    Exit Sub

Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit

End Sub


'------------------------------------------------------------
' cmdClose_Click
'
'------------------------------------------------------------
Private Sub cmdClose_Click()
On Error GoTo cmdClose_Click_Err

    DoCmd.Close , ""
    DoCmd.RunCommand acCmdRefresh


cmdClose_Click_Exit:
    Exit Sub

cmdClose_Click_Err:
    MsgBox Error$
    Resume cmdClose_Click_Exit

End Sub


This is what I have for the parent work order form:

Option Compare Database

'------------------------------------------------------------
' GotoTenantCombo_AfterUpdate
'
'------------------------------------------------------------
Private Sub GoToTenantCombo_AfterUpdate()
On Error GoTo GoToTenantCombo_AfterUpdate_Err

    DoCmd.SearchForRecord , "", acFirst, "[TenantID] = " & Str(Nz(Screen.ActiveControl, 0))
    DoCmd.RunCommand acCmdRefresh
    
GoToTenantCombo_AfterUpdate_Exit:
    Exit Sub

GoToTenantCombo_AfterUpdate_Err:
    MsgBox Error$
    Resume GoToTenantCombo_AfterUpdate_Exit

End Sub


'------------------------------------------------------------
' AppointmentCmd_Click
'
'------------------------------------------------------------
Private Sub AppointmentCmd_Click()
On Error GoTo AppointmentCmd_Click_Err

    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    On Error GoTo 0
    DoCmd.OpenForm "AppointmentsF", acNormal, "", "[TenantID]=" & Nz(TenantID, 0), , acNormal
    If (Not IsNull(TenantID)) Then
        TempVars.Add "CurrentID", "[TenantID]"
    End If
    If (IsNull(TenantID)) Then
        TempVars.Add "CurrentID", "Nz(DMax(""[tenantID]"",[Form].[RecordSource]),0)"
    End If
    DoCmd.Requery ""
    DoCmd.SearchForRecord , "", acFirst, "[tenantID]=" & TempVars!CurrentID
    TempVars.Remove "CurrentID"


AppointmentCmd_Click_Exit:
    Exit Sub

AppointmentCmd_Click_Err:
    MsgBox Error$
    Resume AppointmentCmd_Click_Exit

End Sub


'------------------------------------------------------------
' PurchaseOrderCmd_Click
'
'------------------------------------------------------------
Private Sub PurchaseOrderCmd_Click()
On Error GoTo PurchaseOrderCmd_Click_Err

    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    On Error GoTo 0
    DoCmd.OpenForm "PurchaseOrdersF", acNormal, "", "[WOID]=" & Nz(WOID, 0), , acNormal
    If (Not IsNull(WOID)) Then
        TempVars.Add "CurrentID", "[WOID]"
    End If
    If (IsNull(WOID)) Then
        TempVars.Add "CurrentID", "Nz(DMax(""[WOID]"",[Form].[RecordSource]),0)"
    End If
    DoCmd.Requery ""
    DoCmd.SearchForRecord , "", acFirst, "[WOID]=" & TempVars!CurrentID
    TempVars.Remove "CurrentID"


PurchaseOrderCmd_Click_Exit:
    Exit Sub

PurchaseOrderCmd_Click_Err:
    MsgBox Error$
    Resume PurchaseOrderCmd_Click_Exit

End Sub


'------------------------------------------------------------
' CustomerDetailsaddCmd_Click
'
'------------------------------------------------------------
Private Sub CustomerDetailsaddCmd_Click()
On Error GoTo CustomerDetailsaddCmd_Click_Err

    DoCmd.OpenForm "TenantsDetailsF", acNormal, "", "", , acNormal


CustomerDetailsaddCmd_Click_Exit:
    Exit Sub

CustomerDetailsaddCmd_Click_Err:
    MsgBox Error$
    Resume CustomerDetailsaddCmd_Click_Exit

End Sub


'------------------------------------------------------------
' NewWOCmd_Click
'
'------------------------------------------------------------
Private Sub NewWOCmd_Click()
On Error GoTo NewWOCmd_Click_Err

    DoCmd.OpenForm "WOCreateNewF", acNormal, "", "", , acNormal


NewWOCmd_Click_Exit:
    Exit Sub

NewWOCmd_Click_Err:
    MsgBox Error$
    Resume NewWOCmd_Click_Exit

End Sub


'------------------------------------------------------------
' cmdClose_Click
'
'------------------------------------------------------------
Private Sub cmdClose_Click()
On Error GoTo cmdClose_Click_Err

    DoCmd.Close , ""


cmdClose_Click_Exit:
    Exit Sub

cmdClose_Click_Err:
    MsgBox Error$
    Resume cmdClose_Click_Exit

End Sub




'------------------------------------------------------------
' workOrderReceiptCmd_Click
'
'------------------------------------------------------------
Private Sub workOrderReceiptCmd_Click()
On Error GoTo workOrderReceiptCmd_Click_Err

    DoCmd.OpenReport "WorkOrderReceipt", acViewReport, "", "[TenantID]=" & Nz(TenantID, 0), acNormal


workOrderReceiptCmd_Click_Exit:
    Exit Sub

workOrderReceiptCmd_Click_Err:
    MsgBox Error$
    Resume workOrderReceiptCmd_Click_Exit

End Sub


'------------------------------------------------------------
' WorkOrderReceiptAppointmentCmd_Click
'
'------------------------------------------------------------
Private Sub WorkOrderReceiptAppointmentCmd_Click()
On Error GoTo WorkOrderReceiptAppointmentCmd_Click_Err

    DoCmd.OpenReport "Work Order Receipt Appointment", acViewReport, "", "[TenantID]=" & Nz(TenantID, 0), acNormal


WorkOrderReceiptAppointmentCmd_Click_Exit:
    Exit Sub

WorkOrderReceiptAppointmentCmd_Click_Err:
    MsgBox Error$
    Resume WorkOrderReceiptAppointmentCmd_Click_Exit

End Sub

The WODDescriptionS F subform has no codes or macro.

[nico5038]

Try changing:

 
   3. Private Sub TenantID_AfterUpdate()
   4.     [Forms]![WorkOrderF]![WODDescriptionSF].[Form].Requery
   5. End Sub

into

   3. Private Sub TenantID_AfterUpdate()
   4.     [Forms]![WorkOrderF]![WODDescriptionSF].[Form].Recordsource = [Forms]![WorkOrderF]![WODDescriptionSF].[Form].Recordsource 
   5. End Sub

Nic;o)

[jaad]

Sorry.... It still didn't work. Not only that but now the tenants are getting mixed up and somehow some work orders have been assigned to other clients. good chance I backed up the database prior lol.

[nico5038]

Strange, can you post a sample mdb so I can have a look ?

Nic;o)

[jaad]

How can I do post a sample MDB?
There are personal information in this MDB, not sure if it would mean my neck if any of these information got out???? not sure if I can do that????

[nico5038]

Just create a new .mdb with just these forms and some fake data. Just enough to show the not working form / pop-up form mechanism.
You can attach a file (just zip the sample) in the advanced mode.

Nic;o)

[jaad]

Ok I will try to do that. I will have to see if I have a zip maker; I never done that before. so when I manage to get the sample mdb how do I upload it here? I don't see any mean of doing this??? I;m not all that great with computers, sorry. Also, will I be able to attach a message? explaining what I would like to see happening? This is really awsome, I really appreciate this.

[nico5038]

When you press the button [Go Advanced] below the Quick Reply, you'll get a form with below the textbox a button [Attachments].

For zipping a file (when you have Vista) just select the right-click pop-up and select "Send to -> Compressed(zipp ed) folder".

Nic;o)

[jaad]

OK, thank for the tip Nic;o)

I've been fiddling around and managed to get it close to working...

Everything works! Except one thing that I can't figure out:

the new record won't show on form when selected??????? ?

All the other records work perfectly except the new one that I just added. I double checked in the work order table and its there.... I also check in the query that is my record source for my work order form, it is there also but I still need to click RefreshAll button on the ribbon to make it work.

This is so frustrating... I spent my whole weekend trying to figure this out and tried everything to no avail.

I won't be able to recreate a database with sample data because there are too many relationships that depends on one another and I am getting tons of error messages that I can change anything.

[MMcCarthy]

You need to save the new record after you add it and before running the code Nico gave you.

Something like ...

DoCmd.RunCommand acCmdSaveRecord