Numbering subrecords

[Hydra]

From a previous question:

Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

I tried your code but when I substituted my table and field names it threw a
type mismatch.

So I stuck in msgBoxes to check all the values. (with My fild and table names)

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _

"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1

Throws a type mismatch. All of the ID fields in the table are number fields.

I had a different version of the same basic approach, and it also threw a
type mismatch.

Any suggestions?

 

[Dale Fye]

In your DMAX function you used:

"me!level2ID = " & Me!Level2ID

as part of the critieria. I think this should read:

"[Level2ID] = " & Me!Level2ID

If that doesn't fix it, then try providing a default value for the NZ
function. Have not had this problem recently, but a while back I ran into a
couple of instances where the NZ( ) function did not return a zero when I
left it off. So I started putting it in every time I use the NZ( ) function:

Me!Level3ID = NZ(DMax("[Level3ID]",
"[Table3]",
"[Level1ID] = " & Me![Level1ID] " AND " & _
"[Level2ID] = " & Me!Level2ID), 0) + 1


HTH
Dale
--
----
HTH
Dale

From a previous question:

Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

I tried your code but when I substituted my table and field names it threw a
type mismatch.

So I stuck in msgBoxes to check all the values. (with My fild and table names)

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _

"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1

Throws a type mismatch. All of the ID fields in the table are number fields.

I had a different version of the same basic approach, and it also threw a
type mismatch.

Any suggestions?

 

[Hydra]

In my actual code I have the square brackets because the field names are two
words.

You are saying it should be Nz(Dmax( StuffGoesHere),0) ???

In your DMAX function you used:

"me!level2ID = " & Me!Level2ID

as part of the critieria. I think this should read:

"[Level2ID] = " & Me!Level2ID

If that doesn't fix it, then try providing a default value for the NZ
function. Have not had this problem recently, but a while back I ran into a
couple of instances where the NZ( ) function did not return a zero when I
left it off. So I started putting it in every time I use the NZ( ) function:

Me!Level3ID = NZ(DMax("[Level3ID]",
"[Table3]",
"[Level1ID] = " & Me![Level1ID] " AND " & _
"[Level2ID] = " & Me!Level2ID), 0) + 1


HTH
Dale
--
----
HTH
Dale

From a previous question:

Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

I tried your code but when I substituted my table and field names it threw a
type mismatch.

So I stuck in msgBoxes to check all the values. (with My fild and table names)

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _

"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1

Throws a type mismatch. All of the ID fields in the table are number fields.

I had a different version of the same basic approach, and it also threw a
type mismatch.

Any suggestions?

 

[Hydra]

Still throws an error. Actual code is:


Me![Criteria Number] = Nz(DMax("[Criteria Number]", "[Criteria Table]",
"[Major Event Key] = " & Me![Major Event Key] And "Me![SA Key] = " & Me![SA
Key]), 0) + 1

In your DMAX function you used:

"me!level2ID = " & Me!Level2ID

as part of the critieria. I think this should read:

"[Level2ID] = " & Me!Level2ID

If that doesn't fix it, then try providing a default value for the NZ
function. Have not had this problem recently, but a while back I ran into a
couple of instances where the NZ( ) function did not return a zero when I
left it off. So I started putting it in every time I use the NZ( ) function:

Me!Level3ID = NZ(DMax("[Level3ID]",
"[Table3]",
"[Level1ID] = " & Me![Level1ID] " AND " & _
"[Level2ID] = " & Me!Level2ID), 0) + 1


HTH
Dale
--
----
HTH
Dale

From a previous question:

Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

I tried your code but when I substituted my table and field names it threw a
type mismatch.

So I stuck in msgBoxes to check all the values. (with My fild and table names)

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _

"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1

Throws a type mismatch. All of the ID fields in the table are number fields.

I had a different version of the same basic approach, and it also threw a
type mismatch.

Any suggestions?

 

[Dale Fye]

The type mismatch error inclines me to believe that:

1. one of the fields ([Criteria Number], [Major Event Key], [SA Key]) is
not numeric

or

2. one of the fields contains a NULL

Before running this, have you tested to see what the value of Me![Major
Event Key] and Me![SA Key] are?

I would put a breakpoint on that line and step through my code to see what
is happening on that line.

----
HTH
Dale

Still throws an error. Actual code is:


Me![Criteria Number] = Nz(DMax("[Criteria Number]", "[Criteria Table]",
"[Major Event Key] = " & Me![Major Event Key] And "Me![SA Key] = " & Me![SA
Key]), 0) + 1

In your DMAX function you used:

"me!level2ID = " & Me!Level2ID

as part of the critieria. I think this should read:

"[Level2ID] = " & Me!Level2ID

If that doesn't fix it, then try providing a default value for the NZ
function. Have not had this problem recently, but a while back I ran into a
couple of instances where the NZ( ) function did not return a zero when I
left it off. So I started putting it in every time I use the NZ( ) function:

