Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

show autonumber

Posted on 2009-12-29
50
Medium Priority
?
449 Views
Last Modified: 2012-08-13
Is possible to show the autonumber on the form when it's a new record and before save it?
0
Comment
Question by:jodstrr2
  • 24
  • 22
  • 3
  • +1
50 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 26138913
1. place a textbox on your form bound to the autonumber field, set visible property = False.
2. in the form's beforeUpdate event, place the following code
Private Sub Form_BeforeUpdate()
If Me.NewRecord Then
        Me.txtAutoNo.Visible = True
Else
        Me.txtAutoNo.Visible = False
End If
End Sub

0
 

Author Comment

by:jodstrr2
ID: 26138993
it doesn't work.  what I want to do is every time when I add a new record, I can see the autonumber for this record right away on the form.  I tried the code
If Me.NewRecord Then
        Me.txtAutoNo.Visible = True
Else
        Me.txtAutoNo.Visible = False
End If
I can see the autonumber after I close the form and reopen the form.
0
 
LVL 75
ID: 26139064
Put a text box on the form to display the AN.  In the Control Source of the text box, put this expression:

=IIF(IsNull([YourAutoNumberFieldName],"<new record>", [YourAutoNumberFieldName])

When you come to a New Record 'position', it will display <new record>.  As soon as you enter data into any bound field on the form, you will see the new AN.

mx
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:jodstrr2
ID: 26139119
what do you mean <new record> ? what do you referr as <new record>?  do I put <new record> or I have to change to something else?
0
 
LVL 75
ID: 26139157
Put the expression exactly as I showed, including the double quotes around <new record>

You can change that to display whatever ...

=IIF(IsNull([YourAutoNumberFieldName],"<Hi MX>", [YourAutoNumberFieldName])
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 26139196
In addition to the code I gave you, add the following:
Private Sub Form_Load()
      Me.txtAutoNo.Visible = False
End Sub

Private Sub Form_Current()
If Not Me.NewRecord Than
      Me.txtAutoNo.Visible = False
End If
End Sub
End Sub
0
 

Author Comment

by:jodstrr2
ID: 26139201
I did try, but I got the error message that:
"The expression you entered has a function containing the wron number of arguments"
0
 

Author Comment

by:jodstrr2
ID: 26139218
I did try the code from MX, but I got the error message that:
"The expression you entered has a function containing the wron number of arguments"
0
 
LVL 75
ID: 26139244
TYPO ... missing paren ... tyr this


=IIF(IsNull([YourAutoNumberFieldName]),"<Hi MX>", [YourAutoNumberFieldName])
0
 

Author Comment

by:jodstrr2
ID: 26139277
Sorry, I tried
=IIF(IsNull([YourAutoNumberFieldName]),"<Hi MX>", [YourAutoNumberFieldName])
but when I add a new record and I got #error show on the txtbox on the form instead show the autonumber.
0
 
LVL 75
ID: 26139313
You need to replace 'YourAutoNumberFieldName' with the actual name of Your Auto Number Field name.

mx
0
 

Author Comment

by:jodstrr2
ID: 26139398
I did, here is my code
=IIf(IsNull([EVID]),"<new record>",[EVID])
and I there is <new record> in the text box and after I close the form and reopen the form then the autonumber shows, but it just shows <new record> when I open as a brand new form
0
 
LVL 75
ID: 26139448
ok.  Well, I've been using this scheme since Access 1.0, so something else is going on here.

What is the record source of your Form ... and what version of Access is this.  WHEN ... the AN becomes available has varied somewhat across the many versions of Access.

I have to leave for an appt for a few minutes ... back in a bit.   IF you are still having issues, upload the MDB.

mx
0
 
LVL 85
ID: 26140043
When you open to that new form, is there an existing record showing?
0
 
LVL 75
ID: 26140125
Lets try this:

=IIf(Forms.YourFormNameHere.RecordsetClone.RecordCount = 0 , "<new record>",[EVID])

mx
0
 

Author Comment

by:jodstrr2
ID: 26140234
no, there is not existing record showing because when I open the new form, it's when I add a new record, and it's blank on the autonumberID field until I close the form then I can see the autonumberID shows.
0
 

Author Comment

by:jodstrr2
ID: 26140241
mx,
I tried the last one that you sent to me, it still showing "new record" when I add a new record.  I'm using Access 2003
0
 
LVL 75
ID: 26140260
OK ... a "new record' is not actually started until you enter data in a bound Control.  Simply navigating to the New Record *position* on the Form ... does not constitute 'adding' a new record.  If you are seeing <new record> showing ... try typing a character in a bound text box and see what happens.

mx
0
 

Author Comment

by:jodstrr2
ID: 26140350
I can't type anything in that text box
0
 
LVL 75
ID: 26140373
Not in *that* text box ... in any other text box on the form where a use would enter data.

mx
0
 

Author Comment

by:jodstrr2
ID: 26140415
nothing changed when I enter information on the form and autonumberID shows after I save and close the form
0
 

Author Comment

by:jodstrr2
ID: 26140434
the autonumberID still not showing when the form is open or when the form is in current before I save or clase the form.  I was wondering it might be impossible to show the autonumber ID before save the record?
0
 
LVL 75
ID: 26140448
Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

NOTE:  If you have an A2007 ACCDB, please convert to A2003 MDB.
mx
0
 
LVL 75
ID: 26140460
"I was wondering it might be impossible to show the autonumber ID before save the record?"

Yes.  As I said, something else is going on here.  I need to see the mdb at this point.

mx
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 26140662
I was wondering it might be impossible to show the autonumber ID before save the record?
            No it is possible,  See below:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iResponse As Integer
Me.txtAutoNo.Visible = True

If iResponse = MsgBox("Do you want to save this record?",vbYesNo)
If iResponse = vbYes Then
   DoCmd.acCmdSaveRecord
Else
   Cancel = True
    If Me.Dirty
       Me.Undo
    End If
End If
End Sub
0
 
LVL 75
ID: 26140679
Sure it's possible.  I do it all the time :-)

mx
0
 

Author Comment

by:jodstrr2
ID: 26141234
I attach a copy of the form view and a form design
New-Compressed--zipped--Folder.zip
0
 
LVL 75
ID: 26141257
ok ... standby ...

mx
0
 
LVL 75
ID: 26141297
There is no mdb in the zip file.  I really need to see the mdb ... and be *very* specific how to reproduce the issue and exactly where.

thx.mx
0
 

Author Comment

by:jodstrr2
ID: 26141462
I have to coding for you on the word document
0
 

Author Comment

by:jodstrr2
ID: 26141488
Doc1 is the Form design and Doc2 is Form View and coding for the Note Button, Coding for Save/Close and my problem.
0
 
LVL 75
ID: 26141510
Sorry ... I need the mdb to test what is going on ...

mx
0
 

Author Comment

by:jodstrr2
ID: 26141768
I'll send the DB tomorrow
0
 
LVL 75
ID: 26141787
ok ... I'm sure we can find the issue.

mx
0
 

Author Comment

by:jodstrr2
ID: 26146109
Hi mx,
here is my DB, for some reason, I can't make it automatically open the Mainmenu, so when you open the DB, it shows you the form section, you can click on Mainmenu, and the case# is C01255756, there are two different event, one is Court and the other one is BodyAtt.  My problem is when I add a new record under court event, I enter all the information (in the Court Event Type of the Court event Form, please choose "Body Attachment Issued", this suppose to creates Bodyattach), you can do two things then you know what I'm taking about.
1). add a new court event, select either off call or continue then select "Body Attachment Issued" then click save/close, you will see a pop up message ask you to update the Bodyattachment and once you click ok on the message, the body attachment event form will be pop up.
2). do the exactly same thing as #1 (make sure you select the "Body Attachment Issued" on the Court Event Type drop down) but before you click save/close, click on the Notes button, then you can either enter something on the Notes Form or not, then you close the notes form and click Save/Close on the Court Event Form, you will not see the message to remind you to update the BodyAttachment which it should do the same as #1.  That's what I don't know what to do .

