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

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

P: 16
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
3 Days Ago #1
Share this Question
Share on Google+
26 Replies


twinnyfo
Expert Mod 100+
P: 2,297
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!
3 Days Ago #2

P: 16
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
3 Days Ago #3

twinnyfo
Expert Mod 100+
P: 2,297
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!
3 Days Ago #4

P: 16
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?
3 Days Ago #5

twinnyfo
Expert Mod 100+
P: 2,297
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!
3 Days Ago #6

P: 16
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.
3 Days Ago #7

twinnyfo
Expert Mod 100+
P: 2,297
Let us know how you come along with the process and if you run across any snags!
3 Days Ago #8

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

Phil
3 Days Ago #9

P: 16
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.
3 Days Ago #10

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

Phil
3 Days Ago #11

P: 16
Hi Phil, I do have it set to Indexed no duplicates. Thank you
3 Days Ago #12

P: 16
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.
3 Days Ago #13

twinnyfo
Expert Mod 100+
P: 2,297
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.
3 Days Ago #14

P: 16
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
3 Days Ago #15

twinnyfo
Expert Mod 100+
P: 2,297
I think your ID check should occur on the AfterUpdate Event.
3 Days Ago #16

P: 16
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
3 Days Ago #17

twinnyfo
Expert Mod 100+
P: 2,297
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.
3 Days Ago #18

PhilOfWalton
Expert 100+
P: 1,113
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
3 Days Ago #19

P: 16
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.
2 Days Ago #20

PhilOfWalton
Expert 100+
P: 1,113
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
2 Days Ago #21

P: 16
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.
2 Days Ago #22

PhilOfWalton
Expert 100+
P: 1,113
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
2 Days Ago #23

P: 16
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?
2 Days Ago #24

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

See if your table changes

Phil
2 Days Ago #25

twinnyfo
Expert Mod 100+
P: 2,297
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....
1 Days ago #26

twinnyfo
Expert Mod 100+
P: 2,297
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.
1 Days ago #27

Post your reply

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