Requery Combo Box from second form

[blueman]

I have a form frmShipTo that has on the On Close event the following code:

Private Sub Form_Close()
If IsFormLoaded("frmOrders") = True Then


DoCmd.RunCommand acCmdSave
Forms![frmOrders].[cboPickPrevShipTo].Requery
End If


End Sub

The frmOrders is always open when I need the event called as the frmShipTo is called from the Orders form.

The combo box on the Orders form is not retrieving the newly added record on the Ship To form. I've tried numerous other actions to get it to work without any luck. The record is in the Ship To Table, as I checked for it. The combo box is just not requerying. I'm getting no Errors. Also, the entry does appear if I move to a new record on the orders form and go back. I tried some variations of saving the record on the at form but they didn't work out. Any help would be appreciated.

Thanks

[ItsMe]

The following is probably not doing what you think it is doing.
DoCmd.RunCommand acCmdSave

IIRC, this is going to save design changes to objects. If you want to commit values of bound controls to a table, I suggest using the form name and maybe the .Dirty property. I do not see the code that is within your IsFormLoaded() function, but I am going to guess there is good chance that a DoCmd method might not be running on the object you think it is. So there are two issues. The DoCmd object is not acting on the desired object and the RunCommand method argument you are providing (acCmdSave) is not doing what you want.

This line of code is requerying the combo named cboPickPrevShipTo that is in the form named frmOrders. Is the combo in a subform to frmOrders?
Forms![frmOrders].[cboPickPrevShipTo].Requery

I would save frmOrders when you open frmShipTo
if me.dirty then me.dirty = false
docmd.openform, "frmshipto"

Sometimes you can get errors when opening multiple forms bound to the same fields within a table. So you need to play around with saving forms as focus shifts from one to the other. I would probably use the UnLoad event before the Close event of frmShipTo. This way I could force a save in frmShipTo and also FrmOrders.

So something like

'Save frmShipTo
If Me.Dirty Then Me.Dirty = False
    If IsFormLoaded("frmOrders") = True Then
        
        If Forms!frmOrders.Dirty = True Then
            'Commit the values in the bound controls to the table.
            Forms!frmOrders.Dirty = False
            'Requery the combo
            Forms![frmOrders].[cboPickPrevShipTo].Requery
        End If  'frmOrders.Dirty
    
    End If  'IsFormLoaded

[Micron]

ItsMe: Isn't it possible that frmOrders data has been saved when frmShipto is opened, thus it is not dirty at this point?
If Forms!frmOrders.Dirty = True Then
Maybe just requery the form?

[blueman]

The following is probably not doing what you think it is doing.
DoCmd.RunCommand acCmdSave

I had my doubts about that one but I had tried so many options and it wasn't creating an error so I just left it in.

Your suggestions make perfect sense. Especially forcing the save of frmOrders on the Unload event of ShipTo. I didn't realize how that event differs from On Close but I do now. However, I'm still not getting cboPickPrevShipTo on the Orders Form to requery/refresh/or the form record to save. Again, the values entered are going into the table and once the record on Orders is manually saved the newly added record from ShipTo form is there in cboPickPrevShipTo .

To give you some more info:

The cboPickPrevShipTo is not in a subform on frmOrders.

Here is the code on the OnClick Event of of the Command Button That opens frmShipTo:

Private Sub cboAddNewShipTo_Click()
 DoCmd.OpenForm "frmShipTo", _
        DataMode:=acFormAdd, _
        OpenArgs:=Me.Combo27
End Sub

This is selecting the customerID to use on the frmShipTo (which works fine)


This is the IsFormLoaded Module:

Function IsFormLoaded(ByVal strFormName As String) As Integer
'======================================
'Returns True if the specified form is open in Form view or Datasheet view.
'Test to make sur that a form is open (IsLoaded) before a procedure is run.
'======================================
On Error GoTo ErrHandler


    Const conObjStateClosed = 0
    Const conDesignView = 0
    
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If


Exit Function
ErrHandler:
        MsgBox "Error: " & Err.Number & " - " & Err.Description, vbInformation, "Opps Error"
    Exit Function
End Function

Finally, here is the code you suggested in the Unload event of frmShipTo (I did try other variations of this which didn't work, but it's back to your suggested code):

Private Sub Form_Unload(Cancel As Integer)
If Me.Dirty Then Me.Dirty = False
    If IsFormLoaded("frmOrders") = True Then
             If Forms!frmOrders.Dirty = True Then
            'Commit the values in the bound controls to the table.
            Forms!frmOrders.Dirty = False
            'Requery the combo
           Forms![frmOrders].[cboPickPrevShipTo].Requery
        End If  'frmOrders.Dirty
    
    End If  'IsFormLoaded