New-Compressed--zipped--Folder.zip
0
 

Author Comment

by:jodstrr2
ID: 26147482
I tried to use
If Me.NewRecord Then
   Me!HoldID = DMax("[Evid]", "Event_Court") + 1
Else
   Me!HoldID = Me!EVID
End If
but this only works for if I don't delete any record, but if I delete the record that recently entered, then it gave me the wrong autonumber.  For instance, when I entered a new record, the HoldID shows my autonumber 572697, but after I close, it shows 572862 because the last autonumber in the table was 572697, but the actual autonmuber(after I delete so many) is 572862, is anyway to get the actual include those already delete?
0
 
LVL 75
ID: 26147848
Before I wade through all of this ... one note.  Whereas you can 'see' the next value using the technique I showed,  in code ... the value is not really available *until* you save the record.  And this has changed from time to time across the many releases of Access.  Basically, there is no guarantee the AN is availabel until the record is saved.

Is this what your issue is ?

mx
0
 

Author Comment

by:jodstrr2
ID: 26147987
Yes, because if I don't need to carry over the AN from one table to another table, don't have to put docmd.runcommand acsaverecord then the program is fine and everything works perfect, but since I have to carry over the AN, I have to have use docmd.runcommand acsaverecord, and that's the problem occur, so what do you suggest?  I have no idea how to make it works for both sitution, I don't want to take out the docmd.runcommand acsaverecord  and manually search for the AN number in Court Table to put it to the Notes Table, but I also don't want to end users finished everything then go back to the Court event to enter the Notes.  That's why I'm ask either get the AN show on the form once when the create new record, or tell the system this is a new recrod when they Save/close after they enter the Notes.  any idea?
0
 
