UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Selected User Ribbon Values As The Query, Access 2010    
 
   
Boki
post Jun 12 2019, 12:23 AM
Post#1



Posts: 2
Joined: 31-May 19



Hi,
I have a database that has a user ribbon that opens a report when users choose different criteria and then choose the layout of the report.
The first criterion is selected from the drop-down menu with the list of places => [rib_ID_Obj]
Another criterion is the time period that is defined through two text fields. => [ribBeginDate] and [ribEndDate]
These values from the user ribbon should be the criterion for the query that is the source of the selected report.

Is there any idea how to put these criteria into the query?

Attached File  QryRibbon.jpg ( 42.06K )Number of downloads: 1


the code that the full report is below

SELECT tbl_SAMPLING.ID_SAMPLING, tbl_SAMPLING.SAMPLING_DATE, tbl_SAMPLING.ID_OBJECT, tbl_OBJECT.OZNAKA, tbl_ OBJECT.NAZIV, tbl_SAMPLING.ID_PLEACE
FROM tbl_ OBJECT INNER JOIN (tbl_MJESTO INNER JOIN tbl_SAMPLING ON tbl_MJESTO.ID_MJESTO = tbl_SAMPLING.[ID_PLEACE]) ON tbl_ OBJECT.ID_ OBJECT = tbl_SAMPLING.[ID_OBJECT]
WHERE (((tbl_SAMPLING.SAMPLING_DATE)>=[ribBeginDate] And (tbl_SAMPLING.SAMPLING_DATE)<=[ribEndDate]) AND ((tbl_SAMPLING.ID_OBJECT)=[rib_ID_Obj]));


the code that runs the Ribbon from modRthe is below

Public globalRibbon As IRibbonUI
Dim ORS As DAO.Recordset '
Dim rptSelectedName As String
Dim ribBeginDate As Date
Dim ribEndDate As Date
Dim RS As DAO.Recordset ' ReportRecordSet

Public Sub onRibbonLoad(ByVal ribbon As IRibbonUI)
Set globalRibbon = ribbon
End Sub

Public Sub ribOpenForm(control As IRibbonControl)
DoCmd.OpenForm (control.Tag)
End Sub

Public Sub ControlEnabled(control As IRibbonControl, ByRef enabled)
Select Case control.ID
Case "MakeReport"
If rptSelectedName = "" Then
enabled = False
Else
enabled = True
End If

End Select
End Sub

' ####################################
'Get the item count for the Object dropdown
Public Sub GetObjCount(control As IRibbonControl, ByRef count)
Set ORS = GetObjData()
ORS.MoveLast
count = ORS.RecordCount
ORS.MoveFirst
End Sub
'Get the item IDs for the Object dropdown
Public Sub GetObjIDs(control As IRibbonControl, index As Integer, ByRef ID)
If Not ORS.EOF Then
ID = Nz(ORS!OZNAKA, "")
ORS.MoveNext
If ORS.EOF Then
ORS.Close
Set ORS = Nothing
End If
End If
End Sub
'Get the item Labels for the Object dropdown
Public Sub GetObjLabels(control As IRibbonControl, index As Integer, ByRef label)
If Not ORS.EOF Then
label = Nz(ORS!Name, "")
End If
End Sub
Private Function GetObjData() As DAO.Recordset
On Error GoTo SubError
Dim SQL As String
Dim ORS As DAO.Recordset
SQL = "SELECT OZNAKA, NAME FROM tbl_OBJECT, ORDER BY ID_OBJECT;"
Set ORS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
Set GetObjData = ORS
SubExit:
On Error Resume Next
Set ORS = Nothing
Exit Function
SubError:
MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
"An error occurred"
GoTo SubExit
End Function

