Solved Help with DMax() (1 Viewer)

Garcimat

Member
Local time
Today, 15:43
Joined
Jun 7, 2022
Messages
67
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

AWF VIP
Local time
Yesterday, 21:43
Joined
Mar 9, 2014
Messages
4,462
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

Enthusiastic Amateur
Local time
Today, 06:43
Joined
Sep 21, 2011
Messages
10,868
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.?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:43
Joined
May 7, 2009
Messages
16,875
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

Super Moderator
Staff member
Local time
Today, 01:43
Joined
Feb 19, 2002
Messages
36,961
@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

Member
Local time
Today, 15:43
Joined
Jun 7, 2022
Messages
67
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.
 

Attachments

  • 3FE70E93-77D3-4DAA-97A3-E90FB9E94746.jpeg
    3FE70E93-77D3-4DAA-97A3-E90FB9E94746.jpeg
    5.8 MB · Views: 54
  • 200477CB-CDA9-46C9-9EC1-0975A2A24796.jpeg
    200477CB-CDA9-46C9-9EC1-0975A2A24796.jpeg
    6.5 MB · Views: 54
  • D5C8BCBC-7BE2-4932-85C5-43502999F981.jpeg
    D5C8BCBC-7BE2-4932-85C5-43502999F981.jpeg
    5 MB · Views: 55

Garcimat

Member
Local time
Today, 15:43
Joined
Jun 7, 2022
Messages
67
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

Member
Local time
Today, 15:43
Joined
Jun 7, 2022
Messages
67
Worked !!!! Thank you everyone
 

Attachments

  • 02754B71-25F7-4683-8892-13265717D185.jpeg
    02754B71-25F7-4683-8892-13265717D185.jpeg
    6.6 MB · Views: 56

Users who are viewing this thread

Top Bottom