End Sub

I appreciate your further thoughts on this. I know it's an issue that has cropped up for a lot of people by reading other threads and there seems to be a myriad of answers and trials that have been made to solve this issue for many.

Thanks

[blueman]

I found a workaround (unless this is the best method after all). I found that I needed to requery the entire form (Orders). Also, I possibly needed to save the record on Form ShipTo (frmShipTo) where this code is on the Unload event. This worked except that it sent me back to the first record in the form so I included some code to move to the last record. Please still take a look at this and let me know if this sufficient or are there are pitfalls here that would make a different approach better. I left the suggested code in there for now but commented it out.
Thanks

Private Sub Form_Unload(Cancel As Integer)
DoCmd.RunCommand acCmdSaveRecord
Forms("frmOrders").Requery
DoCmd.RunCommand acCmdRecordsGoToLast
'If Me.Dirty Then Me.Dirty = False
'    If IsFormLoaded("frmOrders") = True Then
'             If Forms!frmOrders.Dirty = True Then
'            'Commit the values in the bound controls to the table.
'            Forms!frmOrders.Dirty = False
'            'Requery the combo
'           Forms![frmOrders].[cboPickPrevShipTo].Requery
'        End If  'frmOrders.Dirty
'
'    End If  'IsFormLoaded
End Sub

[ItsMe]

ItsMe: Isn't it possible that frmOrders data has been saved when frmShipto is opened, thus it is not dirty at this point?
...

I use code to save when the second form is opened. So, to answer your question, yes, it is saved. However, if the second form is used to make subsequent changes to a field that is part of the original form's recordset, a second save is required. At least, that is the way I remember it and that is how I manage it on my DB's. This is why I save the second form after edits and before closing, then save the first, then requery the combo.

[ItsMe]

... I found that I needed to requery the entire form...

As you have discovered, requerying the form may cause the current record to change. This is why I choose to commit the values of the controls to the table, in the order I previously described.

[blueman]

Did I miss something in your ordering? I thought I had laid it out the same. I could not get it to work. I have a feeling using the IF statement and "Dirty", is a better way to go, as you suggested. But unless I missed something. I couldn't get it to work.

[ItsMe]

Well, I missed one thing. The requery of the combo in my example is within the If Else statement. It should be on the outside. So maybe ...

        If Forms!frmOrders.Dirty = True Then
            'Commit the values in the bound controls to the table.
            Forms!frmOrders.Dirty = False
        End If  'frmOrders.Dirty

            'Requery the combo
            Forms![frmOrders].[cboPickPrevShipTo].Requery

Also, I usually save the current form when opening the second form. I see that you are using the click event. I like click events to launch forms, but it seems you are using a combo's click event? Anyway, when the second form opens, I would add another line to save the current. The saves are to prevent Access from determining that two users are competing for edits on the same record and displaying a msg to the User.

if me.dirty then me.dirty = false
 DoCmd.OpenForm "frmShipTo", _
        DataMode:=acFormAdd, _
        OpenArgs:=Me.Combo27

[blueman]

Thanks, Yes it works perfectly with much better coding now. The only thing I now realize is that I do need to include back into the statement

If IsFormLoaded("frmOrders") = True Then

and add something to stop the procedure if it's not open as there may be occasions when ShipTo is Open and Orders is not.

BTW, to answer your question, the click event is on a command button.

Thanks again

[ItsMe]

You can use Exit Sub in case the form is not loaded. Regardles of frmOrders being loaded or not, you will probably want to save frmShipTo. Otherwise, you can move the if me.dirty statement within the IsFormLoaded, too.

Private Sub ControlName_Click()
If Me.Dirty Then Me.Dirty = False
    If IsFormLoaded("frmOrders") = True Then
    
            If Forms!frmOrders.Dirty = True Then
                'Commit the values in the bound controls to the table.
                Forms!frmOrders.Dirty = False
            End If  'frmOrders.Dirty
        'Requery the combo
        Forms![frmOrders].[cboPickPrevShipTo].Requery
        
    'The form is not open so bail
    Else
    Exit Sub
    End If  'IsFormLoaded
 
End Sub

[blueman]

Strange:

When I use the new code with the IsFormLoaded statement:

