Can anyone help me?

I am trying to develop a database based on the Contacts database in “Beginning VB6 Database”. Its purpose is to track children’s developmental progress through Nursery school or kindergarten, which here in the UK is broken down in to 383 steps. A child’s personal data is held in a table called tblPupils, whose Primary Key is Pupil_ID. The Steps are in another table, tblStepping_Stones with the Primary Key being SteppingStone_ID. These two tables are linked by a third tblAchieved, giving a Many-to-Many relationship between Pupils and Stepping Stones.

tblAchieved is very simple it has 4 fields: Pupil_ID and SteppingStone_ID, which together form the Primary Key. The other two fields are Achieved and Evidence, both of which are supposed to be updated by Combo boxes (cmbAchieved & cmbEvidence) in the database front end.

I have 2 problems, when I try to add a new child to the database, the personal data is added to tblPupils successfully, but I then get the following error message:

Run-time error ‘3022’:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship…

As a result no records for the new child are created in tblAchieved.

The other problem is that when I try to edit a child’s achievements, (which is the whole point of the database!), no changes are made in tblAchieved, although the program still runs and I get no error message.

I include the offending subroutine and add a last piece of information – both recordsets (rsChildTable and rsAchievedTable) have been declared public.

Thanks

Rob

Public Sub postChild()

Dim lNewChild_ID As Long
Dim iCount As Integer

Screen.MousePointer = vbHourglass
sbStatus.Panels.Item(2).Text = "Making changes...."

If (iCurrentState = NOW_ADDING) Then
     rsChildTable.AddNew
Else
  With rsChildTable
     .MoveFirst
     .Index = "PrimaryKey"
     .Seek "=", lCurrentChildKey
     If Not .NoMatch Then
       rsChildTable.Edit
     Else
      MsgBox ("Ohhhh Nooo")
     End If
   End With
End If

With rsChildTable
    mskUPN.PromptInclude = False
    If (Len(mskUPN)) Then !UPN = mskUPN
    mskUPN.PromptInclude = True
    If (Len(txtFirstName)) Then !FirstName = txtFirstName
    If (Len(txtMiddleInitial)) Then !MiddleInitial = _
    txtMiddleInitial
    If (Len(txtLastName)) Then !LastName = txtLastName
    If (Len(txtZip)) Then !HomeZip = txtZip
    mskBirthday.PromptInclude = False
    !Gender = cmbGender.Text
    If (Len(mskBirthday.Text) > 0) Then
      mskBirthday.PromptInclude = True
      !Birthday = mskBirthday
      lblBirthday = Format$(!Birthday, "dddd mmmm dd, yyyy")
    End If
    mskBirthday.PromptInclude = True
    mskUPN.PromptInclude = True
    .Update
End With

'*** code for saving achievements data ***

If (iCurrentState = NOW_ADDING) Then
    With rsChildTable
        .MoveLast
        .Index = "PrimaryKey"
        lNewChild_ID = !Child_ID
    End With
    With rsAchievedTable
        For iCount = 1 To 383
        .AddNew
        !Child_ID = lNewChild_ID
        !SteppingStone_ID = iCount
        !Achieved = cmbAchieved(iCount - 1).Text
        !Evidence = cmbEvidence(iCount - 1).Text
        .Update
        Next iCount
    End With

ElseIf (iCurrentState = NOW_EDITING) Then
With rsChildTable
     .MoveFirst
     .Index = "PrimaryKey"
     .Seek "=", lCurrentChildKey
     If Not .NoMatch Then
    With rsAchievedTable
        For iCount = 1 To 383
        .Edit
        !Achieved = cmbAchieved(iCount - 1).Text
        !Evidence = cmbEvidence(iCount - 1).Text
        .Update
        Next iCount
    End With

     Else
      MsgBox ("Ohhhh Nooo")
     End If
   End With

End If

DoEvents

If (iCurrentState = NOW_ADDING) Then
  Call initializeForm
Else
  iCurrentState = NOW_IDLE
  Call lockFields(True)
  Call updateForm
End If

sbStatus.Panels.Item(2).Text = "Ready."
Screen.MousePointer = vbDefault

End Sub

hi there..

i was trying to understand your problem...

where do you get the 3022 error??

adding where???

and another Q... why are you saving 383 positions from a combo box that only show one, and you save all of them??? i dont understand the logic behind that.. could you xplain a little more???

HTH

Gonzalo

I suspect that your 1st problem stems from having a field that is supposed to be the foreign key of a 1:many relationship has been established as a unique field. Of course, if there will be many, they cannot all be equal and unique at the same time...

While waiting for you to answer Gonzalo’s questions, a few tips.

I would recommend making the messages more specific. Rather than “("Ohhhh Nooo")”, have something like “"Failed to find an achievement record."” (Also MsgBox () is the message box function which returns a value; without the parnetheses is the message box statement, which displays the message box, but does not return a value. VB put a space after MsgBox, turning the parenthetical statement into an implicit type conversion, but your intent would be clearer without the parens.)

I would change      to      â€œNot No Match” is so awkward... Doesn’t change the behavior, but it is easier on the brain.

