Query, update fields on form on click of button

[smicklo]

Hi All



Very new novice on Access DB, and trying to learn terminology and how to set things up.

I have a table (table 1-- I tried to attach but could not get it into the 500kb file size allowed). I have tried to redo it below

ID	Substance	Condition	Current	Store	Date Completed	Who	Final
1	TNT	Controlled	Active	Ambient
2	Cocaine	Controlled	Active	Fridge
3	cough Syrup	OTC	Closed	Discarded	3/2/2018	AAA	Discarded

What I would like to be able to do (if possible):

Query on ID number and enter the initials of who did this.

then click a button (called Discard Substance) and
* the fields "Condition", "Store", "Final" all change to "Discarded"
* the field "date completed" pulls in the current date
* update the "who" field to the initials I put in above.

So If I query on ID 2 and input initials BBB then the table should look like :

ID	Substance	Condition	Current	Store	Date Completed	Who	Final
1	TNT	Controlled	Active	Ambient
2	Cocaine	Discarded	Closed	Discarded	3/14/218	BBB	Discarded
3	Cough Syrup	OTC	Closed	Discarded	3/2/2018	AAA	Discarded

I have searched and read several posts but I believe the terminology is whats tripping me up and I am not getting what I am looking for. I believe I can do this using a form, but not sure. Is this better done using VBA code?

Thanks so much!

[ranman256]

most everything can be done with queries.
create an update query to do this.
select the ID on a form ( goto the record)
set button to run the query when clicked, macro: openquery "quDiscardSubstance"

the query will resemble:
update table set [Condition]='Discarded' , [Current]='Discarded' , [Final]='Discarded' where [id]=forms!myForm!ID

[kd2017]

To answer your question, yes you can do that. You can create a saved updated query that references your form's controls to get it's parameters, you could call this from a macro or VBA, or you could do it all with VBA. I'd personally use VBA.

Is this your table structure? I see I think what should be at least 5 different tables here. What is the purpose of the Final column? Is the final column always Null or Discarded or are there other options? If a store=discarded does that mean Final=discarded?

[EDIT]
If the button is on the form with the record source you don't need a update query, you can just have the button set the fields like this

Private Sub cmdDiscard_Click()
    Me![Condition] = "Discarded"
    Me![Store] = "Discarded"
    Me![Final] = "Discarded"
End Sub

You can set the [Who] and [DateCompleted] field in the same sub as well, you could have it check to see if either are blank, if so then prompt the user for the information, or just fail to "discard" the substance if the fields are blank...

But note that each of those columns should probably actually be foreign key fields.

[smicklo]

To answer your question, yes you can do that. You can create a saved updated query that references your form's controls to get it's parameters, you could call this from a macro or VBA, or you could do it all with VBA. I'd personally use VBA.

Is this your table structure? I see I think what should be at least 5 different tables here. What is the purpose of the Final column? Is the final column always Null or Discarded or are there other options? If a store=discarded does that mean Final=discarded?

Yes. The table is exactly as shown above in OP.
The purpose of the final column is the final disposition of substance. Yes, it will mirror store on 2 conditions:
1) IF store= Discarded then Final Disposition = Discarded
2) If store = Returned then final disposition = Returned
* ) if store = anything else, then final is null.

[smicklo]

I tried to do the update query but after I set it up (or what i thought was setting it up), it changed every record in the fields I selected to the discarded tag.

I am wanting to query just the id number. And only that record gets changed.

I am hoping to do it with only inputting the id and initials, and click a button to do this.

[kd2017]

Yes. The table is exactly as shown above in OP.
The purpose of the final column is the final disposition of substance. Yes, it will mirror store on 2 conditions:
1) IF store= Discarded then Final Disposition = Discarded
2) If store = Returned then final disposition = Returned
* ) if store = anything else, then final is null.

In that case it would seem that the Final column appears to be a calculated column, you can get rid of that field all together... Are you interested in help with your db's table structure?

[kd2017]

I tried to do the update query but after I set it up (or what i thought was setting it up), it changed every record in the fields I selected to the discarded tag.

I am wanting to query just the id number. And only that record gets changed.

I am hoping to do it with only inputting the id and initials, and click a button to do this.

It will do that if you don't define criteria fields. You need to define criteria to select the correct row(s) that you want to update. https://www.youtube.com/watch?v=NT1xvodbUQE

[smicklo]

It will do that if you don't define criteria fields. You need to define criteria to select the correct row(s) that you want to update. https://www.youtube.com/watch?v=NT1xvodbUQE

I thank you for the info. I have done that and while it works here is my issue with it. I may be doing 50 + of these entries daily. That is a lot of "clicks" and typing in to do each time or can I save this update query and just type in the id number in the update field each time?


My thought is if Access has the ability where I can search on an id number, and 1 click later it changes that record. Then another id number, and it changes that record.

[smicklo]

In that case it would seem that the Final column appears to be a calculated column, you can get rid of that field altogether... Are you interested in help with your db's table structure?

Unfortunately, I can not change the structure of the table - my company wants it exactly like it is. So I am working with it as is.

[kd2017]

You can save the query so you don't have to rebuild it every time. In the query's criteria textbox right-click and then click "Build..." to open the expression builder. You'll have three list boxes on the bottom half of this window, in the "expression elements" list box you'll see a list item with your database's name. Expand it, then expand "Forms", find the name of your form and select it. Now in the middle list box select "<Field List>". In the third list box double click your ID field. This will paste a string on the upper textbox something like Forms![Substances]![ID]. Click ok.

Now everytime you run this query it will get the value of the ID in the form you just selected and then use it to select and update your fields, there's no need for you to manually input an ID.

[kd2017]

Unfortunately, I can not change the structure of the table - my company wants it exactly like it is. So I am working with it as is.

But you're not utilizing the power of relational databases! You gotta do what you gotta do. But know that you could properly normalize your table structure and simply run a query that will return a single "flattened" table that looks exactly like what the bosses expect in the first post.

[kd2017]

Instead of entering your initials and date and then clicking the discard button you could have the query do that for you too:



(Don't change the [Please enter your initials] part and enter it just like it is, every time the query is run access will prompt the user for their initials and fill it in for you.)

Also note that after your button runs the query you will want to refresh your form to display the updated data.

[kd2017]

Unfortunately, I can not change the structure of the table - my company wants it exactly like it is. So I am working with it as is.

For your consideration please check out the attached database, and note the query qryFlat. An approach like this I think could reduce the risk of bad data. I'm sure there are many more business rules you should enforce EG if it's discarded then it should be closed or maybe don't allow changes after it's discarded, etc.

Substances.zip

[smicklo]

You can save the query so you don't have to rebuild it every time. In the query's criteria textbox right-click and then click "Build..." to open the expression builder. You'll have three list boxes on the bottom half of this window, in the "expression elements" list box you'll see a list item with your database's name. Expand it, then expand "Forms", find the name of your form and select it. Now in the middle list box select "<Field List>". In the third list box double click your ID field. This will paste a string on the upper textbox something like Forms![Substances]![ID]. Click ok.

Now everytime you run this query it will get the value of the ID in the form you just selected and then use it to select and update your fields, there's no need for you to manually input an ID.

Thanks so much!

I was able to follow your instructions and it works great! ANd a great learning opportunity for me.

Thanks again!

[smicklo]

For your consideration please check out the attached database, and note the query qryFlat. An approach like this I think could reduce the risk of bad data. I'm sure there are many more business rules you should enforce EG if it's discarded then it should be closed or maybe don't allow changes after it's discarded, etc.

Substances.zip

Thank you very much for this.

I like the way you have it set up, but when you have some time I do have some learning questions about Access and its powers.

1) In the tables, you have them all set up with "codes" for each field. Why is this a better or more powerful way to organize the data ?

2) Is there something special you needed to do for the qryFlat to show all the fields back to their "boss mandated" way?

3) In the substances forms, how did you get the "discard" button? I noticed that there is some VBA script behind them, but it didnt seem like there was code to change the field names when clicked.


I do appreciate all this you have done. I am working on understanding it all and with your answers and some more instruction, I am hoping to take it to my boss and see about doing some changes.