Run Time error when trying to use DMax on a line value

J

Jim Brooks

Please can someone help me with run time error I am getting when I try to use
a DMax function on a LineID control in a subform. I have a main form
frmDeliveries and a subform sfrmDeliveryDetails They are linked on master
and child by a DeliveryID control. On the sfrmDeliveryDetails BeforeUpdate I
entered :
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![LineID] = Nz(DMax("[LineID]", "[sfrmDeliveryDetails]"), 0) + 1
End Sub (which I modified from a previous posting)
When I try to enter a selection from a Productscombo on the
sfrmDeliveriesDetails I get a Run Time error ‘3078’ which says Microsoft Jet
Database cannot find the input table or query sfrmDeliveryDetails. Make sure
it exists and that the name is spelled correctly.
If I remove the Dmax sub then everything works correctly. Have I put the sub
in the correct place and is the syntax correct in the line Me![LineID] =
Nz(DMax("[LineID]", "[sfrmDeliveryDetails]"), 0) + 1 Can someone please
head me in the right direction as I am still learning my way round VBA in
Access.
Thanks in advance Jim Brooks
 
L

Linq Adams via AccessMonster.com

This is untested but I think

Me![LineID] = Nz(DMax("[LineID]", "[sfrmDeliveryDetails]"), 0) + 1

should be

Me![LineID] = Nz(DMax("[LineID]", "sfrmDeliveryDetails"), 0) + 1

without the square brackets around the table name.

Linq
 
J

Jim Brooks

Hi Linq tried your suggestion still getting the same run time error
jim brooks
 
D

Dirk Goldgar

Jim Brooks said:
Please can someone help me with run time error I am getting when I try to
use
a DMax function on a LineID control in a subform. I have a main form
frmDeliveries and a subform sfrmDeliveryDetails They are linked on master
and child by a DeliveryID control. On the sfrmDeliveryDetails
BeforeUpdate I
entered :
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![LineID] = Nz(DMax("[LineID]", "[sfrmDeliveryDetails]"), 0) + 1
End Sub (which I modified from a previous posting)
When I try to enter a selection from a Productscombo on the
sfrmDeliveriesDetails I get a Run Time error ‘3078’ which says Microsoft
Jet
Database cannot find the input table or query sfrmDeliveryDetails. Make
sure
it exists and that the name is spelled correctly.
If I remove the Dmax sub then everything works correctly. Have I put the
sub
in the correct place and is the syntax correct in the line Me![LineID] =
Nz(DMax("[LineID]", "[sfrmDeliveryDetails]"), 0) + 1 Can someone please
head me in the right direction as I am still learning my way round VBA in
Access.


From your description, "sfrmDeliveryDetails" is the name of a form, but it
doesn't sound like the name of a table. What is the name of the table that
sfrmDeliveryDetails is based on? Is it maybe "DeliveryDetails" or
"tblDeliveryDetails"? Your DMax expression has to look thinks up in the
*table*, not the form or subform.
 
J

Jim Brooks

Hi dirk I did what you suggested and it works. LineID now increments by 1 for
every line entry but what I did not put in my initial question was that for
every new DeliveryID (the primary key) I wanted the LineID to start at 1 and
increment so as to get Delivery 1 LineID 1, LineID 2, LineID 3
Delivery 2 LineID 1, LineID 2, LineID 3 etc
at the moment the Dmax function is looking for the largest number in LineID
field in the tblDeliveryDetails. Is there a way to look for a new record on
the tblDeliveryDetails and then fire the Dmax function starting at 1?
Sorry not to have explained this in the first question because I thought
this would happen when a new record was created.
thanks in advance
jim brooks


--
Don''''''''t put off till tomorrow something you could do today because if
you do it today and enjoy it you can do it again tomorrow


Dirk Goldgar said:
Jim Brooks said:
Please can someone help me with run time error I am getting when I try to
use
a DMax function on a LineID control in a subform. I have a main form
frmDeliveries and a subform sfrmDeliveryDetails They are linked on master
and child by a DeliveryID control. On the sfrmDeliveryDetails
BeforeUpdate I
entered :
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![LineID] = Nz(DMax("[LineID]", "[sfrmDeliveryDetails]"), 0) + 1
End Sub (which I modified from a previous posting)
When I try to enter a selection from a Productscombo on the
sfrmDeliveriesDetails I get a Run Time error ‘3078’ which says Microsoft
Jet
Database cannot find the input table or query sfrmDeliveryDetails. Make
sure
it exists and that the name is spelled correctly.
If I remove the Dmax sub then everything works correctly. Have I put the
sub
in the correct place and is the syntax correct in the line Me![LineID] =
Nz(DMax("[LineID]", "[sfrmDeliveryDetails]"), 0) + 1 Can someone please
head me in the right direction as I am still learning my way round VBA in
Access.


From your description, "sfrmDeliveryDetails" is the name of a form, but it
doesn't sound like the name of a table. What is the name of the table that
sfrmDeliveryDetails is based on? Is it maybe "DeliveryDetails" or
"tblDeliveryDetails"? Your DMax expression has to look thinks up in the
*table*, not the form or subform.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dirk Goldgar

Jim Brooks said:
Hi dirk I did what you suggested and it works. LineID now increments by 1
for
every line entry but what I did not put in my initial question was that
for
every new DeliveryID (the primary key) I wanted the LineID to start at 1
and
increment so as to get Delivery 1 LineID 1, LineID 2, LineID 3
Delivery 2 LineID 1, LineID 2, LineID 3 etc
at the moment the Dmax function is looking for the largest number in
LineID
field in the tblDeliveryDetails. Is there a way to look for a new record
on
the tblDeliveryDetails and then fire the Dmax function starting at 1?
Sorry not to have explained this in the first question because I thought
this would happen when a new record was created.

You just have to include the DeliveryID in a criterion specified for the
DMax function:

Me![LineID] = 1 + _
Nz(DMax("[LineID]", "tblDeliveryDetails", _
"DeliveryID = " & Me!DeliveryID), _
0)

That ought to work, if I've understood you correctly. Note that I
reorganized the statement slightly, for clarity and to avoid any confusion
caused by unwanted line breaks in the post.
 
J

Jim Brooks

Hi Dick works perfectly for what I wanted many thanks
Regards Jim Brooks
--
Don''''''''t put off till tomorrow something you could do today because if
you do it today and enjoy it you can do it again tomorrow


Dirk Goldgar said:
Jim Brooks said:
Hi dirk I did what you suggested and it works. LineID now increments by 1
for
every line entry but what I did not put in my initial question was that
for
every new DeliveryID (the primary key) I wanted the LineID to start at 1
and
increment so as to get Delivery 1 LineID 1, LineID 2, LineID 3
Delivery 2 LineID 1, LineID 2, LineID 3 etc
at the moment the Dmax function is looking for the largest number in
LineID
field in the tblDeliveryDetails. Is there a way to look for a new record
on
the tblDeliveryDetails and then fire the Dmax function starting at 1?
Sorry not to have explained this in the first question because I thought
this would happen when a new record was created.

You just have to include the DeliveryID in a criterion specified for the
DMax function:

Me![LineID] = 1 + _
Nz(DMax("[LineID]", "tblDeliveryDetails", _
"DeliveryID = " & Me!DeliveryID), _
0)

That ought to work, if I've understood you correctly. Note that I
reorganized the statement slightly, for clarity and to avoid any confusion
caused by unwanted line breaks in the post.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 

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

Top