Numbering using dmax problem

[steve ferry]

Problem Multi-level numbering.

I consider myself a moderate user of access a little out of my depth here I hope someone can help.

I have a multi level numbering system based on the Dmax function as follows:

Me!SEQEL = Nz(DMax("[SEQEL]", "[Task_Analysis_Elements]", "[Related Job Position TA] = '" & RJPT & "' AND [Related Duty Area TA] = '" & RDAT & "' AND [Related SEQ TA] = '" & RST & "'")) + 1

My counter is SEQEL.

The table referencing is Task_Analysis_Elements.

The Criterea: "[Related Job Position TA] = '" & RJPT & "' AND [Related Duty Area TA] = '" & RDAT & "' AND str$([Related SEQ TA]) = '" & RST &"'".

The above checks criteria in three fields.

The trouble arises in the Dmax function that the criteria is a string expression.

[Related Job Position TA] and [Related Duty Area TA] are both strings in there respective tables whilst ([Related SEQ TA]) is a Number and has to remain so.

So I thought okay I'd use the string function "str$" to tell access to get the value from the respective table and treat it as a string.

Problem is it not working my counter SEQEL will not go beyond 1.

Am I missing something fundamental?

The code is and after_update event procedure based on a required field on the form.

thanks

Steve Ferry

 

[steve ferry]

Thanks for the advice Pat unfortunately its not the answer SEQEL still not getting above 1.

Been suggested to me I have to go down the rout of unbound forms & have code running on it but this leads me up another avenue of submitting data, when and where, also Editing and deleting data really needs to be defined.

This is a path I'm ill equipped to go down, oh we'll thats my lot I'll have to deal with it.

 

[steve ferry]

thanks Pat but problem sorted. I was just looking throught the object browser when I came across Cstr.

I replaced str$([Related SEQ TA]) with Cstr([Related SEQ TA])

apparentntly Cstr is what I should of used as it converts numeric to strings wereas str$ converts numeric to Variant (strings).

to be honest I had to look up what variant (strings) are, and I have never used them nor can I think of an occasion I would.

But it works.

Thanks for your time

Steve Ferry

 

[steve ferry]

strings & variant(strings)

thanks Pat but problem sorted. I was just looking throught the object browser when I came across Cstr.

I replaced str$([Related SEQ TA]) with Cstr([Related SEQ TA])

apparentntly Cstr is what I should of used as it converts numeric to strings wereas str$ converts numeric to Variant (strings).

to be honest I had to look up what variant (strings) are, and I have never used them nor can I think of an occasion I would.

But it works.

Thanks for your time

Steve Ferry