update query

I think this will work for you. Create a form for tblCSD if you haven't
already. Create another form for tblCSD_Specialties, and embed it into the
first form as a subform. Link the two via CSDID, and add a command button on
the main form that opens/refreshes the crosstab query. hth

>I have 3 tables joined (tblCSD, tblSpecialties & tblCSD_Specialties). tblCSD
>has my demographics with primary key CSDID, tblSpecialties has a list of
[quoted text clipped - 5 lines]
>I know I likely haven't given enough information, but I'm not sure what else
>you will need. Thank you in advance!!!

Reply to this Message

I'm not sure this will work. The tblCSD_Specialties only contains the
clinical specialties that were checked yes previously. There are 46 total
options. I need to be able to allow the user to "uncheck" those previously
checked yes, as well as give them the option to check any of the other
specialties. Any other ideas?

Reply to this Message

Hello again Jenny

What you need to do here is add and delete records from your junction table
as the user selects or unselects specialties from the list.

The "traditional" way to do this is manually, using a continuous subform.
The subform is bound to your junction table and is linked to your main CSD
form by CSDID and CSDFK. It contains a single combo box, bound to
SpecialtiesFK with its RowSource set to:

Select SpecialtyID, SpecialtyName from tblSpecialties order by SpecialtyName

It should have ColumnCount=2, BoundColumn=1 and ColumnWidths=0 (this hides
the ID column).

Now the user can add a specialty by selecting a value form the combo in a
new record, and unselect a specialty by deleting an existing record.

You may find this method a bit clunky for your liking. There are three
alternative methods you could use, in ascending of complexity of coding
required:

1. using a listbox to show the selected specialties, with a combo box to add
to the list and a command button to remove from the list.

2. using a multi-select listbox to show the selected specialties and to
add/remove them from the list.

3. using a form (or subform) with checkboxes to show the selections and
transparent command buttons over the checkboxes to toggle them.

If you're interested in one of these methods, post back and I'll give you
some help with the code.

Signature

Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

>I have 3 tables joined (tblCSD, tblSpecialties & tblCSD_Specialties).
>tblCSD
[quoted text clipped - 9 lines]
> else
> you will need. Thank you in advance!!!

Reply to this Message

Jenny - 12 Jul 2007 17:22 GMT

Graham! Thanks for coming back to my rescue!!
I think for my users, option 2 or 3 would be best. If you think I can
handle it, probably #3 would work better.

THANKS!!!!!!!!!

> Hello again Jenny
>
[quoted text clipped - 42 lines]
> > else
> > you will need. Thank you in advance!!!

Reply to this Message

Graham Mandeno - 12 Jul 2007 23:31 GMT

Hi Jenny

The problem I see with both options 2 and 3 is that with 46 possible
specialty options the list will need to be scrollable and the user will not
see all of the selected options at once.

How many of the 46 specialties will typically be selected for a given CSD?
If the answer is small - say fewer than ten, then I think option 1 would
provide a more user-friendly interface. This way the user can see all the
specialties that have been selected in one view, and needs only see the
unselected ones when adding to the list.

What do you think?

:-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> Graham! Thanks for coming back to my rescue!!
> I think for my users, option 2 or 3 would be best. If you think I can
[quoted text clipped - 56 lines]
>> > else
>> > you will need. Thank you in advance!!!

Reply to this Message

Jenny - 13 Jul 2007 00:00 GMT

You're the boss! :)

Reply to this Message

Graham Mandeno - 14 Jul 2007 06:15 GMT

> You're the boss! :)

LOL! Well, that didn't quite answer my questions, but I think I get your
drift :-)

I'm assuming the following table structures. Please correct me if I'm
wrong, because that might alter the way we do things:

tblCSD:
CSDID - autonumber, primary key
[... other inconsequential fields]

tblSpecialties:
SpecialtyID - autonumber, primary key
SpecialtyName - text, required, no duplicates

tblCSD_Specialties:
CSDFK - long integer
SpecialtiesFK - long integer
[these two fields TOGETHER should form a composite primary key]

First, add some controls to your form:

1. A listbox names lstSpecialties:
RowSource: <leave blank>
RowSourceType: Table/Query
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the first column)
You could give it an attached label with the caption "Specialties"

2. A combo box named cboAddSpecialty
RowSource: <leave blank>
RowSourceType: Table/Query
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the first column)
Visible: No

3. Two command buttons: cmdAddSpecialty and cmdDeleteSpecialty

Now, add the following event procedure code to your form module:

Private Sub Form_Current()
'Hide the combo just in case it didn't get hidden already
cboAddSpecialty.Visible = False
'Update the row source for the listbox for the current user
lstSpecialties.RowSource = "Select SpecialtyID, SpecialtyName " _
& "from tblSpecialties inner join tblCSD_Specialties on " _
& "tblSpecialties.SpecialtyID=tblCSD_Specialties.SpecialtiesFK " _
& "where CSDFK=" & Me.CSDID & ";"
End Sub

Private Sub cmdDeleteSpecialty_Click()
'If nothing is selected, give an error message
If lstSpecialties.ListIndex < 0 Then
MsgBox "Please select a specialty to delete!", vbInformation
Exit Sub
End If
' Delete the currently selected specialty from the junction table
CurrentDb.Execute "Delete From tblCSD_Specialties where CSDFK=" _
& Me.CSDID & " and SpecialtiesFK=" & lstSpecialties & ";"
'Requery the list
lstSpecialties.Requery
End Sub

