R
ryguy7272
Hello! I am relatively new to Access development work. I have been doing
excel programming for a few yeas now, and understand that tool pretty well,
but using Access is a completely different experience. Anyway, I am
currently using a sample that I found at this site:
http://www.fontstuff.com/access/acctut18.htm
The file at the bottom of the page is: AccessAndSQL5_2000.zip
I mostly got it working for my particular scenario, thanks to some great
people on this DG. I still haven’t figured out two things.
1) How can I choose the columns that displayed in the query?
I am using this piece of code now:
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"
I know the SELECT tblTFI.* part selects ALL columns form the table. I have
about 50 columns in the table, but want to display only about 12 or so. I
would really like to display certain columns, such as ‘Project Name’,
‘Broker’, ‘Project’, ‘Sub-Project’, ‘Status’, and a few others, but not ALL
columns.
2) How can I modify the code to ask me for a date range, such as a
beginning date and an ending date? Right now I build a query based on one of
three criteria: Broker, Product, or Status. I suspect I will have to add
something on the form, like a check box, that causes the date-range question
pop up after the query based on the three criteria is entered, and just
before the query is actually built. Else, if the box is not checked, just
return everything that matches the query criteria.
The code that I am running right now is listed below:
Option Compare Database
Option Explicit
' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM tblStaff"
cat.Views.Append "SelQuery", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen
Then
DoCmd.Close acQuery, "SelQuery"
End If
' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = Right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If
' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = Right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If
' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = Right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("SelQuery").Command
cmd.CommandText = strSQL
Set cat.Views("SelQuery").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "SelQuery"
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.DESCRIPTION _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
The SQL in the query looks like this:
SELECT tblTFI.*
FROM tblTFI
WHERE tblTFI.[Broker] Like '*'AND tblTFI.[Prod] Like '*'AND tblTFI.[Status]
Like '*';
If someone could please help me out with this I’d reapply appreciate it!! I
am looking forward to learning more about Access, but I’m a little scared of
the tool too!
Regards,
Ryan---
excel programming for a few yeas now, and understand that tool pretty well,
but using Access is a completely different experience. Anyway, I am
currently using a sample that I found at this site:
http://www.fontstuff.com/access/acctut18.htm
The file at the bottom of the page is: AccessAndSQL5_2000.zip
I mostly got it working for my particular scenario, thanks to some great
people on this DG. I still haven’t figured out two things.
1) How can I choose the columns that displayed in the query?
I am using this piece of code now:
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"
I know the SELECT tblTFI.* part selects ALL columns form the table. I have
about 50 columns in the table, but want to display only about 12 or so. I
would really like to display certain columns, such as ‘Project Name’,
‘Broker’, ‘Project’, ‘Sub-Project’, ‘Status’, and a few others, but not ALL
columns.
2) How can I modify the code to ask me for a date range, such as a
beginning date and an ending date? Right now I build a query based on one of
three criteria: Broker, Product, or Status. I suspect I will have to add
something on the form, like a check box, that causes the date-range question
pop up after the query based on the three criteria is entered, and just
before the query is actually built. Else, if the box is not checked, just
return everything that matches the query criteria.
The code that I am running right now is listed below:
Option Compare Database
Option Explicit
' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM tblStaff"
cat.Views.Append "SelQuery", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen
Then
DoCmd.Close acQuery, "SelQuery"
End If
' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = Right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If
' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = Right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If
' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = Right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("SelQuery").Command
cmd.CommandText = strSQL
Set cat.Views("SelQuery").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "SelQuery"
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.DESCRIPTION _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
The SQL in the query looks like this:
SELECT tblTFI.*
FROM tblTFI
WHERE tblTFI.[Broker] Like '*'AND tblTFI.[Prod] Like '*'AND tblTFI.[Status]
Like '*';
If someone could please help me out with this I’d reapply appreciate it!! I
am looking forward to learning more about Access, but I’m a little scared of
the tool too!
Regards,
Ryan---