Automatic field population in Access?

[Psycho_NZ]

Hi all,

I have a Access DB and the key field is a code like this C####. (e.g. C0001, C0054). The number is meant to match up to the record number, so when they're browsing the form the thing at the bottom says record number 5 and they're on C0005.

Make sense? I hope so I would like to automate this field (at the moment the user just types in the key code, but they keep messing it up). Is there some way I can make a macro or module that knows when a new record is created and automatically puts the correct value in this field?

Any help would be greatly appreciated, thanks heaps for your time.

Ang

[Granty]

You could get the recordcount of the table before you insert the new record, add 1 and then concatenate the "C" (and any leading zeros you require) on the front of it.

This all seems a bit dangerous to me tho. If you ever delete anything from that table it's likely to skew your recordnumber to keyfield relationship anyway......

[Matt_T_hat]

Originally posted by Psycho_NZ
I have a Access DB and the key field is a code like this C####. (e.g. C0001, C0054). The number is meant to match up to the record number, so when they're browsing the form the thing at the bottom says record number 5 and they're on C0005.

Try this: forget setting the Keyfields to preformatted stuff (bare with me this leads somewhere) AND turn off the navigation buttons on the form (set to No).

No put in the bottom left an unbound textbox. Set the lable to "Record Number:" and set Locked to true in the prperties. It is now unchangable.

Now for some custom navigation: turn the tools wizard on (it's the wand next to the arrow). draw a button and use the wizard to add custom: back_one; next; New_Record; etc buttons. Now you can move around and see an empty box.

Now to populate the box:

on the table: Set the key field to an autonumber. Put the field for this on your form and set it to (Visible: No).

Now you unbound box should get this kind of code in it:

="C" & KeyField & "."
Make sure that your auto number is formatted to the size you want and bingo you have the named records.

Now to navigate by named records

Create a query: with KeyField and this "RecName: "C" & KeyField & "." as the two fields.

Now go back to the form and create an unbound combobox with Row Source as your query, bound to column 1. Goto the combobox's format properties and set column count = 2 and column widths = 0;3 (yes it looks funny but trust me)

now the dropdown should show all the C##### records.

Next select the forms properties and select event (tab 3). Select [Event Procedure] for the On Current event and press the [...] button. You are now in the code section.

hopefully you remember what you called you combobox (I'll assume you called it ComboRecs)

In the Sub put this code:

ComboRecs.Requery

Now everytime the form moves to a different record any changes update.

Now for the navigation I prommised:

use this code for the AfterUpdate Property of the combo box.

Private Sub ComboRecs_AfterUpdate()
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Franchise ID] = " & Str(Me![Namer])
    Me.Bookmark = rs.Bookmark
End Sub

Now you have all the appirence of a standard nameing convention for records BUT none of the hassle it's automated and fully customised. Your boss (or whoever) will love it.

Users can also type record names into the drop down and it will do it's best to guess what they might be typeing try it and see.

hope I was clear with those instructions.

[Psycho_NZ]

Thanks Matt_T_Hat!

That looks like a mighty-good way to do things. I'll try it out tomorrow when I'm back in the office and let you know.

Thanks again
Ang

[Psycho_NZ]

Thanks again, that's working good.


Ang