423,113 Members | 1,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,113 IT Pros & Developers. It's quick & easy.

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

P: 17
Hello,

I have a access form that has a new button that opens a new record form that has all the fields in my table bound to textbox controls. I want to make sure that the user populates the ID field first before adding the record to the table. The table has this field already set as the primary key and indexed no duplicates. What is the best approach to handle this type of situation?

Thank you
Jun 14 '18 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdSave_Click()
  2.  
  3.     Me.Dirty = False
  4.  
  5. End Sub
  6.  
At the top of EVERY Module you should have
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
Phil

Share this Question
Share on Google+
28 Replies


twinnyfo
Expert Mod 100+
P: 2,499
Jerry W,

The best/easiest approach to this is to have the Primary Key be an Autonumber field, then the user doesn't have to worry about entering it. A new record automatically creates a new PK for each record. Then the field does not even need to be on your form.

Hope this hepps!
Jun 14 '18 #2

P: 17
Thank you twinnyFo for the suggestion, but in this situation I can't do that. The id is a specific set of characters between 5 & 6 depending on which form the user enters the information. This did give me an idea that I have to run past my lead. If I can take the max id and increment it by 1 and make it the id. If that doesn't work what would be an alternative? Thanks
Jun 14 '18 #3

twinnyfo
Expert Mod 100+
P: 2,499
Again, the "best" method is to do as described above for the Primary Key. The Primary Key is used by the Database, so that it can identify unique records. Now, if there is a different code that you use so that you can identify the record, the best way to do this, is to not have the user enter the data, but for the Form to generate that string/code/ID.

Typically when doing something like this, you establish an easily repeatable pattern. For example, if you are tracking orders, that ID could be something like "ORD201806-001". This tells you that this record is 1) for an order, 2) the order was placed in 2018, 3) it was placed in June of 2018 and 4) it is the first order of that month.

There are many ways to do this and many combinations. I am glad to work with you to figure out exactly how you want to do this and establish a method to accomplish it.

You are on the right track though--you must query your current records before you can establish the next increment of IDs.

Standing by for more hepp!
Jun 14 '18 #4

P: 17
I just checked with the lead and he said the number is generated in a different process. We have to access to it. So the user has to enter in the number. It is a 5 or 6 character value that has only numbers. For example "000010" and "00001" are current ids.

Should I disable all the fields except the id field and when the row is added to the database I can enable the rest of the fields and disable the id field? Is AfterUpdate the event I would use to re-enable the fields?
Jun 14 '18 #5

twinnyfo
Expert Mod 100+
P: 2,499
I just checked with the lead and he said the number is generated in a different process. We have to access to it. So the user has to enter in the number. It is a 5 or 6 character value that has only numbers. For example "000010" and "00001" are current ids.
So, I have to ask: what, exactly, is the requirement that the user enter a specific ID? The reason I ask is if this is a unique identifier, then, as a database guy, I shy away from allowing the user to fat-finger anything so important. We can still do it this way--but again, I recommend against this, if at all possible. Your choice.

Should I disable all the fields except the id field and when the row is added to the database I can enable the rest of the fields and disable the id field? Is AfterUpdate the event I would use to re-enable the fields?
Yes, I would think the best way to do this is to diable all controls on the form, except the ID. Once the ID is entered, re-enable all controls. I would, however, if the user is entering the ID, run a thorough double-triple-check to make sure the ID meets the exact criteria required for a new record. This is similar to the "recommended method" but in reverse.

Either way will work. Standing by to hepp!
Jun 14 '18 #6

P: 17
Thanks twinnyfo. I agree with you 100%. I do not like the users being allowed to enter the ID, but in this situation I have no control. What I'm going to do is to only allow 5 or 6 numbers. I can message the user with the ID asking if this is the correct ID so they can double check before I do the table insert.
Jun 14 '18 #7

twinnyfo
Expert Mod 100+
P: 2,499
Let us know how you come along with the process and if you run across any snags!
Jun 14 '18 #8

PhilOfWalton
Expert 100+
P: 1,257
Have you at least ensured that the ID is Unique?

Phil
Jun 14 '18 #9

P: 17
Hi Phil, Yes I'm using DCount to check for that specific ID that is entered in the BeforeUpdate event of the id textbox. If the ID exists then it does an undo and messages the user.
Jun 14 '18 #10

PhilOfWalton
Expert 100+
P: 1,257
Best option, Jerry, is to set the ID field's Index in the table to Unique.

Phil
Jun 14 '18 #11

P: 17
Hi Phil, I do have it set to Indexed no duplicates. Thank you
Jun 14 '18 #12

P: 17
What I did was set focus to the ID textbox when the user enters the new new data form I created. The user is forced to stay in the ID textbox till they enter in a 5 or 6 character ID that doesn't already exist. If they don't match that criteria then they get a message of what is wrong with the ID and the focus is still on the ID field. Do you see any issues with this approach? I didn't disable the rest of the fields since they cannot edit them till the ID is populated.
Jun 14 '18 #13

twinnyfo
Expert Mod 100+
P: 2,499
I would recommend disabling just for good measure. You never know what might happen if you don't make your DB gorilla-proof. Always err on the side of protecting your data.
Jun 14 '18 #14

P: 17
Twinnyfo, I'm running into an issue now that the lostfocus event is causing. If I open the new record form my focus is on the id textbox. But say I want to do a search or exit out as soon as the id textbox loses focus my message about the id field being empty appears. Should I add a save button that will validate everything and if so how would I tell access to add the row if they are bound fields? Thank you
Jun 14 '18 #15

twinnyfo
Expert Mod 100+
P: 2,499
I think your ID check should occur on the AfterUpdate Event.
Jun 14 '18 #16

P: 17
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
Jun 14 '18 #17

twinnyfo
Expert Mod 100+
P: 2,499
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.
Jun 14 '18 #18

PhilOfWalton
Expert 100+
P: 1,257
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
Jun 14 '18 #19

P: 17
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.
Jun 15 '18 #20

PhilOfWalton
Expert 100+
P: 1,257
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
Jun 15 '18 #21

P: 17
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.
Jun 15 '18 #22

PhilOfWalton
Expert 100+
P: 1,257
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdSave_Click()
  2.  
  3.     Me.Dirty = False
  4.  
  5. End Sub
  6.  
At the top of EVERY Module you should have
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
Phil
Jun 15 '18 #23

P: 17
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?
Jun 15 '18 #24

PhilOfWalton
Expert 100+
P: 1,257
Sorry, I'm not prepared to spoon feed you.

See if your table changes

Phil
Jun 15 '18 #25

twinnyfo
Expert Mod 100+
P: 2,499
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....
Jun 16 '18 #26

twinnyfo
Expert Mod 100+
P: 2,499
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.
Jun 16 '18 #27

P: 17
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.
Jun 19 '18 #28

twinnyfo
Expert Mod 100+
P: 2,499
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!
Jun 19 '18 #29

Post your reply

Sign in to post your reply or Sign up for a free account.