Me!Level3ID = NZ(DMax("[Level3ID]",
"[Table3]",
"[Level1ID] = " & Me![Level1ID] " AND " & _
"[Level2ID] = " & Me!Level2ID), 0) + 1


HTH
Dale
--
----
HTH
Dale

From a previous question:

Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

I tried your code but when I substituted my table and field names it threw a
type mismatch.

So I stuck in msgBoxes to check all the values. (with My fild and table names)

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1


Throws a type mismatch. All of the ID fields in the table are number fields.

I had a different version of the same basic approach, and it also threw a
type mismatch.

Any suggestions?

 

[Hydra]

That was my bet, too.

All of the fields are (temporarily) tested with a msgbox as shown below, and
all return the correct value. All of the key fields are number fields in the
table definition.

The type mismatch error inclines me to believe that:

1. one of the fields ([Criteria Number], [Major Event Key], [SA Key]) is
not numeric

or

2. one of the fields contains a NULL

Before running this, have you tested to see what the value of Me![Major
Event Key] and Me![SA Key] are?

I would put a breakpoint on that line and step through my code to see what
is happening on that line.

----
HTH
Dale

Still throws an error. Actual code is:


Me![Criteria Number] = Nz(DMax("[Criteria Number]", "[Criteria Table]",
"[Major Event Key] = " & Me![Major Event Key] And "Me![SA Key] = " & Me![SA
Key]), 0) + 1

In your DMAX function you used:

"me!level2ID = " & Me!Level2ID

as part of the critieria. I think this should read:

"[Level2ID] = " & Me!Level2ID

If that doesn't fix it, then try providing a default value for the NZ
function. Have not had this problem recently, but a while back I ran into a
couple of instances where the NZ( ) function did not return a zero when I
left it off. So I started putting it in every time I use the NZ( ) function:

Me!Level3ID = NZ(DMax("[Level3ID]",
"[Table3]",
"[Level1ID] = " & Me![Level1ID] " AND " & _
"[Level2ID] = " & Me!Level2ID), 0) + 1


HTH
Dale
--
----
HTH
Dale



:

From a previous question:

Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

I tried your code but when I substituted my table and field names it threw a
type mismatch.

So I stuck in msgBoxes to check all the values. (with My fild and table names)

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1


Throws a type mismatch. All of the ID fields in the table are number fields.

I had a different version of the same basic approach, and it also threw a
type mismatch.

Any suggestions?

 

[Hydra]

Maybe something in my execution? I enter the title field in the Criteria
Table to enter a new criteria. As soon as I start to type the code executes
and throws a type mismatch.

but the code runs against Form_BeforeInsert(Cancel As Integer) and
has nothing to do with the title field.

?????

The type mismatch error inclines me to believe that:

1. one of the fields ([Criteria Number], [Major Event Key], [SA Key]) is
not numeric

or

2. one of the fields contains a NULL

Before running this, have you tested to see what the value of Me![Major
Event Key] and Me![SA Key] are?

I would put a breakpoint on that line and step through my code to see what
is happening on that line.

----
HTH
Dale

Still throws an error. Actual code is:


Me![Criteria Number] = Nz(DMax("[Criteria Number]", "[Criteria Table]",
"[Major Event Key] = " & Me![Major Event Key] And "Me![SA Key] = " & Me![SA
Key]), 0) + 1

In your DMAX function you used:

"me!level2ID = " & Me!Level2ID

as part of the critieria. I think this should read:

"[Level2ID] = " & Me!Level2ID

If that doesn't fix it, then try providing a default value for the NZ
function. Have not had this problem recently, but a while back I ran into a
couple of instances where the NZ( ) function did not return a zero when I
left it off. So I started putting it in every time I use the NZ( ) function:

Me!Level3ID = NZ(DMax("[Level3ID]",
"[Table3]",
"[Level1ID] = " & Me![Level1ID] " AND " & _
"[Level2ID] = " & Me!Level2ID), 0) + 1


HTH
Dale
--
----
HTH
Dale



:

From a previous question:

Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

I tried your code but when I substituted my table and field names it threw a
type mismatch.

So I stuck in msgBoxes to check all the values. (with My fild and table names)

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1


Throws a type mismatch. All of the ID fields in the table are number fields.

I had a different version of the same basic approach, and it also threw a
type mismatch.

Any suggestions?

 

[Hydra]

Except for the msgboxes, which report back the correct values, there is only
one line of code. how do I step through that?

I cut and pasted the contents of the msgbox parens directly from the line
that does not work.

The type mismatch error inclines me to believe that:

1. one of the fields ([Criteria Number], [Major Event Key], [SA Key]) is
not numeric

or

2. one of the fields contains a NULL

Before running this, have you tested to see what the value of Me![Major
Event Key] and Me![SA Key] are?

I would put a breakpoint on that line and step through my code to see what
is happening on that line.

----
HTH
Dale

Still throws an error. Actual code is:


Me![Criteria Number] = Nz(DMax("[Criteria Number]", "[Criteria Table]",
"[Major Event Key] = " & Me![Major Event Key] And "Me![SA Key] = " & Me![SA
Key]), 0) + 1

