By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,161 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,161 IT Pros & Developers. It's quick & easy.

Generate Autonumber in Textfield On Form of Access

P: 101
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
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 378
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:

Expand|Select|Wrap|Line Numbers
  1. If PalletNo_Text.value = "" then
  2.  PalletNo_Text.Value = YourNewAutoNumField.Value
  3. End if
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

P: 101
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

Expert 2.5K+
P: 3,532
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

P: 101
Hey thanks Buddy now its working.
Again thank you both very much for ur help.
Nov 20 '07 #5

Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
Nov 20 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.