input mask help

[demax182]

Looking for a way to do this, I have a field "contractor" and another field "call number". Here's what I want to do, based on "contractor", I want "call number" to have a certain input mask. May not be possible, but would entertain any ideas. For example, this is what I want to do.

If contractor = Kinko Juki, I want the format of call number to be A0001, A0002, A0003, and so forth.

If contractor = Ameku Juki, then I would want K0001, K0002, K0003 and so forth. The reason being is that there may be duplicate "call numbers" for each contractor. That being said, I can't make the field a primary field. Any ideas how to get through this little dilemna? Thanks in advance for your help.

Mike

 

[Trendsetter]

Could you clarify how you want to set the prefix for the call number!
you are saying if called Kinko Juki then A0001
but if called Ameku Juki then K0001..........

If you don't want duplicates you don't need to make it a primary key but index without duplicates.
If you have duplicates in your list it will not allow that field to become a Primary key.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[demax182]

Trendsetter,

I see what you mean. I want those with Kinko Juki to have "A" in front of the number. That being said, I want the value A0001 to be inputted into the database, not just 0001. Same goes for Ameku Juki. I just realized this can't be done with the input mask.

Actually, maybe a prefix is not necessary, I want to make sure that there is only one combo of "contractor" and "call number". I want to ensure that if there's one and only one entry of

Contractor = "Kinko Juki" and
Call Number = "0001"

I hope this is making sense.

 

[Trendsetter]

There is no problem putting a prefixed number into the database but you are asking for
Kinko = A0001 and
Ameku = K0001.
Are you using the first letter of the first name (and have got them the wrong way round) or are the letters just random???

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[demax182]

they are just random, sorry for the confusion

 

[Trendsetter]

OK!
Do you have any data in the system at present or are you just building?
Do you want the system to generate the next number when you enter a new contractor's name?

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[demax182]

Presently, I have no data, I'm still building. It would be nice if the system does generate the next number. Makes my database that much more user friendly. Thanks.

 

[Trendsetter]

Put this code in the After_Update event in the properties page of the textbox holding the contractor's name.

Dim rst as recordset
Dim NextNum as Integer
Set rst = Currentdb.OpenRecordset("SELECT * FROM tablename")
NextNum = rst.RecordCount + 1
Me.CallNumber = Format(NextNum, "0000")
rst.Close
Set rst = Nothing

Me.CallNumber is the textbox for the contractor call number

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[demax182]

Thanks!

Anyway of setting a prefix before the number?

 

[Trendsetter]

You could take the first letter of the name and put that at the front....

Dim rst as recordset
Dim NextNum as Integer
Dim ident as string
ident = Left(Me.ContractorName, 1)
Set rst = Currentdb.OpenRecordset("SELECT * FROM tablename")
NextNum = rst.RecordCount + 1
Me.CallNumber = ident & Format(NextNum, "0000")
rst.Close
Set rst = Nothing

Me.ContractorName is the textbox holding the contractor name... I bet you are shocked by that!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[RoyVidar]

Just some grumpy warnings...

Why prefix with a letter - what happens when the number of contractors exceeds 27 (Z)?

It seems you're trying to use this field to identify both the contractor and the "recordnumber"

This is kind of violating rules of normalization (or the "atomic" rule) - you should not store more than one piece of information in one field - it will create headaches later.

To me, it seems you need some more work on the table structure. From the info provided, I'd say you should probably have two tables Contractor and Calls, with a one to many relationship. The primary key from contractor goes as foreign key field in calls. On the form where you intend to use it, use a combobox looking up the Contractor table.

The primary key of Calls can then be, based on your preferences an increment (autonumber or calculated programatically as above (remember the .MoveLast prior to fetching the .recordcount, though, and be aware of dangers of such scheme in multiuser setups, see for instance MichaelRed's faq faq700-184)), a combination of the foreign key (from Contractor) and for instance a timestamp, or...

Just save yourself from the future headaches of doing a concatenation scheme that is (in my view, most certainly) going to bomb.

Roy-Vidar

 

[Trendsetter]

Some of the concerns expressed are valid so...
a)let's link the recordcount to the first letter of the name so that we can have A0001 to A9999 through to Z....

b)If the letter selected has no values we will assign 0001

Dim rst as recordset
Dim NextNum as Integer
Dim ident as string
ident = Left(Me.ContractorName, 1)
Set rst = Currentdb.OpenRecordset("SELECT * FROM tablename WHERE "ContractorName Like" & ident* & ")
If rst.EOF Then 'no records exist starting with selected letter
NextNum = 1
Else
rst.MoveLast
NextNum = rst.RecordCount + 1
End if
Me.CallNumber = ident & Format(NextNum, "0000")
rst.Close
Set rst = Nothing

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[mustangcoupe]

I was browsing this thread and came up with a second what if.....

What is you have more then one contractor with the same first letter? Could this ever happen in your database?

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)