VBA code, in Form, to check a Table, to see if a part has already been tested.

[arneym]

Currently, I can check if a combobox and a textbox meet a certain condition. If it doesn't, then it says, "Test Piece is required." Now, I need it to check if the part that needs a test piece has already been ran earlier in the shift. I have attached what I currently have. Thank you in advanced for ANY help! Had to Zip it! 100kb too big.
Shaft Steel Cut.zip

[June7]

You are saving MeltCharge into Log and joining tables on this field. But MeltCharge is not a unique identifier. The combination of MeltCharge and Diameter is a unique key. Have to join tables on both fields. The alternative is to instead save the ID primary key from SteelInfo into Log and join tables on that value. That is the principle reason for autonumber ID. No need to duplicate MeltCharge and Diameter in Log.

Should have the diameter fields as number type. They will work as text with the 2-digit values because they will still sort/filter properly. However, it is alpha sort rules. If there were a diameter of 8 then 10, 20, 30, 40, 50, 60, 70, 100 would sort before 8.

[ItsMe]

The DB you uploaded only contains one form and no VBA in its Class Module. There is another Class Module for a form that does not exist. In it I see a lot of If Then statements.

I have no clue how you are collecting the data that needs to be validated against condition/criteria. But I will say you will probably be better off creating dynamic SQL SELECT statments that look for existing data inside your tables. You are hard coding a LOT of literal text inside your If Then statements. Any small change in operations will have to be reflected in your VBA code.

[arneym]

I was not able to make MeltCharge a primary key because some of the different ShaftDiameter values have the same MeltCharge value.

[arneym]

The DB you uploaded only contains one form and no VBA in its Class Module. There is another Class Module for a form that does not exist. In it I see a lot of If Then statements.

I have no clue how you are collecting the data that needs to be validated against condition/criteria. But I will say you will probably be better off creating dynamic SQL SELECT statments that look for existing data inside your tables. You are hard coding a LOT of literal text inside your If Then statements. Any small change in operations will have to be reflected in your VBA code.

I am sorry. I forgot to unhide everything. Here it is again.Shaft Steel Cut.zip

[June7]

I may have edited my previous post as you were posting. Read it again.

[arneym]

Ha ha! I must have read it before I uploaded the new one. I did change it to number. Thank you June7!

[June7]

Did you change the table relationship?

[arneym]

Did you change the table relationship?

June7,
Yes, I did. I wasn't sure what to do. I was trying to take care of the primary key thing, and I deleted the relationship. I basically want to add a "*" to a MeltCharge that is not in the Table. While the operators are cutting more steel, and they run into the same melt charge and diameter during the same day, I want it to check the CutDate and MeltCharge & "*" and return the msgbox "No test piece needed".

[June7]

Sorry, not understanding that business process. A Log record is not required to have a MeltCharge/Diameter? Exactly what is purpose of Log - what does it document?

The alternative to ID as PK/FK is having to join tables with 2 fields (compound primary key) in order to retrieve other related data in the SteelInfo table. Personally, I avoid compound keys whenever possible.

Did you only delete relationship in Relationship builder and not populate ID as foreign key in Log? Or did you just remove SteelInfo from the query? It really wasn't necessary for the search you want to do because MeltCharge and Diameter are in Log. However, the join will be needed if you change to ID as PK/FK and remove MeltCharge and Diameter from Log.

[arneym]

Sorry, not understanding that business process. A Log record is not required to have a MeltCharge/Diameter? Exactly what is purpose of Log - what does it document?

The alternative to ID as PK/FK is having to join tables with 2 fields (compound primary key) in order to retrieve other related data in the SteelInfo table. Personally, I avoid compound keys whenever possible.


Did you only delete relationship in Relationship builder and not populate ID as foreign key in Log? Or did you just remove SteelInfo from the query? It really wasn't necessary for the search you want to do because MeltCharge and Diameter are in Log. However, the join will be needed if you change to ID as PK/FK and remove MeltCharge and Diameter from Log.

The tblLog is a record of the operator checks including: steel diameter, melting "mixture"(MeltCharge), cutting date, and dimensions.

The tblSteelInfo is a record of the steel diameters and melting "mixtures"(MeltCharges) that do not require a test piece.

If a steel diameter and melt charge combination is checked on the frmLog, that in not in the tblSteelInfo, then it needs a test piece. I have that part figured out, and it works well. The kicker is that if that same steel diameter and melt charge combination has been cut already in the same day, then it will NOT require a test piece. This is because one has already been cut from a previous time in the same day.

I need it to check the [tblLog]![SteelDiameter], [tblLog]![MeltCharge], and [tblLog]![CutDate] to see if that combination has already been cut.

[ItsMe]

I need it to check the [tblLog]![SteelDiameter], [tblLog]![MeltCharge], and [tblLog]![CutDate] to see if that combination has already been cut.

Are these values in the current form? For instance, do you have a control named "SteelDiameter" on your form? is it the value of this control that you want to check against the tblLog?

What are the control names and types on the form?

[June7]

Before we can proceed, need to get table relationships committed. Do you want to use the autonumber ID as PK/FK since it is designated as the PK and is designed for that or do you want the compound key?

[arneym]

Are these values in the current form? For instance, do you have a control named "SteelDiameter" on your form? is it the value of this control that you want to check against the tblLog?

What are the control names and types on the form?

I am horrible! Please forgive me. Steel Diameter should be ShaftDiameter. It is located in the Table [Log].

The controls on the form that need to check against the table [Log] are:
cboDiameter
txtMeltCharge
CutDate

[arneym]

Before we can proceed, need to get table relationships committed. Do you want to use the autonumber ID as PK/FK since it is designated as the PK and is designed for that or do you want the compound key?

I am sorry. What is the compound key? This Database is open for any changes. What do you suggest, and may I get an example?