Subform refresh/requery question

[magster06]

Ok, I have tried to get my subform to requery and for the life of me, I cannot get it to work with code, but it will work with the F9 key.

Main form - frmCaseTimeline
subform - sfTimeline
combo box - cboTimeline
Main table - tblCaseDetails
2nd table - tblCaseTimeline

I have frmCaseTimeline with a combo box and subform (and textboxes).

The subform pulls 2 fields from the main table and this is done by query.

The combo box draws its fill from a query for ID numbers from the main table (not the timeline table). When a user selects an ID from the comb box, then the textboxes and subform are populated; this works fine.

If there is not a record assosciated with the ID selected, then a message box will ask the user if he would like to create a record for that ID. When the user clicks ok, then the ID is placed on the main form and should pull the 2 fields from the main table.

The problem is that the values from the 2 fields pulled do not show up until I close the form and reopen or press F9.

I have tried placing requery code in the following:
combo box afterupdate event
main form afterupdate event
subform afterupdate event
subform current event

Like I mentioned, if I press F9 or click in the subform it will refresh, but not automatic.

I hope that I am making sense.

 

[boblarson]

I have found that sometimes a requery will not work for a subform and the thing that WILL work is reassigning its record source.

 

[magster06]

Thanks for the response Bob!

So are you saying to change the recordsource by creating another query and then reference it in the afterupdate event of the combo box?

 

[boblarson]

Thanks for the response Bob!

So are you saying to change the recordsource by creating another query and then reference it in the afterupdate event of the combo box?

No just reassign it:

Me.SubformControlNameHere.Form.RecordSource = "OriginalQueryNameOrSQLHere"

in the after update of the combo box. No requery code would be used (whenever you set the record source the query happens anyway).

 

[magster06]

Bob,

I did as you described and now I get an error message:

Method 'Form' of object _subform failed

 

[boblarson]

Bob,

I did as you described and now I get an error message:

Method 'Form' of object _subform failed

A little education here. You need to use the subform CONTROL name (control on the parent form which HOUSES/DISPLAYS the subform) and NOT the name of the subform itself UNLESS the name of the subform control is the exact same name of the subform itself. So, see the screenshot here which shows you what to use:
http://www.btabdevelopment.com/ts/ewtrhtrts

 

[magster06]

Yes, the subform control and subform are the same name. The article was an interesting read.

This is what I have in the combo after update event:

Me.sfTimeline.Form.Recordsource = "QCallIncidentDate"

Hmmm, nothing seems to work.

 

[magster06]

Ok, I decided to create a new db and import everything into it from the old db. The error message has now gone, but I have to select the ID twice in the drop down to get the subform to populate.

Not sure if I still need to change the reference to the subform or not.

 

[boblarson]

Ok, I decided to create a new db and import everything into it from the old db. The error message has now gone, but I have to select the ID twice in the drop down to get the subform to populate.

Not sure if I still need to change the reference to the subform or not.

Which event did you put the code on?

 

[magster06]

The after update of the combo box that is on the main form

 

[boblarson]

Try doing this:

Application.Echo False
Me.sfTimeline.Form.Recordsource = vbNullString
Me.sfTimeline.Form.Recordsource = "QCallIncidentDate"
Application.Echo True

 

[magster06]

Bob,

Could the issue be with this code that I have in my combo box to check if the ID is in the table (tblTimeline) and if not, then it will add it to the form?

Private Sub cboCaseTimeline_Click()
    Dim intAnswer As Integer
     If IsNull(Me!cboCaseTimeline) Then Exit Sub
    With Me.RecordsetClone
      .FindFirst "CaseNumber = """ & Me!cboCaseTimeline & """"
      If Not .NoMatch Then
         If Me.Dirty Then Me.Dirty = False
         Me.Bookmark = .Bookmark
      Else
         intAnswer = MsgBox("The Case Number was not found! Do you wish to add a Focus to PSU Case Number """ & Me!cboCaseTimeline & """ ", vbYesNo + vbInformation, "Case Number Not Found")
            If intAnswer = vbYes Then
                DoCmd.GoToRecord , , acNewRec
                Me.txtCaseNumber = "" & Me!cboCaseTimeline & ""
            Else
                Exit Sub
            End If
      End If
    End With
End Sub

 

[boblarson]

Bob,

Could the issue be with this code that I have in my combo box to check if the ID is in the table (tblTimeline) and if not, then it will add it to the form?

Private Sub cboCaseTimeline_Click()
    Dim intAnswer As Integer
     If IsNull(Me!cboCaseTimeline) Then Exit Sub
    With Me.RecordsetClone
      .FindFirst "CaseNumber = """ & Me!cboCaseTimeline & """"
      If Not .NoMatch Then
         If Me.Dirty Then Me.Dirty = False
         Me.Bookmark = .Bookmark
      Else
         intAnswer = MsgBox("The Case Number was not found! Do you wish to add a Focus to PSU Case Number """ & Me!cboCaseTimeline & """ ", vbYesNo + vbInformation, "Case Number Not Found")
            If intAnswer = vbYes Then
                DoCmd.GoToRecord , , acNewRec
                Me.txtCaseNumber = "" & Me!cboCaseTimeline & ""
            Else
                Exit Sub
            End If
      End If
    End With
End Sub

That should ALSO be on the AFTER UPDATE event of the Combo and NOT the Click event like it is now.

 

[magster06]

I placed the code in the proper place, but I still do not get a refresh. I am going to chop down the db and see if I can upload it.

 

[magster06]

Here is the db. If you use the drop down and go to a case number not in the tblCaseDetails, then it will prompt you to add it to the form.

Oh, and be gentle with me, I am not a vba guy or anything else for that matter. I am just a grunt firefighter trying to help our department.

 

[boblarson]

Have to wait until I get home tonight. I only have 2007 here and it would appear, from the Unrecognized Database Format error that it is in 2010 with 2010 features. So, if anyone else, who has 2010, can look maybe they can do it before I can.

 

[magster06]

Thanks Bob!

 

[boblarson]

Okay, a couple of things. I changed your code to this (you weren't assigning the recordset clone to a recordset object):

Private Sub cboCaseTimeline_AfterUpdate()
    Dim intAnswer As Integer
    Dim rst As DAO.Recordset
    If Len(Me.cboCaseTimeline & vbNullString) <> 0 Then
        Set rst = Me.RecordsetClone
        With rst
            .FindFirst "CaseNumber = " & Chr(34) & Me.cboCaseTimeline & Chr(34)
            If .NoMatch Then
                intAnswer = MsgBox("The Case Number was not found! Do you wish to add a Focus to PSU Case Number """ & Me!cboCaseTimeline & """ ", vbYesNo + vbInformation, "Case Number Not Found")
                If intAnswer = vbYes Then
                    Me.Recordset.AddNew
                    Me.txtCaseNumber = Me.cboCaseTimeline
                End If
            Else

                Me.Bookmark = .Bookmark

            End If


        End With
        rst.Close
        Set rst = Nothing
    End If
End Sub

But, after looking at this, I can see that you have it backwards. The way you have the tables (take a look at the relationships window) you have a ONE to MANY but you have the wrong table as the subform. The main form should be the ONE and the subform the MANY.

As for your table structure. I don't know the extent to what your processes are so I can only make generalized examples.

Example:

tblTimelineTypes
TimelineTypeID - Autonumber (PK)
TimelineDescription - Text

tblTimelines
TimelineID - Autonumber(PK)
CaseID - Long Integer (FK)
TimelineTypeID - Long Integer (FK from tblTimelineTypes)
TimelineDate - Date


tblCases
CaseID - Autonumber (PK)
CaseNumber - Text
...and the rest of the same fields currently in your case details table EXCEPT witnesses. Witnesses, since there can be more than one, should have its own table.

tblWitnesses
WitnessID - Autonumber(PK)
CaseID - Long Integer (FK)
WitnessFirstName - Text
WitnessLastName - Text
WitnessAddress - Text
WitnessCity - Text
WitnessState - Text
WitnessPostCode - Text

Does any of that make sense?

 

[magster06]

Bob,

Thank you for taking the time to look at my issue.

But, after looking at this, I can see that you have it backwards. The way you have the tables (take a look at the relationships window) you have a ONE to MANY but you have the wrong table as the subform. The main form should be the ONE and the subform the MANY.

I see what you are saying. If I change things, then doesnt that mean I will have to change my form design? Will I have to use combo boxes for the user to select each Timeline event? (JZ gave me an example of a junction table setup, but I wanted to stay away from combos to avoid the user selecting the wrong timeline event).

What if I change my main form and subform around? Will that work?

Oh, yes, the witnesses already have their own table.

OR, is there a way to preset the Timeline Descriptions on the main form and then the user selects the date?

 

[magster06]

Ok, I changed the form to have the main form populate from the main table and the subform from the secondary table.

Bob, if I could ask a favor, when you get the time would you be able to create an example (from the db example I posted) of what you were suggesting with the tblTimelineTypes, tblTimeline, tblCaseDetails (I cant seem to wrap my head around that).

Oh, I am posting my updated db to see if it is acceptable