Numbering subrecords

H

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
Click to expand...

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
Click to expand...
Click to expand...


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

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



Hydra said:
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
Click to expand...

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
Click to expand...
Click to expand...


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?
Click to expand...
 
H

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



Dale Fye said:
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



Hydra said:
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
Click to expand...

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
Click to expand...


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?
Click to expand...
Click to expand...
 
H

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

Dale Fye said:
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



Hydra said:
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
Click to expand...

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
Click to expand...


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?
Click to expand...
Click to expand...
 
D

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



Hydra said:
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

Dale Fye said:
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



Hydra said:
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?
Click to expand...
Click to expand...
Click to expand...
 
H

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.

Dale Fye said:
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



Hydra said:
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

Dale Fye said:
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?
Click to expand...
Click to expand...
Click to expand...
 
H

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.

?????

Dale Fye said:
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



Hydra said:
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

Dale Fye said:
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?
Click to expand...
Click to expand...
Click to expand...
 
H

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.

Dale Fye said:
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



Hydra said:
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

Dale Fye said:
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?
Click to expand...
Click to expand...
Click to expand...
 
J

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
 
H

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.



John Spencer said:
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
Click to expand...
Click to expand...
 
H

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))

John Spencer said:
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
Click to expand...
Click to expand...
 
J

John W. Vinson

It must be something to do with the And portion.
Click to expand...

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))
Click to expand...

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
 
H

Hydra

Aaarrrggghhh.

Thank You.

John W. Vinson said:
It must be something to do with the And portion.
Click to expand...

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))
Click to expand...

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Deleting records 2
Auto Numbering...Default Value: =DMax("[Task#]","[QryTask#]")+1 4
Help with this code please. 2
My Dates are Zeros 3
Going to end of field prob. SelStart?? 1
Sequentially Increment a Text Field Based on Select Criteria 11
Decimal point dissappears on form 0
Link Forms with multiple fields 2

Top