Auto Insert for primary key with reset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with two primary keys PN & TestID.
Using a form, can the user enter the PN value and the TestID value is
assigned auto. starting from 1. Also it must be reseted for new PN value.
Thus it may look like this
PN TestId
222 1
222 2
223 1
Best Regards
 
Try this

Me.TestIdFieldName = nz(dmax("TestId","Table Name","PN =" &
me.PNFieldName),1) ' If Pn is a number

Me.TestIdFieldName = nz(dmax("TestId","Table Name","PN ='" & me.PNFieldName
& "'"),1) ' If Pn is a string

' Put this code on the after update event of the PN Field
 
Thank you for your reply.
It worked fine for new PN values. But if PN is repeated it also insert 1 to
the TestID.
Example PN TestId
1 1
2 1
1 1-------This should be two.
Can you help me with this.
Best Regards.
 
So I can help you, please post
1. the dmax you wrote
2. the name of the table
3. the name of the pn field in the table
4. the name of the pn field on the form
5. the name of the TestId field in the table
did you put the code in the after update event of the pn field?
 
Thank you for your help
1. the dmax is Me.TestID = Nz(DMax("TestId", "Tests", "PN =" & Me.PN), 1)
2. the name of the table is Tests
3. the name of the pn field in the table is PN
4. the name of the pn field on the form is PN
5. the name of the TestId field in the table TestID
I did put the code in the after update event of the Pn field.

Best regards
 
It's look fine, two more questions
1. Is the subform bounded to Tests table, and not to another table?
2. What is the datatype of PN as TestId, are they both number?
 
1- The form is bounded to tests table
2- Datatype of both fields is number.
I think the expression just evaluate if testId is null then insert 1.
It doesn't add 1 if testId is not null for same PN
Both are numbers
 
You are so right, I can't believe I forgot that

Try this
Me.TestID = Nz(DMax("TestId", "Tests", "PN =" & Me.PN), 0) + 1
 
Thank you It is working fine.

Ofer said:
You are so right, I can't believe I forgot that

Try this
Me.TestID = Nz(DMax("TestId", "Tests", "PN =" & Me.PN), 0) + 1
Click to expand...
 
Back
Top