Some basic questions regarding forms

[elifish]

Hello, very basic user here.

I've created a form that has a text field that concatenates values using:
=![PREVOWN] & ![PARKNAME] & ![TRNSNUM]
I know concatenation is a big no-no with normal forms, however, the agency I work for, wants to use this concatenated value as their unique identifier. Using a query alone is not feasible as the database was set up to use this value as the primary key, so we need to include the value.

I am having two problems:

The first is that the field [TRNSNUM], is an incremental autonumber field. The values in this field need to start at 10,000 so I used a Format of "10"000.
This works great in the [TRNSNUM] field itself, but in the concatenated field, I am not seeing the first two digits, so my values for the last third of the concatenation are showing up 123, 124, ... instead of 10123, 10124. Any help on this issue? Also, is this just assigning the two digit field of 10 before my numbers? If so, I need a better way to do this, as my values will reach up to 11,000 and so on.

The next issue, and the larger problem is that my concatenated text field is not being input when I click on my forms, "submit" control button. I used the command button wizard to create this button. I selected "Record Operations" and then "Save record." It seems to save all of my other text field, except the concatenated field. This is a problem, since the value is supposed to be my primary key, so if I cannot obtain the value from my form, I cannot add more records, as the primary key cannot be null.

Any ideas would be of great help, I am a very beginning user, with very little knowledge of SQL or VB, so I am slogging my way through.
Thanks.

 

[aleb]

Well you need to resolve the couple of issues.
1. With your autonumber field
2. Why the field doesn't accept the value of concatenated fields

"Format" doesn't mean that you actually parsing this value to a field it is still 123, try to add 10000

It could be that you are trying to create the data in the field when there is no data at all.
I didn't have any problems with subform by setting the default value to = field1&field2&(10000+field3)
but on the main form without programming I really don't know how to do it.

 

[neileg]

I've created a form that has a text field that concatenates values using:
=![PREVOWN] & ![PARKNAME] & ![TRNSNUM]
I know concatenation is a big no-no with normal forms, however, the agency I work for, wants to use this concatenated value as their unique identifier. Using a query alone is not feasible as the database was set up to use this value as the primary key, so we need to include the value.

1. What's the ! for?
2. Surely the autonumber field is a unique identifier any way?
3. Which value is the PK, the autonumber or the concatenated field?

The next issue, and the larger problem is that my concatenated text field is not being input when I click on my forms, "submit" control button. I used the command button wizard to create this button. I selected "Record Operations" and then "Save record." It seems to save all of my other text field, except the concatenated field. This is a problem, since the value is supposed to be my primary key, so if I cannot obtain the value from my form, I cannot add more records, as the primary key cannot be null.

1. Why on earth do you feel that this field needs to be the PK? The user should have no idea what the PK is. The user can use whatever they want as their unique ID, that doen't mean that this should be the PK.
2. Is the text box in which you are carrying out the concatenation actually bound to the field? Otherwise the value won't be saved.

 

[elifish]

1. What's the ! for?

2. Surely the autonumber field is a unique identifier any way?

3. Which value is the PK, the autonumber or the concatenated field?

1. Oops, you are right, the form works fine without the !'s
2. Yes, that is why we added the autonumbered field to provide "unique-ness"
3. The concatenated field is the primary key. I see the value of setting up the autonumber as the PK, however, it doesn't "mean" anything, and giving it additional values, such as property owner and park unit allow us to use the concatenated value to track down these particular transactions outside of Access in a GIS. Even if I do switch the primary key to the autogenerated number, I still need the concatenated field in the database for the GIS to link spatial data to individual transactions. Plus the fact that the database was already set up with this as the primary key and all of the relationships are already built. There may be a better way to do this, but as I said, the agency likes the concatenated value, as it is intuitive as to which record we are looking at in the GIS, without even having to open the database or even a table within the GIS while viewing the data spatially. We are trying to make the GIS user friendly while incorporating the database and its rules too. So far it has proven interesting...

1. Why on earth do you feel that this field needs to be the PK? The user should have no idea what the PK is. The user can use whatever they want as their unique ID, that doen't mean that this should be the PK.

