DMax Criteria Issues

[MG101]

Hi,

I am new to access and have struggled to get to where I am. I am currently creating a form to inout new return parts into a database. I am trying to automatically generate a tracking number (##-AA-####-####). I have gotten myself to generate the ##-AA-#### in a list box and almost was able to generate the sequence number, 0001, 0002, etc. using the dmax function. I would like to generate the sequence number one higher than the highest, depending on part type and last 4 digits of part number. Our parts have unique last 4 different p/n but more than one can fall under the same type.

Any help would be appreciated.
Thanks!

 

[billmeye]

Is your ##-AA-####-#### field stored as text? If so you can not increment. A suggestion would be to create a separate integer field just for keeping track of the last four digits. You can semi-automate things by setting the default value for this field to dmax("[Last4Dig]","tblParts") + 1 and then the part number field you can concatenate the different sections of your part number.

 

[MG101]

Thanks for helpin out!

What I did in my table was create a column next to my tracking number column labled Seq (sequence). It contains the same sequence number as the tracking number to the left, but it keeps it seperated. My idea was to use dmax to reference the database table, cross referencing the part type and last four digits, and then increasing the highest seq of that cross ref by one. This would result in a number in a text box. Then I would have an additional box that would add the base tracking number (Which I have already generated) and the new sequence number. Hope this helps.

The code for the base tracking number:
SELECT DISTINCT EVAP_Parts.TrackingNumber FROM EVAP_Parts, [EVAP Database] WHERE ((([EVAP Database].Part)=[Forms]![New Part]![Combo53]) AND (([EVAP Database].Last)=[Forms]![New Part]![Combo96]) AND ((EVAP_Parts.PartType)=[Forms]![New Part]![Combo53]) AND ((EVAP_Parts.Last)=[Forms]![New Part]![Combo96]));

The Code for the auto sequence (What Ive tried but doesnt store in database for some reason, so it can never plus 1 unless you click it to store.):
SELECT Max(([EVAP Database].[seq])+1) AS MaxOfSeq FROM [EVAP Database] WHERE ((([EVAP Database].Part)=Forms![New Part]!Combo53) And (([EVAP Database].Last)=Forms![New Part]!Combo96));

 

[billmeye]

It strikes me as unnecessary to use SQL statements in this circumstance. Just for clarification, could you answer a few questions:

1. Which table are you attempting to update
2. Is this table the record source for your form
3. Is Combo53 a choice of part types or what is it
4. What is the row source for Combo53
5. Is Combo96 the latest TrackingNumber assigned
6. What is the row source for Combo96
7. Do you increment over all of your parts or over part types

Thanks.

 

[MG101]

Sorry for the confusion, I have no clue on which methods are best.

The answers to your question:
1) My main database. (EVAP Database)

2) If you mean the table where The values are being stored than yes. The sequence number is to be generated based off of the highest sequence number in the EVAP Database.

3) Combo 53 is a choice of part types. (The user selects a part type, then selects last four didgits (these are narrowed down depending on which part type is selected)(Narrowing is done using seperate table called EVAP Parts)

4)Combo 53 Row Source: SELECT DISTINCT EVAP_Parts.PartType FROM EVAP_Parts ORDER BY EVAP_Parts.PartType;

5)Combo 96 is the last 4 digits list, narrowed down depending on part tyoe selection

6)Combo 96 row source : SELECT DISTINCT EVAP_Parts.Last FROM EVAP_Parts WHERE (((EVAP_Parts.PartType)=Forms![New Part]!Combo53)) ORDER BY [EVAP_Parts.Last];

7)The parts are tracked using this format ##-AA-####-##
The first two numbers is part type: DCV3=01, DCV2=00, etc. (EVAP Parts Table)
The second two are the shortcut letters for product customer: GM, CH, FO, etc
The Third set of 4 numbers, is the last 4 of the part number
The last set of 4 numbers is the sequence for repeating parts.


Sorry this is so confusing :/

 

[billmeye]

Thank you. So, in the end you are adding a new part to the EVAP Database and depending on the part type, you are incrementing the last four digits.

Combo53 makes sense, it is a list of part types but Combo96 does not make sense and I'm guessing it is unneeded since a user would not be choosing what the last four digits could be, that should be automatic when adding the new TrackingNumber.

