?
Solved

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

Posted on 2014-12-02
2
Medium Priority
?
133 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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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