Private Sub ControlName_Click()
If Me.Dirty Then Me.Dirty = False
    If IsFormLoaded("frmOrders") = True Then
    
            If Forms!frmOrders.Dirty = True Then
                'Commit the values in the bound controls to the table.
                Forms!frmOrders.Dirty = False
            End If  'frmOrders.Dirty
        'Requery the combo
        Forms![frmOrders].[cboPickPrevShipTo].Requery
        
    'The form is not open so bail
    Else
    Exit Sub
    End If  'IsFormLoaded
 
End Sub

If the Orders form is not loaded, of course the procedure does not run. Great!

However,I'm back to the orders combo box, cboPickPrevShipTo, not refreshing and showing the new record from ship to. However, if i remove the IsFormLoaded part and just have this:

Private Sub Form_Unload(Cancel As Integer)


    If Forms!frmOrders.Dirty = True Then
            'Commit the values in the bound controls to the table.
            Forms!frmOrders.Dirty = False
        End If  'frmOrders.Dirty


            'Requery the combo
            Forms![frmOrders].[cboPickPrevShipTo].Requery
           
End Sub

Then we're back to having the the record show but of course, no exit if Orders isn't open.

One note, this is all set to Unload:

Private Sub Form_Unload(Cancel As Integer)

The code you had above was On Click (I was referring to the code on the command button that opens the ship to in one of my recent replies).

Thanks again for looking at this

[ItsMe]

However,I'm back to the orders combo box, cboPickPrevShipTo, not refreshing and showing the new record from ship to.

If the Order form is not loaded, you will not be able to requery the combo or save the Order form.

            If Forms!frmOrders.Dirty = True Then
                'Commit the values in the bound controls to the table.
                Forms!frmOrders.Dirty = False
            End If  'frmOrders.Dirty
        'Requery the combo
        Forms![frmOrders].[cboPickPrevShipTo].Requery

However, if i remove the IsFormLoaded part and just have this:

You cannot just have that. You need to add saving the current form.

If Me.Dirty Then Me.Dirty = False

You need to save the current form before checking isformloaded and requerying the combo that is located in the orders form. If frmOrders is not loaded, you will not be able to requery the combo.

Then we're back to having the the record show but of course, no exit if Orders isn't open.

You do not need to use Exit Sub. If you do not want to execute code below a particular line/statement, place Exit Sub just above the code you do not want to execute. If you want to cancel the form from Unloading, use the statement Cancel = True

[blueman]

However,I'm back to the orders combo box, cboPickPrevShipTo, not refreshing and showing the new record from ship to.

If the Order form is not loaded, you will not be able to requery the combo or save the Order form

The order form is loaded when the above occurs using the full code you provided before. Which is this:

Private Sub Form_Unload(Cancel As Integer)
If Me.Dirty Then Me.Dirty = False
    If IsFormLoaded("frmOrders") = True Then
                If Forms!frmOrders.Dirty = True Then
                'Commit the values in the bound controls to the table.
                Forms!frmOrders.Dirty = False
            End If  'frmOrders.Dirty
        'Requery the combo
        Forms![frmOrders].[cboPickPrevShipTo].Requery
        
    'The form is not open so bail
    Else
    Exit Sub
    End If  'IsFormLoaded
 
End Sub

I've moved the

If Me.Dirty Then Me.Dirty = False

to various points in the procedure with the same results: no requerying of the combo box on the orders form (or it may not be the requerying but the new record does not show however the record does show in the tblShipTo.

I also tried removing the Exit Sub: same results

The only thing that gives me the results showing in the combo box on the order form is this:

Private Sub Form_Unload(Cancel As Integer)
  If Me.Dirty Then Me.Dirty = False
  If Forms!frmOrders.Dirty = True Then
     'Commit the values in the bound controls to the table.
  Forms!frmOrders.Dirty = False
  End If  'frmOrders.Dirty
      'Requery the combo
  Forms![frmOrders].[cboPickPrevShipTo].Requery
                
End Sub

The only thing removed is:

 If IsFormLoaded("frmOrders") = True Then



 'The form is not open so bail
    Else
    Exit Sub
    End If  'IsFormLoaded

Which I need so I can close frmShipTo if that form is being used when frmOrders is not being used. Otherwise I get an error where the code is looking for frmOrders to close and it's not open.

Thanks

[ItsMe]

If you can understand what the various statements do, then the problem will not seem so big. One way that helps is to use debugging features of the VBA editor. Of course, understand VBA language and the Order of Events helps things, too.

I believe the best way to get over this hump is for you to create a new DB file and upload it here. In the new DB, import the necessary form's, tables and queries. Compact and repair the DB, zip it down and upload it here. Do not include personal or confidential data.

I have a few things to take care of but will take a look at it when I have the chance. Maybe someone else subscribed to this thread will have a chance to look at it also.