Phucon-
Ah, so you're assigning the SQL to the Record Source. If the query returns no
records and it is not updatable (the DISTINCT will cause that), then the form
will go blank - that's just the way Access works. I recommend you try opening
the SQL as a Recordset in code first. If it returns 0 rows, tell the user and
quit. If it returns 1 or more rows, go ahead and assign it to the form's
Recordset or the SQL to the RecordSource.
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 11:59 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar