DMax not working in Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form "frm_Estimating_Input" that is based on a table
"tbl_Estimating_Input" and the first field in the form is "Quote_Number"
which the default value is [Forms]![frm_Quote_Information]![Quote_Number].
The next field is "Item_Number" this is the one where I have the DMax
formula in and not working: =DMax("Item_Number","qry_frm_Estimating_Input")+1
(this is on the line Default Value) - do I have this (a) in the wrong spot
(b) not working due to the Quote_Number criteria or (c) just have it wrong
all together.

What my goal is I have the form "frm_Quote_Information" which is based on
table "tbl_Quote_Information"that has basic input with the Quote_Number as
the primary key. From this form I choose the command button "Open Estimating
Input Screen" which brings up form "frm_Estimating_Input" with the
Quote_Number already filled in from the previous form. Now I want for this
specific quote to have an increment Item_Number not an autonumber.

Where am I going wrong?

Thanks!!
Stacey
 
I have a form "frm_Estimating_Input" that is based on a table
"tbl_Estimating_Input" and the first field in the form is "Quote_Number"
which the default value is [Forms]![frm_Quote_Information]![Quote_Number].
Click to expand...

Is it necessary to have this as a separate form? If you make
frm_Estimating_Input a Subform of frm_Quote_Information it will fill
this in automatically.
The next field is "Item_Number" this is the one where I have the DMax
formula in and not working: =DMax("Item_Number","qry_frm_Estimating_Input")+1
(this is on the line Default Value) - do I have this (a) in the wrong spot
(b) not working due to the Quote_Number criteria or (c) just have it wrong
all together.
Click to expand...

I'd suggest putting code in the form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Item_Number] = _
NZ(DMax("Item_Number","qry_frm_Estimating_Input"))+1
End Sub
What my goal is I have the form "frm_Quote_Information" which is based on
table "tbl_Quote_Information"that has basic input with the Quote_Number as
the primary key. From this form I choose the command button "Open Estimating
Input Screen" which brings up form "frm_Estimating_Input" with the
Quote_Number already filled in from the previous form. Now I want for this
specific quote to have an increment Item_Number not an autonumber.
Click to expand...

If you want the Item_Number to start afresh with each quote, you may
want to change the DMax() to look directly at the Table rather than
the query. Guessing that you have tbl_Estimating_Input this would be

Me![Item_Number] = _
NZ(DMax("Item_Number", "tbl_Estimating_Input", _
"[QuoteNumber] = " & _
[Forms]![frm_Quote_Information]![Quote_Number]))+1

Sorry about the linewrap - the _ continuation characters should be
correct though.

John W. Vinson[MVP]
 
Your code at the bottom worked perfect, I had thought of this code but had it
in the wrong place - that's nice to know.

I had thought of the subform, if I do that will the code be different? I
tried in place of Me![Item_Number] I put
[Forms]![frm_Estimating_Input]![Item_Number] but got Run-time error 3464,
Data Type Mismatch in criteria Expression. - Any suggestions?

Thanks again!!

John Vinson said:
I have a form "frm_Estimating_Input" that is based on a table
"tbl_Estimating_Input" and the first field in the form is "Quote_Number"
which the default value is [Forms]![frm_Quote_Information]![Quote_Number].
Click to expand...

Is it necessary to have this as a separate form? If you make
frm_Estimating_Input a Subform of frm_Quote_Information it will fill
this in automatically.
The next field is "Item_Number" this is the one where I have the DMax
formula in and not working: =DMax("Item_Number","qry_frm_Estimating_Input")+1
(this is on the line Default Value) - do I have this (a) in the wrong spot
(b) not working due to the Quote_Number criteria or (c) just have it wrong
all together.
Click to expand...

I'd suggest putting code in the form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Item_Number] = _
NZ(DMax("Item_Number","qry_frm_Estimating_Input"))+1
End Sub
What my goal is I have the form "frm_Quote_Information" which is based on
table "tbl_Quote_Information"that has basic input with the Quote_Number as
the primary key. From this form I choose the command button "Open Estimating
Input Screen" which brings up form "frm_Estimating_Input" with the
Quote_Number already filled in from the previous form. Now I want for this
specific quote to have an increment Item_Number not an autonumber.
Click to expand...

If you want the Item_Number to start afresh with each quote, you may
want to change the DMax() to look directly at the Table rather than
the query. Guessing that you have tbl_Estimating_Input this would be

Me![Item_Number] = _
NZ(DMax("Item_Number", "tbl_Estimating_Input", _
"[QuoteNumber] = " & _
[Forms]![frm_Quote_Information]![Quote_Number]))+1

Sorry about the linewrap - the _ continuation characters should be
correct though.

John W. Vinson[MVP]
Click to expand...
 
Your code at the bottom worked perfect, I had thought of this code but had it
in the wrong place - that's nice to know.

I had thought of the subform, if I do that will the code be different? I
tried in place of Me![Item_Number] I put
[Forms]![frm_Estimating_Input]![Item_Number] but got Run-time error 3464,
Data Type Mismatch in criteria Expression. - Any suggestions?
Click to expand...

Well, I have NO idea what the datatype of Item_Number is, nor anything
about Forms!frm_Estimating_Input!Item_Number. Does that control (a
textbox? combo box? some other control?) contain a value which matches
the field in the DMax expression?

John W. Vinson[MVP]
 
Back
Top