Edit Records Selected by Query

johndohnal

Registered User.
Local time
Today, 17:21
Joined
Sep 12, 2013
Messages
41
I have a query that selects records based on certain flags in each record. I can view the record in datasheet view, but I need to be able to edit the records selected by the query using another form. Is there any way to automate this process?
Thanks,
John
 
The normal record source for the form is the table which I need to edit. The records returned by the query are the records in the table which I would like to edit using a form. I tried switching the record source to the query and just got a blank form.
Thanks,
John
 
You can .Filter the form by the same criteria as your query uses, without changing the RecordSource. That will be much safer/more foolproof.
 
Thanks, David R! This was exactly what I was hoping for. But can you be a little more explicit as to where I use the .Filter argument?
Thanks,
John D
 
Sure, but I need you to be a little clearer about what " I can view the record in datasheet view, but I need to be able to edit the records selected by the query using another form" means. Right now I am assuming that you have some FormDatasheet that actually shows your data for editing, and then maybe a button that runs/defines the parameters for your query? Or are you currently running the query directly from the left-hand Navigation pane? (if so, stop, because it's dangerous to let your users have access to that Nav pane).

Also is the query the same every time, or does the user have to enter some parameters (the difference between "Show me all records for the last 30 days" and "Show me the Selected type of Vehicle")? Post the VBA or Macro that runs the query.

To get you started, look at the SQL view of your query. Near the end there will be a bit that looks something like:
Code:
[COLOR="Red"]WHERE[/COLOR] ((([Priority List].Activity) Like "Inspection 30 Day*"))
ORDER BY blah blah blah
Everything on that WHERE line, after the word where, is going to be your filter (probably). Post that too.
 
Ok, so to give a little background, we have supplies that come in to our supply room. Once they are received, they need to be QA inspected as well. The query selects items that have been received, but not yet QA'd. I'd like to be able to open a form for the QA inspection of only those items selected by the query. The information such as item name, manufacturer, supplier, etc. is stored in a table and the itemID is the link between the items table, and receipt table, and the QA release table. But there is other information on the release form which needs to be saved after the items have been inspected. So I want the query to select items that haven't been inspected, open the release form showing only those items, enter the inspection information, and save it to the release table. And here's the blah, blah, blah:
WHERE (((tblReceipt.Reconciled)=False) AND ((tblReceipt.ReceiptConditionSat)=True) AND ((tblReceipt.QSUNotified)=True));
Thanks for your time and effort!
John D
 
This pseudocode assumes that you have a switchboard that your users will start your forms from. You should be able to use your existing Release form, just with a different filter.

On your switchboard, build a new button. Call it buttonQA if you like.
Code:
Private Sub buttonQA_Click()
      If CurrentProject.AllForms("formRelease").IsLoaded = False Then DoCmd.OpenForm "formRelease"
      Forms!formRelease.Filter = "(((tblReceipt.Reconciled)=False) AND " & _
                                 "((tblReceipt.ReceiptConditionSat)=True) AND " & _
                                 "((tblReceipt.QSUNotified)=True))"
      Forms!formRelease.FilterOn = True
End Sub
 
Thanks David R! I'll try it, but it intuitively looks like just what I needed.

Best,
John D
 
Sorry David R, I spoke too soon. I entered the code you provided, but now I get asked for the parameters for Reconciled, ReceiptConditionSat, and QSUNotified. If I enter the parameters, the Release form opens, but is blank. I tried changing the true/false conditions to -1 and 0, but it still didn't work. Any suggestions?

Thanks,
John D
 
That usually means that formRelease doesn't have tblReceipt in its recordsource/query. Does that sound accurate?

Post the SQL behind the old query that works. If formRelease isn't based directly on tblReceipt, post the SQL of that query too. Use
Code:
 tags for readability please.
 
Hi David,

Please forgive my ignorance, but it may be a little more complicated than that. I have a Receipt table and the query is based on that table. However, I need the Release form to find the records in the Receipt table, but save the linking field (ReceiptID) along with other information in the Release table. Also, I don't know what
Code:
 tags are.  Sorry!
John D
 
Code:
 tags are here on the forum, they make code snippets easier to read (see reply #8 for an example).

...okay, let's back up a step. What is the difference between the Receipt and the Release tables? Do you have a form at all that's based on the Release table?
 
Yes, I have a table and a form for releases. When material is received, the receipt table and form are used. When the material is inspected, the release table and form are used. The release table is linked to the receipt table by the receiptID field. I wanted to run a query which would find each record in the receipt table which had not yet been QA inspected. I have a flag in the receipt table called reconciled. I wanted those records to come up and cycle through the release form, be able to document the QA inspection, and save the results to the release table.

I see reply #8 for the code tags. Is that the pound sign in the format section?

Thanks again for your help!
John D
 
I see reply #8 for the code tags. Is that the pound sign in the format section?
Yes, exactly correct.

Yes, I have a table and a form for releases. When material is received, the receipt table and form are used. When the material is inspected, the release table and form are used. The release table is linked to the receipt table by the receiptID field. I wanted to run a query which would find each record in the receipt table which had not yet been QA inspected. I have a flag in the receipt table called reconciled. I wanted those records to come up and cycle through the release form, be able to document the QA inspection, and save the results to the release table.
You cannot (without a lot of jiggery pokery) edit your Releases table from your Receipt form... hopefully the reasons for that are self-evident. :D

The code I gave above was assuming that Receipts and Releases were in the same table... why are they separate? You should just have a 'status' flag on your records that says where it is at in your process... trying to move stuff among tables in Access every time it changes status will make you crazy! Then you can just build a query that says 'show me Released but no Receipt' or 'show me those with Receipts', etc. Those queries will look and act JUST like your current tables, but without many of the problems stemming from your unnormalized database.

Unless I have completely misunderstood your business process, again... do me a favor and post the fields in each of your Receipt and Releases tables?
Kinda like this:
tableReleases
------------------
orderID
OrderIntakeDate
etc...
 

Users who are viewing this thread

Back
Top Bottom