Form Rowsource Not Updating

[Meleagant]

All,

I have two forms, the main form contains information on the clients survey, the second form contains information about the questions. Before I launch the second form I exec a sql proc that makes sure that all of the information needed on the second form is included in the DB, if it is not there I insert it. I do this by:

    Dim SqlStr As String
    Dim ConnStr As String
    Dim oConn As ADODB.Connection
    Dim rs As ADODB.Recordset
    ConnStr = "Provider=SQLOLEDB.1...."
    Set oConn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    oConn.Open ConnStr
    SqlStr = "Exec NewSurvey '" & sDNIS & "', " & sItem
    rs.Open SqlStr, oConn
    Set rs = Nothing
    Set oConn = Nothing
    
    DoCmd.OpenForm "frmQuestions"

Once I open frmQuestions I create a SQL String and set that equal to the RecordSource Property of frmQuestions:

    SqlStr = "Select S.SurveyId, S.ClientName, Q.QuestionNumber, Q.QuestionText" & _
             " From SurveyMaster S" & _
             "  Inner Join QuestionMaster Q on Q.SurveyId = S.SurveyId" & _
                " Where ActiveDNIS = '" & sDNIS & "'" & _
                "  and Item = " & sItem
    Me.RecordSource = SqlStr

All of this works great if there is no inserting, but if I insert anything the RecordSource is null (at EOF). I know I am executing two types of SQL, one on the actual SQL server and one inside the access db, but the tables are linked, shouldn't the access tables refresh? If I pause the execution of the code right after my stored proc call I can see the data in the access tables. I can take the RecordSource Sql and paste it into a Access Query and get the information. Can anyone please help?

Journeyman -- The Order of the Seekers of Truth and Penitence

 

[jadams0173]

I would think that you need to open a recordset with SqlStr and assign the recordset variable to the form Recordsource.

rs.open Sqlstr........

me.recordsource=rs


I tried to have patience but it took to long! -DW

 

[Meleagant]

Tried that but the RecordSource property can only be set to a table name, a query name, or an SQL statement. I went back and took out all of the code that I used to update the SQL database directely and replaced it all with this:

        SqlStr = "Insert Into SurveyMaster(SurveyId, ClientName...) Select " & SurveyId & "," & ClientName...

        DoCmd.RunSQL SqlStr, 0

So this way I am updating the access tables directly, I even get the warning that I am about to append N rows to the database until I set the warnings to off. I am baffled. I know my frmQuestions code works because when I open it without having to do any inserts it works fine.

Journeyman -- The Order of the Seekers of Truth and Penitence

 

[Meleagant]

After a day and a half I got it, I finally realized that the Sub that was opening the new form was not finished executing and for whatever reason the newly opened form could not see the new database information that was inserted until that Sub had finished executing (hit End Sub). Now I need to figure out a way for that sub to end AND then populate my form. The AfterFinalRender event has promise, but it doesn't consistently execute after the calling sub ends.

Journeyman -- The Order of the Seekers of Truth and Penitence