Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Sheet Displays then Refreshes? (A2k (9.0.3821) SR-1)

    Data Sheet Displays then Refreshes?
    A2k (9.0.3821) SR-1

    I have a columnar form with a button that displays a data sheet form of records based on a query.

    When I press the button the data sheet display the records OK, however it redisplays the data sheet a second time.

    It

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

    Re: Data Sheet Displays then Refreshes? (A2k (9.0.3821) SR-1)

    Sorry, John, but I don't understand what you mean by "it redisplays the data sheet a second time". Are you saying it displays the datasheet and then requeries it? If so, I'd check the form you're opening as a datasheet to see what code in it is causing the requery.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Sheet Displays then Refreshes? (A2k (9.0.3821) SR-1)

    Hi Charlotte

    It looks like a requery

    I put the button on a blank form and adjusted button code as follows with same results

    I click the button, the data sheet fills up once, then it fires the message, when I acknowledge the message the the data sheet requeries ? itself

    There is no code in the data sheet.

    Should I have received the message when the data sheet populates up the first time?

    Any clues?

    John


    Private Sub View_Records_Click()
    On Error GoTo Err_View_Records_Click

    'DoCmd.OpenForm mstrLast20FormName, acFormDS, , , , , Me.Name
    DoCmd.OpenForm "frm_Last_20_Permit_Numbers_HP", acFormDS, , , , , "frm_HP_Applicant"
    MsgBox "Just Opened Data Sheet Form: "
    'Me!cboLookupSequence.SetFocus

    Exit_View_Records_Click:
    Exit Sub

    Err_View_Records_Click:
    MsgBox Err.Description
    Resume Exit_View_Records_Click

    End Sub


    PS: the data sheet record source query looks this:

    SELECT tbl_HP_Applicant.*, [strLastName] & ", " & [strFirstName] & " " & [strMI] AS Applicant, [strDrLastName] & ", " & [strDrFirstName] & " " & [strDrMI] AS Physician, [strAddrName] & ", " & [strAddrNo] AS Address, Format(Right([strHomePhone],7),"@@@-@@@@") AS ApplPhone7, Format(Right([strDrPhone],7),"@@@-@@@@") AS DrPhone7, tbl_HP_Permit.strPermitNo, tbl_HP_Permit.dtmIssueDate, tbl_HP_Permit.dtmExpireDate, tbl_HP_Permit.memNotes, tbl_HP_Permit.lngOutcomeID, tbl_HP_Permit.lngOperatorID, tbl_HP_Permit.strPermitNo AS AdjustPermit, tbl_HP_Permit.dtmStatusDate, tbl_HP_Permit.ysnActivePermit, DMin("strPermitNo","tbl_HP_Permit","strPermitNo>'" & [strPermitNo] & "'") AS DMinNo, DMax("strPermitNo","tbl_HP_Permit","strPermitNo<'" & [strPermitNo] & "'") AS DMaxNo, Val(Right(Nz(DMin("strPermitNo","tbl_HP_Permit","s trPermitNo>'" & [strPermitNo] & "'")),7))<>(Val(Right([strPermitNo],7))+1) AS [IsGap Before], Val(Right(Nz(DMax("strPermitNo","tbl_HP_Permit","s trPermitNo<'" & [strPermitNo] & "'")),7))<>(Val(Right([strPermitNo],7))-1) AS [IsGap After], IIf([IsGap After] And Not IsNull([DMaxNo]),"*",IIf([IsGap Before] And Not IsNull([DMinNo]),"*","")) AS Gap, tbl_HP_OC.strDescription AS OC_DESC, tbl_HP_OC.strNumberofIntervals AS Months, tbl_OtherLookup_Clerk.strCode AS Oper_Init, tbl_OtherLookup_Clerk.strDescription AS Oper_Name, tbl_OtherLookup_Clerk.strTypeCode AS Oper_Type
    FROM (tbl_HP_Applicant INNER JOIN (tbl_HP_Permit INNER JOIN tbl_HP_OC ON tbl_HP_Permit.lngOutcomeID = tbl_HP_OC.lngOutcomeID) ON tbl_HP_Applicant.lngApplicantID = tbl_HP_Permit.lngApplicantID) INNER JOIN tbl_OtherLookup_Clerk ON tbl_HP_Permit.lngOperatorID = tbl_OtherLookup_Clerk.lngID
    WHERE (((tbl_OtherLookup_Clerk.strTypeCode)="COP"));

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

    Re: Data Sheet Displays then Refreshes? (A2k (9.0.3821) SR-1)

    If there's no code behind the datasheet, why are you passing it an OpenArgs string? If I create a datasheet form, give it a load event and a current event and pop msgboxes in each event, I still don't see a requery. If I open it as a datasheet using DoCmd.OpenForm, then pop up a msgbox in the calling routine, I don't see the behavior you described, not even when I ass a line setting focus to a control on the calling form. Of course, I didn't even try for as braided together a query as you have behind your datasheet, but regardless, it shouldn't requery unless you tell it to or something changes. Do you have a timer event running in the background somewhere or is something polling you drive? Try building a simpler datasheet and see if you can reproduce the behavior on sonething like a couple of Northwind tables.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Sheet Displays then Refreshes? (A2k (9.0.3821) SR-1)

    Hi Charlotte

    I disabled all the code on the subform and just opening the subform duplicates my problem.

    It's the datasheet record source query that's causing the problem.

    If I remove the following code from the datasheet record source query in previous post, it eliminates the requery, works OK, strange Gap is what I'm trying to generate and it dispalys Gap on the initial display as well as the requery.

    DMin("strPermitNo","tbl_HP_Permit","strPermitNo>'" & [strPermitNo] & "'") AS DMinNo, DMax("strPermitNo","tbl_HP_Permit","strPermitNo<'" & [strPermitNo] & "'") AS DMaxNo, Val(Right(Nz(DMin("strPermitNo","tbl_HP_Permit","s trPermitNo>'" & [strPermitNo] & "'")),7))<>(Val(Right([strPermitNo],7))+1) AS [IsGap Before], Val(Right(Nz(DMax("strPermitNo","tbl_HP_Permit","s trPermitNo<'" & [strPermitNo] & "'")),7))<>(Val(Right([strPermitNo],7))-1) AS [IsGap After], IIf([IsGap After] And Not IsNull([DMaxNo]),"*",IIf([IsGap Before] And Not IsNull([DMinNo]),"*","")) AS Gap,

    Any thoughts

    John

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

    Re: Data Sheet Displays then Refreshes? (A2k (9.0.3821) SR-1)

    Create a funtion to calculate the Gap value and call it from the query, passing the current record's PermitNo as the argument. See if you get the same problem.
    Charlotte

Posting Permissions

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