Update on Save Only

[brsawvel]

I have a form that opens in new and includes a couple fields with DMAX code On Load. But, sometimes the rest of the form isn't filled out. Is there a way to set the form to save only when the other fields are populated? Otherwise, I end up with a table with a bunch of empty records.

 

[pbaldy]

The most reliable is probably to make the other fields required at the table level. This is how validation is typically done:

http://www.baldyweb.com/BeforeUpdate.htm

 

[brsawvel]

Thanks! But, I think my problem is a little bigger than I thought. Now, I'm trying to set some of the fields to copy by DLast. The DMax fld works just fine, but the remainder populate based on a row in the middle of my table (confirmed by a Me.Refresh test). I can't seem to figure out why. I have the table and a query set to ORDER BY fldA and fldB, but the form still considers the middle of the table and query to be DLast. Argh!!!

Here's what I tried...

DoCmd.GoToRecord Record:=acNewRec (Variant: DoCmd.GoToRecord,,acNext)
Me.fldA = DMax ("[fldA]", "tblA")
Me.fldB = DLast ("[fldB]", "tblA") + 1
Me.fldC = DLast ("[fldC]", "tblA")

Do you know what I'm doing wrong?

 

[pbaldy]

DFirst() and DLast() can't be counted on to return values from particular records. Note the word "random":

https://support.office.com/en-us/article/DFirst-DLast-Functions-adb06169-bf49-4f80-bf04-1e4b5a48a8a7

If you want 3 values from the same record, I'd probably open a recordset. You could also use the result of the DMax() in the criteria of the others.

 

[missinglinq]

If you assign the values to fldA, fldB, fldC and fldD, using their Default Value Property, the Record will only save if other/additional data is entered.

Linq ;0)>

 

[brsawvel]

How do you use the result of the DMax() in the criteria of others?

 

[pbaldy]

DLookup("Field", "Table", "KeyField = " & DMax(...))

 

[brsawvel]

What goes in "KeyField ="? I think that's clogging it up...

 

[pbaldy]

The name of the field to be filtered on.

http://access.mvps.org/access/general/gen0018.htm

 

[brsawvel]

As always, thanks!

 

[pbaldy]

Happy to help!