' ####################################
'Get the item count for the report dropdown
Public Sub GetReportCount(control As IRibbonControl, ByRef count)
Set RS = GetReportData()
RS.MoveLast
count = RS.RecordCount
RS.MoveFirst
End Sub
'Get the item Labels for the report dropdown
Public Sub GetReportLabels(control As IRibbonControl, index As Integer, ByRef label)
If Not RS.EOF Then
label = Nz(RS!ReportDisplayName, "")
End If
End Sub
'Get the item IDs for the report dropdown
Public Sub GetReportIDs(control As IRibbonControl, index As Integer, ByRef ID)
If Not RS.EOF Then
ID = Nz(RS!ReportShortName, "")
RS.MoveNext
If RS.EOF Then
RS.Close
Set RS = Nothing
End If
End If
End Sub
Private Function GetReportData() As DAO.Recordset
On Error GoTo SubError
Dim SQL As String
Dim RS As DAO.Recordset
SQL = "SELECT ReportShortName, ReportDisplayName FROM Reports"
Set RS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
Set GetReportData = RS
SubExit:
On Error Resume Next
Set RS = Nothing
Exit Function
SubError:
MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
"An error occurred"
GoTo SubExit
End Function
' ####################################
Public Sub GetDefaultStartDate(control As IRibbonControl, ByRef text)
text = #1/1/2019#
ribBeginDate = text
End Sub
Public Sub GetDefaultEndDate(control As IRibbonControl, ByRef text)
text = Date
ribEndDate = text
End Sub
'Populate the report name for use later
Public Sub PickReport(control As IRibbonControl, selectedId As String, selectedIndex As Integer)

rptSelectedName = selectedId
globalRibbon.InvalidateControl ("MakeReport")

End Sub





Go to the top of the page
 
theDBguy
post Jun 12 2019, 07:45 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,602
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

In your ribbon call back try putting the values into TempVars and then use the TempVars in your queries.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Boki
post Jun 13 2019, 03:31 PM
Post#3



Posts: 2
Joined: 31-May 19



Thank you for your reply
But for the first time, I tried to use it and found a huge hole in my knowledge.
Having reviewed a large number of webinars and read a lot of texts for and against TempVars
I tried to incorporate that knowledge into my code

definition of object

Private Function GetObjData() As DAO.Recordset
On Error GoTo SubError
Dim rib_ID_Obj As Integer
Dim SQL As String
Dim ORS As DAO.Recordset
TempVars("rib_ID_Obj").Value = "LABEL_OBJ" 'My 1. TempVars
SQL = "SELECT LABEL_OBJ, NAME_OBJ FROM tbl_OBJECT WHERE (LABEL_OBJ = TempVars!rib_ID_Obj PASIV=False And HACCP=True) ORDER BY OZNAKA ASC"
Set ORS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
Set GetObjData = ORS

SubExit:
On Error Resume Next
Set ORS = Nothing
Exit Function
SubError:
MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
"An error occurred"
GoTo SubExit
End Function



and the definition of the time period

Public Sub GetDefaultStartDate(control As IRibbonControl, ByRef text)
Dim ribBeginDate As Date
TempVars("ribBeginDate").Value = "ribBeginDate"
text = #1/1/2019#
ribBeginDate = text
End Sub
Public Sub GetDefaultEndDate(control As IRibbonControl, ByRef text)
Dim ribEndDate As Date
TempVars("ribEndDate").Value = "ribEndDate"
text = Date
ribEndDate = text
End Sub


I installed it in the Qry
[attachment=90609:TempVars_Qry.jpg]


And I get the same notice again
[attachment=90611:TempVars...Error_91.jpg]

[attachment=90608:TempVars..._91_code.jpg]


Conclusion: I set TempVars in the wrong place in the code.
Is there any more experience with TempVars?
Make a note or link to expand my experience
This post has been edited by Boki: Jun 13 2019, 03:33 PM
Attached File(s)
Attached File  TempVars_Qry.jpg ( 68.86K )Number of downloads: 4
Attached File  TempVars_Qry_Error_91_code.jpg ( 34.73K )Number of downloads: 4
Attached File  TempVars_Qry_Error_91.jpg ( 16.58K )Number of downloads: 0
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th November 2019 - 10:51 PM