Do not understand code on a form 1

[netrusher]

Can anyone tell me what this code does and how it works?
I think it is adding a number in sequence for each part
number added to a form and putting the number in a table. I
am trying to understand how this works.

Private Sub Form_BeforeInsert(Cancel As Integer)    
        Forms!ECNBCNVIPfrm.Seq = Forms!ECNBCNVIPfrm.Seq + 1    
Me.Seq = Forms!ECNBCNVIPfrm.SeqEnd Sub

 

[MajP]

Ok try this

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.NewRecord Then
    Me.seq = Nz(DMax("seq", "ECNPartstbl","[ECNBCNVIP ID] = " & Me.Parent.[ECNBCNVIP ID]), 0) + 1
  End If
End Sub

for error checkking I have included two msgboxes. Tell me what error you get or message. If the first one fails quote it out and try the other. Without seeing your form I need to know how to get the current ECN ID. Is it on the main form or sub form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.NewRecord Then
  
   msgbox Me.Parent.[ECNBCNVIP ID]
   'msgbox me.[ECNBCNVIP ID]

   Me.seq = Nz(DMax("seq", "ECNPartstbl","[ECNBCNVIP ID] = " & Me.Parent.[ECNBCNVIP ID]), 0) + 1
  End If
End Sub

 

[netrusher]

MajP

Thanks for your help. Right now it is pasteing in the
records but I am getting nothing in the Seq field. It is
blank. I ried the code with both msgbox. No msgbox
came up. The ECNBCNVIP ID is on the Main Form

   If Me.NewRecord Then
  
   'MsgBox Me.Parent.[ECNBCNVIP ID]
   MsgBox Me.[ECNBCNVIP ID]

   Me.Seq = Nz(DMax("seq", "ECNPartstbl", "[ECNBCNVIP ID] = " & Me.Parent.[ECNBCNVIP ID]), 0) + 1
  End If

 

[netrusher]

Something weird seems to be going on. No matter
what code I use nothing is appearing in the SEQ field. Also,
even if I use the old code I do not even get the popup with
-1 like I was yesterday.?????

 

[MajP]

Check to make sure under properties that the event procedure still says [event procedure]

 

[netrusher]

MajP,

I had the code on BeforeUpdate instead of BeforeInsert.

Now I get three popups showing the Form number which is the
ECNBCNVIP ID number and each of the the Part numbers I enter
have the same SEQ number. I need for the SEQ number to be
different for each part number entered. I am copying and
pasteing the Partnumbers so they are being entered multiple
part numbers at the same time if that has any bearing.

I still think we are on the right track and close. Thanks
again for all of your help.

 

[netrusher]

MajP,

Below is teh code I just used.

Private Sub Form_BeforeInsert(Cancel As Integer)
   If Me.NewRecord Then
  
   MsgBox Me.Parent.[ECNBCNVIP ID]
   'MsgBox Me.[ECNBCNVIP ID]

   Me.Seq = Nz(DMax("seq", "ECNPartstbl", "[ECNBCNVIP ID] = " & Me.Parent.[ECNBCNVIP ID]), 0) + 1
  End If

End Sub

 

[MajP]

1)Try putting this code in the part number field before update instead of the form's before insert.
2)It has to do with the order of operation and without seeing how you are interfacing it is hard to figure out. But the solution is making sure the events happen in the correct order. This is what is happening: You paste the values, the code runs gets the max sequence at once for all pasted records. So the new Seq is not added before the other records pull a max sequence.

Need to force it to get the last sequence, increment the sequence, then pull the next one.

By doing number 1 this may do it. Does it work if you do one record at a time? Is it returning the top seqence for a given ECN or just the very top Sequence?

 

[netrusher]

MajP,

I get the same results when putting the code in the PartNumber
field.

If I paste one record at a time it goes in sequence. 1, 2, 3
etc. If I paste multiple records it gives each record the
same Seq number.

 

[MajP]

Can you explain what you are pasting and how you are doing the copy and paste? If everything else is working then it is just a matter of finding the correct event.
Pasting multiple records into a database is just not a typical interface. Normally this tells me the database is incorrectly designed or a more efficient interface is needed. I am sure we can make the current procedure work, but there may be an even better solution. Another question. Why do you need a sequence number? If you have an autonumber or time stamp you can display a sequence dynamically in a form or report.

 

[netrusher]

MajP,

I am copying records from an excel file and pasting them
into the form in access. The only way I can get the records
to paste into Access is from Excel File totally separate from
the database.

 

[netrusher]

I need a Seq Number so i can I can sort the records on the
form I am pasting them into. We pull up information by ECN
Number. The Part Numbers we paste into the form are for that
specific ECN. Sometimes we need to manipulate the order of
the PartNumbers after they have been pasted into the form. The
easy way to do that Is to change the Seq number of a PartNumber
and the re-sort it.

 

[netrusher]

MajP,

Are you still looking at this? If not, does anyone else out
there have any suggestions?