2. Is the text box in which you are carrying out the concatenation actually bound to the field? Otherwise the value won't be saved.

1. I agree, but i'm trying to work within the constraints I metion above.
2. Okay, this may be the issue. I had the =[PREVOWN] & [PARKNAME] & [TRNSNUM] in the control source field for the text box, which means the text was not bound to the form. Where do I need to put the =[PREVOWN] & [PARKNAME] & [TRNSNUM] control in order for it to both display the concatonated value on the form in the text box as well as have the text box bound to my field and insert the displayed (and concatenated) value into my database?

Thanks for humoring a true access beginner.

 

[neileg]

3. The concatenated field is the primary key. I see the value of setting up the autonumber as the PK, however, it doesn't "mean" anything, and giving it additional values, such as property owner and park unit allow us to use the concatenated value to track down these particular transactions outside of Access in a GIS. Even if I do switch the primary key to the autogenerated number, I still need the concatenated field in the database for the GIS to link spatial data to individual transactions. Plus the fact that the database was already set up with this as the primary key and all of the relationships are already built. There may be a better way to do this, but as I said, the agency likes the concatenated value, as it is intuitive as to which record we are looking at in the GIS, without even having to open the database or even a table within the GIS while viewing the data spatially. We are trying to make the GIS user friendly while incorporating the database and its rules too. So far it has proven interesting...

I understand the problems working with inheirited design flaws but sometimes you have to bite the bullet...
The problem you have is that there is no place in a well designed database for storing both a concatenated field and the fields that make it up. I wonder if you are trying to do things at a table level that you should be doing in a query. In a query, you can create a calculated field that will do the concatenation on the fly, so there is no need to store it. The issues about having the concatenaed field available to GIS users should not be an issue since anywhere you use a table you can use a query instead. In Access, when you thgink you are looking at the raw data in the table, you're not, you are looking at a query!
The Agency can have it's concatenated value in the query and you as db designer can work with proper normalised data.
If you really want to store the concantenanded data, you need a bit of code in the form that does the work and places the value in the field. However this means that the form will need to hold the three fields plus the concatenated field, although you could make the last one invisible. But it's messy.

1. I agree, but i'm trying to work within the constraints I metion above.

It will all end in tears!

2. Okay, this may be the issue. I had the =[PREVOWN] & [PARKNAME] & [TRNSNUM] in the control source field for the text box, which means the text was not bound to the form. Where do I need to put the =[PREVOWN] & [PARKNAME] & [TRNSNUM] control in order for it to both display the concatonated value on the form in the text box as well as have the text box bound to my field and insert the displayed (and concatenated) value into my database?

As I suggested, you need a bit of code in the AfterUpdate event of your controls that performs the calculation.

Have a look at the attached.

Thanks for humoring a true access beginner.

We're all beginners sometime!

 

[elifish]

I understand the problems working with inheirited design flaws but sometimes you have to bite the bullet...
The problem you have is that there is no place in a well designed database for storing both a concatenated field and the fields that make it up. I wonder if you are trying to do things at a table level that you should be doing in a query. In a query, you can create a calculated field that will do the concatenation on the fly, so there is no need to store it. The issues about having the concatenaed field available to GIS users should not be an issue since anywhere you use a table you can use a query instead. In Access, when you thgink you are looking at the raw data in the table, you're not, you are looking at a query!

Okay, Once I have the query built, how do I link another table to my original table, based on the concatenated value? (even if I do not make it the primary key). Ex: Now I have a query which gives me a unique transaction value. Each transaction will contain many parcels. What value do I place in my parcel table to link to my transaction table? If I do it through the query don't I loose my referential integrity? Now my form question is turning into a query question...

 

[neileg]

No, your form question is turning into a design question which is what it was in the start!

You autonumber is the primary key! Thats how you link to another table. The query ios simply presenting the data the way your users want to see it.

 

[elifish]

okay, if my autonumber field is the one I am using to join, it should be in each table and be the primary key for the main table and the foreign key for the other tables? Is this correct? Sorry if this seems dense....
thanks for the help so far, it is ironing out slowly I think

 

[neileg]

Yes, that's correct. Keep going!