Update query for filtered form

[Lateralusx777]

I am attempting to create an update query that allows me to input a value into a specific field for all records based on a filter that the user inputs. When the form launches, it asks for the starting tag number and then the last tag number. It then pulls up all records between x and y inclusive. I have a button on the form that runs my update query. The issue I am running into is getting my update query to only update the values inside of the filter. I cannot find anything online that is helpful. Any help would be greatly appreciated.

[ranman256]

no, you cant update from a filter, but you CAN update based on controls on the form.
(if that's how you filtered to begin with)

use the form controls for the where clause in the update.

[Lateralusx777]

The filter uses two values from a primary key in the table and pulls up all values in between. This will change every time the form is ran. Is there a way to specify the range without a specific control value?

[Micron]

Posting the sql of what you tried might provide more clues and more focused answers. Seems like it ought to be as simple as " Where myfileld Between value1 And value2..."
Not clear if the user is providing the PK values or something else or even how they're providing the values. I'm guessing Inputbox function.

[Lateralusx777]

The user provides the values.

The form that is opened -

SELECT ([Field 3]*[Field 1]/12) AS Feet, [Tags]![Unit Cost]*[Tags]![Quantity] AS [Value], Tags.[Date Found], Tags.[REC Date], Int(([Field 1]*[Field 3]/12)*(([Field 4]-[Field 2])*[Field 2]*10.68)) AS TubeWeight, Tags.[Tag Number], Tags.[Prior Tag Number], Tags.Status, Tags.[PO Number], Tags.[Item #], Tags.[Cert #], Tags.Weight, Tags.[Field 1], Tags.[Field 2], Tags.[Field 3], Tags.[Field 4], Tags.[Field 5], Tags.[Field 6], Tags.[Type of Material], Tags.Comments, Tags.[Printed Tags Comments], Tags.Printed, Tags.[Invoice #], Tags.[Invoice Date], Tags.[AP #], Tags.[AP Date], Tags.[Type of Inventory], Tags.Location, Tags.Date, Tags.[Work Order #], Tags.[WO# Tags Made], Tags.SlitWorkOrder, Tags.WhseLoc, Tags.CheckBy, Tags.PackID, Tags.[B/L#], Tags.CoilType, Tags.Quantity, Tags.[Unit Cost], Tags.UOM, Tags.Temper, Tags.[Item Master #], Tags.[BP Code], Tags.[Spare Flag], Tags.[Spare Flag 2]
FROM Tags
WHERE (((Tags.[Tag Number]) Between [Enter First Tag #] And [Enter Last Tag #]));

The update query -

UPDATE Tags SET Tags.[Work Order #] = [Forms]![View Tags Filtered]![Work Order #]
WHERE (((Tags.[Tag Number])=[Forms]![View Tags Filtered]![Tag Number]));

[Micron]

Tags.[Tag Number] is part of the form recordsource by the look of it so I'd either have 2 unbound textboxes (invisible) that receive the 2 values. When you run the append, reference both of those controls as WHERE Tags.[Tag Number] BETWEEN [Forms]![View Tags Filtered]![BeginTag] AND [Forms]![View Tags Filtered]![EndTag]. However, better to use Inputbox rather than simple parameter prompts - can't retrieve values from parameter prompts AFAIK.

In other words, I agree with Ranman - refer to form controls, not the control value as you mentioned. You might have one control that shows the tag field value for a record, but you need 2; 1 for beginning and 1 for ending value, so 2 hidden controls.

[Lateralusx777]

Thank you. I will try this and let you know!

[Lateralusx777]

I have not had any luck with this. I am fairly new to access and have not used input boxes before. Is there a way to set the value of two invisible text boxes to the first and last value in a specific field?

[Micron]

Should be doable - as long as you use a query, not a table, and can sort the records correctly and reliably. Access has First and Last functions, but they can produce unexpected results so I'd try to stay away from them. You'd need to sort the query and use Min and Max on some field that gives you access to the values you need.

Or post what you last tried and explain the outcome.
Or post a zipped db copy with instructions. As per post 2, if your query is filtered, there shouldn't be an issue updating only that subset of records. I don't think you've adequately explained what the problem is regarding the updating.

BTW, work order and tag number fields are number data type (not text)?
And since there are so many fields in the Select query, I'm wondering how many are not included. Might be easier to just write SELECT * ?