S
skyrise
I have a small amount of experience with Access. A researcher has asked me
to build a database to hold data pulled from published studies: Publication
Name, Location of Study, Particpant Data, etc. The researcher wants several
List fields in the the database to be updatable by the users. For example,
instead of loading many possible Publications Names up front into a large
Combo Box, allow an exact list of publications to be created as the users
come across them. I can't get the Event Procedure to work and don't know
what I need to do to fix it. This is the main thing that is holding up the
build of the database. The database is needed within the next few days.
PLEASE HELP!
Publication Name can be used as the example (I'm assuming the other fields
can be set up the same way).
I've created a Lookup Table called "lkpPublicationName" to hold the list of
publication names.
The table fields:
PublicationNameID - Primary Key autonumber.
PublicationName - Text field set to Allow Zero Length = Yes, Indexed = Yes
(No Duplicates)
I'm using a form called "PublicationData" to created records. Fields such as
"Publication Name" are being added in the Form Design view as Bound Combo
Boxes that use a SELECT Query to pull the user selected information from it's
Lookup Table and place it in the field on the form.
The PublicationData Table has primarily two fields of it's own:
PublicationRecordID - Primary Key autonumber.
PublicationNumber - Text field to enter the code that ID's the publication.
All other fields are represented in the table by the Primary Key field from
their corresponding LookupTable (to link to the combo box that will be added
on the form). These fields are set as Numbers with Properties of Field Size
= Long Integer, Indexed = Yes (Duplicates ok). Some of these fields are:
PublicationNameID
PubYearID
StudyLocationID
Etc.
Here is the SELECT Query for Publication Name:
SELECT [lkpPublicationName].[PublicationNameID], [lkpPublicationName].
[PublicationName] FROM lkpJournalName;
The names for the Combo Boxes appears as the name that Access automatically
assigns.
The name for the Publication Name Combo Box is Combo13.
Here is the NotInList Event Procedure for Publication Name:
Private Sub Combo13_NotInList(NewData As String, Response As Integer)
On Error GoTo Combo13_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The publication name " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Add Publication Name?")
If intAnswer = vbYes Then
strSQL = "INSERT INTO lkpPublicationName ([PublicationName]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new publication name has been added to the list." _
, vbInformation, "Publication Name Added"
Response = acDataErrAdded
Else
MsgBox "Please choose a publication name from the list." _
, vbInformation, "Use Publication Name List"
Response = acDataErrContinue
End If
Combo13_NotInList_Exit:
Exit Sub
Combo13_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Combo13_NotInList_Exit
End Sub
to build a database to hold data pulled from published studies: Publication
Name, Location of Study, Particpant Data, etc. The researcher wants several
List fields in the the database to be updatable by the users. For example,
instead of loading many possible Publications Names up front into a large
Combo Box, allow an exact list of publications to be created as the users
come across them. I can't get the Event Procedure to work and don't know
what I need to do to fix it. This is the main thing that is holding up the
build of the database. The database is needed within the next few days.
PLEASE HELP!
Publication Name can be used as the example (I'm assuming the other fields
can be set up the same way).
I've created a Lookup Table called "lkpPublicationName" to hold the list of
publication names.
The table fields:
PublicationNameID - Primary Key autonumber.
PublicationName - Text field set to Allow Zero Length = Yes, Indexed = Yes
(No Duplicates)
I'm using a form called "PublicationData" to created records. Fields such as
"Publication Name" are being added in the Form Design view as Bound Combo
Boxes that use a SELECT Query to pull the user selected information from it's
Lookup Table and place it in the field on the form.
The PublicationData Table has primarily two fields of it's own:
PublicationRecordID - Primary Key autonumber.
PublicationNumber - Text field to enter the code that ID's the publication.
All other fields are represented in the table by the Primary Key field from
their corresponding LookupTable (to link to the combo box that will be added
on the form). These fields are set as Numbers with Properties of Field Size
= Long Integer, Indexed = Yes (Duplicates ok). Some of these fields are:
PublicationNameID
PubYearID
StudyLocationID
Etc.
Here is the SELECT Query for Publication Name:
SELECT [lkpPublicationName].[PublicationNameID], [lkpPublicationName].
[PublicationName] FROM lkpJournalName;
The names for the Combo Boxes appears as the name that Access automatically
assigns.
The name for the Publication Name Combo Box is Combo13.
Here is the NotInList Event Procedure for Publication Name:
Private Sub Combo13_NotInList(NewData As String, Response As Integer)
On Error GoTo Combo13_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The publication name " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Add Publication Name?")
If intAnswer = vbYes Then
strSQL = "INSERT INTO lkpPublicationName ([PublicationName]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new publication name has been added to the list." _
, vbInformation, "Publication Name Added"
Response = acDataErrAdded
Else
MsgBox "Please choose a publication name from the list." _
, vbInformation, "Use Publication Name List"
Response = acDataErrContinue
End If
Combo13_NotInList_Exit:
Exit Sub
Combo13_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Combo13_NotInList_Exit
End Sub