Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

2 Access Questions.

Discussion in 'Information Technology' started by Rob Cooney, Jul 28, 2006.

  1. Rob Cooney

    Rob Cooney
    Expand Collapse
    Guest

    The first problem that I am having is this:
    I have a form in which once I search for a record it displays it. Then once
    I've entered some numbers for that record, within the form, I have a
    calculation built in to generate the numbers I want to see automatically.
    However, the query I have with this form runs from a table in which the data
    from the calculation on the form does not automatically update that field on
    the table.
    How do I update a table from a form so that the query based on that table
    will run with the new information? Is there a way to populate the specific
    record with that new data automatically after it runs the calculation?

    The second question is this:
    How do I get a query to show only the information from one record from a
    form? For instance, if a product has multiple records, what logic can you put
    on the query to where it only shows the records for one product after you
    have it displayed on a form?

    Thank you for your help.
     
  2. Loading...


  3. ortaias@hotmail.com

    ortaias@hotmail.com
    Expand Collapse
    Guest

    This may answer your questions: What this code does is display all
    pending projects. Pending projects are those that have a STATUS < 10.
    When the project becomes complete the STATUS => 10 and ME.REFRESH is
    used to update the screen to remove the now complete projects.

    The projects which have a STATUS <10 are shown is LIST7. As you scroll
    down the listbox, the underlying form is updated to display the current
    record. Changes made to the form do update the underlying table, only
    the due dates are calculated values and the underlying form and table
    are updated as expected..

    This code does still have some issues: "List7.Selected(1) = True" is
    causing a minor problem, which I hope to fix someday when I get a
    better understanding of what is happening. "Case 2 'This is run to
    refresh form" is empty (place holder) code (nothing happens and may
    prove to be unneccessary).
    -------------------------------------------------------------------------------------------------------------------
    Option Compare Database
    Public formopened As Boolean
    Public recordcnt01 As Integer
    Public recordcnt02 As Integer
    Public recordcnt03 As Integer
    Public casetest As Integer
    Rem --------------------------------
    Rem Pending4frm
    Rem Created June 6, 2006
    Rem Modified June 21, 2006
    Rem ---------------------------------


    Private Sub Form_Open(Cancel As Integer)
    Rem set initial parameters before opening form

    formopened = True
    Forms![pending4frm].Filter = "[status] <10"
    Forms![pending4frm].FilterOn = True
    Forms![pending4frm].OrderBy = "[projectnum]"
    Forms![pending4frm].AllowAdditions = False

    End Sub

    Private Sub Form_Activate()
    Rem This code runs every time the form becomes active.

    casetest = 0
    recordcnt01 = 0 'If 0 no record exists. If 1 then record still valid
    recordcnt02 = 0 'Total number of Records

    Rem -------------------------------------------
    Rem check to see if the current record is still valid
    recordcnt01 = DCount("[projectnum]", "[projectnumqry]", "[projectnum] =
    Forms![pending4frm]![Text1] And Forms![pending4frm]![status] < 10")
    recordcnt02 = DCount("[status]", "[projectnumqry]", "[status] < 10")
    [Text29] = recordcnt02

    If formopened And recordcnt01 > 0 Then casetest = 1 'Form initialized
    If Not formopened And recordcnt01 > 0 Then casetest = 2 'just need to
    refresh form
    If recordcnt01 = 0 Then casetest = 3 'Record deleted need to
    reinitialize form

    DoCmd.GoToControl "[list7]"
    Select Case casetest
    Case 1 'This is run when the form is opened
    DoCmd.GoToControl "[list7]"
    [List7].Selected(1) = True
    Case 2 'This is run to refresh form
    Case 3 'This is run when a record no longer has a status < 10
    DoCmd.GoToControl "[list7]"
    [List7].Selected(1) = True
    Me.Refresh
    Case Else 'Unexpected Error
    DoCmd.GoToControl "[List7]"
    List7.Selected(1) = True
    End Select

    Rem Set Values of various boxes
    Call setcontrols

    Rem Once the form has been opened this value is set to false to stop
    duplicate code from executing.
    formopened = False

    End Sub

    Private Sub List7_Click()
    Rem Pulls-up the summary information for the selected project
    DoCmd.GoToControl "[Text1]"
    DoCmd.FindRecord [List7]
    DoCmd.GoToControl "[List7]"
    Call setcontrols

    End Sub

    Private Sub Command11_Click()
    Rem Pulls-up the selected form for editing
    On Error GoTo Err_Command11_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim numrecordcount As Integer
    DoCmd.Save

    stDocName = "edit3frm"
    stLinkCriteria = "[projectnum]=" & Me![List7]
    DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

    Exit_Command11_Click:
    Exit Sub

    Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click

    End Sub

    Private Sub Text12_AfterUpdate()
    Me.Refresh
    End Sub

    Private Sub Text15_AfterUpdate()
    Me.Refresh
    End Sub

    Private Sub Text3_AfterUpdate()

    Me.Refresh

    End Sub

    Private Sub Command14_Click()
    On Error GoTo Err_Command14_Click

    DoCmd.Close

    Exit_Command14_Click:
    Exit Sub

    Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click

    End Sub
    Private Function setcontrols()

    [Text21].Visible = True
    [Label22].Visible = True
    [Text23].ForeColor = 255
    If Forms![pending4frm]![status] = 3 And Forms![pending4frm]![type] < 5
    And Now() > Forms![pending4frm]![commentenddate] Then
    Forms![pending4frm]![status] = 4: DoCmd.Save: Me.Refresh
    If Forms![pending4frm]![status] = 4 And Forms![pending4frm]![type] < 5
    And Now() <= Forms![pending4frm]![commentenddate] Then
    Forms![pending4frm]![status] = 3: DoCmd.Save: Me.Refresh

    Select Case Forms![pending4frm]![status]
    Case 1 'Received
    MSG1 = "MISTAKE IN DETERMINING THE DUE DATE"
    MSG2 = "PENDING4frm ERROR"
    [Label20].Caption = "Under Review"
    [Label22].Caption = "Due Date"
    [Label24].Caption = "Days Left"
    [Text19] = Forms![pending4frm]![receiveddate]
    [Text25] = "Under Review for Filing"
    If Forms![pending4frm]![type] = 1 Then [Text21] = DateAdd("d",
    14, [receiveddate])
    If Forms![pending4frm]![type] = 2 Then [Text21] = DateAdd("d",
    30, [receiveddate])
    If Forms![pending4frm]![type] > 2 Then MsgBox MSG1, vbOKOnly,
    MSG2
    [Text23] = DateDiff("d", Now(), [Text21])
    Case 2 'Incomplete
    [Label20].Caption = "Incomplete"
    [Label22].Visible = False
    [Text19] = Forms![pending4frm]![incompletedate]
    [Text21].Visible = False
    [Label24].Caption = "Elapsed Days"
    [Text25] = "Filed Incomplete"
    [Text23] = DateDiff("d", [receiveddate], Now())
    [Text23].ForeColor = 13451722
    Case 3 'Public Comment
    [Text19] = Forms![pending4frm]![completedate]
    If Forms![pending4frm]![type] = 1 Then [Text21] = DateAdd("d",
    60, [Text19])
    If Forms![pending4frm]![type] = 2 Then [Text21] = DateAdd("m",
    6, [Text19])
    If Forms![pending4frm]![type] > 2 Then [Text21] =
    [deadlinedate]
    [Label20].Caption = "Filed Complete"
    [Label22].Caption = "Due Date"
    [Label24].Caption = "Days Left"
    [Text23] = DateDiff("d", Now(), [Text21])
    [Text25] = "Public Comment"
    Case 4 'Post Public Comment, Decision Period
    [Text19] = Forms![pending4frm]![completedate]
    If Forms![pending4frm]![type] = 1 Then [Text21] = DateAdd("d",
    60, [Text19])
    If Forms![pending4frm]![type] = 2 Then [Text21] = DateAdd("m",
    6, [Text19])
    If Forms![pending4frm]![type] > 2 Then [Text21] =
    [deadlinedate]
    If Not IsNull([extensiondate].Value) Then [Text21] =
    [extensiondate]
    [Label20].Caption = "Filed Complete"
    [Label22].Caption = "Due Date"
    [Label24].Caption = "Days Left"
    [Text25] = "Decision Period"
    [Text23] = DateDiff("d", Now(), [Text21])
    Case 5 'Extension
    If Not IsNull([extensiondate].Value) Then [Text21] =
    [extensiondate]
    [Text23] = DateDiff("d", Now(), [Text21])
    [Label20].Caption = "Complete"
    [Label22].Caption = "Due Date"
    [Text19] = Forms![pending4frm]![completedate]
    [Label24].Caption = "Days Left"
    [Text25] = "Extension"
    Case 6 'Appeal
    [Label20].Caption = "Complete"
    [Label22].Caption = "Due Date"
    [Text19] = Forms![pending4frm]![completedate]
    [Text25] = "Appealed"
    Case 7 'Signature Pending
    If Forms![pending4frm]![type] = 1 Then [Text21] = DateAdd("d",
    60, [Text19])
    If Forms![pending4frm]![type] = 2 Then [Text21] = DateAdd("m",
    6, [Text19])
    If Forms![pending4frm]![type] > 2 Then [Text21] =
    [deadlinedate]
    If Not IsNull([extensiondate].Value) Then [Text21] =
    [extensiondate]
    [Label20].Caption = "Filed Complete"
    [Label22].Caption = "Due Date"
    [Text19] = Forms![pending4frm]![completedate]
    [Label24].Caption = "Days Left"
    [Text23] = DateDiff("d", Now(), [Text21])
    [Text25] = "Signature Pending"
    Case Else
    End Select

    End Function

    Private Sub Text5_AfterUpdate()
    Me.Refresh
    End Sub
     

Share This Page