Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Requerying a Subform (A2k-SR1)

    Hi,

    I have a main form with a datasheet style subform. The record source for the main form is a SQL statement that returns the unique dates from a table (tblAssays) that is the recordsource for the subform and I use this together with an unbound combobox to navigate through the records in tblAssays one day at a time. I have added a click button to the main form to add a set of new records to tblAssays. When the user clicks this button, the code asks the user for the sample time, and then adds as many new records to tblAssays as there are records in another table containing a set of sample names. Along with the sample names, the relevant fields are also populated with today's date and the time that the user entered. The subform should therefore display a set of records that have the first three columns (Sample Name, Date and Time) filled in and then the user simply fills in all the assays for each record. Two hours later, when the next batch of samples have been analysed, the user will simply click the button again, provide the new sample time and the whole set of records is then ready for the assays to be typed in again. I have attached a screen dump of the form.

    The problem is that sometimes the form and the sub-form do not always refresh properly and display the new records that have been added. After the code that steps through the table of sample names has completed and all the new records have been added, I have a requery command :

    <font color=red> Me!ctlsfrmInputAssays.Form.Requery</font color=red>

    I have tried closing and opening the form from the code, and again sometimes this shows the new records, and sometimes it doesn't. I also put a for loop in between the close and open commands with a DoEvents function in it, but to no avail. To be honest I was surprised that the for loop executes as I would have expected the for-loop variable to be destroyed when the form closed, but the code seems to run.

    Any ideas on how to consistently make the subform show the newly added records ? TIA,
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Requerying a Subform (A2k-SR1)

    Adrian,

    This is strange - Requery should display the newly added records. How do you link the main form and subform? Using master and child link fields, or in code? I modified an existing database with a main form and subform linked by master and child link fields; it seems to update correctly.

    I'm afraid I don't have an explanation or solution for you. I have attached my test Access 97 database (with everything else stripped out; it is much simpler than yours of course). See if it behaves correctly after converting to Access 2000. If so, perhaps you can spot a difference in how the link and code is set up.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requerying a Subform (A2k-SR1)

    Hans, thanks for the speedy reply. I will check out your sample db over the weekend - hopefully I can pick up a significant difference. Your db certainly does show the new records straight away. I haven't looked at your code yet, but it is already after beer o'clock on a Friday !!

    The main and sub forms are linked as you described using the master and child fields.

    One thing I omitted to mention is that my tables live in a back end db on a server. When I "talk" to the table to add the records, I am using ADO with the connection specified using a DSN that I have defined on my PC. I don't know if this could be part of the problem ? The fact that this problem is not completely reproducible possibly suggests to me that their is a "time lag" problem so that when the requery runs, the new records have not yet been committed to the table ??!! Or am I grasping at straws here ?

    Regards,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Requerying a Subform (A2k-SR1)

    Adrian,

    I had hoped that somebody else would have picked up on the additional questions you asked during the weekend. I am using Access 97 with DAO; my sample database still functions correctly with a linked table in a backend database. I have no experience with Access 2000 and ADO, so I don't know if and how that influences requerying the subform. Sorry!

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Requerying a Subform (A2k-SR1)

    Why don't you post the relevant code. Otherwise we're just guessing about what might be the problem.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requerying a Subform (A2k-SR1)

    OK - thanks - here is the code. As Hans mentioned, he used DAO and I am using ADO, but I don't see why it should work sometimes and not others.

    <font color=red>Private Sub cmdAddSet_Click()

    Dim cnn As ADODB.Connection
    Dim rstAssays As ADODB.Recordset, rstSampleNames As ADODB.Recordset

    Dim strDSN As String
    Dim strForm As String
    Dim i As Long, j As Long
    Dim dteTime As Date
    Dim strTemp As String

    strTemp = InputBox("Enter the time as hhmm - e.g. 1430 for 14:30 ?", "What time were these samples taken ?")

    If strTemp = "" Then
    Exit Sub
    Else
    If Len(strTemp) <> 4 Then
    MsgBox "You didn't enter 4 numbers to represent the time." & vbCrLf & vbCrLf & _
    "Enter the time as hhmm - e.g. 0600 or 0830 or 0915 ?", vbExclamation + vbOKOnly, "Error . . ."
    Exit Sub
    End If

    strTemp = Left(strTemp, 2) & ":" & Right(strTemp, 2)

    End If

    dteTime = Format(CDate(strTemp), "Short Time")

    strDSN = "CM LIMS_BE"

    Set cnn = New ADODB.Connection
    On Error GoTo Err_Connection
    cnn.Open strDSN

    Set rstAssays = New ADODB.Recordset
    Set rstSampleNames = New ADODB.Recordset

    rstAssays.Open "tblAssays", cnn, adOpenKeyset, adLockOptimistic, adCmdTable
    rstSampleNames.Open "SELECT [SampleID] FROM tblSampleNames WHERE [Enabled] = " & vbTrue & ";", cnn, adOpenKeyset, adLockOptimistic, adCmdText

    On Error GoTo Err_Update

    rstSampleNames.MoveFirst

    Do While Not rstSampleNames.EOF

    rstAssays.AddNew
    rstAssays!SampleID = rstSampleNames!SampleID
    rstAssays!xDate = Date
    rstAssays!xTime = dteTime
    rstAssays.Update

    rstSampleNames.MoveNext

    Loop

    ErrorHandlerExit:

    rstAssays.Close
    rstSampleNames.Close
    cnn.Close

    Me!ctlsfrmInputAssays.Form.Requery

    </font color=red><font color=448800>' strForm = Me.Form.Name
    ' DoCmd.Close acForm, strForm
    '
    ' For i = 1 To 5000
    ' j = DoEvents()
    ' Next i
    '
    ' DoCmd.OpenForm strForm</font color=448800><font color=red>

    Exit Sub

    Err_Connection:

    MsgBox "Could not open a connection to the ODBC datasource named " & strDSN, vbCritical + vbOKOnly, "Error"
    Exit Sub

    Err_Update:

    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description, vbCritical, "Error updating table"
    Resume ErrorHandlerExit

    End Sub</font color=red>

    Regards,

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Requerying a Subform (A2k-SR1)

    Hi Adrian,
    What version of ADO are you using? There were some dicey spots as far as the original ADO version that came with Access 2000 was concerned. Is there a particular reason you are using ADO? Since you are working with a subform, I assume you have either a local table or a linked table as the data source for the subform. We tend to use DAO in most cases of this kind with 2000 - 2002 has more robust support for ADO so we do use it more frequently with it.
    Wendell

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Requerying a Subform (A2k-SR1)

    In my experience, not all errors get passed back to the application's errors collection. You might try testing the connection's error collection to see if there are errors you aren't seeing. In your error handler, add something like this:

    <pre> If cnn.Errors.Count <> 0 Then
    For Each objErr in cnn.Errors
    MsgBox objErr.Number & "--" & objErr.Description
    Next objErr
    cnn.Errors.Clear
    End If </pre>


    Remember to Dim objErr as an ADODB.Error object in your declarations.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requerying a Subform (A2k-SR1)

    Hi Wendell,

    I was using ADO V2.1, but I have now tried it with V2.7 and still no luck. I couldn't remember which version shipped standard with Office 2000, so I thought 2.1 would do fine, thereby preventing me from having to install the latest MDAC on any user machines that would use my db front end from the server.

    The reason that I am using ADO instead of DAO was that I wanted to practice using it, because at some stage in the near future I was thinking of moving my db into SQL Server and converting the front end into a .adp. I would then not be able to use DAO to manipulate data.

    As a matter of interest, following Hans' example, I changed the code to use DAO and it works fine. I just had to do a <font color=red>Me.Requery</font color=red> followed by a <font color=red> DoCmd.GoToRecord , , acLast</font color=red> as well to make the main form go to the latest date. The newly added records were then visible every time.

    Previously, once the ADO code had run, the main form still reflected only one date record and nothing for the latest date record. If I manually closed the form and then reopened it, I would have two date records (the original dummy record plus today's record) and the sub form would show the newly added records.

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requerying a Subform (A2k-SR1)

    Hi Charlotte,

    There were never any errors - the code updated the table every time, but the form mostly didn't reflect the changes straight away. Closing and opening the form from the code didn't always do the trick. Closing the form manually and then re-opening it would always show the new records.

    Thanks,

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Requerying a Subform (A2k-SR1)

    Adrian,

    In your code you're connecting to the backend directly. Have you tried using the linked table in the frontend instead (still with ADO)?

  12. #12
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requerying a Subform (A2k-SR1)

    Hans,

    I was going to try this, but got stuck - I don't know how to specify a connection string to the local linked table. It was easy to use the DSN to the back end.

    Hopefully someone will reply to this message with the few lines of code required as all the examples I have seen want provider info and then path and filename info. Is there an easy way to refer to the current database in the ADO connection definition I wonder ? I don't want to hard-code a reference to the .mdb file if I can help it.

    I know you said you don't use ADO, so hopefully someone will tell us,

    Thanks,

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Requerying a Subform (A2k-SR1)

    How about using CurrentProject.Connection to specify a connection to the current database? Something like

    Set cnn = CurrentProject.Connection

  14. #14
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requerying a Subform (A2k-SR1)

    Hans,

    Thanks, that did the trick - I thought it was a timing problem and that the changes hadn't been committed yet or the application wasn't seeing the changes in the back end table yet. Who knows ! I even tried with <font color=red>cnn.BeginTrans</font color=red> and <font color=red>cnn.CommitTrans</font color=red>, but that gave me an error telling me that there was no active transaction when it hit the <font color=red>.BeginTrans</font color=red> line. I know there wasn't an active transaction - I was trying to start one <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    To put this one to bed, the code that worked was :

    <font color=red>Private Sub cmdAddSet_Click()

    Dim cnn As ADODB.Connection
    Dim rstAssays As ADODB.Recordset, rstSampleNames As ADODB.Recordset

    Dim strDSN As String, strSQL As String
    Dim i As Long, j As Long
    Dim dteTime As Date
    Dim strTemp As String
    Dim a As Integer

    On Error GoTo Error_Handler

    </font color=red><font color=blue> . . . code for sorting out sample time . . . </font color=blue><font color=red>

    </font color=red><font color=448800> ' Set cnn = New ADODB.Connection</font color=448800><font color=red>

    Set cnn = CurrentProject.Connection

    Set rstAssays = New ADODB.Recordset
    Set rstSampleNames = New ADODB.Recordset

    rstAssays.Open "tblAssays", cnn, adOpenDynamic, adLockPessimistic, adCmdTable
    rstSampleNames.Open "SELECT [SampleID] FROM tblSampleNames WHERE [Enabled] = " & vbTrue & ";", cnn, adOpenKeyset, adLockOptimistic, adCmdText

    rstSampleNames.MoveFirst

    Do While Not rstSampleNames.EOF

    strSQL = "INSERT INTO tblAssays (SampleID, xDate, xTime) VALUES (" & rstSampleNames!SampleID & ", #" & Format(Date, "mm/dd/yy") & "#, #" & dteTime & "#);"
    cnn.Execute strSQL

    rstSampleNames.MoveNext

    Loop

    Exit_Handler:
    On Error Resume Next
    rstAssays.Close
    rstSampleNames.Close
    cnn.Close
    Me.Requery
    DoCmd.GoToRecord , , acLast
    Me!ctlsfrmInputAssays.Form.Requery
    Exit Sub

    Error_Handler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description, vbCritical, "Error . . . "
    Resume Exit_Handler

    End Sub</font color=red>

    Some other points about this problem and it's solution :

    - I don't seem to need the <font color=448800>Set cnn = New ADODB.Connection</font color=448800> that I commented out - does the next line initialise the connection completely ? The code worked with or without that line.

    - I used an SQL "INSERT INTO" query to add the new records, althought the previous code with the <font color=red>.AddNew</font color=red> and <font color=red>.Update</font color=red> methods also works.

    - The second <font color=red>.Requery</font color=red> on the sub form is no longer necessary - Doing a requery on the main form seems to requery the sub form as well.

    - I am clueless about the different cursortypes and locktypes used when opening the recordsets, but the ones above seemed to work in this case. More reading <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    Thanks everyone for your help,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •