Ellen Manning wrote:
I'm developing an A2K adp that contains doctor info and the months
they are not available for teaching. A doctor can be unavailable for
some months for one year and other months for another year. I want
the user to enter the year then use a multi-select list box to choose
the months not available, then go to a fresh screen, enter another
year and choose the months not available for that year, and so on. In
the table, there is one record per year/month per doctor.
I've got the form set up and I can enter one year and the months but
can't figure out how to navigate to a fresh screen and enter the next
year. Maybe using a multi-select listbox isn't the way to go??? Any
ideas? Thanks.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The list box method of selecting months is OK, but you have to know that
all the selected values (records) will have to be written to the table
one-at-a-time. You can use a stored procedure (SP) & VBA code. Example
of the SP:
CREATE PROCEDURE usp_AddDoctorUnavailable
- -- If the DoctorNotAvailable table has more than these columns
- -- you will have to adjust the SP & the VBA code to insert those
- -- columns.
@DoctorID INTEGER ,
@Month DATETIME -- Format YYYYMM01
AS
INSERT INTO DoctorNotAvailable (DoctorID, Month)
VALUES (@DoctorID, @Month)
====
There should be a CommandButton on the form that saves the data to the
table "DoctorNotAvailable" (use the real table's name). E.g.
(untested):
Private Sub cmdSave_Click()
' In:
' Me!lstMonths The months to save
' Me!txtDoctorID The doctor's ID
' Me!txtYear The year the months are in
'
Dim cn As New ADODB.Connection
Dim strSQL As String
Dim strExec As String
Dim varRow as Variant
On Error GoTo err_
cn.Connection = CurrentProject.Connection
cn.Open
strSQL = "EXEC usp_AddDoctorUnavailable " & Me!txtDoctorID & ", "
' Assumes the list box's bound column returns the month number,
' and that txtYear holds a 4-digit year.
For Each varRow In Me!lstMonths.ItemsSelected
With Me!lstMonths
' Format the date to "YYYYMM01" & attach to EXEC statement.
' Note: single quotes have to surround the date value.
strExec = strSQL & " '" & Me!txtYear & _
Format(.ItemData(varRow),"00") & "01'"
cn.Execute strExec,,adCmdText
End With
Next varRow
' If reached here - success!
' Clear the selected values from the list box
' to show the user that the info was saved.
' Or show a MsgBox saying saved.
With Me!lstMonths
For Each varRow In .ItemsSelected
.Selected(varRow) = False
Next varRow
End With
exit_:
cn.Close
Set cn = Nothing
Exit Sub
err_:
' You may want to check for duplicates & just Resume Next
' instead of running the MsgBox.
MsgBox "An error occurred while saving the Doctor's " & _
"Unavailable months" & vbcr & vbcr & "Error: " & _
err.Description, vbCritical
Resume exit_
End Sub
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQH2udYechKqOuFEgEQK7ygCgkHfBI/HrAT4myW0qLQ50aeXwuOgAoLeY
rE92g44aXedjv2946IxhwxIY
=kzJ9
-----END PGP SIGNATURE-----