Generate Autonumber in Textfield On Form of Access

billa856

hi,
i have to use the table(PRODUCTION) already generated in which all fields are of TEXT type.fields like (orderdate,palletno,customercode,itemno,pono,carto n,pcs,totalquantity)Now i have already some data in table like 7954 rows.Now when i open the form for entering details of new customer i want to generate new PalletNo (7955)automatically in TEXTFIELD(PalletNo_Text).Means whatever the last PalletNo in table +1.means i have the IDNO from 1-7954.Now i want to generate 7955.So what to do?
I already try this code but it is not working.

Private Sub Form_Load()
PalletNo_Text = Nz(DMax("[PalletNo]", "PRODUCTION") + 1)
End Sub

any solution appriciated.plz reply me as soon as possible.its urgent

Nov 19 '07 #1

Megalog

Ok,
This may be a stupid question, but are you able to change the table? I would add an autonumber field (if all your palletno values truly are sequential, and unique already). I would just add the autonumber as a new field in the table, add a text box for it on the form, set the visible property to false, and then On_Current do:

Either way, I'd think adding that autonumber field as a genuine unique index and primary key would help things in general.

Ideally you'd want to just get rid of the duplicate information, and rename the new autonumber field to PalletNo.

Nov 19 '07 #2

billa856

Sorry but i can't change the table,whenever i try to change it will show me that u can't change type because u have data in table.And my palletno are not sequntial in starting but after that it is, but they are unique.
So i only want that whatever is the last palletno in my table, i want to add next no in my form as palletno. is there any query or coding so that i can select the last pallatno of my table.and generate next no to that palletno.?pls reply me

Nov 19 '07 #3

missinglinq

You say that all fields are text, so I think you need to make [PalletNo] numeric in order to find DMax! I've used something like this successfully

Nz(DMax("val([PalletNo])", "PRODUCTION") + 1)

And a true AutoNumber, which is to say one generated automatically by Access, is, as John Vinson, MVP says, not fit for human comsumption! They should be left for Access to use for its internal housekeeping, and numbers for purposes such as we are addressing here should be generated just as we're doing here!

And to both of you, Welcome to TheScripts!

Linq ;0)>

Nov 19 '07 #4

billa856

Hey thanks Buddy now its working.
Again thank you both very much for ur help.

Nov 20 '07 #5

missinglinq

Glad we could help!

Linq ;0)>

Nov 20 '07 #6