Search Form with Updateable Results

[ITwannabe]

Hi,

I built a search form based off of a query because I need the results to be able to return more than one record. For example, search by ID # and return several rows like so:

IDNameCase #Date IssuedLevel12345Mouse, Mickey2222204/11/2018First12345Mouse, Mickey3333304/01/2018Second12345Mouse, Mickey4444405/16/2018Third

This was easy enough, however I need the user to be able to make updates to the results as needed that will in turn update the source table. Is there a way to build the form that can search a table and return multiple records that are editable? I don't want the user to have to manually navigate to each record.

Hope this makes sense.

Thanks!

 

[arnelgp]

Im afraid not.

 

[June7]

You want multiple records returned as a single concatenated string of data and allow edit? Agree with arnelgp, no way.

Apply normal filter to form dataset and user edits each record. This means clicking into or tabbing to record.

 

[MajP]

I do not really understand your output but I assume it is supposed to be rows of records all with the same ID#.

If that is the case one easy way is to put an unbound combobox in the form header or footer with all the ids. Then you can filter your form.

Private Sub cmboID_AfterUpdate()
If Not IsNull(Me.cmboID) Then
    Me.Filter = "employeeID = " & Me.cmboID
    Me.FilterOn = True
  Else
    Me.FilterOn = False
  End If
End Sub

However, I do not know how you set up your search form and why the returned records are not editable. Most queries are editable, (only some types are not, based on how they are designed).

I might not be interpreting your question the same way as Arnelgp, but I would say of course you can build a form to do that. And you can do that with a form filter or modifying the recorsource of the form.

 

[ITwannabe]

Sorry everyone, I should've previewed before I posted. It wasn't supposed to be one continuous string but multiple rows. 1 Record for each.

 

[MajP]

So like I said, you should easily be able to make a form with the ability to filter records (either by changing the recordsource or applying a filter) and it be editable. So explain a little more how you would like this to work.

 

[Pat Hartman]

Access attempts to make all queries updateable. If the query is not updateable, it is because you have done something to prevent it. Are you aggregating data by using functions such as Min(), First(), etc?

Are you joining tables on incorrect fields?

And finally, have you considered that this might be a design flaw? If all rows of a set are supposed to have the same value (except for the foreign key), then the field doesn't belong in the child table, it belongs in the parent table where it will be stored ONCE for the set.

 

[MajP]

Access attempts to make all queries updateable. If the query is not updateable, it is because you have done something to prevent it. Are you aggregating data by using functions such as Min(), First(), etc?

Are you joining tables on incorrect fields?

Hold on a second. There a plenty of reasons a search would be based on an un-editable query. You make it sound as if that is a mistake. "Select top 5 records per group", then go edit them. That for sure does not mean the records returned cannot be used for loading an editable form. I am just saying with 100% certainty that I can search for records and then edit them, just need to understand what the OP is doing.

 

[ITwannabe]

Thanks for all of the responses. I apologize for being unclear. I'm not a coder so I don't always explain things the right way.

I created a query that I want to have users run (using a form) and then update the results if needed. The field being used to run the search is an ID field and there may be several records tied to that ID. This is why I'm using a query because I need all records with that ID to be returned.

Unfortunately the I'm not able to edit the results of the query and I'm not sure why. Unique Values are set to No, and it's only built on 2 tables. Here is the SQL:

SELECT tblAssociates.[Employee ID], tblAssociates.[Legal Name in Reporting Display Format], tblCorrectiveActions2018.[Case #], tblAssociates.Location, tblAssociates.[Manager - Level 01], tblCorrectiveActions2018.Program, tblAssociates.[Original Hire Date], tblCorrectiveActions2018.[Date Issued], tblCorrectiveActions2018.[CA Level], tblCorrectiveActions2018.[# of Weeks (PIP)], tblCorrectiveActions2018.[Reason(s)], tblCorrectiveActions2018.[Active or Inactive], tblCorrectiveActions2018.Retracted, tblCorrectiveActions2018.[Final Status], tblAssociates.[HR Business Partner], tblCorrectiveActions2018.[Rectraction Comments], tblCorrectiveActions2018.[HR Advisor]
FROM tblAssociates INNER JOIN tblCorrectiveActions2018 ON tblAssociates.[Employee ID] = tblCorrectiveActions2018.ID
WHERE (((tblAssociates.[Employee ID])=[Enter the Employee ID:]));

I can't seem to figure out why I can't edit the query results.

 

[MajP]

Here are some reasons

-It has a GROUP BY clause. A Totals query is always read-only.
-It has a TRANSFORM clause. A Crosstab query is always read-only.
-It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only.
-It contains a DISTINCT predicate. Set Unique Values to No in the query's Properties.
-It involves a UNION. Union queries are always read-only.
-It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.
-It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.
-The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.
-The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties.
-The query is based on another query that is read-only (stacked query.)
Your permissions are read-only (Access security.)
-The database is opened read-only, or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)
-The query calls a VBA function, but the database is not in a trusted location so the code cannot run. (See the yellow box at the top of this Access 2007 page.)
-The fields that the query outputs are Calcluated fields (Access 2010.)

Is there is a primary key or unique index on the JOINed fields?

 

[June7]

You are linking on [Employee ID] and tblCorrectiveActions2018.ID. Is tblCorrectiveActions2018.ID a foreign key field that holds the [Employee ID] or is it an autonumber PK for tblCorrectiveActions2018?

Why does table have a year in its name? Should not have a separate table for each year.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Better would be CaseNum or Case_Num.

Why don't you use form/subform arrangement?

 

[Pat Hartman]

Hold on a second.

Your example aggregates data. I didn't mention ALL the things that could make a query not updateable, only the most common. You listed a bunch more.

Except for the remarks about object names including data (a table for each year for example) and the poor naming standards, the query looks fine.

That leaves the join as the cause of the problem. When you join two tables, the join field from one table must be the primary key. Otherwise, Access cannot determine the cardinality of the relationship and may not allow updates. Is [Employee ID] the primary key (or a unique index) for tblAssociates?

A table for each year is how you would design a spreadsheet, not how you would design a relational database.