Full Version: Subreport refresh problems
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
GRIFF
I have a main form and 3 subforms all in a one to many relationship. I use an unbound combobox to move through the records with this code
Me.RecordsetClone.findfirst "[RoutingNum] = '" & Me![cboRoutNum] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
If the user types in a new routing number I add the record with the not in list event using acDataErrAdded this works fine but this dose not refresh my subforms. I have looked on this forum and use every possible I think fix .requery the subform control requery the form. I tried setting the bookmark. I put the code in the current event in the not in list event. After I add the new record the subforms show the records for the previous entry.As I use the combobox to move to a new record the and go back to the new entry the subform still shows the wrong record here is my code
Private Sub cboRoutNum_NotInList(NewData As String, Response As Integer)
If MsgBox("Routing Number not found! Add new One", _
vbYesNo + vbQuestion, "add new one?") = vbYes Then

Dim db As Database
Dim RS As DAO.Recordset

Set db = CurrentDb
Set RS = db.OpenRecordset("tblRouting", dbOpenDynaset)
RS.AddNew
RS!RoutingNum = NewData
RS.Update
RS.Close
Set RS = Nothing
Set db = Nothing
Response = acDataErrAdded

Else
Response = acDataErrContinue
End If
Thanks for for help
Jack Cowley
Welcome to Utter Access!
id you try requerying the combo box that you just changed? I assume the combo works if you close the form and then reopen it...
hth,
Jack
GRIFF
Yes if I requery the combobox I get the I get runtime error 2118 you must save the current field before running the requery action
Thanks for thr reply
Jack Cowley
Instead of the code you are using try the code in Method 1 that you will find here.
th,
Jack
GRIFF
I wil give it a shot thanks
Jack Cowley
You are welcome. I know that the code in that article works so that is why I suggested it...
Good luck.
Jack
GRIFF
Well I tried and my subforms do not refresh with this new code. But I did observe some interesting behavior the subforms do change they all go back to the first record. Lets say I have the combo box moved to the 3 rd record and the suforms show correctly. then I type in a new number all the prompts work fine I add the new number and the subforms change but all to the first record?????
Jack Cowley
Let's see if I have this right.. You have a combo box and your subforms are linked to this combo box. Selecting an item in the combo will show the related record(s) in the subform(s). Now if you add a new record to the combo box the subforms will not show that new item you just added, right? If that is the case are you updating the records in the subform as well so that a record exists that has the same value as the value you entered into the combo box? Or is the number in the combo box the foreign key in the subform(s)?
If you close our form and reopen it does your new combo item work? If it does then the problem is on of requerying or making sure the new Row Source for the combo box has properly been saved....
Jack
GRIFF
I think you have me on the right track. When I add a new record it goes into the " main table" but at this point no related records exist in the other tables so when I requery the subforms they go back to the first record .So on the form the combo box show say the 14th record and the subforms show the first record . So I'm thinking I should clear the controls in the subforms to let the user know to add nwe data for this record. If this is right is there an easy way to clear all the controls on a subform?
Thank you very much!!
Jack Cowley
I would not clear the subforms but hide them. You can use DCount() to see if there are any records in the tables that are the Record Source for the subform(s) that have the new number and if the count is 0 then set the visible property of the subform control to No and pop up a message box telling the user that no records meet the criteria in the combo box.
Just between you and me it seems a bit backwards to add the number, or whatever, to the combo box before adding the data to the related tables. The combo box should be getting its data from the tables or queries the subform(s) are based on and if the data is not there then open the form that will add the data to the table that is the Record Source for the subform(s). That will mean that the new data will appear in the combo box and since the records have been created in the table(s) all will be well.
hth,
Jack
GRIFF
I an still banging my head on this. I added code to add the values to the field in table that the combo box gets ist list from and the fields in the related table that the subform is based on the values are there in the right fields but when the not in list event finishes the subform dosen't refresh. I also placed this code in the aferupdate event Private Sub cboRoutNum_AfterUpdate()
' Find the record that matches the control.
' Me.RecordsetClone.FindFirst "[RoutingNum] = '" & Me![cboRoutNum] & "'"
' Me.Bookmark = Me.RecordsetClone.Bookmark

Dim rscombo As DAO.Recordset
Set rscombo = Me.RecordsetClone
rscombo.FindFirst "[RoutingNum] = '" & Me![cboRoutNum] & "'"
If rscombo.NoMatch Then
MsgBox "Routing not found"
Else
Me.Bookmark = rscombo.Bookmark

End If
End Sub
So when I pick these new records off the list I get the "Routing not found message" so the combo cant see the new record in the table if I close the form and start it up everything is fine.
Would you care to look at the whole thing I can zip it up and send it sorry to be a pain shrug.gif
GRIFF
I am stiil not getting the subform to refresh
Oadded code to put the right values in the correct tables and the data is there correctly
I added this to the after update event
Private Sub cboRoutNum_AfterUpdate()
' Find the record that matches the control.
' Me.RecordsetClone.FindFirst "[RoutingNum] = '" & Me![cboRoutNum] & "'"
' Me.Bookmark = Me.RecordsetClone.Bookmark

Dim rscombo As DAO.Recordset
Set rscombo = Me.RecordsetClone
rscombo.FindFirst "[RoutingNum] = '" & Me![cboRoutNum] & "'"
If rscombo.NoMatch Then
MsgBox "Routing not found"
Else
Me.Bookmark = rscombo.Bookmark

End If
End Sub
After the not in list event is finished if I select the added record i get "routing not found" so the combobox can't see the new record.Closing the form and opening and everything is ok
Would you like tto take a look at the whole thing I can zip and send? Sorry to be a pain.
Jack Cowley
If closing the form and reopening the form causes the combo box to work then it sounds like the record is not being saved to the table. Force a save in you code with a DoCmd.RunCommand acCmdSaveRecord and see if that fixes the problem.
would look at your db, but I am beyond busy and the soonest I could look at it would probably be later this afternoon...
hth,
Jack
GRIFF
Thanks Take a look today.... tommorrow any time
Jack Cowley
Which form am I to look at? Where is the code the adds the numbers to the subforms? Which combo box should I be looking at?
It will be at least 2 hours before I can look at the db, possibly longer so please be patient...
Jack
GRIFF
The name of the form is tblRoutingNum The combobox is at the top of the form called cboRoutNum. all the code is in the not in list event of the cboRoutNum combo box that deals with adding the new records.I only add records to two tables in the code so far the the tblRouting table which is the data for tblRoutingNum form and the tblPrograms table which is the data for the tblProgramsSubform
Take as long as you want... days to to this I realize it is a huge favor so only do it if you have time
Thanks again
Jack Cowley
Thank you for the information. I will get to this ASAP.
ack
Jack Cowley
Try form "tblRoutingNum1" in the attached....
tossed this together fast, but I think it will do what you want...
Jack
GRIFF
I am unable to open the form access is looking for a dll and then crashes it's looing for msadomd.dll ver 2.8 I can open the database and open the form in design mode If you tell me what you changed I can Hunt it down
Thanks
Jack Cowley
Not sure what is going on but I had problems at this end too, but finally got it to work... Delete the code you have in the Not In List event and then copy and paste in this code:
CODE
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewName As String
On Error GoTo Err_CustomerID_NotInList
    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub
    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add a customer, set the Response
        ' argument to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        MsgBox "Please try again."
    Else
        ' If the user chose to add a new customer, open a recordset
        ' using the Customers table.
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("tblRouting", dbOpenDynaset)
        ' Ask the user to input a new Customer ID.
        Msg = "Please enter a Name"
        NewName = InputBox(Msg)
        ' Create a new record.
        Rs.AddNew
        ' Assign the NewID to the CustomerID field.
        Rs![Name] = NewName
        ' Assign the NewData argument to the CompanyName field.
        Rs![RoutingNum] = NewData
        ' Save the record.
        Rs.Update
        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
    End If
Exit_CustomerID_NotInList:
       Exit Sub
Err_CustomerID_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue

Good luck!
hth,
Jack
GRIFF
Thanks for everything notworthy.gif
Jack Cowley
You are welcome. I hope that solved your problem... As I said I did it quickly, because of time constraints, so the InputBox is not the way that I would normally go. If you have additional questions just let me know....
ack
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.