Question

Locked

access database autoupdate query

By oneunder ·
here is the problem>>i am trying to update a field where in my sql staement i have changed from automatically grabbing data and it gives a result but now i changed it to grab the info from a different location >>it works but only if i update the field manually. the colums are two dates sometimes they are different dates its a date of occurence and a date of report > i used to have another field that gave the day of week based on the date of occurence but now changed it to pull the info to auto input the day of week from the report day instead>but although it works i have 100 thousand entries that need to be updated and the only way i have found is to redo the dates then it updates but that many entries are to time consuming.
BELOW IS THE CODE I HAVE

Private Sub Command98_Click()
On Error GoTo Err_Command98_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command98_Click:
Exit Sub

Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click

End Sub
Private Sub Command99_Click()
On Error GoTo Err_Command99_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command99_Click:
Exit Sub

Err_Command99_Click:
MsgBox Err.Description
Resume Exit_Command99_Click

End Sub
Private Sub Command100_Click()
On Error GoTo Err_Command100_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command100_Click:
Exit Sub

Err_Command100_Click:
MsgBox Err.Description
Resume Exit_Command100_Click

End Sub
Private Sub Command101_Click()
On Error GoTo Err_Command101_Click


DoCmd.GoToRecord , , acFirst

Exit_Command101_Click:
Exit Sub

Err_Command101_Click:
MsgBox Err.Description
Resume Exit_Command101_Click

End Sub
Private Sub Command102_Click()
On Error GoTo Err_Command102_Click


DoCmd.GoToRecord , , acLast

Exit_Command102_Click:
Exit Sub

Err_Command102_Click:
MsgBox Err.Description
Resume Exit_Command102_Click

End Sub
Private Sub Command103_Click()
On Error GoTo Err_Command103_Click


DoCmd.GoToRecord , , acPrevious

Exit_Command103_Click:
Exit Sub

Err_Command103_Click:
MsgBox Err.Description
Resume Exit_Command103_Click

End Sub
Private Sub Command104_Click()
DAY_OF_WEEK = DatePart("W", [DATE_OF_REPORT])
On Error GoTo Err_Command104_Click


DoCmd.GoToRecord , , acNext

Exit_Command104_Click:
Exit Sub

Err_Command104_Click:
MsgBox Err.Description
Resume Exit_Command104_Click

End Sub
Private Sub Command105_Click()
On Error GoTo Err_Command105_Click


DoCmd.Close

Exit_Command105_Click:
Exit Sub

Err_Command105_Click:
MsgBox Err.Description
Resume Exit_Command105_Click

End Sub
Private Sub Command106_Click()
On Error GoTo Err_Command106_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "DATE TO DIALOG"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command106_Click:
Exit Sub

Err_Command106_Click:
MsgBox Err.Description
Resume Exit_Command106_Click

End Sub
Private Sub Command107_Click()
On Error GoTo Err_Command107_Click

Dim stDocName As String

stDocName = "WEEKLY GRAND LARCENY 7"
DoCmd.OpenReport stDocName, acPreview

Exit_Command107_Click:
Exit Sub

Err_Command107_Click:
MsgBox Err.Description
Resume Exit_Command107_Click

End Sub
Private Sub Command108_Click()
On Error GoTo Err_Command108_Click

Dim stDocName As String

stDocName = "28 DAY GRAND LARCENY"
DoCmd.OpenReport stDocName, acPreview

Exit_Command108_Click:
Exit Sub

Err_Command108_Click:
MsgBox Err.Description
Resume Exit_Command108_Click

End Sub

Private Sub DATE_OF_REPORT_BeforeUpdate(Cancel As Integer)
DAY_OF_WEEK = DatePart("W", [DATE_OF_REPORT])
End Sub

Private Sub Time_BeforeUpdate(Cancel As Integer)
If Me.TIME.Value > #11:59:00 PM# Or Me.TIME.Value < #8:00:00 AM# Then
Me.TOUR.Value = "1"
ElseIf Me.TIME.Value < #4:00:00 PM# Then
Me.TOUR.Value = "2"
ElseIf Me.TIME.Value > #3:59:00 PM# Then
Me.TOUR.Value = "3"
End If
End Sub

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Form or Query?

by LarryD4 In reply to access database autoupdat ...

It looks like your doing of all of this via a form and I have a requery entry for the OnCurrent event to update all my queried drop downs..

Private Sub Form_Current()
Me.dept.Requery
Me.supervisor.Requery
Me.funding.Requery
Me.chambers.Requery
End Sub

Back to Web Development Forum
1 total post (Page 1 of 1)  

Related Forums