Solved Auto-Update a Unique Code in a row (1 Viewer)

IbrBadri

Member
Local time
Today, 15:40
Joined
May 24, 2020
Messages
35
Hi,
Can you help me?

I have main form and sub form "one-to-many" in the sub form have three key fields:-
Code1 reading from the main form "Index Duplicate Ok". 12Digits "123-456-789-012"
Code2 Field "Index Duplicate Ok" 3Digits "000". If code1 changed then can use the same code2 again.
Code3 "Index No Duplicate" contain the Code1 & Code2 to be a unique code3 automatically. 15 digits "123-456-789-012-345"

I am using "default value" = Code1 & Code2 in the "Property Sheet\Data" of my Sub-Form to fill the Code3 in a unique field automatically without enable editing to it.

However, sometimes, the Code2 in the Sub-Form changed, and need the unique Code3 to change accordingly in the same row.

Can you give me a sample of command to use in macro or visual basic?

Thanks in advance for your time to help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:40
Joined
Oct 29, 2018
Messages
21,357
Hi. Since Code3 is merely a combination of Code1 and Code2, then you don't need a Field for Code3. You simply use the same formula in a query to display Code3, and it will always be correct (no need to update it when Code2 changes).
 

IbrBadri

Member
Local time
Today, 15:40
Joined
May 24, 2020
Messages
35
Hi. Since Code3 is merely a combination of Code1 and Code2, then you don't need a Field for Code3. You simply use the same formula in a query to display Code3, and it will always be correct (no need to update it when Code2 changes).

Thanks for replay,

But I need this code as a unique key in the table, NO DUPLICATE, or not just unbound field. that is because this code3 the same procedure shall be applied for the Code4 in another sub form with 18Digits.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:40
Joined
Oct 29, 2018
Messages
21,357
Thanks for replay,

But I need this code as a unique key in the table, NO DUPLICATE, or not just unbound field. that is because this code3 the same procedure shall be applied for the Code4 in another sub form with 18Digits.
You can still create a unique key based on two fields. Try searching on multi-field unique index.
 

IbrBadri

Member
Local time
Today, 15:40
Joined
May 24, 2020
Messages
35
You can still create a unique key based on two fields. Try searching on multi-field unique index.

Thanks a lot.
I searched for it and found the attached below text, but not solved my problem, because I already used the multi-field unique index (see the pic below).

My problem is with "how to update the data code "already saved" when I change one of the two fields data?"