Private Sub cmdAddSpecialty_Click()
'list only those specialties NOT already selected in the combo box
With cboAddSpecialty
.RowSource = "Select SpecialtyID, SpecialtyName From tblSpecialties " _
& "where Not SpecialtyID In (Select SpecialtiesFK from " _
& "tblCSD_Specialties where CDSFK= " & Me.CSDID & ");"
.Value = Null
.Visible = True
.SetFocus
.Dropdown
End With
End Sub

Private Sub cboAddSpecialty_AfterUpdate()
If cboAddSpecialty.ListIndex >= 0 Then
CurrentDb.Execute "Insert Into tblCSD_Specialties (CSDFK, " _
& "SpecialtiesFK) VALUES (" & Me.CSDID & ", " & cboAddSpecialty & ");"
lstSpecialties.Requery
lstSpecialties = cboAddSpecialty
lstSpecialties.SetFocus
End If
End Sub

Private Sub lstSpecialties_GotFocus()
cboAddSpecialty.Visible = False
End Sub

I hope this code is all OK - it's just off the top of my head and probably
full of syntax errors but have fun finding them :-)

Signature

Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Reply to this Message

Graham Mandeno - 15 Jul 2007 00:10 GMT

> You're the boss! :)

LOL! Well, that didn't quite answer my questions, but I think I get your
drift :-)

I'm assuming the following table structures. Please correct me if I'm
wrong, because that might alter the way we do things:

tblCSD:
CSDID - autonumber, primary key
[... other inconsequential fields]

tblSpecialties:
SpecialtyID - autonumber, primary key
SpecialtyName - text, required, no duplicates

tblCSD_Specialties:
CSDFK - long integer
SpecialtiesFK - long integer
[these two fields TOGETHER should form a composite primary key]

First, add some controls to your form:

1. A listbox names lstSpecialties:
RowSource: <leave blank>
RowSourceType: Table/Query
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the first column)
You could give it an attached label with the caption "Specialties"

2. A combo box named cboAddSpecialty
RowSource: <leave blank>
RowSourceType: Table/Query
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the first column)
Visible: No

3. Two command buttons: cmdAddSpecialty and cmdDeleteSpecialty

Now, add the following event procedure code to your form module:

Private Sub Form_Current()
'Hide the combo just in case it didn't get hidden already
cboAddSpecialty.Visible = False
'Update the row source for the listbox for the current user
lstSpecialties.RowSource = "Select SpecialtyID, SpecialtyName " _
& "from tblSpecialties inner join tblCSD_Specialties on " _
& "tblSpecialties.SpecialtyID=tblCSD_Specialties.SpecialtiesFK " _
& "where CSDFK=" & Me.CSDID & ";"
End Sub

Private Sub cmdDeleteSpecialty_Click()
'If nothing is selected, give an error message
If lstSpecialties.ListIndex < 0 Then
MsgBox "Please select a specialty to delete!", vbInformation
Exit Sub
End If
' Delete the currently selected specialty from the junction table
CurrentDb.Execute "Delete From tblCSD_Specialties where CSDFK=" _
& Me.CSDID & " and SpecialtiesFK=" & lstSpecialties & ";"
'Requery the list
lstSpecialties.Requery
End Sub

Private Sub cmdAddSpecialty_Click()
'list only those specialties NOT already selected in the combo box
With cboAddSpecialty
.RowSource = "Select SpecialtyID, SpecialtyName From tblSpecialties " _
& "where Not SpecialtyID In (Select SpecialtiesFK from " _
& "tblCSD_Specialties where CDSFK= " & Me.CSDID & ");"
.Value = Null
.Visible = True
.SetFocus
.Dropdown
End With
End Sub

Private Sub cboAddSpecialty_AfterUpdate()
If cboAddSpecialty.ListIndex >= 0 Then
CurrentDb.Execute "Insert Into tblCSD_Specialties (CSDFK, " _
& "SpecialtiesFK) VALUES (" & Me.CSDID & ", " & cboAddSpecialty & ");"
lstSpecialties.Requery
lstSpecialties = cboAddSpecialty
lstSpecialties.SetFocus
End If
End Sub

Private Sub lstSpecialties_GotFocus()
cboAddSpecialty.Visible = False
End Sub

I hope this code is all OK - it's just off the top of my head and probably
full of syntax errors but have fun finding them :-)

Signature

Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Reply to this Message

Jenny - 17 Jul 2007 16:10 GMT

Graham - You are BRILLIANT!!!!!!!!!!!!!!!!!
Everything seems to be working great.

I really appreciate your help!

> > You're the boss! :)
>
[quoted text clipped - 91 lines]
> I hope this code is all OK - it's just off the top of my head and probably
> full of syntax errors but have fun finding them :-)

Reply to this Message

Graham Mandeno - 17 Jul 2007 22:18 GMT

That's great news, Jenny! I hope you've learned heaps.

Signature

Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> Graham - You are BRILLIANT!!!!!!!!!!!!!!!!!
> Everything seems to be working great.
[quoted text clipped - 98 lines]
>> probably
>> full of syntax errors but have fun finding them :-)

Reply to this Message

Jenny - 17 Jul 2007 22:54 GMT

Thank you so much! I am forever in your debt! Stay well. I'm sure I'll
have more questions when I start my next project!

Reply to this Message