Get that Autonumbered field value for a form

[EdwardMartinIII]

My small database has an Auto-Numbered field. I have a form for entering data into the database, but part of what the form is supposed to do is to perform a manipulation that requires the "current" value of that Autonumbered field.

I can even grab the last completed value and add 1 to it, if I knew how, or could search the archives.

Advice?

Thanks!

Edward "Do not read this sentence."

 

[SBendBuckeye]

Hello Edward,

You should NEVER, EVER do any calculations based on an autonumber primary key field. The reason for that is because you have absolutely no control over the number. If you delete some records there will be gaps in it, etc.

What are you trying to do with your calculation? If you want some kind of an incrementing number for invoices or purchase orders, you could do that with a primary key like you are talking about, just don't make it autonumber. Of course then you are responsible for updating it, etc.

If it is some other kind of calculation, perhaps it could be accomplished another way. Please repost with some additional information.

Good Luck!

PS. As an aside, I have read in a couple places that you should not use autonumber primary keys in a multi-user environment either. I am not in that situation so I can't speak to the issue from experience.

 

[EdwardMartinIII]

Well...

The number is supposed to be a unique identifier for that record. The column is called "Trouble Ticket Number" and they never change. Trouble Ticket #414 (the autonumbered field) will always be the same record, right? I don't want subsequent numbers to change if a record is deleted, because people refer to the Tickets by their numbers. Autonumbering seemed ideal because it wasn't changeable, even when records were deleted.

There is a form for inputting new Trouble Tickets, and that form also allows you to &quot;attach&quot; a file. The file is written to a special network location, but appended to the front of its name is &quot;TT_xxxx_&quot;, where &quot;xxxx&quot; is the value of the new Trouble Ticket Number. That way, once I know the Trouble Ticket Number, I can easily search for all files associated with it (tt_<number>_*.*, etc.). To build the new file name, of course, I need the value of the new field.

After reading what you wrote, it totally makes sense to not just grab the last one and add 1 to it. That didn't take much convincing! But if I can grab the &quot;current&quot; one, I'm still a happy fella.

This was only the second database I've ever built, so forgive me if I've done something really heinous. I suppose I could add a new field, but I would tell it to adopt whatever the value of the AutoNumber field was, because that's the behavior I want in that field.

Am I way off-base on this?

Cheers,

Edward &quot;Do not read this sentence.&quot;

 

[scriverb]

If you just want to use the AutoNumber field value you can create a bound form object on your form. You can make it invisible if you wish. The bound object can now be referred to in your code by whatever name you give it.

I say you can keep it invisible so that the user will only see your vinalized manipulated trouble ticket id. Bob Scriver

 

[SBendBuckeye]

Hello Edward,

The way you want to use it is perfectly fine now that you have explained it. Once assigned, the autonumber will not change as other records are added or deleted. The part that threw me was the Add 1 part. If you just let the system take care of it, everything will be OK.

Your code to attach the file will also work just fine with one caveat. Make sure that the new trouble ticket record is saved before you save the attached file. If you save the attachment before the record is saved and the user cancels the record, I'm not sure exactly what happens. I've never had to do this, but it seems like it could get out of sync in such a situation.

Have a great day!

PS. It sounds like you are off to a good start. Access is a lot of fun to work with most of the time.

 

[EdwardMartinIII]

The way I figured it, if the user cancels the entry via the Cancel button, then a sequence of code looks for and deletes any files with the associated number. Likewise when someone &quot;closes&quot; a Trouble Ticket, the associated files are deleted (well, that's the plan, anyway, but I suspect my employer will insist that the files not be deleted ever).

So, I can bind a control to the field and it'll give me the value? Even if the Ticket hasn't been submitted to the database yet? Currently, the Control Source of that control is

=[WS_TT_TroubleTickets]![Trouble Ticket Number]

and it produces a

#Name?

in the field at runtime.

Obviously, there's something silly I'm not doing right...

Cheers,

Edward &quot;Do not read this sentence.&quot;

 

[EdwardMartinIII]

Ack! Silly me. I'm actually going to keep the file names in an array. Then, when they click &quot;Save Record&quot;, I'll parse and eval the necessary commands to copy the files.

Here's what I ended up doing, thanks to help from a co-worker. It allows me to use the data to report the Trouble Ticket number. Eventually, I'll add the rest of the code, but as long as the msgbox reports the right number, I'm on the right path. So to speak.

    ' Open database
      Dim db As DAO.Database, rst As DAO.Recordset
      Set db = CurrentDb
      Set rst = db.OpenRecordset("select * from WS_TT_TroubleTickets where 1 = 0", dbOpenDynaset, dbSeeChanges)
        ' This particular SQL statement returns the structure, but not the data
    ' Add new record
      With rst
        .AddNew
          Combo23.SetFocus
          ![Affected Module] = Combo23.Text
          Combo19.SetFocus
          ![Tester] = Combo19.Text
          Text6.SetFocus
          ![Short Description] = Text6.Text
          Text8.SetFocus
          ![Detailed Description] = Text8.Text
          Combo25.SetFocus
          ![Priority] = Combo25.Text
          ![Date Submitted] = CurrentDate
          ![Status] = ("Not Assigned")
        .Update
      End With
      
      With rst
        .MoveLast ' go to the last record, the one we just entered
        x = CStr(![Trouble Ticket Number]) + Chr(10) + "Detailed Description: " + ![Detailed Description]
      End With
      
    ' Close database
      rst.Close
      Set rst = Nothing
      Set db = Nothing
      MsgBox ("Data written to Trouble Ticket Number " + x)

All that to support the last line...

Cheers,

Edward &quot;Do not read this sentence.&quot;

 

[SBendBuckeye]

After seeing what you posted, I'm not sure exactly what you are trying to do. If you have a form already created with bound controls on it, then much or maybe all of what you are doing above is not needed.

If you are in the Access Table tab, select your table by single clicking it, but don't open it. Once it is selected, there is an option on the Insert menu for AutoForm (also on a tool bar depending on how you have them setup). If you select AutoForm, it will generate a table for you with a bound control for every field in the database.

When the form is in design mode, select the form properties by clicking in the upper left hand corner (similar to the way you select an entire spreadsheet in Excel). Then select the Event tab and click on AfterUpdate and then the Code Builder option. This will create a little block of code for you. That is where you can put the save of the text file because you will never get to that code unless a save successfully happens.

If you click on the individual controls and select the Other tab, there is a Name property. Then you can name your controls ShortDesc, Tester, AffectedModule, etc which will make your code a lot easier to maintain in the future. I would also recommend that you prefix your control names to easily distinguish them from table field names. Use names such as cboTestor or txtPriority, etc.

If you already know this stuff, I apologize for boring you to tears with it.

Good Luck!

PS. For terminology's sake, a bound form is one that is tied directly to the underlying table or query because it has an entry in the control's Data tab ControlSource. If that property is cleared it is an unbound control and the programmer is responsible for handling it.