LVL 75
ID: 26148080
Ok I have the mdb.  Please tell me in as few as words as possible ... which form you are trying to display the next Auto Number on - specifically related to your original Q:

"Is possible to show the autonumber on the form when it's a new record and before save it?"

mx

0
 

Author Comment

by:jodstrr2
ID: 26148197
Court Event Form
0
 
LVL 75
ID: 26148452
OK ... that form does not even open ... it's asking for multiple parameters, and then fails to open.  I'm sorry, but I don't have time to troubleshoot all of that.

Sooooo ... I created a test form 'frmAutoNumberShowTest' to demonstrate what I've been talking about.
(see attached MDB)

1) Open frmAutoNumberShowTest
You will see the first AN ... 563603 in the text box on the form header (in Red).

2) Using the Record Navigation buttons ... click on the New Record button.
You will now see "<new record>" displayed in this text box.

3) Now, type any character in the EEVENT (or text1) text box. You will immediately see the next AN that Access will be assigning to this new record, now that the new record has actually been started -  by typing a character in a Bound text box (or other control).

I'm using my original expression in the text box:

=IIf(IsNull([EVID]),"<new record>",[EVID])

The RecordsetClone.RecordCount actually is not appropriate in this case.

There is also a button to show the AN at any time.

So ... that's how it works
CARE-MX01.zip
0
 

Author Comment

by:jodstrr2
ID: 26148814
thanks mx, but I just don't understand why when I tried on your frmAutoNumberShow and enter something, it gave me the AN right away but it doesn't matter what i enter on my form, unless I sace/close, otherwise I never get the AN.  I wonder if I did something wrong on the coding.

p.s. if you rignt click on the zip folder and extract all, the program works fine. if you don't extract all, it will give you the error message
0
 
LVL 75
ID: 26149061
"it gave me the AN right away but it doesn't matter what i enter on my form"

Not following ..?

As I indicated before, an actual new record is not created until ... a bound control of any kind (text box, combo, list box. etc.) ... So right, it doesn't matter what or where you enter.

Again, you Q was ... "Is possible to show the autonumber on the form when it's a new record .."
And the answer is  Yes ... and the demo form shows how to do that.

mx
0
 

Author Comment

by:jodstrr2
ID: 26149171
mx, I following you, I tried your form, it works, but I just don't know why my form doesn't work, is because my form I add a new record from different way instead have a button for Add New record? that's why it doesn't work on my form?
0
 
LVL 75
ID: 26149180
btw ... I did unzip all files ... but still get the Enter parameter errors when I open form frmEventView_Court

mx
0
 

Author Comment

by:jodstrr2
ID: 26149222
I think because the network login name.
0
 

Author Comment

by:jodstrr2
ID: 26151318
mx,
it's very weired that when I open the MDB at home with Access 2007,  when I create a new record, I can see the autonumber shows on the form right away, but there is not autonumber showing until I save the record on Access 2003.  do you think I might miss some of the Microsoft updated or might be something got disable on the program?
0
 
LVL 75
ID: 26151841
" do you think I might miss some of the Microsoft updated "
I don't think so.  I've been using that technique for a long time ... and I've never seen it not appear.

At least you see what I'm talking about in A2007.

mx
0
 

Author Comment

by:jodstrr2
ID: 26153434
mx,
I think I know the problem, I test the one that I sent to you in Access 2003, and tables are in local, I see the AN in the form when I create a new record, but the actual program in production that all tables are in SQL server not in local, so I think that's the problem with SQL server, because everything works fine when the tables are in local, but things are not working when it's in SQL server.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 26155226
SQL Server ?  <sigh>  Now you tell me.  That's a completely different story.  And I don't work with SQL Server.  And no doubt that is the problem.

mx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question