Making access split form read only - building safety business model

[Frothingslosh]

No, Autonumber is a field type, just like Long, String, Yes/No, and Date. That's why I keep telling you it needs to be in the tables. You don't 'get autonumbers for each consultant', you pull the consultant login and whatever you're using as the ID for the tickets.

Why do you think concatenation forces a query to be placed in the back end, despite me telling you repeatedly that it does no such thing?

 

[plog]

So this query with autonumbers...

Nope. Stop right there, Autonumber lesson time.

Tables have fields. Fields have datatypes. [Birthdate] would use a Date/Time datatype, [FirstName] would use a Short Text datatype and [TransactionAmount] would use a Number. Another datatype is Autonumber. Most commonly fields that use autonumbers use ID in their name (e.g. [TransactionID], [CustomerID], etc.).

So 'query with autonumbers' makes little sense. I mean technically, a query can just show all the data from a table and since a table can have a field populated with autonumbers, you could show the autonumber in a query. But when you say it like you did, it makes it seem like you don't know where they belong. Nor how they are to be used.

The only purpose they should serve is ensuring that all the records in a table have their own unique value. That way, you can use that value as a foreign key (https://en.wikipedia.org/wiki/Foreign_key) in another table, thus linking records together. Autonumber ID fields such as those should hold no meaning, nor should you assign them any meaning. You also shouldn't assume that they will come out in any particular order or try to assign them yourself. They should only be used to tie records in different tables together.

So, you trying to prefix those numbers with anything is a bad idea and serves no purpose. The next big issue is your mis-understanding of how tables are to work. You will not have one table for each consultant. You will have one table for all that data and a field whose value designates which consultant each record belongs to.

 

[jaryszek]

Thank you plog and Frothingslosh for your answers.

Thanks to your explanation i know what you have in mind. I watched a several youtube films and read about it.

So autonumber field is within my Main Table.
Ok so this is in back-end !

At least i know

Now i have to do something like this:

http://stackoverflow.com/questions/19762250/generate-a-sequential-number-per-group-when-adding-a-row-to-an-access-table

but i can not implement it.
I want to do autonumber based on login name. So using group by clause.

Can you help me ?

Jacek

 

[Minty]

Nope - You aren't quite getting it - You cannot control an autonumber like that. Keep your case number completely unique. Don't get into a repeating sequence per consultant, it's unnecessary and won't really mean anything.

What's the difference between SPE-0001, SPE-2365, LJC-5432 ? They are different.
If you are thinking you could identify how many cases they have had by the number don't. What if you delete one? Just count the number of cases by consultant.

You have a consultant ID this is unique per Consultant. You have a case ID this is unique. You can create a query that joins them together for display purposes only.

CaseRef = ConsultantID & "-" & CaseID

 

[jaryszek]

Minty, thank you.

I understand your answer but i have to do it per each consultant. Unfortunately (business requirement).

Additionally i have to avoid doing queries in my back-end. So the best result will be one table with autonumbers per each consultant.

I somehow i can do it (but i don't know how yet

Jacek

 

[Minty]

You can't. Not using Autonumbers. You will have to create a function / vba to do it for you. The business requirement is one thing but what is the real purpose of the "Sequential" case number?

If it's only because "We've always done it that way" then now is the time to change. It really won't make any difference, and will massively simplify your coding and setup.

Edit - And definitely not a table per consultant!

Please listen to the advice you are being given - between the answers you have been given there is probably 80 + years worth of database design experience being dished out to you.

 

[jaryszek]

Minty,

I agree with you and i am listen to you all here
And you have right - it was always done it that way but I have no that big impact on it to change it.

And I know that one table will be the best solution and i want to do it but i have to do this with agreement with my business.

So I will have to do it with function or VBA so i am asking you Access Gurus for help.

Jacek

 

[Minty]

I'll try and simplify the table argument for you;

You wouldn't store data for a phone directory in a small table per town would you? Hopefully not.
You would store all the peoples names and phone numbers and an area / area code with that information to allow you to identify which town it was in.

The number of records doesn't really matter, as you can very quickly filter the data just to one area "SmallTown" or "BigTown".
So you would only have one report (rptPhoneBook) and filter it to produce the phone book for an area.
You would only have one Form to look this information up for the whole data. Just filter it.

This is the same as your case data. You just filter the single forms and report to the Consultant logged in.

 

[jaryszek]

Minty,

I understand this, thank you for that explanation.
And i know that the best solution is to have all data in one table.

Problem is my business - i am only developing applications in my company so i can't in specific aspects impact on their needs.

In excel they have unique number for each consultant and if i want to do this in Access - i have to move solution from Excel to Access.
So i have to do table with autonumbers for each consultant. Unfortunately.

Please help with this. I know that teoritically it has no matter but in my company - i cant avoid this...

Jacek

 

[Minty]

Well I'm afraid I can't assist any more at the moment as I'm closing down for the Christmas break, and my wife gets grumpy with me doing database stuff when I should be drinking and eating

You definitely don't need or want the separate consultant tables. We can program a sequential number for the cases by consultant but it's messier than not having to have it that way.

Do a search on here for sequential numbering and you will find many similar threads with many examples of how and why it's not a good idea, but with work around's if management must insist on things that in reality make no sense and will have no impact on them in the long run but will make your job harder

 

[jaryszek]

Thank You Minty !

Merry Christmas for you!

I will search here or i will create new topic connected with sequential numbering.
I know that i can use Dcount function for this but this will create a extra query for my back-end...

Jacek

 

[jaryszek]

Guys,

if i have to write function or vba can i write vba code and still have one Main table?

I would to have only one back-end table and vba code to do autonumbers for each consultant.
Is it possible?

Merry Christmas,
Jacek

 

[plog]

if i have to write function or vba can i write vba code and still have one Main table?

I would to have only one back-end table and vba code to do autonumbers for each consultant.
Is it possible?

It probably won't soften whats to come, but I rewrote this 3 times trying to make it as polite as possible. So please understand, its only meant to be half-harsh. Here goes:

This is the 3rd page of this thread and the more questions you ask, the more it seems this project is beyond your capabilities. I suggest you either contract this project someone familiar with Access, or you enroll in an Access class. Many can be found online, most likely a community college near you might offer some.

At this point, after all the time we have taken to thoroughly explain autonumbers and detailed why your approach of multiple tables/custmozed ID numbers is wrong your questions make it seem none of it has gotten through.

 

[jaryszek]

Thank you plog.

I know that i can learn Access only with online help and i don't have anybody who can help me with Access in my company.
The same was with my Russian language (2 years fluent speaker) and with my VBA -
I am expert within Excel and macros and i am a self-taught man.

If you can't answer me or at least show me the way - I will be create new topics to achive what i want.

You wrote me yes, even more then 3 times but if i don't understand it means that i have to ask about more basics.
And i will try to do it

I wrote with Minty about doing one big Main Table in back-end. And we also established that if i want to have autonumber for each consultant there will be needed a function or vba.
So next i should find a way to do it.

aa once more:
plog - you wrote about making autonumber but this autonumber was not for each consultant only for the main table.

Jacek

 

[jaryszek]

And i know that this forum is also for helping others, even with basics... so please help me,

thank you in advance!
Jacek

 

[jaryszek]

Hi Guys!

I have found maybe a solution, what do you think ?

In front-end i can create a VBA connected to my table in back - end like this:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As Database
Dim rs As DAO.Recordset
Dim currentCode As String
Dim seq As Long
Dim strSQL As String
Dim numer As String
Dim strCountry As String

strCountry = "skle00"

Set db = CurrentDb
strSQL = "table1"

Set rs = CurrentDb.OpenRecordset(strSQL)

With rs




Debug.Print rs.Fields("Login")
numer = DCount("Login", "table1", "[Login]= '" & strCountry & "'")

'' rs.Fields("Login") = numer

MsgBox numer

.MoveNext


Set rs = Nothing
Set db = Nothing
End With

End Sub

This code counting ticket numbers based on each consultant. (Dcount function).
I have to change it a little bit in order to input result in good column.

And do this function in good place when user insertiing new row for example in before_update action

And whole users writing their recors should be unique autonumbers per each consultant in front - end. And still will be one table only

Best Regards,
Jacek

 

[jaryszek]

Hi Guys,

how are you after Christmas?
In good mood?

What do you think about my last solution?

Jacek

 

[jaryszek]

Hello People.

How are you ?

I would like to ask you more about this topic.

1. What do you think about my idea to do VBA macro in front end and automatically add consultants' tickets number?

2. Security. I have already front-end and back-und and all data in Access Database. No Excel as you said and recommended for me

What about security? What if one user will delete somehow whole data in Access?
In Excel - Access model even if whole Access Database was deleted - each consultant had his data in Excel so automatically i have a back-up.

Here all consultants are working in one front-end file. This is safe?

This front-end file should be shared on network drive i suppose?

Thank you for your suggestions,
Warm Regards,
Jacek Antek

 

[Frothingslosh]

Look, we've told you what you need to do.

You've refused our advice. Repeatedly.

You're likely not going to get any more help on this issue as long as you continue to refuse to listen.

 

[jaryszek]

Why Frothingslosh?

I did as you told me. I have only Access Database. And i have front-end and back-end only And this front-end will be on network drive shared for all consultans.
So 2 files as you told me.
Everything as you told me.

But Can I have my own point of view and modify a little yours suggestions?
My question is about security and about explaining it.
And second question is about my macro in front-end which is numbering ticket as my clients want. It is a good code for this? Or maybe not?

One difference between your suggestions and my model is that I have to do different numbering for tickets, but as i wrote...this is my business requirement and I have to do it this way. I must do it in this way...
So i have to solve this differently and create special numbering for each consultant...unfortunately...

I would love to do it with your way but I can't so I have to find out something also good working and done with best practices.

Thank You Guys for your support, clues and help.
But this 2 more things I would like to explain.
Could you please help me?

I don't want to cross post and looking for another forums - i know that here are smart people who can help me.

Warm Regards,
Jacek Antek