In your DMAX function you used:

"me!level2ID = " & Me!Level2ID

as part of the critieria. I think this should read:

"[Level2ID] = " & Me!Level2ID

If that doesn't fix it, then try providing a default value for the NZ
function. Have not had this problem recently, but a while back I ran into a
couple of instances where the NZ( ) function did not return a zero when I
left it off. So I started putting it in every time I use the NZ( ) function:

Me!Level3ID = NZ(DMax("[Level3ID]",
"[Table3]",
"[Level1ID] = " & Me![Level1ID] " AND " & _
"[Level2ID] = " & Me!Level2ID), 0) + 1


HTH
Dale
--
----
HTH
Dale



:

From a previous question:

Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

I tried your code but when I substituted my table and field names it threw a
type mismatch.

So I stuck in msgBoxes to check all the values. (with My fild and table names)

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1


Throws a type mismatch. All of the ID fields in the table are number fields.

I had a different version of the same basic approach, and it also threw a
type mismatch.

Any suggestions?

 

[John Spencer]

Assumption: Major Event Key and SA Key are number fields.

Me![Criteria Number] = Nz(DMax("[Criteria Number]", "[Criteria Table]",
"[Major Event Key] = " & Me![Major Event Key] & " And [SA Key] = " & Me![SA
Key]), 0) + 1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Hydra]

Yes, and double verified. All three key fields are identified as number
fields in the Criteria Table (and obviously in the other tables).

Except the Major Event Key in the primary table is an autonumber. don't see
why that would matter since it is not involved in this code.

Assumption: Major Event Key and SA Key are number fields.

Me![Criteria Number] = Nz(DMax("[Criteria Number]", "[Criteria Table]",
"[Major Event Key] = " & Me![Major Event Key] & " And [SA Key] = " & Me![SA
Key]), 0) + 1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Except for the msgboxes, which report back the correct values, there is only
one line of code. how do I step through that?

I cut and pasted the contents of the msgbox parens directly from the line

 

[Hydra]

It must be something to do with the And portion.

If I run the following code with EITHER the major Event key part OR the SA
Key part it will run.

But when I put them both in it won't


MsgBox (Nz(DMax("[Criteria Number]", "[Significant Accomplishment
Criteria]", "[Major Event Key] = " & Me![Major Event Key] And "[SA Key] = " &
Me![SA Key]), 90))

Assumption: Major Event Key and SA Key are number fields.

Me![Criteria Number] = Nz(DMax("[Criteria Number]", "[Criteria Table]",
"[Major Event Key] = " & Me![Major Event Key] & " And [SA Key] = " & Me![SA
Key]), 0) + 1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Except for the msgboxes, which report back the correct values, there is only
one line of code. how do I step through that?

I cut and pasted the contents of the msgbox parens directly from the line

 

[John W. Vinson]

It must be something to do with the And portion.

It is. You've got the word AND *outside* the quoted text string. It needs to
be part of the text string (inside the quotes).

If I run the following code with EITHER the major Event key part OR the SA
Key part it will run.

But when I put them both in it won't


MsgBox (Nz(DMax("[Criteria Number]", "[Significant Accomplishment
Criteria]", "[Major Event Key] = " & Me![Major Event Key] And "[SA Key] = " &
Me![SA Key]), 90))

Try

MsgBox (Nz(DMax("[Criteria Number]", "[Significant Accomplishment
Criteria]", "[Major Event Key] = " & Me![Major Event Key] & " And [SA Key] = "
& Me![SA Key]), 90))

What you're doing here is using the & string concatenation operator to build
up a criteria string that's a valid SQL WHERE clause. You'll be stringing
together string constants and (number) values from your form:

"[Major Event Key] = "
&
Me![Major Event Key] (123 let's say)
&
" And [SA Key] = "
&
Me![SA Key] (345 maybe)

to get

[Major Event Key] = 123 And [SA Key] = 345

 

[Hydra]

Aaarrrggghhh.

Thank You.

It must be something to do with the And portion.

It is. You've got the word AND *outside* the quoted text string. It needs to
be part of the text string (inside the quotes).

If I run the following code with EITHER the major Event key part OR the SA
Key part it will run.

But when I put them both in it won't


MsgBox (Nz(DMax("[Criteria Number]", "[Significant Accomplishment
Criteria]", "[Major Event Key] = " & Me![Major Event Key] And "[SA Key] = " &
Me![SA Key]), 90))

Try

MsgBox (Nz(DMax("[Criteria Number]", "[Significant Accomplishment
Criteria]", "[Major Event Key] = " & Me![Major Event Key] & " And [SA Key] = "
& Me![SA Key]), 90))

What you're doing here is using the & string concatenation operator to build
up a criteria string that's a valid SQL WHERE clause. You'll be stringing
together string constants and (number) values from your form:

"[Major Event Key] = "
&
Me![Major Event Key] (123 let's say)
&
" And [SA Key] = "
&
Me![SA Key] (345 maybe)

to get

[Major Event Key] = 123 And [SA Key] = 345