Here is some of the forms code:
Option Compare Database
Option Explicit
Dim db As Database
Dim temprec As Recordset
Dim JBrec As Recordset
Dim JBTrec As Recordset
Dim JBTdaterec As Recordset
Dim JBTjobrec As Recordset
Dim JBTemprec As Recordset
Dim EMrec As Recordset
Dim EMTrec As Recordset
Dim EMTrecNo As Integer
Dim strSQL As String
Dim hldDate As Date
Dim hldJobNum As Integer
Dim hldEmpNo As Integer
Dim hldName As String
Dim hldDOW As String
Dim hldWorkHrs As Integer
Dim rhldWorkHrs As Long
Dim realWorkHrs As Single
Dim RecFound As Boolean
Dim EditRecchk As Boolean
Const strQuote = """"
Const strDQuote = "" '""
Const ErrColor = True
Const MsgColor = False
Private fastrFormNames() As String 'Array of maintenance form names
Private Sub Form_Load()
'Fill temprecs window, initialize switches and hld fields
'MsgBox "Form_Load"
'This WORKS
'Stop
Call GetRecsEnterTimes("")
chkEDITrecs.Value = False
chkAddTempRec.Value = False
lsbEmpList.Visible = True
EnterDate.SetFocus
txtErrorText.Visible = False
hldJobNum = 0
hldDate = 0
hldEmpNo = 0
hldWorkHrs = 0
hldDOW = ""
End Sub
Public Sub GetRecsEnterTimes(wherestr As String)
'MsgBox "GetRecsEnterTimes"
'Stop 'display wherestr, what is 'old'?
'fill EmpTimeList subform with data from JBT_EmpTime
Dim selstr As String
Dim lstDatestr As String
Dim lstJobNumstr As String
Dim lstEmpNostr As String
Dim lstEmpNamestr As String
Dim lstWrkHrsstr As String
Dim lstDOWstr As String
Dim dspcnt As Integer
Dim old As Boolean
old = False
On Error GoTo Err_GetRecsEnterTimes
'Stop
Set db = CurrentDb()
Set JBrec = db.OpenRecordset("JB_Jobs", dbOpenDynaset)
Set JBTrec = db.OpenRecordset("JBT_EmpTime", dbOpenDynaset)
If old Then
selstr = strDQuote & "SELECT DISTINCTROW ET.JB_JobBidNo, ET.JBT_Date, ET.JBT_EmpNum, ET.JBT_WorkHrs, ET.JBT_DOW, "
selstr = selstr & "E.EM_EmpNumber, E.EM_LastName, E.EM_FirstName, E.EM_Wage "
selstr = selstr & "from EM_Emp AS E INNER JOIN JBT_EmpTime AS ET ON E.EM_EmpNumber = ET.JBT_EmpNum "
'selstr = selstr & "and ET.JBT_Date < 02/02/2007 "
If Len(wherestr) > 0 Then
selstr = selstr & wherestr & " " & "order by ET.JBT_Date DESC" & strDQuote
Else
selstr = selstr & "order by ET.JBT_Date DESC" & strDQuote
End If
Else
selstr = strDQuote & "SELECT DISTINCTROW ET.JB_JobBidNo, ET.JBT_Date, ET.JBT_EmpNum, ET.JBT_WorkHrs, ET.JBT_DOW, "
selstr = selstr & "E.EM_EmpNumber, E.EM_LastName, E.EM_FirstName, E.EM_Wage "
selstr = selstr & "from EM_Emp AS E INNER JOIN JBT_EmpTime AS ET ON E.EM_EmpNumber = ET.JBT_EmpNum "
selstr = selstr & "where (((ET.JBT_Date)< #1/1/2007#)) "
selstr = selstr & "order by ET.JBT_Date DESC, ET.JBT_EmpNum" & strDQuote
End If
'MsgBox "selstr = " & selstr
Exit_GetRecsEnterTimes:
Exit Sub
Err_GetRecsEnterTimes:
MsgBox Err.Description
Resume Exit_GetRecsEnterTimes
End Sub
Private Sub chkAddTempRec_Click()
'Sub to check if record already exists
'MsgBox "chkAddTempRec_Click"
'Stop
txtErrorText.Visible = False
Dim qrycall As Boolean
chkAddTempRec.Value = False
If IsNull(EnterDate) Then
Call SetErrorText("must enter valid Date to Add a record to the Job Times table", ErrColor)
EnterDate.SetFocus
ElseIf IsNull(EnterJobNum) Then
Call SetErrorText("must enter valid Job Number to Add a record to the Job Times table", ErrColor)
EnterJobNum.SetFocus
ElseIf IsNull(txtEmpNo) Then
Call SetErrorText("must enter valid Employee to Add a record to the Job Times table", ErrColor)
txtEmpNo.SetFocus
ElseIf IsNull(EnterWorkHrs) Then
Call SetErrorText("must enter valid Work Hours to Add a record to the Job Times table", ErrColor)
EnterWorkHrs.SetFocus
'Test for data change
ElseIf hldJobNum = EnterJobNum And hldDate = EnterDate And hldEmpNo = txtEmpNo And hldWorkHrs = EnterWorkHrs Then
Call SetErrorText("must change at least one field to Add a record to the Job Times table", ErrColor)
EnterDate.SetFocus
ElseIf frecordexists(EnterJobNum, EnterDate, txtEmpNo, EnterWorkHrs) Then
Call SetErrorText("Record already EXISTS; change at least one of the key fields and ADD record again", ErrColor)
EnterDate.SetFocus
Else
'Add New JBT record
MsgBox "1work hours entered are " & strQuote & EnterWorkHrs & strQuote
hldJobNum = EnterJobNum
hldDate = EnterDate
hldEmpNo = txtEmpNo
'hldWorkHrs = EnterWorkHrs
realWorkHrs = EnterWorkHrs
hldDOW = txtDOW
With JBTrec
.AddNew
!JB_JobBidNo = hldJobNum
!JBT_Date = hldDate
!JBT_EmpNum = hldEmpNo
'!JBT_WorkHrs = hldWorkHrs
'!JBT_WorkHrs = EnterWorkHrs
!JBT_WorkHrs = realWorkHrs
!JBT_DOW = hldDOW
.Update
End With
chkAddTempRec.Value = False
Call SetErrorText("Record ADDED to Job Times table", MsgColor)
EnterWorkHrs.Value = Null
lsbEmpList.Visible = True
MsgBox "hldJobNum = " & hldJobNum & " " & hldWorkHrs
MsgBox "realWorkHrs = " & realWorkHrs
'Query by job window (upper right)
'qrycall = ChangeQueryDef("UpdateTimeQuerybyDate", "select * from [JBT_EmpTime] where JB_JobBidNo = '" & hldJobNum & "' order by JBT_Date DESC")
qrycall = ChangeQueryDef("UpdateTimeQuerybyDate", "select * from [JBT_EmpTime] where JB_JobBidNo = '" & hldJobNum & "' order by JBT_Date DESC, JBT_EmpNum")
Me.TimebyDate.Form.RecordSource = "UpdateTimeQuerybyDate"
'[TimebyDate].Form.Requery
Stop
qrycall = ChangeQueryDef("UpdateTimeQuery", "select * from [JBT_EmpTime] order by JBT_Date DESC, JB_JobBidNo")
Me.TimebyDate.Form.RecordSource = "UpdateTimeQuery"
[JBT_EmpTimeWindow].Form.Requery
'qrycall = ChangeQueryDef("UpdateTimeQuerybyDate", "select * from [JBT_EmpTime] where JBT_Date = #" & hldDate & "# order by JBT_Date DESC")
'qrycall = ChangeQueryDef("UpdateTimeQuerybyDate", "select * from [JBT_EmpTime] where JBT_Date = #" & hldDate & "# and JB_JobBidNo = '" & hldJobNum & "' order by JBT_Date DESC")
'qrycall = ChangeQueryDef("UpdateTimeQuerybyDate", "select * from [JBT_EmpTime] where JB_JobBidNo = '" & hldJobNum & "' order by JBT_Date DESC")
'[TimebyDate].Form.Requery
'Me.SubFormCtlName.Form.RecordSource = "MyQueryName"
'Me.TimebyDate.Form.RecordSource = "UpdateTimeQuerybyDate"
'MsgBox "qrycall = " & qrycall
'qrycall = ChangeQueryDef("UpdateTimeQuerybyEmp", "select * from [JBT_EmpTime] where JBT_EmpNum = " & hldEmpNo & " order by JBT_Date DESC, JB_JobBidNo")
' qrycall = ChangeQueryDef("UpdateTimeQuerybyEmp", "select * from [JBT_EmpTime] where JBT_EmpNum = " & hldEmpNo & " and JB_JobBidNo = '" & hldJobNum & "' order by JBT_Date DESC")
' MsgBox "qrycall = " & qrycall
' [TimebyEmp].Form.Requery
' qrycall = ChangeQueryDef("UpdateTimeQuerybyJob", "select * from [JBT_EmpTime] where JB_JobBidNo = '" & hldJobNum & "' order by JBT_Date DESC, JBT_EmpNum")
' MsgBox "qrycall = " & qrycall
' [TimebyJob].Form.Requery ' this works
' [JBT_EmpTimeWindow].Requery ' this works
' MsgBox "ChangeQueryDefbyDate = " & qrycall
lsbEmpList.SetFocus
End If
Call chkAddTempRec_AfterUpdate
End Sub
Function ChangeQueryDef(strQuery As String, strSQL As String) As Boolean
'Function will modify a QueryDef where strQuery is name of query to modify, strSQL is new SQL of Query
'msgbox "ChangeQueryDefbyDate"
If strSQL = "" Then Exit Function
Dim qdfqry As QueryDef
MsgBox "strQuery = " & strQuery
MsgBox "strSQL = " & strSQL
Set qdfqry = CurrentDb.QueryDefs(strQuery)
qdfqry.SQL = strSQL
qdfqry.Close
ChangeQueryDef = True
End Function
Hope this can help. Form has 4 windows sorted by different fields. I'm tring to get the first one working at this time.
