how to make the autonumber field be triggered by opening a new record

[ruthcali]

i am using Access 97 in a Novell environment.

i have a form that has an autonumber field called ID.

I have an 'Add New Record' button on the form. if the user clicks on the 'Add New Record' button, they must type something in one of the text boxes (or even hit the spacebar once) to populate the autonumber.

This can create a problem because one of my text boxes has a mousedown event that opens up another form and uses the link criteria of ID.

so if the user clicks on the 'Add New Record' button, then right away clicks in the text box that triggers the MouseDown event, they get a syntax error: [missing operator] in query expression '[id]='

i understand the error. it is because the autonumber hadn't 'kicked in' yet.

How can i make the autonumber be triggered as soon as the user clicks on the 'Add New Record' button? or does anyone have any suggestions how i can avoid this potential error?

 

[Shirley]

Is that the first box that the user click when the form is open? If so why not have the user click in another textbox before the box with the Mouse down event is click. This is just an idea

Shirley

 

[ruthcali]

The user can click on several different text boxes when they open a new record. They don't have to first click on the text box with the MouseDown event. but, they might, and i don't want them to see the syntax error.

And just clicking in another text box isn't enough. They have to, at the least, hit the spacebar in order for the autonumber field to be populated.

but, thanks for your response.

 

[CarlyA]

I have a suggestion.

In my form's "on open" I have it go directly to a new record. But I don't force it to assign a record num here-- I wait for them to enter some data . If you write code to add a record and assign a number in the "on open" or "on load" properties for the form you may start adding blank records everytime a user opens the form but then decides not to add data now and quits (which will happen enough to be annoying).

Pick a field that is required. Put it at the top of the form where one would naturally begin entering data. If they try to select data in the mousedown/up txtbox you have first, pop a msgbox requesting they enter data in the required txtbox. You can put the code for the msgbox in the object's on click event, or possibly the on change event.

Let me know if I can be of more help. Sorry I didn't answer your question.

 

[Lightning]

Apart from the ID field, are there any other fields that can be auto-filled?? For instance, a Date Created field, or a UserID field, or something similar.

If so, in the forms OnCurrent event, put some code to enter the required information in that field. This will also action the autonumber field, thus preventing the problem from occurring at all.

Hope this helps.

Lightning

 

[ruthcali]

Thanks for everyone's help! i really appreciate it.

I ended up doing this:


On Error Resume Next
DoCmd.OpenForm stDocName, , , stlinkcriteria, , acDialog

If Err.Number = 3075 Then
MsgBox "Please enter a value for the ring id or the ER# first", , "Ring ID or ER# needed"
Exit Sub
End If

It's not exactly what i wanted, since it requires the user to enter a value before clicking on the text box. But it stops the run time error (error number 3075) from appearing.

Thank you again so much for everyone's help in this situation!

 

[Quehay]

This totally off the cuff rather than from knowledge, but what about putting a save command in the code following the creation of a new record. If you don't have required fields in the form there should be a record created with only the autonumber filled in--but this should be sufficient for your purposes.

Or: Have the Enable property of the problem control set to false until there is a value for the ID field.

 

[Quehay]

Sorry, "Enabled" property.

 

[ruthcali]

i actually had the code:

DoCmd.RunCommand acCmdSaveRecord

on my MouseDown event for the text box. but that didn't cause the autonumber to be triggered.

making the text box un-enabled until there is an ID filled in is also a good idea. i was able to accomplish the same sort of thing by using my code above (If Err.Number = 3075 Then...)

CarlyA: You had written above "If you write code to add a record and assign a number in the "on open" or "on load" properties for the form you may start adding blank records everytime a user opens the form...'

i understand what you mean about the possibility of many blank records being created, but, if i wanted to, how would i write the code to add a record and assign a number to it?

 

[MichaelRed]

ruthcali,

It is almost always better to prevent the user from doing the wrong thing than to clean up the mess afterwards. You should set the problem controls (the one w/ the mouse-cown event) to Enabled=NO in the create Record command. In other data entry fields on the form, place some validation code (there should be some of this anyway). Each of the fields which trigger the autonumber should - at the completion of the validation code - either call a routine to set the problem control to valid or do this directly in code.

also, you should look closely at the need for the "Mouse_Down" event code. There must be something strange in your app, as the use of the mouse events are not generally 'encouraged' because they - usually - take up a lot of processor time.


MichaelRed
There is never enough time to do it right but there is always time to do it over

 

[ruthcali]

Thanks for your input. I was just wondering, why do you think it is better to have the text box set to enabled=false instead of my method:

If Err.Number = 3075 Then
MsgBox "Please enter a value for the ring id or the ER# first", , "Ring ID or ER# needed"
Exit Sub
End If

Your way required more code since each of the fields which trigger the autonumber will have to have some code setting the enabled to True. My way required only code in the MouseDown text box. i was just wondering.

The reason i am using a Mouse Down event is because of Excel. My form is set to Continous. There are a total of about 100 records in my form and each record contains 7 fields. My boss was using an Excel spreadsheet before agreeing to switch to Access. He liked the fact that in Excel, he could see many of his records at once.

So, to duplicate that in Access, i made the height of my records as small as possible. That way, he could view many records on his screen. Most fields contain only one line of data, such as Name, Date, ID#, etc.

But, there are also 3 text boxes that are memo fields and contain much data. Because of the height of the records, the data in these 3 fields are impossible to read without scrolling down for each word! In order for my boss to view many records on his screen, but still be able to read and type into these 3 text boxes, i created an extra form.

This extra form contains only the three text boxes, but they are big. So when my boss wants to read or type into one of these 3 text boxes from the main form, he clicks in the box and that triggers the mouse down event and opens up the extra form using the link criteria: "[id]=" & Me![ID].

he can read and type using the extra form and when he is done, he clicks on the Close button and the information shows up on the main form.

Can you think of a better way to accomplish this without using the Mouse Down event?

Thanks for your input.

 

[MichaelRed]

ruthcali,

To provide access to the 'other' info, there are (at least) two convenient approaches:

Put the three memo type thinggys on (one to three) subforms. On the Main form provide command buttons to hide/show the subforms. You can then switch the enabled = Yes | No to operate on these new command buttons. This prevents 'the boss'from getting into trouble, because he will always need to be in a 'valid' record before he can edit the memo fields.

Make a new "table" which only includess the memo fields and a key to link to the 'Main" field. make either sub form(s) or a seerate form to display the fields from this new table. Again, use the command button(s) to access the new form(s) and manipulate the enabled property in code to accomplish what you want.

Backing upo a bit. The reason you get the error to begin with is that dear old Ms. Access doesn't store the memo field directly in the table, but only puts a "pointer" (e.g. reference) to the 'Real' memo field. When you (your boss) attempts to edit the memo field, little old lady Access goes looking for the pointer in the current record - but since it doesn't exist - we get the old "Houston, we have a problem here ..." treatement.

Going on to the 'why do it my way ...". This is somewhat more complex. The easy answer is that there are always different approaches to solving problems, and nowhere is this more true than our kindly little old lady "Ms. Access". Your approach - trapping the error - is certainly valid and recommended (or just plain necessary) in many instances. The major reason for me to recommend that you do not do this here is that Ms. Access monitors the mouse events for the entire form (and possbly the entire App) when you have it instantiated for ANY control or form, not just for the specific control or form. Thus you are -indirectly - creating a large number of events for your app to deal with which 'do nothing' - but consume a (relatively) large ammount of the system's attention. Going somewhat further, every time an error occurs, it goes through several layers of Microsoft code (read numerous system level subroutines and functions) - BEFORE you get to handle it. Avoiding errors is less costly - in terms of your application's execution time - than letting it happen and trapping the error after the fact.

In many cases, validation code is PRIMARILY for the prevention of errors, not just to restrict the data entry to specific values.


HTH, MichaelRed
There is never time to do it right but there is always time to do it over