Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    10

    Unanswered: Using a form to dynamically query and edit a record

    Hey guys, so I am working on this MS access DB for the first time. Am getting familiar with my way around it.

    So here is the problem. There is an existing form that takes in the user input values and then dynamiclly constructs the search string. The issue is the form output from this is not correct. However, if I take that dynamically created string and then query it directly against the DB, I get correct results. Are their any constraints in the query construction (maybe not compatible) that might not work? The form query looks like below. "StrWhere" is the dynamically constructed condition. So this string seems to return the correct values when directly used in a query against the DB ...

    Me.Browse.Form.RecordSource = "SELECT 'Edit' AS Edit, Table.Unique_ID, Table.Issue_Type, Table.Due_Date, * FROM Table"
    Me.Browse.Form.Filter = strWhere
    Me.Browse.Form.FilterOn = True

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. The SQL statement is oddly built:
    a) SELECT 'Edit' AS Edit: Why do you include a litteral in the query to display it on a form?
    b) * FROM Table: Why do you specify some columns (Table.Unique_ID, Table.Issue_Type, Table.Due_Date) then use * that returns all columns anyway.

    2."The issue is the form output from this is not correct." What's wrong? Does this generates any error (supply the error code if it is), or does the form display something else than what is expected?

    3. Are the controls of the form correctly bound to the columns returned by the query?

    4. Does it work if you do not apply the filter?

    5. Why don't you use a permanent query as the RecordSource of the form as there is no WHERE clause in the dynamic query you build (the query is invariant)?
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    10

    Thanks Sinndho

    1. I felt it was a bit awkwardly built as well. But I did not create this DB. It's an existing DB that I need to fix! I figured I should try to use as much as possible of the existing code ..

    2. It returns something that is not expected. The returned records do not line up with the conditions selected in the form.

    3. I believe so, but not 100% positive

    4. It does work if you apply the filter. I believe it returns all the records ..

    5. I am not really familiar with access yet to answer that question! Could you give me more guidance on that?

    Sorry for being asked to be spoonfed, but given the time contraint, I do not have a lot of choice!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    1.You can create a permanent query with this SQL statement (let's call it "qry_SelectFromTable"):
    Code:
    "SELECT Table.*, 'Edit' AS Edit FROM Table;"
    2. You can now open the subform "Browse"in Design mode, open the Properties window, select the Data Tab and type the name of the query (qry_SelectFromTable) into the RecordSource property line. You don't need this line anymore:
    Code:
    Me.Browse.Form.RecordSource = "SELECT 'Edit' AS Edit, Table.Unique_ID, Table.Issue_Type, Table.Due_Date, * FROM Table"
    3. If you need to bind/unbind the subform you can use:
    a) Binding:
    Code:
    Me.Browse.Form.RecordSource = "qry_SelectFromTable"
    b) Unbinding:
    Code:
    Me.Browse.Form.RecordSource = ""
    3. Obviously, the problem comes from the filter. Can you post its contents and how it is built?
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    10

    Thanks Sinndho

    The filter is created as follows:

    strWhere = "1=1"

    'If Status
    If Nz(Me.cboStatus.Value) <> "" Then
    'Create Predicate
    strWhere = strWhere & " AND " & "Table.[Status]=""" & cboStatus.Value & """"
    End If

    'If Issue Type
    If Nz(Me.cboIssueTyp) <> "" Then
    'Create Predicate
    strWhere = strWhere & " AND " & "Table.[Issue_Type] =""" & cboIssueTyp.Value & """"
    End If

    'If Officer
    If Nz(Me.cboOfficer) <> "" Then
    'Create Predicate
    strWhere = strWhere & " AND " & "Table.[Officer] =""" & cboOfficer.Value & """"
    End If

    'If Corrective Action Lead
    If Nz(Me.cboCorActLead) <> "" Then
    'Create Predicate
    strWhere = strWhere & " AND " & "Table.[Corrective_Action_Lead] =""" & cboCorActLead.Value & """"
    End If

    'If SF/MF
    If Nz(Me.cboSFMF) <> "" Then
    'Create Predicate
    strWhere = strWhere & " AND " & "Table.[SF_MF] I=""" & cboSFMF.Value & """"
    End If

    'If MRO Type
    If Nz(Me.cboMRO) <> "" Then
    'Create Predicate
    strWhere = strWhere & " AND " & "Table.[MRO_Type] =""" & cboMRO.Value & """"
    End If


    So the filter is created dynamically based on the selection of the user from the form. All the inputs are strings.

    Any ideas? Thanks so much!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From MS Knowledge Base (http://support.microsoft.com/kb/227053/EN-US):
    When you try to edit data in a form based on an ActiveX Data Objects (ADO) recordset, the form is read-only. This occurs even if you can successfully edit the recordset directly with ADO.
    They provide a workaround but I never tried it.
    Have a nice day!

  7. #7
    Join Date
    Sep 2011
    Posts
    10
    I am not sure I follow what you are saying. Are the dynamic values (the user inputs) that are read in from the form read-only? and this causes an issue with the filter?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Oops ! I had two answers ready and posted the wrong one, sorry!

    The way you build the filter seems correct. However, can you check what strWhere contains at the end of the string building process?

    There could have been an issue if some cboxxx.Value was Null but you already cared for that. Are all columns used into the filter of type Text or Memo?
    Have a nice day!

  9. #9
    Join Date
    Sep 2011
    Posts
    10
    I did check the content of the filter just before it's passed to the form.browse. It seems to be correct. I also copied and pasted that filter as a criteria for a separate query (to check for the returned values). Those values are correct!

    So only when the filter is passed onto the browse.form etc. are the returned values incorrect. This is really puzzling to me.

    The columns that are being passed as an input are of the type "text". Does that help?

    Thanks!

  10. #10
    Join Date
    Sep 2011
    Posts
    10

    Some more information

    I was digging around the table. So there is a column in the table that's titled "Unique_id". That's not the key, so there are duplicates.

    This incorrect data that's returned only happens for records that have the same "Unique_id" column. I do not see this particular referenced at all in the filter/query. Once I changed the unique_id column to another different value, the search and edit form returns the correct values.

    Note that this particular column is not even entered in the search form. So I am not sure how this is influencing the search.

    I guess some searching is being done internally using this column? Is there a way to figure out where that might be happening?

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •