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]

1. Private Sub Form_BeforeInsert(Cancel As Integer)
this is happening during the before insert event of the form. Basically before the record on the form is saved

2.Forms!ECNBCNVIPfrm.Seq
There is a form named ECNBCNVIPfrm and it has a bound field called "Seq" or possibly a control called "Seq"

3. Forms!ECNBCNVIPfrm.Seq + 1
Add one to the "Seq" field to the value currently in the "Seq" field

4. Me.Seq = Forms!ECNBCNVIPfrm.Seq
On the current form where this code resides, set its "Seq" field equal to the value of the "Seq" field on the form "ECNBCNVIPfrm"

End Sub

Seems kind of clunky. Is it working the way you want?

 

[netrusher]

Well I still do not quite understand it. Basically what
we are doing is entering part numbers on a form (ECNPartsfrm).
This is tied to a table (ECNPartstbl). There is a
(Seq) Field in this table. If 5 parts are entered on the
form they appear in the table and the (Seq) field will
have numbers 1-5.

What I am trying to accomplish is:
If I enter 10 Part numbers on the form, I would like for
the Seq field to show numbers 1-10. Then I could change
the numbers and sort on the Seq field. I had posted
another post requesting info on how to accomplish this but
had no takers. I really need the ability to rearrange
the part numbers after they are entered on the form. I
can accomplish this by putting a number in the (Seq) field
and then sorting but I was really wanting to have the
numbers go into the (Seq) field automatically. I just
do not know how or when to make that happen. I am open
to any suggestions you might have and I certainly appreciate
your help.

 

[MajP]

Private Sub Form_BeforeUpdate(Cancel As Integer)
  MsgBox Me.NewRecord
  If Me.NewRecord Then
    Me.seq = DMax("seq", "ECNPartstbl") + 1
  End If
End Sub

If I come back to this form and enter another 10 numbers do they get ordered 1-10 or do I pickup where I left off.

 

[netrusher]

Good Point!

It would be best if they picked up where they left off but not necessarily a must!

 

[MajP]

The above code should do that.

 

[netrusher]

I will give it a try.

and thanks for all of the help you have provided!!!

 

[netrusher]

MajP,

It is working sort of. It is picking up numbers in the
1000's. I need it to start over at one for each group
of Part numbers I enter. Each Group of Part numbers will
be associated with a ECN Number that is unique to the
table.

What we are doing is inputting ECN Numbers*unique). With each
ECN Number there will be Part Numbers. The Part Numbers
are input using the ECNPartsfrm2 that is a page of the
BEREAECNBCNVIPfrm. I need Part Numbers entered for each
ECN Number to start at 1. I think your code is finding
the Highes Seq Number and starting with that (DMax).

I think we are on the right track though.

 

[MajP]

Easy fix, so I need you to tell the below
Would need to know the field name for the ECN number field.
Is this in a form - subform setup?

Likely the code needs to look like
Dmax("seq", "ECNPartstbl","ECNnumber = "& me.ECNnumber)+ 1

where "ECNnumber" is the name of the Field for ECN number and me.ECNnumber is the number of the ECN that you are working.

 

[netrusher]

ECNBCNVIP ID is the name of the field where the ECN number
is stored.

 

[MajP]

Try this

Dmax("seq", "ECNPartstbl","[ECNBCNVIP ID] = "& me.[ECNBCNVIP ID])+ 1

This assumes that in table ECNPartstbl you have a field [ECNBCNVIP ID]. Also on your form there is a bound field [ECNBCNVIP ID] that stores the current ECN ID now that might be a problem because likely that field is on a main form if you have a Form - Sub Form design. If This code is in the subform and you have the ECN ID on the main form you will likely need something like modify the above to
[ECNBCNVIP ID] = "& me.parent.[ECNBCNVIP ID]

 

[PHV]

And I'd use the Nz function:
Nz(DMax("seq", "ECNPartstbl", "[ECNBCNVIP ID] = " & Me![ECNBCNVIP ID]), 0) + 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

I have put the below code in the BeforeInsert Event of
the ECNPartsfrm2.

When I Paste in the Part number records I get a popup box
that says -1 When I click ok I get the following:

Run-time error 3075:
Syntax error (missing operator) in query expressions
'[ECNBCNVIP ID]' ='.

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

 

[netrusher]

I tried the below code also. I pasted 3 parts onto the form.
I got 3 popup boxes with -1. I clicked ok and the
parts were there but the seq number was still up in the
6,000.

  MsgBox Me.NewRecord
  If Me.NewRecord Then
    Me.Seq = Nz(DMax("seq", "ECNPartstbl", [ECNBCNVIP ID] = "& me.parent.[ECNBCNVIP ID]"), 0) + 1
  End If

 

[netrusher]

I also tried this:

  MsgBox Me.NewRecord
  If Me.NewRecord Then
    Me.Seq = DMax("seq", "ECNPartstbl", "[ECNBCNVIP ID] = " & Me.Parent.[ECNBCNVIP ID]) + 1

  End If

I still get the -1 popup-when I click ok for all 3
popups the parts are in the table with the same (Seq) number.
In this case it is 6030 for all 3 part numbers I added.

 

[PHV]

What is the popup saying ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

-1

 

[PHV]

No title ?

 

[netrusher]

Just says
Microsoft Office Access

-1

 

[MajP]

my fault I left this in while I was testing
MsgBox Me.NewRecord
To double check if I was on a new record.
The -1 means it True (a new record)
please delete.