Dmax Problem

[richard]

Hi

I have a table for sub jobs and I want to increment the sub job number
starting at 1 for each main job number. However the code below just adds one
to the last sub job number, therefore my "criteria clause" in the DMAX
statement isn't working. Could someone take a look and tell me why.
Sorry about the long form names, they did seem a good idea at the time!!!!

Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![SubJobNumber] =
Nz(DMax("[SubJobNumber]", "tblAssistFMSubJobNumbers"),
"Forms!frmnapswork![jobnumber]=Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![JobNumber]") + 1


Thanks

Richard

 

[Allen Browne]

At the time you run this, the JobNumber may not have been populated in the
subform yet. The code therefore needs to read the job number from the parent
form. Of course if there is no job number in the parent form (because it's
at a new record), we need to block the entry in the subform.

Set the subform's BeforeInsert property to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

The code will need to be something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "JobNumber = " & !JobNumber
Me.SubJobNumber = Nz(DMax("SubJobNumber", _
"tblAssistFMSubJobNumbers", strWhere), 0) + 1
End If
End With
End Sub

 

[richard]

Allen

Thanks for the answer.

1 I have made the user save the main job before allowing them the option
of creating sub jobs so that the Job Number in the main form already exists
2 I have copied the code you supplied and each sub job is getting the
number 1.
Any thoughts
Richard

At the time you run this, the JobNumber may not have been populated in the
subform yet. The code therefore needs to read the job number from the parent
form. Of course if there is no job number in the parent form (because it's
at a new record), we need to block the entry in the subform.

Set the subform's BeforeInsert property to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

The code will need to be something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "JobNumber = " & !JobNumber
Me.SubJobNumber = Nz(DMax("SubJobNumber", _
"tblAssistFMSubJobNumbers", strWhere), 0) + 1
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table for sub jobs and I want to increment the sub job number
starting at 1 for each main job number. However the code below just adds
one
to the last sub job number, therefore my "criteria clause" in the DMAX
statement isn't working. Could someone take a look and tell me why.
Sorry about the long form names, they did seem a good idea at the time!!!!

Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![SubJobNumber] =
Nz(DMax("[SubJobNumber]", "tblAssistFMSubJobNumbers"),
"Forms!frmnapswork![jobnumber]=Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![JobNumber]")
+ 1


Thanks

Richard

 

[Allen Browne]

This suggests that the DMax() is not finding any records for the foreign
key value.

To debug it, comment out any error handling and see if an error is occuring.
Add the line:
Stop
in the code. When it runs and stops there, open the Immediate Window
(Ctrl+G) and debug what's going on.

You may need to change the field or table names in the code I suggested.

Allen

Thanks for the answer.

1 I have made the user save the main job before allowing them the
option
of creating sub jobs so that the Job Number in the main form already
exists
2 I have copied the code you supplied and each sub job is getting the
number 1.
Any thoughts
Richard

At the time you run this, the JobNumber may not have been populated in
the
subform yet. The code therefore needs to read the job number from the
parent
form. Of course if there is no job number in the parent form (because
it's
at a new record), we need to block the entry in the subform.

Set the subform's BeforeInsert property to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

The code will need to be something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "JobNumber = " & !JobNumber
Me.SubJobNumber = Nz(DMax("SubJobNumber", _
"tblAssistFMSubJobNumbers", strWhere), 0) + 1
End If
End With
End Sub

I have a table for sub jobs and I want to increment the sub job number
starting at 1 for each main job number. However the code below just
adds
one
to the last sub job number, therefore my "criteria clause" in the DMAX
statement isn't working. Could someone take a look and tell me why.
Sorry about the long form names, they did seem a good idea at the
time!!!!


Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![SubJobNumber] =
Nz(DMax("[SubJobNumber]", "tblAssistFMSubJobNumbers"),
"Forms!frmnapswork![jobnumber]=Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![JobNumber]")
+ 1

 

[richard]

Allen

OK here are my further thoughts

The reason the foreign key is not working with the DMAX is that I am not
selecting the record in the table using the parent JobNumber. The field
JobNumber is the relationship between the two tables and I suggest the
criteria Strwhere is not finding the record in tblAssistFMSubJobNumbers to
filter before using the DMAX.
Have you any further thoughts based upon what I have said above.
I had no error handling in and the code gives no errors.

