Option Boxes showing data

[RedHeadedMonster via AccessMonster.com]

I have a database that tracks Problem Tickets. When a user comes in and adds
a NEW problem ticket, a Pop-up appears and they must select at least one of 8
choices, that bascially puts the ticket into responsible departments. The
majority of the time its only going to be one department, but occasionally a
ticket will effect more than one so They can select more that one if they
have to.

Im using Radio Buttons. I know that Radio buttons are generally used as a
Option only one, but People were freaking out with the check boxes so I went
radio buttons. Anyway.....

Once they select their choices, I run some SQL that places the Problem ticket
number, and the number assigned to the Radio button, i.e., 1 - 8, into a
table. I have a lookup table that Lists the 8 choices with their number
designation. Bascially I didnt want to have a table with a field for each
choice.

So now, here is the problem, I am currently displaying a list of these
Departments when one looks at the overall ticket. The Problem Ticket
Coordinator, wants to be able to see, basically the same radio buttons, with
the correct departments "selected". Instead of a laundry list. So that if
he decides that another department needs to be added he just select a radio
button.

So i have a group of the Radio buttons, they are named, for example:
optDesign, optArchitecture, optTest.....etc.

Im thinking, I need to have some kind of code run, that looks at my
tblLinkTicketDept looking only at the Ticket Number that matches my form's
Ticket number, and then matching the DeptIDs with the Options buttons so if
for example the the DeptID = 3 then optArchitecture = True....remembering
that more than one can be selected

Clear as mud?

Any ideas?
RHM

 

[Beetle]

So what happens if someone wants to change an existing
record from one department to another? Run a Delete query
to delete the old record from the ProblemTicket table, then
an Append query to add the new record, then refresh your
form to reflect the new value?

Or what if they add another Department at some point?
Redesign your form to add another radio button, and possibly
modify all your code to reflect the new option? Ouch!

Sounds like you and Access are working a lot harder than
necessary because your underlying structure is flawed. You
essentially have a many-to-many relationship between
Departments and Tickets, from what you describe, so you
need a table that will manage that relationship. For example;

tblTickets
*******
TicketID (PK)
other attributes of the Ticket

tblDepartments
***********
DeptID (PK)
other attributes of the Department

tblTicketDepts
**********
TicketID (FK to tblTickets)
DeptID (FK to tblDepartments)
(the above two fields would be combined PK for this table)

You would then have your main form based on tblTickets
with a subform based on tblTicketDepts (the junction table)
using TicketID as the Master/Child link. This subform could
have a combo box bound to the DeptID field where the users
would select as few - or as many - Departments as are needed
for any given Ticket, and each record would always display
the correct associated Depts. If the users want to modify
a record they simply change the selection in one of the combo
boxes. If they ever add a new Department, you simply add a new
record to tblDepartments. No need for all the extra brain damage.

 

[RedHeadedMonster via AccessMonster.com]

First, the tables are already set up this way. Second, since I am developing
this for a customer, & they want buttons, I'd like to give them the buttons.
Just trying to find the easiest way to give it to them, but thanks for your
sarcastic post.

So what happens if someone wants to change an existing
record from one department to another? Run a Delete query
to delete the old record from the ProblemTicket table, then
an Append query to add the new record, then refresh your
form to reflect the new value?

Or what if they add another Department at some point?
Redesign your form to add another radio button, and possibly
modify all your code to reflect the new option? Ouch!

Sounds like you and Access are working a lot harder than
necessary because your underlying structure is flawed. You
essentially have a many-to-many relationship between
Departments and Tickets, from what you describe, so you
need a table that will manage that relationship. For example;

tblTickets
*******
TicketID (PK)
other attributes of the Ticket

tblDepartments
***********
DeptID (PK)
other attributes of the Department

tblTicketDepts
**********
TicketID (FK to tblTickets)
DeptID (FK to tblDepartments)
(the above two fields would be combined PK for this table)

You would then have your main form based on tblTickets
with a subform based on tblTicketDepts (the junction table)
using TicketID as the Master/Child link. This subform could
have a combo box bound to the DeptID field where the users
would select as few - or as many - Departments as are needed
for any given Ticket, and each record would always display
the correct associated Depts. If the users want to modify
a record they simply change the selection in one of the combo
boxes. If they ever add a new Department, you simply add a new
record to tblDepartments. No need for all the extra brain damage.

I have a database that tracks Problem Tickets. When a user comes in and adds
a NEW problem ticket, a Pop-up appears and they must select at least one of 8

[quoted text clipped - 33 lines]

Any ideas?
RHM

 

[Beetle]

My post wasn't sarcastic. I was pointing out the problems
you're going to face in designing the application as you are.

If you're willing to go to all the extra trouble because they
like buttons, that's your choice.

--
_________

Sean Bailey

First, the tables are already set up this way. Second, since I am developing
this for a customer, & they want buttons, I'd like to give them the buttons.
Just trying to find the easiest way to give it to them, but thanks for your
sarcastic post.

So what happens if someone wants to change an existing
record from one department to another? Run a Delete query
to delete the old record from the ProblemTicket table, then
an Append query to add the new record, then refresh your
form to reflect the new value?

Or what if they add another Department at some point?
Redesign your form to add another radio button, and possibly
modify all your code to reflect the new option? Ouch!

Sounds like you and Access are working a lot harder than
necessary because your underlying structure is flawed. You
essentially have a many-to-many relationship between
Departments and Tickets, from what you describe, so you
need a table that will manage that relationship. For example;

tblTickets
*******
TicketID (PK)
other attributes of the Ticket

tblDepartments
***********
DeptID (PK)
other attributes of the Department

tblTicketDepts
**********
TicketID (FK to tblTickets)
DeptID (FK to tblDepartments)
(the above two fields would be combined PK for this table)

You would then have your main form based on tblTickets
with a subform based on tblTicketDepts (the junction table)
using TicketID as the Master/Child link. This subform could
have a combo box bound to the DeptID field where the users
would select as few - or as many - Departments as are needed
for any given Ticket, and each record would always display
the correct associated Depts. If the users want to modify
a record they simply change the selection in one of the combo
boxes. If they ever add a new Department, you simply add a new
record to tblDepartments. No need for all the extra brain damage.

I have a database that tracks Problem Tickets. When a user comes in and adds
a NEW problem ticket, a Pop-up appears and they must select at least one of 8

[quoted text clipped - 33 lines]

Any ideas?
RHM