Auto Insert for primary key with reset

B

Bassel

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
 
O

Ofer

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
 
B

Bassel

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.
 
O

Ofer

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?
 
B

Bassel

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
 
O

Ofer

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?
 
B

Bassel

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
 
O

Ofer

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

Try this
Me.TestID = Nz(DMax("TestId", "Tests", "PN =" & Me.PN), 0) + 1
 
B

Bassel

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...
 
Top