Solved Help with DMax() (1 Viewer)

[Garcimat]

Hi guys
I need help, I have a table with “SWO’s” it is like a work order…. Under one specific SWO I can have many “PTF’s” it is a secondary working order under the primary working order “SWO”. On my input form I have a field for the “PTF NUMBER” I want this field to be automatic, so it will eliminate user input errors.
I need a instruction or query that will search my master data table find the SWO number, find the last/highest PTF NUMBER under that SWO and fill the “PTF NUMBER” field with last number + 1
I have managed to find the highest PTF NUMBER with a query… I am trying not to use too much SQL, I like to keep everything in VBA.

thank you in advance

 

[June7]

DMax("PDF", "table", "SWO='" & [SWO] & "'") + 1

The real trick is figuring out what event to code in. Also, reducing risk multiple users could generate same value. Timing is a factor.

Why do you need to save this sequential number? It can be generated when needed on a report by use of textbox RunningSum property.

 

[Gasman]

DMax("PDF", "table", "SWO='" & [SWO] & "'") + 1

The real trick is figuring out what event to code in. Also, reducing risk multiple users could generate same value. Timing is a factor.

Why do you need to save this sequential number? It can be generated when needed on a report by use of textbox RunningSum property.

I seem to recall that Pat Hartman recommends the Form BeforeInsert event, so as not to dirty the record imemdiately?
Strangely enough, I was thinking about this, this morning, as in the past I had set my key if a new record in the Load event of a form.
I believe a user would want to see the new value, but what if they then cancel? I used to use Escape key.

I was wondering whether one could clear the control source via vba, so as to make it unbound and not affect the record, but still be visible to user.
Then set yourself via VBA in the BeforeInsert event?
If not a new record, then just leave the control bound, as you would not be changing the data.?

 

[arnelgp]

on Design view of your Form, set the Locked Property of “PTF NUMBER” to Yes so nobody can change it
except the VBA.
add code to the BeforeUpdate event of SWO textbox:

private sub SWO_BeforeUpdate(Cancel As Integer)
Me![PTF NUMBER] = Val(DMax("[PTF Number]", "yourTableName", "SWO = '" & [SWO] & "'") & "") + 1
end sub

 

[Pat Hartman]

@Gasman the BeforeInsert recommendation refers to when you open a popup form rather than using a subform to add child records. Many people attempt to set the FK from formA rather than waiting until the user actually tries to add a record into formB.

 

[Garcimat]

Thank you guys
It still not working, looks like it is not getting the value from the table because the textbox return “1”

this is my first form, when double click in the listbox I get the values in some global variables and I transfer it all to the next form in the event open() I tried the Dmax() in the same form event without success.
Everything looks ok, I can’t find a way around, this have to be zero mistakes, I can’t let people manually input data.

 

[Garcimat]

I think I understand what is going on
I need to get the SWO_number chosen in the first form (I am using a global variable gvar_SWONumber ) with that number do the Dmax() bringing the highest PTF_Number +1 to the text box txt_Ptf_Number.

 

[Garcimat]

Worked !!!! Thank you everyone