Solved

If I hard code my query...why do I need to built in my DB??

Posted on 2014-12-02
2
131 Views
Last Modified: 2014-12-02
I have my query hard coded..

Public Sub querydef()
Dim db As Database, qdf As querydef, strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Allot_Q")


Analyst = ""
For Each Item In Forms!AllotSearch_frm!lstEmployeeID.ItemsSelected
    If Analyst = "" Then
        Analyst = "'" & Forms!AllotSearch_frm!lstEmployeeID.ItemData(Item) & "'"
    Else:
        Analyst = Analyst & " , '" & Forms!AllotSearch_frm!lstEmployeeID.ItemData(Item) & "'"
    End If
Next
Analyst = "IN(" & Analyst & ")"


Org = ""
For Each Item In Forms!AllotSearch_frm!lstOrg.ItemsSelected
    If Org = "" Then
        Org = "'" & Forms!AllotSearch_frm!lstOrg.ItemData(Item) & "'"
    Else:
        Org = Org & " , '" & Forms!AllotSearch_frm!lstOrg.ItemData(Item) & "'"
    End If
Next
Org = "IN(" & Org & ")"

CostCenter = ""
For Each Item In Forms!AllotSearch_frm!lstCostCenter.ItemsSelected
    If CostCenter = "" Then
        CostCenter = "'" & Forms!AllotSearch_frm!lstCostCenter.ItemData(Item) & "'"
    Else:
       CostCenter = CostCenter & " , '" & Forms!AllotSearch_frm!lstCostCenter.ItemData(Item) & "'"
    End If
Next
CostCenter = "IN(" & CostCenter & ")"

Fund = ""
For Each Item In Forms!AllotSearch_frm!lstFund.ItemsSelected
    If Fund = "" Then
        Fund = "'" & Forms!AllotSearch_frm!lstFund.ItemData(Item) & "'"
    Else:
        Fund = Fund & " , '" & Forms!AllotSearch_frm!lstFund.ItemData(Item) & "'"
    End If
Next
Fund = "IN(" & Fund & ")"

PEC = ""
For Each Item In Forms!AllotSearch_frm!lstPEC.ItemsSelected
    If PEC = "" Then
        PEC = "'" & Forms!AllotSearch_frm!lstPEC.ItemData(Item) & "'"
    Else:
        PEC = PEC & " , '" & Forms!AllotSearch_frm!lstPEC.ItemData(Item) & "'"
    End If
Next
PEC = "IN(" & PEC & ")"


strSQL = "SELECT Distinct Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Org Name:], Final_Table.CostCen, Final_Table.[Fund:], Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.[BC1Change], Final_Table.[TotalBC1], Final_Table.[BC2Change], Final_Table.[TotalBC2], Final_Table.[BC3Change], Final_Table.[TotalBC3], Final_Table.[BC4Change], Final_Table.[TotalBC4]"
strSQL = strSQL & "FROM Final_Table LEFT JOIN dbo_tblOrgLook_master ON (Final_Table.CostCen = dbo_tblOrgLook_master.CostCenter) AND (Final_Table.PEC = dbo_tblOrgLook_master.PEC) WHERE dbo_tblOrgLook_master.Analyst " & Analyst & " AND dbo_tblOrgLook_master.Org " & Org & " AND dbo_tblOrgLook_master.CostCenter " & CostCenter & " AND dbo_tblOrgLook_master.Fund " & Fund & " AND dbo_tblOrgLook_master.PEC " & PEC

qdf.SQL = strSQL

End Sub


Ad I have it in my DB..is this where I am having my problem??  I have some fields that I need user input and it will not allow User input.
0
Comment
Question by:Jass Saini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40477439
The particular query the form is based on is non-updateable.

Recordsets are updateable if
a. based on a single table
b. a one to one join between tables
c. do not include any aggregation (grouping)
d. All ODBC linked tables have primary keys defined

In your case the sql query includes the DISTINCT keyword, which means it may otherwise contain duplicates. That being the case there is a possibility that the database engine would not be able to decide which single record to update with your changes.

Try Googling "recordset is not updatable" for more details and examples
0
 

Author Closing Comment

by:Jass Saini
ID: 40477536
Thank You
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question