Make user enter the primary key field before adding row in table

[twinnyfo]

I think your ID check should occur on the AfterUpdate Event.

[Jerry W]

Wouldn't that cause an issue since the id might already be in the table? Should it be afterinsert since this is a new row being added? Thank you so much for your help

[twinnyfo]

Use an unbound text box. When it receives a viable entry, save it to the ID field. Make sense?

Again, this is much easier if there is an actual PK for the record and a DB-assigned ID.... But I digress.

[PhilOfWalton]

Sorry to butt in again, but does it actually matter that the ID field is filled in first?
My guess is that providing the ID field is filled in and verified before the RECORD is saved, you will get what you want.

So do checks on the BeforeUpdate of the ID field that you have a valid number, and on the BeforeUpdate of the form that the required fields are Not Null (Particularly the ID field).

So if the record is blank, you should be able to search on a field.

Phil

[Jerry W]

For a new record wouldn't I have to check beforeinsert on the form not beforeupdate?


Thanks again to both of you. I really appreciate it.

[PhilOfWalton]

BeforeInsert occurs as you type the first letter into any field on your form.

BeforeUpdate id the moment before the record is saved, so that is when you need to validate your data.

hil

[Jerry W]

Perfect. Thank you Phil.

I did add a save button through the wizard so that the user explicitly clicks on save and the record is saved. That way the user doesn't have to tab through all the controls or click outside of the subform.

The save button is using a macro. Do you know the syntax I could use in a macro to set SourceOjbect of the subform to blank? After the save I want to blank the subform and hide it.

Is this a SetProperty option?

Thank you.

[PhilOfWalton]

Firstly i strongly urge you NOT to use Macros.
Two Reasons (Others may disagree, but their comments are welcome)
1) VBA (Visual Basic for Applications) is far more versatile and powerful There are many things that can be done with VBA that are impossible with macros
2) It is highly convenient when looking at the code behind a form, to see ALL the code. Macros don't appear and it gets very confusing for example if there is a BeforeUpdate Macro, and an AfterUpdate bit of code

That said, create a Save Button called CmdSave (Many programmers start the name of a Command buttons with "Cmd")

Then on the On Click, Type [Event Procedure], then press the 3 dots to the right. The first & last line of the code appear automatically, so just add the second line.

Private Sub CmdSave_Click()

    Me.Dirty = False
    
End Sub

At the top of EVERY Module you should have

Option Compare Database
Option Explicit

Phil

[Jerry W]

Thank you Phil. I will change it right away. I would rather look at the VBA code then go through the macro setup.

Does Me.Dirty = false cause the new row to be inserted into the table?

[PhilOfWalton]

Sorry, I'm not prepared to spoon feed you.

See if your table changes

Phil

[twinnyfo]

This entire process should not be this complicated. I know we have discussed it before, but other than “the higher ups want it this way” what is the REASON that the user must enter the PK?

This is not a difficult thing....

[twinnyfo]

How about this as an easy solution: When the user wants to enter a new record, a new form pops up. One text box on it. The user enters the PK. Hitting enter or click a button "Add Record" simply checks for the existence of that PK and validates that the value entered meets the criteria. If not, we go back to the text box. User has the option to cancel out of this form. If the value entered meets all criteria, accept it, create a new record and assign that value to the PK of the record.

Very simple, very straightforward , no need to mess with dirty forms or creating a partial record.

[Jerry W]

Hi Guys,

Thank you both. Both your advice helped a lot and I have it working exactly like I need it too.

twinnyfo, I was going to switch to the approach you mention in your last post of having just a form for the PK value and then validate and show the other form. I know this was way over complicated but I have to follow what the lead wants. I would have done it a much different way if it was up to me.

Thanks again for all your help it was really appreciated.

[twinnyfo]

Not a problem, Jerry. We all run into occasions in which the best way is simply not an available option.

Thanks for your efforts here!