Creating a unique ref with a twist!!

[frangipani]

Creating a unique ref with a twist!! Help please!!!

Please help!!

I am creating a DB that records inspection data. This data falls into a number of categories, A,B,C..

I need to create a unique ref no for each report based on the category and the number of entries for that category, ie.

Cat: No
A 1
A 2
A 3
B 1
C 1
A 4
C 2

This needs to be created when I select the Cat from a combo box on the form!

REally hope someone can help I am at a complete loss - am almost a beginner too!!! - Thanks

 

[IfButOnly]

franqipana - I'm not sure you have provided enough info but let's see if we can get it started.

Assuming you have a field for the reference number, then set the Control source property to:-

=[ComboBoxName].[Column].(0) & " " & intNumOfEntries

this basically just appends a space and the number of entries to the category number you selected from the combo box. It assumes that the combo box has only one field, or the category code is the first field; that you know the number of entries for each category (intNumOfEntries); and that you want a space between the two.

 

[frangipani]

Thank you for your reply Peter,

Unfortunately I do not know the number of entries in each category - as each time a new record is entered in that category the number increases.

I may have 10 health and safety reports,(HS001, HS002..HS010) 10 environmental(ENV001....ENV010), 10 industrial, then some one enters another in say the industrial category - its reference number must be Ind011.

I need access to beable to say well count I guess- this is the 11th record entered in the Industrial category - therefore its ref no is Ind011

Any ideas???

thanks.

 

[IfButOnly]

I have a couple of suggestions but I'm not much more than a beginner myself - so there may be better ways.

Firstly, is it feasible to structure your db differently and use autonumber keys?

Secondly, if I understand it correctly, when you enter the data, you select the category from the combo box and want to count the number of records for that category that already exist- and then take the next number.

One way to do this is to have an after update event on your combo box which will perform a select on the appropriate table to return records for that category.
eq.
strSQL = "SELECT * FROM tblreports WHERE category = " & Chr(39) & Me!ComboBoxName & Chr(39)
Me.tmpListBox.RowSource = strSQL
CountOfCategory = Me.tmpListBox.ListCount

(Chr(39) is just to enclose a literal in quotes, and tmpListBox is somewhere to store the result) - I'm sure there will be a better way to do this but someone else will need to help there.

Therefore you new reference number will be:

NewRefNum = Me!ComboBoxName & " " & CountOfCategory + 1


Hope that makes sense.

 

[pono1]

f,

To supplement Peter's post: Does the number portion of the ref number have to reflect the current count or can it simply be the next available number?

And will records ever be deleted?

Let's say a user creates records and the application provides href numbers as follows:

Hea1
Hea2
Hea3
Env1
Env2
Env3

And now let's say Hea2 is deleted.

When a new record is created for Health and Safety, should it be Hea2 rather than Hea4? If Hea2, this might make things more complicated, technically speaking (the app will have to be able to detect the sequence gap), and perhaps more confusing, functionally speaking, since an old record will be deleted and then its "identity" will be assumed by a new record...

Regards,
Tim

 

[frangipani]

Hi Peter - Can't get it to work - must be doing something daft - it just keeps putting the text in the list box. Also will it work for more than one category and keep count for all of those categories?

Tim, records may be deleted but the record number assigned to them will not be re-assigned.
Thx

 

[IfButOnly]

Tim's point is very valid and my approach won't provide the correct number if the categories can be deleted.

I will have a look around for the solution, but hopefully someone else will know the correct approach and jump in.