So, is Combo53 bound to a field on table EVAP Database? If not, it should be. Actually, what are the fields in the table EVAP Database?

Thanks.

 

[MG101]

Incase the file would help:

 

[MG101]

combo 96 is neccesary because it is possible to have different part numbers for the same part type. Think of combo 53 as catergory (Food, color, material) and the 96 as the actual item (apple, strawberry). They both get recorded. but iattached the file in the above thread in hopes it might be easier to understand

 

[billmeye]

Thanks. So, I can not determine what fields are responsible for:

The second two are the shortcut letters for product customer: GM, CH, FO, etc
The Third set of 4 numbers, is the last 4 of the part number

Could you tell me how they are assigned? Thanks.

 

[MG101]

These values are assigned in the EVAP Parts table. The last four digits are technially assigned the the customer bc they can never change. GM will always be either 7220 or vice versa.

Thanks!

 

[billmeye]

OK, I'm sorry but I need to go. I've attached your DB. Combo53 contains all the info needed and in the forms Before Update event the Tracking Number is calculated. The last 4 digits will increment based on part type. If you have problems let me know and I'll continue to try to help and explain what is going on.

 

[MG101]

Thank you for your time!
That is what i wanted, but i need the tracking number to display immediately or pop up in a box directly after so the user can write the tracking number on the part. Also there are more than one kind of DCV3 which is why I had another drop down to slect which one. Is there a way to differenciate the DCV3's the current way you have it set up?

Thanks!

 

[billmeye]

MG101, I didn't want to leave you hanging. So, in order to be able to have duplicate values of part types you need to save something that will be unique and that is the PartID field. I had to add an additional column to the EVAP Database called PartID. This now allows you to have multiples of the same part types. For my testing I filled this in so on your real data you will need to fill this in order for the code to work correctly.

In the drop down combobox for Part on your form it shows the part type and customer so you can choose which customer. In the After Update event for Part it now calculates the tracking number.

One thing I thought of while doing this, what did you intend if the user creates a part but then goes back and changes the part type? I put in code that tries to address this but it may not be what you are looking for, it deletes the original tracking number and re-calculates a new tracking number.

 

[MG101]

Thank you so much billmeye! This is exactly what I wanted!

The only thing that I wanted is for the drop down list to show Part, Last, and Customer instead of Part, Customer Abbreviation, and customer. Is this Possible?

Thanks for all of your help!

 

[MG101]

Actually I figured it out! Thank you for all your help!

I will now be working on buttons to save, clear and cancel form entry and also making an additional form just for part searching by tracking number.

I also would like to make a report form where I can have it generate a report for me automatically after I finish part entry.

Any advice/help on these would be greatly appreciated!

Thanks again!

 

[MG101]

Billmeye,

There was one problem I found while testing your automatic tracking number. For some reason the Chrysler 7240 jumps from 0009 to 0019 but I cant figure out why. Any thoughts?

Thanks!

 

[billmeye]

You have to look and see what the actual last value for the seq field is for all occurrences of Chrysler 7240 part type. Remember now, I changed it so the Evap Database is storing the PartID and that is what is determining what Part type used in calculating the latest seq value. If this is unclear, attach you latest DB with your real data and I will take a look for any errors.

 

[MG101]

Hi Billmeye,

I did look to see what was highest for Chrysler 7240 and it was sequence of 8. When I made new record for 7240 it made tracking number 01-CH-7240-0009 but recorded seq. number of 18. I dont know why. So when I go to record another 7240 it makes tracking number of 01-CH-7240-0019.

I will attach the file for you to look at.

Thanks again for all your help!

 

[billmeye]

In your evap database table, if you look at record #84, it currently has a PartID of 3 with a seq value of 18. PartID 3 is your Chrysler 7240 from your evap_parts table. If this is a mistake either delete the PartID or the entire record from your evap database table.

Just review your evap database table for any other similar errors by reviewing the PartID.

 

[MG101]

Billmeye,

I did try that. I delete it, then go to test it and it creates 01-CH-7240-0009 with a seq number of 18. I dont know why it makes it with a seq number of 18 when the previous sequence is 8.