update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables joined (tblCSD, tblSpecialties & tblCSD_Specialties). tblCSD
has my demographics with primary key CSDID, tblSpecialties has a list of
clinical specialties with primary key specialtiesID and tblCSD_Specialties
has the joined data of CSDFK & SpecialtiesFK. I then have a crosstab query
to get results of above. My question is how can I create a form for a user
to update the tblCSD_Specialties that would transfer back to my crosstab
query?
I know I likely haven't given enough information, but I'm not sure what else
you will need. Thank you in advance!!!
 
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'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?
 
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.
 
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!!!!!!!!!

Graham Mandeno said:
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.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
I have 3 tables joined (tblCSD, tblSpecialties & tblCSD_Specialties).
tblCSD
has my demographics with primary key CSDID, tblSpecialties has a list of
clinical specialties with primary key specialtiesID and tblCSD_Specialties
has the joined data of CSDFK & SpecialtiesFK. I then have a crosstab
query
to get results of above. My question is how can I create a form for a
user
to update the tblCSD_Specialties that would transfer back to my crosstab
query?
I know I likely haven't given enough information, but I'm not sure what
else
you will need. Thank you in advance!!!
Click to expand...
Click to expand...
 
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

Jenny said:
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!!!!!!!!!

Graham Mandeno said:
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.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jenny said:
I have 3 tables joined (tblCSD, tblSpecialties & tblCSD_Specialties).
tblCSD
has my demographics with primary key CSDID, tblSpecialties has a list
of
clinical specialties with primary key specialtiesID and
tblCSD_Specialties
has the joined data of CSDFK & SpecialtiesFK. I then have a crosstab
query
to get results of above. My question is how can I create a form for a
user
to update the tblCSD_Specialties that would transfer back to my
crosstab
query?
I know I likely haven't given enough information, but I'm not sure what
else
you will need. Thank you in advance!!!
Click to expand...
Click to expand...
Click to expand...
 
Jenny said:
You're the boss! :)
Click to expand...

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 :-)
 
Jenny said:
You're the boss! :)
Click to expand...

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 :-)
 
Graham - You are BRILLIANT!!!!!!!!!!!!!!!!!
Everything seems to be working great.

I really appreciate your help!

Graham Mandeno said:
Jenny said:
You're the boss! :)
Click to expand...

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 :-)
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
 
That's great news, Jenny! I hope you've learned heaps.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

I really appreciate your help!

Graham Mandeno said:
Jenny said:
You're the boss! :)
Click to expand...

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 :-)
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
Click to expand...
 
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!
 
Back
Top