Richard

This suggests that the DMax() is not finding any records for the foreign
key value.

To debug it, comment out any error handling and see if an error is occuring.
Add the line:
Stop
in the code. When it runs and stops there, open the Immediate Window
(Ctrl+G) and debug what's going on.

You may need to change the field or table names in the code I suggested.

Allen

Thanks for the answer.

1 I have made the user save the main job before allowing them the
option
of creating sub jobs so that the Job Number in the main form already
exists
2 I have copied the code you supplied and each sub job is getting the
number 1.
Any thoughts
Richard

At the time you run this, the JobNumber may not have been populated in
the
subform yet. The code therefore needs to read the job number from the
parent
form. Of course if there is no job number in the parent form (because
it's
at a new record), we need to block the entry in the subform.

Set the subform's BeforeInsert property to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

The code will need to be something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "JobNumber = " & !JobNumber
Me.SubJobNumber = Nz(DMax("SubJobNumber", _
"tblAssistFMSubJobNumbers", strWhere), 0) + 1
End If
End With
End Sub


I have a table for sub jobs and I want to increment the sub job number
starting at 1 for each main job number. However the code below just
adds
one
to the last sub job number, therefore my "criteria clause" in the DMAX
statement isn't working. Could someone take a look and tell me why.
Sorry about the long form names, they did seem a good idea at the
time!!!!


Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![SubJobNumber] =
Nz(DMax("[SubJobNumber]", "tblAssistFMSubJobNumbers"),
"Forms!frmnapswork![jobnumber]=Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![JobNumber]")
+ 1

 

[Allen Browne]

The normal way to set up a form and subform is that the main form is bound
to the primary table, and the subform is bound to the related table. The
LinkMasterFields refers to the main table's primary key field. The
LinkChildFields refers to the matching field (the foreign key) in the
related table.

If you set your forms up that way, you can read the value of the main
table's primary key from the parent form. You can use that value in the
criteria of the DMax() expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

OK here are my further thoughts

The reason the foreign key is not working with the DMAX is that I am not
selecting the record in the table using the parent JobNumber. The field
JobNumber is the relationship between the two tables and I suggest the
criteria Strwhere is not finding the record in tblAssistFMSubJobNumbers to
filter before using the DMAX.
Have you any further thoughts based upon what I have said above.
I had no error handling in and the code gives no errors.

Richard

This suggests that the DMax() is not finding any records for the foreign
key value.

To debug it, comment out any error handling and see if an error is
occuring.
Add the line:
Stop
in the code. When it runs and stops there, open the Immediate Window
(Ctrl+G) and debug what's going on.

You may need to change the field or table names in the code I suggested.

Allen

Thanks for the answer.

1 I have made the user save the main job before allowing them the
option
of creating sub jobs so that the Job Number in the main form already
exists
2 I have copied the code you supplied and each sub job is getting
the
number 1.
Any thoughts
Richard

:

At the time you run this, the JobNumber may not have been populated in
the
subform yet. The code therefore needs to read the job number from the
parent
form. Of course if there is no job number in the parent form (because
it's
at a new record), we need to block the entry in the subform.

Set the subform's BeforeInsert property to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

The code will need to be something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "JobNumber = " & !JobNumber
Me.SubJobNumber = Nz(DMax("SubJobNumber", _
"tblAssistFMSubJobNumbers", strWhere), 0) + 1
End If
End With
End Sub


I have a table for sub jobs and I want to increment the sub job
number
starting at 1 for each main job number. However the code below just
adds
one
to the last sub job number, therefore my "criteria clause" in the
DMAX
statement isn't working. Could someone take a look and tell me why.
Sorry about the long form names, they did seem a good idea at the
time!!!!


Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![SubJobNumber]
=
Nz(DMax("[SubJobNumber]", "tblAssistFMSubJobNumbers"),
"Forms!frmnapswork![jobnumber]=Forms!frmnapswork!frmAssistFMSubJobNumbersSubform.Form![JobNumber]")
+ 1