How to create a field similar to a unique identifier field

C

chrisjames25

Registered User.
Local time
Today, 14:26
Joined
Dec 1, 2014
Messages
404
Hi

I have a form where i enter batches of products created, and record the when, where how many were created.

Everytime a batch of products is created it get a unique 4 digit code. Due to internal business purposes it cant be more than 4 digits. So what i am looking in vba is 3 fold:

1. How to make first record in this field be 1000 (for label printing software purposes has to start at 1000 and not 0001)
2. how to make the next field autopopulate with 1001, 1002, 1003 etc etc
3. Assuming the more difficult part, how to make it so that when the batch number gets to 9999 it knows that the next record should return to 1000.

If anymore explantation is required, please shout.

Cheers
 
Hi. You have two options:
  1. Use a table with one record to store the current or next value in the sequence and simply update it each time you use it, or
  2. Use the DMax() function to find the most recent value and simply increment from it.
Based on your requirement to go back to 1000, I am thinking option 1 is probably better than option 2.
 
Hi. THanks for speedy response.

Think i understand what you are saying. So for example when i load the form I would get it to lookup the single record in Tbl_batchno and add 1 to the number in question. When i clicked save in the form to save the batch as part of that sequence i would update the form with the newly used batch number? And then add an if statement which would check if the tbl was at 9999 and if so rather than 9999+1 the n tbl_batchno record 1 is 1000?
 
chrisjames25 said:
Hi. THanks for speedy response.

Think i understand what you are saying. So for example when i load the form I would get it to lookup the single record in Tbl_batchno and add 1 to the number in question. When i clicked save in the form to save the batch as part of that sequence i would update the form with the newly used batch number? And then add an if statement which would check if the tbl was at 9999 and if so rather than 9999+1 the n tbl_batchno record 1 is 1000?
Click to expand...
Exactly! Something like that should work, right?
 
chrisjames25 said:
a unique 4 digit code
Click to expand...

I'll point out that if the value can repeat, it is not a "unique" 4 digit code, and couldn't be used on its own to identify a record (if that's a goal). You'll get more options if you use letters as well as numbers.
 
CHeers Pbaldy. Yep appreciate it isnts a unique number by itself. Explained to business but there rationale is that they only creat about 2000 batches a year so when looking at stock on floor chances of 2 batches of 4444 are slim especially of the same product. Am also writing code in to add the year to the front of the batch no so it will really be 20-1000 if it was produced this year. However the 20- cant fit on the label only the 1000 can. HEAVEN FORBID THE COMPANY GET BIGGER LABELS ;)
 
In your code to increment the 'ID' field, run check code such as
Code: 
If ID=9999 Then
    ID =1000
Else
    ID =ID+1
End if
 
Hi

I have tried option 1 above but the code doesnt seem to fire

Code: 
Dim i As Integer
Dim OldBatch As Integer
Dim Newbatch As Integer


Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_BatchNo")

For i = 0 To RecordCount - 1

    If rs.Fields("id") = 1 Then
    OldBatch = rs.Fields("nextbatchno")
    Newbatch = OldBatch + 1
    rs.Edit
    rs.Fields("nextbatchno") = 9999
    
    rs.Update
    End If
    
rs.MoveNext
Next i




End Sub

Probably going around it all wrong but wanted to have a try rather than just say please write it for me.
 
isladogs said:
In your code to increment the 'ID' field, run check code such as
Code: 
If ID=9999 The
    ID =1000
Else
    ID =ID+1
End if
Click to expand...

Thanks for this, your message came up after i typed my recent post. How would i define ID to link to the speciifc table or should i just bind the textbox im using in the form to that specific table?
 
chrisjames25 said:
Hi

I have tried option 1 above but the code doesnt seem to fire

Code: 
Dim i As Integer
Dim OldBatch As Integer
Dim Newbatch As Integer


Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_BatchNo")

For i = 0 To RecordCount - 1

    If rs.Fields("id") = 1 Then
    OldBatch = rs.Fields("nextbatchno")
    Newbatch = OldBatch + 1
    rs.Edit
    rs.Fields("nextbatchno") = 9999
   
    rs.Update
    End If
   
rs.MoveNext
Next i




End Sub

Probably going around it all wrong but wanted to have a try rather than just say please write it for me.
Click to expand...
Hi. You should be able to use DLookup() rather than all that. For example:

Code: 
Newbatch = Nz(DLookup("nextbatchno","Tbl_BatchNo","id=1"),0) + 1
 
chrisjames25 said:
Thanks for this, your message came up after i typed my recent post. How would i define ID to link to the speciifc table or should i just bind the textbox im using in the form to that specific table?
Click to expand...

