Safest Way to Number Records in a SubForm

[dgutsche]

So I've been spinning around on this one all day:

I have a form which allows the user to enter a number (corresponding to field paperwork being entered). That number is then used to create a new text string for each record which joins that number and a letter together. The challenge I am having is getting the text string to include an incremental letter. I am comfortable shifting and recasting the number as a character to get what I want, my challenge is to use the right way to correctly state the row of the subform that a new record is created on.

For example: The parent form has a field called Set. The user enters "975".



Then my subform (which is in table view) allows the user to add as many records as they need to complete the data entry.
- Adding the 1st row populates the field Cylinder Number with "975A".
- Adding the 2nd row populates the field Cylinder Number with "975B".
- Adding the 3rd row populates the field Cylinder Number with "975C".

These values correlate to my company's current naming convention. I just want this little database to ensure that when a record is added or deleted all the records that have this particular Set number end up being accurately renamed. If someone can get me to 1, 2, 3... with that I can then make the string I am looking for.

Please help? Thanks in advance!

[June7]

Use Asc() and Chr() to convert alpha characters and increment and convert back to alpha.

"A" = ascii 65 so start increment with 65.

Asc("A") will return 65.

Chr(65) will return "A".

Use a variable to increment the number and Chr() to convert to alpha.

[dgutsche]

Use Asc() and Chr() to convert alpha characters and increment and convert back to alpha.

"A" = ascii 65 so start increment with 65.

Asc("A") will return 65.

Chr(65) will return "A".

Use a variable to increment the number and Chr() to convert to alpha.

Thanks for the quick response June7! I'm just headed out for the day but I'm going to take my second kick at it in the morning. What you're saying sounds so familiar to another solution I read online. I just assumed I was getting it wrong as when I deleted a record I didn't get the result I wanted. If I deleted the record with "975B" for instance, I still had "975A" and then a gap before "975C". For my company this is important to avoid because we have standardized the naming convention for the physical object that the record is meant to describe (the cylinder has this tag ID on it).

I'll revisit in the morning. Thanks again for the quick response!

Dustin

[June7]

Right, deleting record will not change value of other records. Either live with gap or reuse the number or don't allow deletion - set a field as 'inactive' or something to tag record with a status indicator.

[dgutsche]

Right, deleting record will not change value of other records. Either live with gap or reuse the number or don't allow deletion - set a field as 'inactive' or something to tag record with a status indicator.

I think I'll have to hunt for a different solution. Maybe something that "refreshes" the values of the field using VBA. That way I can call the function every time they add or delete a row. Marking a record as inactive takes care of the bad field but doesn't rename all the misnamed records below to the correct one. I'll instead have 975A, Inactive, 975C, 975D, 975E when the actual cylinders will be labeled 975A, 975B, 975C and 975D. This will lead to some chaos when we start confusing our samples.

Thanks for the attempt though!

[June7]

When you delete a record, what happens to the cylinder - trash? Cylinder of what?

I am not suggesting 975B be replaced with the word "Inactive". Inactive would be in a different field. Record will still have the 975B identifier.

Would you allow record deletion after the cylinders have been labeled?

[dgutsche]

When you delete a record, what happens to the cylinder - trash? Cylinder of what?

I am not suggesting 975B be replaced with the word "Inactive". Inactive would be in a different field. Record will still have the 975B identifier.

Would you allow record deletion after the cylinders have been labeled?

Hi June7!

In a nutshell our process is about testing concrete, so several samples are collected and labelled on site. They are brought back to our lab and after hardening each sample is crushed at different time intervals to determine its strength rating. If a cylinder is physically mislabeled it will be relabeled in the lab to match the correct name based upon the order of breaks.

The most likely place I will run into errors is when the data sheet of all the cataloged cylinders gets typed into our database using my form. My chosen path right now is to have the parent form with a of data be entered while the subform is just for the records of the cylinders has quite a bit less at that point in time (this auto-generated name and a calculated date to break the cylinder). In order to appease the client (my boss) I need to ensure that my digital form looks like the paper form the field techs are using (which won't be changed due to industry standards - I have to match to it).

To pull that off I think my best option is to now use a macro that queries all cylinders that belong to the set and rename them according to my naming rule. This macro would be called every time a person changes the "Set number" on my master form, adds a record to the sub form, or deletes a record from the sub form. The reason for this is to allow the non-computer savy user to be able to change the number of cylinders after entering the wrong number (too few or too many). In order to pull that off I need to find a way to ensure that whenever it's manipulated, that cylinder name needs to be updated so I always have alphabetically incremented suffixes appended to the set number (which is the same number for all cylinders in that particular batch).

If it helps illustrate what I am trying to do I'll include part of the "Set" ticket here. It's a very particular form which my company has ordered so much stock of it won't ever be changed for another four years. As you can see it's a lot of numerical entry so I'm doing a lot of things to try and prevent transcription error. Ensuring the naming convention is pretty important. Otherwise my database will be instructing our technicians to break the wrong cylinder - which of course can not be undone as the test is a destructive test.

My challenge now will be how to get a macro to overwrite values in the Cylinder Name field of the Cylinders Table where the Set field matches the currently displayed Set field on the parent form. I know I will have users that need to edit the list later both for quantity and to change the age value (as break dates can be revised upon intermediate progress reports).

Any idea the best way to pose my question if you don't have a solution yourself?

Thanks again for all the insight.

Cheers,
Dustin

[June7]

How interesting. I used to work for state materials testing laboratory. I built db for tracking samples. Concrete cylinders were just one sample type.

In fact, the form you posted looks very similar.

I think the requirement is stated clear enough. Now attempt code. The trickiest part is figuring out what event(s) to run code from.

[dgutsche]

How interesting. I used to work for state materials testing laboratory. I built db for tracking samples. Concrete cylinders were just one sample type.

In fact, the form you posted looks very similar.

I think the requirement is stated clear enough. Now attempt code. The trickiest part is figuring out what event(s) to run code from.

Ha ha too funny. The number of times I allude to what I am doing thinking it's beyond most people's familiarity only to have someone say "I've done that". It's such a small world ha ha.

Thanks for the advice. I'm going to collaborate with a colleague on this one here and see if we find a good solution. You're definitely right - getting the trigger is going to be fairly crucial. Luckily I'm pretty good at breaking products in the testing phase. :-P

Thanks again!
Dustin

[June7]

Yes, small world. Not the first time I've run into poster with questions about a materials testing database.