In this case my unique code is "LocationID" = [Square| & [PlotNo]
At the first data entry its works 100%, but lately if Square or PlotNo changed the LocationID not changed.

Please help.

Multi Field Unique Index.jpg
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:40
Joined
Jan 23, 2006
Messages
15,362
I recommend that you describe what you are trying to do in plain English (simple terms). Give us some context rather than some field names and coding that doesn't yet solve your requirement.
I would also recommend an autonumber primary key for Access's use.
If you need something for display and/or control, then design a composite unique index.
But first set up a description for users (and yourself) for reference. Once your description is clear, then build, test and adjust a model (on paper) to ensure it matches your needs. Then build the physical database based on your model/"blueprint".
Good luck with your project.
 

IbrBadri

Member
Local time
Today, 15:40
Joined
May 24, 2020
Messages
35
I recommend that you describe what you are trying to do in plain English (simple terms). Give us some context rather than some field names and coding that doesn't yet solve your requirement.
I would also recommend an autonumber primary key for Access's use.
If you need something for display and/or control, then design a composite unique index.
But first set up a description for users (and yourself) for reference. Once your description is clear, then build, test and adjust a model (on paper) to ensure it matches your needs. Then build the physical database based on your model/"blueprint".
Good luck with your project.

Ok,

After changed the old Sudan government (The thieves government), most of neighborhood communities are trying to serve their population to obtain the necessary services. which include a repeated data, and miss some information, so that, we build this database to organize the data and have correct information.
See the attached relationships.

AddressRelationships.jpg

Location link the plot number in the square with the location code on the map.
The Address code is from combination of location and flat.
Each family quantity in flat required services.
Each 3 digits in code have a meaning in separate table. (Auto number primary key will not help)

This database shall distribute for each communities, so they should have a unique address from combination code.

Hope that I explain my needs.
 

IbrBadri

Member
Local time
Today, 15:40
Joined
May 24, 2020
Messages
35
Test of Address Code.
in the 2nd. sub form,
Record 1 correct 100%
Record 2 not completed (wrong)

Test Address Code.jpg
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:40
Joined
Feb 19, 2002
Messages
42,970
I suggest that you step back and redo the tables. It is far easier in the long run to use autonumber primary keys and that way you don't have to worry about parts of your mushed key changing and having to figure out how to propagate those changes. This is not the way relational databases work. A single piece of data should exist in a single table and therefor have only a single point of change.

To handle your multi-part IDs, you can use complex indexes where you define a unique index that includes 2- 10 fields but the PK of the table is an autonumber so when you create the FK for the child table, it is the autonumber which is stored as the FK rather than the business data.

So:
IDAddressSection becomes
SectionID (autonumber, PK)
SectionNA (unique index to prevent duplicates)

IDAddressRegion becomes:
RegionID (autonumber, PK)
SectionID (FK toIDAddressSection)
RegionName (unique index to prevent duplicates OR create -using the index dialog a two field index of SectionID and RegionName if RegionName might exist in multiple sections)

IDAddressCity becomes:
CityID (autonumber, PK)
RegionID (FK to IDAddressRegion)
CityName (Unique index to prevent duplicates OR create - using the index dialog a two field index of RegionID and CityName if CityName might exist in multiple Regions)

etc.

IF you have existing identifiers for Section, Region, City, etc and you want to keep them for reference to the old record system, that's fine. Just add them as data fields to the appropriate table but DO NOT use them as the primary key.

It is always poor practice to use other system's IDs as the primary key for your new system. You have no control over when/how they might change and you can't have them causing you to constantly update lots of rows to keep them in sync. Remember, one piece of data is only stored in one location. The old SectionNum can be kept as a data field in IdAddressSection. Add an additional index if it is supposed to be unique in that context. Add RegionNum to IDAddressRegion. Add an additional index if regionNum is supposed to be unique in that context.

etc.

And finally, to save yourself mental effort, be consistent with how you name things. You have a number of instances where the FK-PK relationships are using completely differently named fields. If using the same name disturbs you, then use "_FK" as a suffix for the foreign keys. I don't do this and I am not recommending it but some people think it makes the relationships clearer. I removed the numbers from the table names because it didn't make any sense to me for the names to include numbers. Numbers in table names are indicative of a design flaw because the numbers represent data.

When you use autonumbers as the PKs in a schema such as this, you will need to use queries that join to the higher level tables to pick up the bits and pieces of the user readable codes when creating the RecordSource for your forms.
 

IbrBadri

Member
Local time
Today, 15:40
Joined
May 24, 2020
Messages
35
Thanks a lot for your time and help.

Really, I started with unique names without codes, but in Arabic Language we have one character can be written into different ways which is create a duplicate. It is big problem for us, so that I re-structure the tables with unique code numbers.

Example "Madani" can written "مدني" , "مدنى" , "ود مدني" , "ود مدنى"
Access will understand it a 4 different names, and so on.

We are forced to use punctuation instead of names.

Hop you understand the situation
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:40
Joined
Feb 19, 2002
Messages
42,970
Actually I do. I spent a year in Kuwait a long time ago building a human resources application for the Diwan Mosathene (sic). Every one I worked with spoke English so my Arabic never proceeded much beyond greetings and very simple sentences. Although our interpreter, who was Egyptian used to tell me "you will be so beautiful when you speak Arabic". I couldn't tell if he was hitting on me (in front of my husband who was on the same team) or was telling me I had a "cute" accent.

All the more reason to stick with autonumber primary keys and foreign keys.
 

IbrBadri

Member
Local time
Today, 15:40
Joined
May 24, 2020
Messages
35
Auto number is perfect, if the distribution of this database for "One City, One District and One Squire" or an Internet shared database or on a local area network, but it will be distribute for each community and work separately.

Alright,
In this case, I cannot distribute it to the non-entered cities and Districts until I entered them to have a unique auto number.
Otherwise, I just use unbound field for this combination code.

Is there / (I think there) a statement to apply for the combo box of field 2 to change the show of available choices depending on the field 1 data changed in a record of a table?
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:40
Joined
Feb 19, 2002
Messages
42,970
The technique is called "cascading combos" and there's lots of suggestions on how to make them work.

You don't need to create the autonumbers ahead of time. Your users will do that by using your forms. When you use a subform on a form and set the master/child link properties correctly, Access will AUTOMATICALLY populate the FK in the subform as you add a new record.

When you build a database for others to use. you create forms and reports that are accessed via a menu/switchboard. Users NEVER, EVER even see the Navigation pane let alone pick objects from it. YOU have to create an interface that guides the process.

However, if you have the necessary data to create the political divisions, you might as well do it so that everything is consistent.
 

IbrBadri

Member
Local time
Today, 15:40
Joined
May 24, 2020
Messages
35
I am building the database on that way.

However, here I tried to keep a basic reference ready for future use when collecting the data from all the communities who used the software. Then have it with a unique code for each city and district, which can be link easily into one system.
That system shall developed for use as reference for the Sudanese Communities and others.

The political sections and region was completed with their capital cities and some other major cities, but missed the other cities and districts.

Our old government they damage the structure by letting each sections and cities work independently without keeping a link between there details. "Even there is no complete reference for the cities and districts available to use it".

I search in the internet to find a list of all cities in Sudan, but unfortunately, I found only the major cities, and could not find a list for districts, except I should go through google maps and make my own index.

Thanks very much for your kind help, I appreciate all the time spend to help me.

Best Regards,
 

IbrBadri

Member
Local time
Today, 15:40
Joined
May 24, 2020
Messages
35
I suggest that you step back and redo the tables. It is far easier in the long run to use autonumber primary keys and that way you don't have to worry about parts of your mushed key changing and having to figure out how to propagate those changes. This is not the way relational databases work. A single piece of data should exist in a single table and therefor have only a single point of change.

To handle your multi-part IDs, you can use complex indexes where you define a unique index that includes 2- 10 fields but the PK of the table is an autonumber so when you create the FK for the child table, it is the autonumber which is stored as the FK rather than the business data.

So:
IDAddressSection becomes
SectionID (autonumber, PK)
SectionNA (unique index to prevent duplicates)

IDAddressRegion becomes:
RegionID (autonumber, PK)
SectionID (FK toIDAddressSection)
RegionName (unique index to prevent duplicates OR create -using the index dialog a two field index of SectionID and RegionName if RegionName might exist in multiple sections)

IDAddressCity becomes:
CityID (autonumber, PK)
RegionID (FK to IDAddressRegion)
CityName (Unique index to prevent duplicates OR create - using the index dialog a two field index of RegionID and CityName if CityName might exist in multiple Regions)

etc.

IF you have existing identifiers for Section, Region, City, etc and you want to keep them for reference to the old record system, that's fine. Just add them as data fields to the appropriate table but DO NOT use them as the primary key.

It is always poor practice to use other system's IDs as the primary key for your new system. You have no control over when/how they might change and you can't have them causing you to constantly update lots of rows to keep them in sync. Remember, one piece of data is only stored in one location. The old SectionNum can be kept as a data field in IdAddressSection. Add an additional index if it is supposed to be unique in that context. Add RegionNum to IDAddressRegion. Add an additional index if regionNum is supposed to be unique in that context.

etc.

And finally, to save yourself mental effort, be consistent with how you name things. You have a number of instances where the FK-PK relationships are using completely differently named fields. If using the same name disturbs you, then use "_FK" as a suffix for the foreign keys. I don't do this and I am not recommending it but some people think it makes the relationships clearer. I removed the numbers from the table names because it didn't make any sense to me for the names to include numbers. Numbers in table names are indicative of a design flaw because the numbers represent data.

When you use autonumbers as the PKs in a schema such as this, you will need to use queries that join to the higher level tables to pick up the bits and pieces of the user readable codes when creating the RecordSource for your forms.

ISA "In Shaa Allah" I will step back and redo the tables, the way that you suggest to me.

That is fine and easy way.

Thanks a lot.
 

Users who are viewing this thread

Top Bottom