Plus and Minus buttons

[usaemtp]

Hello. I am working with Access 2013 and am very novice at coding. I have a table called "PSV_List" with number fields titled "Responses," "Available," and "Assigned" and a field titled "PSV Name." I wish to create a form that chooses a record by selecting a PSV name from the dropdown list that will then populate the form with the latest data in the record. Then I want to create + and - buttons to increase or decrease each of the number field values. My problem is in creating the buttons. I do not know how to write the code to change the numbers in these table fields from the forms without physically typing in numbers. Is there a coding solution to this problem that I can use in the code builder for the buttons?

[June7]

To filter form or move to record, review https://www.accessforums.net/program...lso-57672.html

To increment value, in VBA, like:

Me.controlname = Me.controlname - 1

Me.controlname = Me.controlname + 1

Why do you want to increase/decrease a value?

[Missinglinq]

If you want to start using these when the Control is Null (empty), you need to also use Nz():

Private Sub PlusButton_Click()
 Me.TargetControl = Nz(Me.TargetControl, 0) + 1
End Sub

Private Sub MinusButton_Click()
 Me.TargetControl = Nz(Me.TargetControl, 0) - 1
End Sub

Linq ;0)>

[usaemtp]

Thank you all so much for your quick replies. I can't wait to give them a try! I will let you know how it went. I am also wondering if there is a way to increase the number by 1 in the number field of 1 table by selecting an option from a dropdown list in another table. Would you have a suggestion for that or should I start another thread? (Not sure of the etiquette on something like that)...

[June7]

Seems like it's related.

I don't understand why you want to do this incrementing at all.

What does value of combobox have to do with incrementing a value?

[usaemtp]

I need to do the incrementing because I have a table that tracks personnel and their responses and availability for incidents that I track and another table. There are more than 15 people that I am trying to be able to quickly enter this information for. And I want the information stored in the table under each person. It becomes extremely cumbersome to physically change the number for each of those people so having a button to push makes it much simpler. However when I assign an incident to an individual, I do this in the table that tracks the actual incidents. For this one it would be better for me to be able to select the person from a drop-down list for the incident. But rather than then having to go to the personnel table and add an assignment it would be nice to be able to have this occur automatically when I select them from the incident table out of the drop-down list.

[June7]

Not much of that makes sense to me. Exactly what number are you changing?

Why would assignments be in Personnel table? Shouldn't assignments be in Assignments table?

Might help if knew your data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

[usaemtp]

I am sorry but I just read over my own explanation and it was not very clear. There are more than 45 people, not 15. And incidents are tracked in their own table and personnel are tracked in a separate table. However, the personnel are listed in a dropdown list in the incident table and when I assign one there I want it to update the incremented number of assignments in the personnel table. I may be rethinking the tracking of availability going forward so I can make it more meaningful data by tracking in a date range as well but for now I just want to know how to make these interactions work between one table and another using the dropdown list if possible.

[June7]

The concept of incrementing an aggregate value is not advised. Saving calculated data, especially aggregate data, is usually a bad idea. Calculations should be done when needed. This 'count' should be a calculated value, not saved into table.

If you need to set a value in a field that is not in the form this requires an SQL UPDATE action.

[usaemtp]

Thank you for the additional guidance. I am rethinking the design of that portion of the database to make it more streamlined. That way calculations can be done in a report instead.

[usaemtp]

OK so in rethinking the design, I am creating another table. So I have a table that contains information for each volunteer (demographic stuff). I also have a table that tracks incidents by incident number. But I need a (reliability) table that tracks responses to incidents and assignments by date for each PSV. Ideally I would like it so that when I enter an incident in the incident table, the reliability table automatically creates a record for each PSV in the PSV table list with reliability defaulted to no (I can then go to each individual one and select yes as appropriate). I do not know, however, how to auto-create records in one table from an entry in another table. Is this possible to do? If so can someone suggest the type of code necessary for this to take place?

[June7]

Requires running an SQL INSERT action. Do not recommend this approach. Why would you need a response record before there is a response?

Can there be multiple responses for each incident?

What does PSV stand for?

[usaemtp]

I oversee a program for my company that assists employees with critical incidents that they encounter in their duties. When an incident occurs, we track the incident and assign Peer Support Volunteers (PSV’s) to each crewmember involved in the incident. I am creating a database to track information related to these incidents.

When I receive an incident, I assign it an incident number based on year and number of incidents (20160001, 20160002, etc). I enter information for the incident in a table containing incident details and notes. I then enter information in another table that assigns a PSV to each crewmember (each incident may have 3-4 employees so each incident number in this table may have 3 to 4 records). When PSV’s are assigned to employees, they are chosen from a dropdown list of PSV’s that is pulled from a PSV table containing demographic info about the PSV’s.

I also need to track reliability of the PSV’s with respect to responses to incidents in three categories (available, responds, assigned). I want to create a table with the three categories as yes/no, defaulted to no. This table should have a record for each PSV for a given incident number (so possibly popping up once an incident number is entered in the first table) that contains each PSV with the three categories. This is what I am wondering if I can cause to populate automatically as new records.

I need the data so that I can print reports related to tallies of incidents by type and date, as well as the ability to report on PSV reliability by date.

Unfortunately I cannot post my database as it has sensitive information in it. But if it will help, I may be able to sterilize a copy of the database and post it.

Hopefully this better clarifies what I am trying to accomplish.

[June7]

As I said, would require an SQL INSERST action. The real trick is figuring out what event to put the code into and also capturing the key to save as foreign key.

Can a reliability record have all 3 categories (available, responds, assigned) selected as Yes?

I am not sure the reliability data needs to be in a table separate from the PSV assignment. It would probably help to view the db.