Where does the value of iCurrentState come from?
__________________

Bassace... Hmmm. Musician? Angler?

Thanks for the quick response guys.

Gonzalo, I didn't make it clear, but the front end is very complex - there are 383 cmbAchieved and 383 cmbEvidence on the form. These are next to the Stepping Stone descriptions, so when the user is making changes he/she will alter the cmbAchieved combo next to a statement AND the cmbEvidence for example:

STEPPING STONE
Responds to simple instructions cmbAchieved(124) cmbEvidence(124)
Listens to others in 1-1 or groups cmbAchieved(125) cmbEvidence(125)

I think that the 3022 error is generated in the With ... End With loop after *** code for saving achievements data ***

Brian, in answer to the last point - musician.

Point taken about the daft error message, but this is just for development and will be changed prior to use.

DO you think part of the problem would be solved by adding a new field - StepAchieved_ID as the primary key?

Thanks

Rob

i still dont get it.. you have 600 combo boxes on the form????

or you have 600 items divide by 2 combo boxes????

can you trace the code the get the exact line of the error, just to see wich table has problem with the key......

HTH

Gonzalo

I would have (as you do) a primary key in tblPupils. Pupil_ID is a good choice. This would be an autonumber field; I would leave the details entirely up to the machine. If you need a unique identifier for each pupil (such as Social Security Number) you should make a separate field for that. The field used to join tables in a relationship should never be anything that actually pertains to the subject of the record.
If you use data like that—data that pertain to the subject of the record—then if there is an error in the data it will be propogated to the records on the many side of the relationship.

In tblStepping_Stones I would have a primary key—SteppingStone_ID is fine, if somewhat lengthy. This, too, would be an autonumber field, letting the machine handle the details. This would be constrained to being unique. This would never be filled in through code; merely creating the record would fill it in.

In your junction table, tblAchieved, have one field which will hold a value from tblPupils (Pupil_ID, the unique machine-centric ID for a pupil), one field which will hold a value from tblStepping_Stones (SteppingStone_ID, the unique machine-centric ID for a stepping stone). These two fields, taken together, form the unique key in the junction table.

Obviously a record can be created in tblPupils without violating any unique constraints, because the machine handles the numbering. The same is true for tblStepping_Stones. (I presume the size of tblStepping_Stones is static though, there being one—and only one—entry for each possible stepping stone...)

Once there is a record for a particular student, and a stepping stone record representing a parameter being stored, a record can be created for that union-of-two-records in the achievment table. This will generate an error if there is already a record for that student-stone combination, but only under that specific circumstance.

Does this clarify for you where the problem might be arising?

You haven’t set this up so that, say, for John Smith there can be more than one entry for the category “Responds to simple instructions,” have you? Like that he can have a score of 125 for that parameter this week, but 142 next week? That would violate the unique key.

As for me: electric guitarist.

Sorry for the delay in answering your posts, guys. I lost the Internet yesterday!!

Gonzalo - it is a very complicated form with a lot of tabbed dialog controls and yes there are ove 700 combo boxes! An array of 383 cmbAchieved and an array of 383 cmbEvidence. A child's progress, as I pointed out in the first post, is broken down into 383 steps and a record of when each is achieved (Autumn, Spring, Summer) and any evidence (none, observation, photo, etc) is recorded using the combo boxes. This means that in tblAchieved records should look something like this:

Pupil_ID 1
SteppingStone_ID 1
Achieved Autumn
Evidence Observation

Pupil_ID 1
SteppingStone_ID 2
Achieved Autumn
Evidence Photo

Pupil_ID 1
SteppingStone_ID 3
Achieved Not achieved
Evidence None

The complexity of the database means that in a typical situation with 60 children, this table will have nearly 23000 records! As the Pupil_ID and SteppingStone_ID fields together form the Primary key, they are unique.

Brian - I didn't answer one of your earlier questions, iCurrentSate comes from making a choice of Adding a new record or Editing an existing one.

Thanks

Rob

hi there..

could you trace your code to see wich line is failing to update the recordset??

use something like this...

put an on error goto errors

put an exit sub rigth before the end sub

add this:

.

this will stop your code after the first error, and the resume will get you back to the line that throw it...

that way we can isolate the problem..

also, a control array has a max of 256 controls.. did you finish you form already??

are you sure there is no other way to achieved what are you looking for?? there is too much data to fill on a form ...

HTH

Gonzalo

Hi Gonzalo

I tried what you suggested to find the error line and it's picking up Stop in the Error: routine, not the last line it executed before generating the error.

Also, your point about a limit of 256 elements in a control array is not right - it will handle the 383 that I have.

I can understand that it is difficult to visualise the form, but it is not as frightening to the user as you might think. They would be using it several times a year for each child, probably only marking 10 or 20 stepping stone achievements at any one time, not all 383.

Have you any other suggestions as to how I could find the line of code generating the error?

Thanks again for taking an interest.

Rob

sorry for that..

once you reach the stop, trace the code with F8 to continue running your prog. line by line, it will reach the resume and then it will jump to the error line again...

and sorry. you where right.. 256 it's the control limits in a form.. the array could be up to 32767

HTH

Gonzalo