ReQuery

[kronar30]

I have a form 'frmEnterTime'
which has a subform 'EmpTimebyDate'
whos record source is 'UpdateTimeQuerybyDate'
order by 'JBT_Date DESC'
UpdateTimeQuerybyDate SQL is 'SELECT * FROM JBT_EmpTime ORDER BY JBT_Date DESC , JB_JobBidNo;'

After User adds a record I run the requery
qrycall = ChangeQueryDef("UpdateTimeQuerybyDate", "select * from [JBT_EmpTime] where JB_JobBidNo = '" & hldJobNum & "' order by JBT_Date DESC")
[TimebyDate].Form.Requery

The subform displays the new record added as long as the new recs job number is the same as the records originally displayed in the subform.
However if the new record has a diferent job number then the subform does not seem to update still showing the original job number records.

I thought that the requery would change the query for the record source and display the new results??

Thanks

 

[dhookom]

Do you have anything in the Link Master/Child properties of the subform control?

Is the subform control named "EmpTimebyDate" or "TimeByDate"?

Duane
Hook'D on Access
MS Access MVP

 

[kronar30]

subform is named EmpTimebtDate
there is nothing listed in the subforms master or child fields.
Am I suposed to requery the subform or the query.
(I don't find anything labeled TimebyDate.

 

[dhookom]

If you "don't find anything labeled TimebyDate" then why would your code state

   [TimebyDate].Form.Requery

Does your code compile? Do you have Option Explicit set in all of your code?

Duane
Hook'D on Access
MS Access MVP

 

[kronar30]

Have not compiled code.
Option Explicit is specified in the code.


Am I suposed to requery the subform or the query.
(I don't find anything labeled TimebyDate).

 

[dhookom]

You would requery the subform. What is your current code and what are your current issues?

Duane
Hook'D on Access
MS Access MVP

 

[kronar30]

That helped some.
Now the subform updates properly if I am adding a record for the job number currently displayed.
The msgbox shows the new job number '601' but the subform still displays the query for the original job number??? shouldn't the whole query be changed to the new qrycall?

MsgBox "hldJobNum = " & hldJobNum
qrycall = ChangeQueryDef("UpdateTimeQuerybyDate", "select * from [JBT_EmpTime] where JB_JobBidNo = '" & hldJobNum & "' order by JBT_Date DESC")
[TimebyDate].Form.Requery

 

[dhookom]

You stated "(I don't find anything labeled TimebyDate)" but your code is:

    [TimebyDate].Form.Requery

What is TimebyDate? Why are you requerying it? Why would your code compile if you don't have anything of the name?

Duane
Hook'D on Access
MS Access MVP

 

[kronar30]

Form is "frmEnterTimes"
it has a window called "TimebyDate"
whos source object is "EmpTimebyDate"
whos query is "UpdateTimeQuerybyDate"
wouldn't the requery change the query criteria to the new sql?

 

[dhookom]

By "window" I assume you mean subform control name TimeByDate.
Did you check the query to see if the new SQL is there?
I would also try

[CODE vba]
Me.[TimebyDate].Requery
[/code]


Duane
Hook'D on Access
MS Access MVP

 

[kronar30]

How would I check the query? Yes, the subform control name is "TimebyDate

 

[dhookom]

To check the query, go to the queries in the Navigation pane and find UpdateTimeQuerybyDate. Open it in design view to see if it matches your expectation.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

The subform displays the new record added as long as the new recs job number is the same as the records originally displayed in the subform.

there is nothing listed in the subforms master or child fields

Without the Subform's Master/Child Fields Options set, Access has no way of telling which Subform Records go with which Main Form Records. In point of fact, you have no Main Form/Subform scenario at all.

And if these were set, Access wouldn't allow you to enter a Subform Record that had a different Job Number than the Main Form Record. You cannot enter a Child Record that doesn't match the Current Parent Record.

Before doing anything else, you need to set the Master Fields/Child Fields Options.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[kronar30]

I checked the query SQL it did change to the new job number, however the subform is not displaying the new job's records( still shows old job's records).
I want to have 3 subform windows on this form showing the job times sorted by date, job times sorted by employee and job times sorted by job number.

 

[dhookom]

I would consider trying to reset the record source of the subform to the query.

   Me.SubFormCtlName.Form.RecordSource = "MyQueryName"

Duane
Hook'D on Access
MS Access MVP

 

[kronar30]

reseting thesubform seems to work. I took out the requery. But now , after new record added, the subform shows all jobs, not just the job entered.
here is my code:

qrycall = ChangeQueryDef("UpdateTimeQuerybyDate", "select * from [JBT_EmpTime] where JB_JobBidNo = '" & hldJobNum & "' order by JBT_Date DESC, JBT_EmpNum")
Me.TimebyDate.Form.RecordSource = "UpdateTimeQuerybyDate"

the query shows the last job number entered on the select * where, but the subform shows all jobs??
( when the form first loads the subform shows only the one job (as the query had been last modified to))

 

[dhookom]

Can you share all the code from the form? I thinks something else is going on.

Duane
Hook'D on Access
MS Access MVP

 

[kronar30]

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.

 

[dhookom]

I would open the debug window to find out the record source of the subform

?Forms.YourFormName.TimebyDate.Form.RecordSource

I wonder if this code is running

  qrycall = ChangeQueryDef("UpdateTimeQuery", "select * from [JBT_EmpTime] order by JBT_Date DESC, JB_JobBidNo")
Me.TimebyDate.Form.RecordSource = "UpdateTimeQuery"
[JBT_EmpTimeWindow].Form.Requery

Duane
Hook'D on Access
MS Access MVP

 

[kronar30]

MsgBox "rec source = " & Forms.frmEnterTimes.TimebyDate.Form.RecordSource
this code displayed "rec source = UpdateTimeQuerybyDate"

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


the query UpdateTimeQuerybyDate was changed to this:
SELECT *
FROM JBT_EmpTime
WHERE JB_JobBidNo = '604'
ORDER BY JBT_Date DESC , JBT_EmpNum


However both windows show the record added but display all job numbers, Time by Date should only show only the last entered job's records.
Is the Form.Requery somehow changing the display?