2 Access Questions.

[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.

 

[ortaias]

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