Heh - Not as easy as it looks ?
smurf
I have spent nearly a week working on my problem and I am now at a loss.
I am NO expert at all and only have a VERY VERY basic knowledge of
programming in Access97.
Infact - what very little I do know, I have taught myself but I just can't
work out my problem,
please bare with me as I am a novice and still learning.
Forgive me if I go into detail but I want to give you all the info I can so
that you can help
me if you decide to do so.
I have a CD Database that I am designing. It contains the following tables
and fields:
Recordings, which has the following Fields: RecordingID, CD_Title,
No_of_tracks, Catalogue_number
and Disc_No.
Tracks, which has the following fields: TrackID, No, Artist, Title, BPM and
Length.
Recording_Artists, which has the following fields: RecordingArtistID and
Artist.
I have a form that uses all the fields from the Recordings table (except
RecordingID as
this is an auto number) together with a subform that does the same for the
Tracks table.
The Artist field from the Tracks subform is linked via a combo box to the
Artist field in the
Recording_Artist table, which in turn is linked to the Artist field in The
Artists table.
What I am trying to do, either with a macro or Code is this. When someone is
in the Artist field
on the tracks subform and trys to enter a artist that is not on the list,
they get a pop up
form with a message that says something like "That artist is not on the
list, would you like
to add them". They then have to click either a Yes or No button depending on
their choice.
If they choose Yes, then the artist name that they have typed in order to
create the error
in the first place is then added to the Artist field in the
Recording_Artists table and the
name of the new artist is then displayed in the Artist field of the Tracks
subform. The coursor
is then moved to the next field which is Title ready for data entry there.
If they chose no, the invalid data that they have typed in order to first
create the error is
cleared and the courser would still be in the Artist field of the Tracks
Subform ready for them
to either reselect a name from the Artist field drop down list or retype a
new name to be added.
I have managed to get some of the way there with the following code that is
an [Event Procedure]
on the "On Not In List" section of the data tab of the properties box of the
Artists field of
the Tracks subform. As you will be able to see, I have worked some of it out
but still need
help with a lot of it.
Here is the code:
Option Compare Database
Option Explicit
Private Sub Artist_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim x As Integer
Dim dbsTracks As Database
Dim rstRecording_Artists As Recordset
strMsg = "Each employee record must have a unique " _
& "employee ID number. Please recheck your data."
x = MsgBox(strMsg, vbQuestion + vbYesNo)
If x = vbYes Then
' Open the Contact Types table and add the NewData value.
Set dbsTracks = CurrentDb()
' Set rstRecording_Artists =
dbsTracks.OpenRecordset("Recording_Artists")
rstRecording_Artists.AddNew
rstRecording_Artists!Artist = NewData
rstRecording_Artists.Update
Response = acDataErrAdded ' Requery the list.
Else
Response = acDataErrDisplay ' Display the error.
End If
End Sub
I am fairly sure that I should have some kind of OnClick event or procedure
but I am at a loss
as to exactly what.
Please, I know you guys are very busy but any help or suggestions would be
most gratefully
received as I am very keen to learn as well as solve my problem.
My grateful thanks in advance,
Kenny
sm...@innotts.co.uk
Bill Nicholson
Add the Sub call to the Item Not In List Event on your form. For example, if the
Terms of Payment field on a vendor form is a List Box, add this code to the
event:
ThingNotInList "tTerms", "Terms", "Terms", NewData, Response
Where "tTerms" is the table name that contains the different payment terms that
are currently on file
"Terms" is the name of the field that contains the description of the
payment terms
"Terms" is the description of the field that will appear in the
prompt to save / abandon the new data
NewData is the new terms data that the user entered in the list box
Response is the users decision to add or abandon the new payment
terms
Here is the structure of the Terms Table (tTerms)
TermsID AutoNumber
Terms Text 50
The primary key is TermsID
' Generic subroutine to add an entry to a table.
============================================================================
Sub ThingNotInList(pThingTableName As String, pThingFieldName As String,
pThingDesc As String, pNewThingName As String, pResponse As Integer)
' Arguments:
' pThingTableName => The name of the table into which the new record will
be inserted
' pThingFieldName => The name of the field in pThingTableName into which
the new data will be inserted
' pThingDesc => A text description of the data that is being handled:
"Employee Name", "Author", "Date of Birth", etc. This text will appear in the
message box that prompts the user to add pThingFieldName to pThingTableName
' pNewThingName => The new data that will be inserted into the
pThingFieldName field. It's the info that the user typed into the list box onthe
form.
' pResponse => The user's decision: Add the data or not: DATA_ERRADDED
or DATA_ERRCONTINUE
' Bill Nicholson: nic...@iglou.com
Dim status As Integer
Dim mydb As Database
Dim thingRecordset As Recordset
Dim thingField As Field
Dim thingTblDef As TableDef
Dim thingSize As Long
status = MsgBox("The " + pThingDesc + " that you entered is not on file." +
vbNewLine + " Do you want to add it?", 4 + 32, "")
If status = 6 Then ' User clicked "Yes" - he wants to add the Thing
Set mydb = CurrentDb
' Look up the field size
Set thingTblDef = mydb.TableDefs(pThingTableName)
With thingTblDef
For Each thingField In .Fields
If thingField.Name = pThingFieldName Then
thingSize = thingField.Size
Exit For
End If
Next thingField
End With
Set thingRecordset = mydb.OpenRecordset(pThingTableName)
thingRecordset.AddNew
thingRecordset(pThingFieldName) = Mid$(pNewThingName, 1, thingSize)
thingRecordset.Update
thingRecordset.Move 0, thingRecordset.LastModified ' Go to new record
thingRecordset.Close
mydb.Close
pResponse = DATA_ERRADDED ' Access will add the data to the combo
box
Else
pResponse = DATA_ERRCONTINUE ' User clicked "No" - he does not want
to add the Thing
End If
End Sub
===================================================================