Replace ID with the appropriate field name for your table
 
theDBguy said:
Hi. You should be able to use DLookup() rather than all that. For example:

Code: 
Newbatch = Nz(DLookup("nextbatchno","Tbl_BatchNo","id=1"),0) + 1
Click to expand...
Yep that works. So much simpler than my option. Many thanks
 
Hi Chris,

If you're asking me, I would use an UPDATE query. For example:

SQL: 
UPDATE Tbl_BatchNo SET nextbatchno = nextbatchno + 1 WHERE id=1

Hope that helps...
 
theDBguy said:
Hi Chris,

If you're asking me, I would use an UPDATE query. For example:

SQL: 
UPDATE Tbl_BatchNo SET nextbatchno = nextbatchno + 1 WHERE id=1

Hope that helps...
Click to expand...
Cheers DB. Is that in vba or do i need to tweak it so i can write a sql statement in vba. Apologies for all the novice questions. Was never great at access and its like my brina has been scrambled with a year away from the project.
 
chrisjames25 said:
Cheers DB. Is that in vba or do i need to tweak it so i can write a sql statement in vba. Apologies for all the novice questions. Was never great at access and its like my brina has been scrambled with a year away from the project.
Click to expand...
No worries. That's a straight up SQL statement for a query, but you can convert that to VBA like so:
Code: 
CurrentDb.Execute "UPDATE Tbl_BatchNo SET nextbatchno = nextbatchno + 1 WHERE id=1", dbFailOnError
 
Perfect. Thanks so much for all your help. Problem solved.

THis was final code for completeness:

Code: 
Dim Newbatch As Integer

Newbatch = Nz(DLookup("nextbatchno", "Tbl_BatchNo", "id=1"), 0) + 1

If Newbatch = 9999 Then

CurrentDb.Execute "UPDATE Tbl_BatchNo SET nextbatchno = 999 WHERE id=1", dbFailOnError

Else

CurrentDb.Execute "UPDATE Tbl_BatchNo SET nextbatchno = nextbatchno + 1 WHERE id=1", dbFailOnError

End If

Me.Txt_BatchNo = Newbatch


End Sub
 
Noticed some flaws with above code. Hard to explain them so will repost shortly with correct code.
 
Hi. FInally cracked it:

Code: 
Dim PreviousBatch As Integer
Dim Newbatch As Integer

PreviousBatch = Nz(DLookup("nextbatchno", "Tbl_BatchNo", "id=1"), 0)


If PreviousBatch = 9999 Then

    CurrentDb.Execute "UPDATE Tbl_BatchNo SET nextbatchno = 1000 WHERE id=1", dbFailOnError
    Newbatch = Nz(DLookup("nextbatchno", "Tbl_BatchNo", "id=1"), 0)
Else

    CurrentDb.Execute "UPDATE Tbl_BatchNo SET nextbatchno = nextbatchno + 1 WHERE id=1", dbFailOnError
    Newbatch = Nz(DLookup("nextbatchno", "Tbl_BatchNo", "id=1"), 0)
End If

Me.Txt_BatchNo = Newbatch


End Sub

Thanks again for all your help
 
chrisjames25 said:
Hi. FInally cracked it:

Code: 
Dim PreviousBatch As Integer
Dim Newbatch As Integer

PreviousBatch = Nz(DLookup("nextbatchno", "Tbl_BatchNo", "id=1"), 0)


If PreviousBatch = 9999 Then

    CurrentDb.Execute "UPDATE Tbl_BatchNo SET nextbatchno = 1000 WHERE id=1", dbFailOnError
    Newbatch = Nz(DLookup("nextbatchno", "Tbl_BatchNo", "id=1"), 0)
Else

    CurrentDb.Execute "UPDATE Tbl_BatchNo SET nextbatchno = nextbatchno + 1 WHERE id=1", dbFailOnError
    Newbatch = Nz(DLookup("nextbatchno", "Tbl_BatchNo", "id=1"), 0)
End If

Me.Txt_BatchNo = Newbatch


End Sub

Thanks again for all your help
Click to expand...
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
You must log in or register to reply here.

Similar threads

W
Solved From with One-To-Many Relationship table
Replies
19
Views
679
wmix
W
D
Checkboxes and unique records.
Replies
5
Views
508
arnelgp
arnelgp
D
How do I set up sequential numbering in one field of a table for each alike value in another field? I want to have this applied in a query.
Replies
13
Views
1,269
Pat Hartman
P
L
Making a form to update multiple tables
Replies
6
Views
591
LakshinK
L
R
Solved Dreaded 'Write Conflict' error after adding a new field to Access form
Replies
12
Views
709
ricardocpa
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom