ACCESS FEED: Re: [MS_AccessPros] What causes the value of a control missing

Kamis, 27 Oktober 2011

Re: [MS_AccessPros] What causes the value of a control missing

Thank you Mr. Viescas, and yes, the form has a Record Source, and the txtQtrDate value is set from the Form Load procedure as shown below.

Private Sub Form_Load()
On Error GoTo ErrorHandler

With Me
!cmdExit.SetFocus
!txtQtrDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 0) 'last day of the previous quater
End With

Me.RecordSource = vbNullString

ExitProcedure:
Exit Sub

ErrorHandler:
MsgBox Err.Description & vbCrLf & Err.Number
Resume ExitProcedure
End Sub

And here is partial SQL code or query of the cmdRun_Click procedure.

Private Sub cmdRun_Click()
On Error GoTo ErrorHandler

Dim strSelect As String
Dim strJoin As String
Dim strFrom As String
Dim strWhere As String
Dim strOrderBy As String
Dim strAssets As String
Dim intCtr as Integer
Dim strDlCtrl1 As String
Dim strDlCtrl2 As String
Dim strDlCtrl3 As String
Dim strDlCtrl4 As String
Dim strDlCtrl5 As String
Dim strDlCtrl6 As String
Dim strDlCtrl7 As String
Dim strDlCtrl8 As String
Dim strDlCtrl9 As String
Dim strDlCtrl10 As String
Dim strDlCtrl11 As String
Dim strDlCtrl12 As String

strFrom = " From "
strJoin = " And "
strWhere = " Where "
strOrderBy = " Order By "

Const EorG = " F.hkce270>= 1000000"
Const Lt = " F.hkce270< 1000000"

strSelect = "SELECT DISTINCT T.id AS T_ID, T.nm AS T_Name, T.city AS TCity, T.state AS T_State, " & _
"F.dt AS T_AsOfDate, F.Asset AS T_Assets, T.sid AS SubID, T.snm AS SubName, T.scity AS SubCity, T.s_state AS SubState, " & _
"A1.a_cd, A1.desc AS Activity_Desc"

strFrom = strFrom & "((dbo_cuv_act AS A1 RIGHT JOIN dbo_cuv_att AS T ON A1.act_cd = T.act_prim_cd) LEFT JOIN dbo_CUV_ENTITY AS E ON T.T_entity= E.S_entity) LEFT JOIN dbo_cuv_f01 AS F ON T.id = F.ID "

strWhere = strWhere & "Not Exists (SELECT ActCode FROM tblNonSubActCd AS A2 WHERE A2.ActCode = A1.a_cd)" & _
strJoin & "T.T_dist = 1" & strJoin & "T.dt_end = 99991231" & _
strJoin & "F.dt = " & Format(Me.txtQtrDate, "yyyymmdd") & strJoin & "E.S_entity Not In ('NIA','ARB','ABG','AIG','EII','ERB','EDB','PTB')"

Select Case fraOpts
Case 1
strAssets = EorG
strRecSr1 = strSelect & strFrom & strWhere & strJoin & strAssets & strOrderBy & "T.nm;"
'Debug.Print strRecSr1
Me.RecordSource = strRecSr1
Case 2
strAssets = Lt
strRecSr2 = strSelect & strFrom & strWhere & strJoin & strAssets & strOrderBy & "T.nm;"
'Debug.Print strRecSr2
Me.RecordSource = strRecSr2
End Select

Dim rsClone As Recordset
Set rsClone = Me.RecordsetClone
With rsClone
.MoveLast
.MoveFirst
If rsClone.RecordCount > 0 Then
txtTotal = rsClone.RecordCount
lblTotal.Visible = True
txtTotal.Visible = True
cmdExprt.Enabled = True
cmdClear.Enabled = True
cmdClear.SetFocus
cmdRun.Enabled = False
cmdExprt.Enabled = True
End If
End With
rsClone.Close

'more code here...

End If

ExitProcedure:
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 3021
MsgBox "No records matched the specified Quarter End Date.", vbInformation, "Query Result"
Case Else
MsgBox Err.Description & vbCrLf & Err.Number
End Select
Resume ExitProcedure
Resume
End Sub

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Phucon-
>
> Is the form bound (has a Record Source)?
>
> What code do you use to set txtQtrDate (the entire procedure, please)?
>
> What code runs the query? Is it an Action query? What is the SQL of the query?
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc
> Sent: Thursday, October 27, 2011 8:26 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] What causes the value of a control missing
>
> I have a form that runs a query. The form has 2 controls named txtCurrentDate,
> txtQtrDate. The date field in the query's criteria is based on the form's
> txtQtrDate control' value,
> its value is like this : Me!txtQtrDate = DateSerial(Year(Me!txtQtrDate),
> Int((Month(Me!txtQtrDate) - 3) / 3) * 3 + 1, 0). The txtCurrentDate'value is set
> to =Date().
> The query is like this: Select * from tblAssets as A Where A.dt = Me.txtQtrDate
> Order by A.dt;
>
> Everything is working fine until no record was matched (err.Number 3021, No
> current record), when this happens, the txtCurrentDate on the form became blank.
> The txtQtrDate stays intact.
> My code, the RunQuery procedure does not set it to Null or "", what causes the
> date missing??
>
> Phucon
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>