Auto Call #

[Galaxiom]

I realise you are working on someone else's database but I just had to comment on the horrible fieldnames. Using special characters, particularly the hash makes reading it very clumsy. Special characters already have meaning in code and should be completely avoided in names.

Names with spaces and special characters always require the square brackets creating unnecessary work. Spaces break up the code unnaturally by placing greater separation within a name than outside of it.

Case is a poor choice to include in a name too because the Select Case statement in VBA.

The worst name I ever saw used in a database was [Select Case].

 

[GinaWhipp]

@GalaxiomAtHome

While I agree, I tend to work with what I have unless a rewrite is in order. Then I post...

http://www.regina-whipp.com/index_files/NamingConventions.htm

 

[ssworthi]

Again I so appreciate your help. If there were a way to get the date/yr somehow in the Case #/Call #/Incident # that would be the ultimate but if we need to use another field that would be fine. I'll take anything. Thanks again!

 

[ssworthi]

I created an incident # as an auto number; could I get a case # generated off of that such as MMDDYY-Incident #?

 

[GinaWhipp]

Sorry have been tied up with Clients, will get to tomorrow...

 

[ssworthi]

And now yet a bigger challenge. I met with the Security team yesterday to show what we had done so far.We started with the call log which is basically folks calling in to report something and based on the call # I was able to create a case #.Call # Field: Auto Number
Case #field: Me.CaseNumber = (Format(Now, "yy") & "-" & Me.CallNumber)

Now they say not all calls are important enough to generate a case # so they would like the ability to create one through a process/button/macro. Any ideas?

Again thank you so very much with your help; it's been invaluable especially in opening my eyes to things on "how to".

 

[GinaWhipp]

Oops, sorry for delay got busy... Intead of putting that line on the Before_Insert you can attach it to a button. BUT my concern is what do they want to happen to the not so important calls... Are they not to be entered into the database OR do they want some other ID assigned to them?

 

[ssworthi]

Thanks for getting back to me. In the meantime I took it to one of our "programmers" here who advised I was working in a very antiquated db and offered to over the project so I put the two together. I do have one other question if you don't mind. I have a recap page for a utility bill which has not invoice #. So I am using the customer number and trying to use mmyy. Unfortuanately, by the time I get the next invoice it's in the month following the service and I want the date for which the service was performed;
Invoice: ([Customer #] & "/" & Format(Date(),"mmyy")). Would it just be easier to update in query? Thanks as always for your help.

 

[GinaWhipp]

Yes, just do a simple UPDATE query...

 

[ssworthi]

Thanks so very much!

 

[ssworthi]

So I think I spoke too soon. This is the field that is part of the summing query for the summary sheet; would it be better to add the Invoice filed to the table and run an update query on the table? Or is there someway to update this expression?
"Invoice: ([Customer #] & "/" & Format(Date(),"mmyy"))".

 

[GinaWhipp]

Unless you do *not* want to store the value then add the field and run the UPDATE query.

 

[ssworthi]

I think I will add the field to the table and run the update query; thanks so much!