use a button to enter a value into a table

[campbell707]

Hello,

I am new to Microsoft access and still learning how to code. I am looking for a way to get a button on a form to enter a certain value into a data table when clicked on.



Cheers!

[June7]

Method depends on whether the form is bound to the table where you want the value saved.

Why do you want to use code to save data?

[campbell707]

The form is bound to the table the data will be entered. Right now I am using yes/no checkboxes but they will still make work for me down the road to transform the data. The ultimate goal is for an employee to click the button on the form and have a specific letter such as "E" be saved to a column in the table that is called Eating.

Thanks!

[campbell707]

Don't need to use code. A coworker had mentioned I may need to code that button specifically to the table to enter the value with an onclickbutton command.

[June7]

Why would you need to enter "E" into field named Eating?

I suspect your data structure is not normalized and you seem to be aware of this issue by your mention of 'transform the data'.

[campbell707]

I am using access for a bit different use than its original intent. We want to use this program for behavior data entry and I am using the forms like an app that employees can tap and it enters the data into the table. Current system is the check boxes which work but they are too small. Therefore there would be columns of each possible behavior the animal will perform and the employee will hit the buttons that are associated with what the animal is doing. Therefore there will be a column called eating and i want an e to be entered if the employee hits the e button when the animal is eating.

Thanks

[June7]

How is this using Access differently? You are managing data and that is Access original intent. Perhaps your form (the user interface) is not a conventional data entry interface, regardless, how data is stored should still follow basic relational database principles.

[vicsaccess]

campbell707, if I understand you correct, you want different buttons representing different behaviors of what the animal is doing. so you're looking to set default values of a button to be "E" or "S" and when clicked on they enter the value into your table. June is the expert all knowing but I would keep it simple with two command buttons and the code behind them as

Private Sub CmdEat_Click()
    Me.Description.Value = "E"
    
End Sub
Private Sub CmdSleep_Click()
    Me.Description.Value = "S"
End Sub

Both command buttons set values into the "Description" box.

[June7]

OP said field is called Eating. Eating is a behavior. If there is a field for each behavior, this is not normalized data structure. So why would "E" be input into field called Eating? What other values would go into this field?

[vicsaccess]

good point, then could he not do the same concept with both command buttons bound to the same field, one with a yes default and the other set to a no default?

[June7]

Certainly.

I wonder how many 'behavior' fields there are. If there are 20 and then there are two buttons for each field ....

A toggle button might be more appropriate.

Still, this is not a normalized data structure.

What if someday needs to add a new behavior? This is a new field. Have to modify table, queries, forms, reports, code. Very annoying.

[campbell707]

Hi all thanks for the responses! I will have 5 mouth, 4 locations, 3 standing postures, and 5 sleeping postures. Each would have its own column on the table, but the worker would just need to select say one of the 3 standing postures and click which one the animal is doing. We will not add any columns in the future. These are permanent for this project. Essentially at the end of the project, we can export these tables to excel and calculate certain things according to the letters. Every behavior will have its own letter associated with it so each table has 17 fields and there would be 17 buttons on the form, one linked to each column in the table. I hope this helps clarify. I am not sure whether this is possible or the best way. I currently have each field on the form as a checkbox which would work but the boxes are too small for the employees and i would have to do a lot in excel to make all those true false words equal the letters they are supposed to be so I am lookong for an easier way just to click te button on the form and ot enters the appropriate letter in the table.

Last thought would be if there is a way to make the form only let you pick one of the three standing postures, that way an employee doesnt accidently hit two. And is there a way to highlight the button once it is clicked so the employee can quickly scan the screen and make sure they have four buttons clicked every time?

Thanks a bunch! I really appreciate the help!

[vicsaccess]

capbell707, so you will have maybe 6 fields in your table? date/time, animal, mouth, location, standing, sleeping? then different numeric or alphabetic values that go into the different fields. will the employee look at one animal at a time or have a list of all and do the evaluations for each animal from one form?

[campbell707]

I will have a field for the date, the time, the animal ID and every possible behavior. Even though say sleeping posture has 5 choices, they will only pick the one the animal is doing so the other 4 cells would just be left empty. Each field then has its own button on the form and ita own letter that would be entered into that particular field if that button is pressed. I have all 17 animals on 1 table by having animal ID as a field and the IDs are already filled in on the table as are the date and time. The only thing empty on the table are the 17 fields for all the possible behaviors the animal could be doing. Each time point would have its own table. I know its a lot of tables to make but i couldnt think of a simple way to make a huge master table. Essentially the employee would start on a form And choose the button for thay particular date, then it would open a form that says time point, then it opens the form where they choose the buttons for all animals at that time point. In the form, i have a scroll that shows all animal ids for that particular timepoint so the employee can just pick which animal they are working on. Its the same form for the same table, but by choosing the ID on the scroll, it sort of is a new form for each ID on the same table. Hope this helps. Thanks!!!!

[vicsaccess]

Ok, I needed a break from the current project and this sounded like fun. in the example each button will give your box a different value. as a bonus it is also color coded to let the employee know if they have selected it. i'm sure June7 will have a better way but this is how I would handle it and keep the tables as normal as possible. would maybe suggest numbers rather than letters for the storage if it was me
behavior.zip