Some Basic Questions - I just want to be sure I understand some things (1 Viewer)

[Umpire]

What I want is any part number that is entered that matches one of several part numbers would trigger the Windows license check.
A Part Number would look like this: 123456 or S123456 or even AB600008-A.
If the entered part number matches 123456 or S123456 or 456789-A for example, the License question comes up otherwise that question is ignored and the other information is entered without interruption.

If part number 789456 is entered nothing happens. If 456789-A is entered, the question comes up.

There would be up to 10 part numbers where I would want the License question to be triggered. I currently have over 200 separate part numbers that could be entered. And that list grows monthly. I do not expect the list of 10 triggering numbers to change for a least a year or 2.

 

[theDBguy]

Okay, if you're saying you want to check for an "exact match," then you could store those 10 part numbers in a separate table and simply use DCount() to check if the entered part number is in that table.

 

[The_Doc_Man]

In this context, you would either do a wild-card search using the LIKE operator or an exact-match search using the "=" operator. With the LIKE case, you can get partial matches. With "=" you can ONLY get exact matches.

X = DCOUNT( "[License]", "PartsTable", "[License]='" & CStr(Me.License) & "'" )
or
X = DCOUNT( "[License]", "PartsTable", "[License] LIKE '*" & CStr(Me.License) & "*'" )

Just watch out for the mixed quotes in use for the two cases. Then if you get anything other than 0 from X after that operation, you have found at least one similar number on record.

 

[Umpire]

Part Numbers that require Windows License:
030799
031290-A
ContlrA
DisplayB
(Plus a few others I would add in after I figure this out.)

Examples of Part Numbers Being entered:
220123
B700005-QA
030799
031290
211456-A

When the 030799 or 031291-A is entered I want the Windows License check to trigger (via an AfterUpdate event)


I think our responses crossed.

 

[theDBguy]

When the 030799 or 031291-A is entered I want the Windows License check to trigger (via an AfterUpdate event)

So, have you tried the DCount() approach?

 

[Umpire]

So, have you tried the DCount() approach?

Not yet. I believe our posts crossed.
That does look like a better option. Then if I do have to add additional part numbers in the future that require a Windows license it is just an entry in one table.

Just to be sure I understand this:
X = DCOUNT( "[License]", "PartsTable", "[License]='" & CStr(Me.License) & "'" )
[License] is the Field I am looking at in the table of parts that need a license
"Parts Table" is the name of the table where [License] resides
"[License]=" is saying I want an exact match
CStr(me.License) is the field on my form I am using to trigger the entire thing. (The part number I want to look for.)
& "" I have no clue what this is for but I am confident it is needed

I hope I have this right.

 

[theDBguy]

Not yet. I believe our posts crossed.
That does look like a better option. Then if I do have to add additional part numbers in the future that require a Windows license it is just an entry in one table.

Just to be sure I understand this:
X = DCOUNT( "[License]", "PartsTable", "[License]='" & CStr(Me.License) & "'" )
[License] is the Field I am looking at in the table of parts that need a license
"Parts Table" is the name of the table where [License] resides
"[License]=" is saying I want an exact match
CStr(me.License) is the field on my form I am using to trigger the entire thing. (The part number I want to look for.)
& "" I have no clue what this is for but I am confident it is needed

I hope I have this right.

My idea was to store all the part numbers requiring a license in a table. Let's call that table PartsWithLicense, and it might contain the following records.

PartNo
030799
031290-A
ContlrA
DisplayB

Now, in, say, the AfterUpdate of the PartNumber textbox on your form, you could check if the entered part number is in the PartsWithLicense table, which means they need a license, you could try the following code.

If DCount("*", "PartsWithLicense", "PartNo='" & Me.PartNumber & "'") > 0 Then
    'needs a license
Else
    'does not need a license
End If

Hope that makes sense...

 

[Pat Hartman]

What I am trying to do is when one of those part numbers that should have a license is entered in a form, I first want to ask if a license has been assigned to the part.

If you are going to allow the record to be saved without a license even if one is required, what is the point? All you are doing is allowing invalid data to be saved. The whole point of validation is to ensure that the data that gets saved is valid.

Also, there is no ambiguity as to which event should be used for this particular validation, you need to use the FORM's BeforeUpdate event. At the time this event runs, the record is about to be saved so presumabily the user has completed the data entry and all required fields shold be present. If the part is one which requires a license number, you would cancel the update if the license isn't present at this time. If you reorder the data entry so that the license is logically entered first, you might think you could use the control's BeforeUpdate event but unless you have taken over control of the user's keyboard and are forcing him to go field by field in the order you define, you really can't rely on fields to have been entered in order and you can't stop a user from backing up and changing a previously entered field. So even if the license was entered at the time you checked for it in the part control's BeforeUpdate event, there is nothing to stop the user from going back to the license field and clearing it. Then if he never changes the part number value again, you will save an incomplete record. The point of validation is to store valid data and a side effect of that is to keep the user from doing something stupid especially if it allows him to bypass your validation edits. Therefore, you can put validation edits in all the events you want to put them in but chances are excellent you won't plug all the holes. Therefore, use the event that the designers of Access intended for this purpose - the FORM's BeforeUpdate event. Think of it as the flapper at the bottom of a funnel. If the flapper is opened, the record gets saved. If it is closed, the record does not get saved. The FORM's BeforeUpdate event is the last event that runs before a record gets saved and it cannot be bypassed regardless of what prompted the record to be saved.

And finally, hard-coding even parts of part numbers to control this is poor design. If you want to specify that some part numbers require a license, than add an attribute to the part table - i.e. RequiresLicenseYN. Set its default to No of most do not require a license or to Yes of most do. Then check this field and don't even think about what part of the part number is relevant.

 

[Umpire]

Private Sub PartNum_AfterUpdate()
If DCount("*", "tblLicense", "LicenseNeeded='" & Me.PartNum & "'") > 0 Then 'Part Number might have a Windows license
    If MsgBox("Does This part have a Windows License?", vbYesNo + vbQuestion, "Windows License Check") = vbYes Then 'Ask if it has a Windows License
    DoCmd.OpenForm "frmWindowsLicenseInfo" ' if it does have a Windows license, open the form to record it
    Else
    End If ' No license do nothing
Else
    'does not need a license
End If ' Do nothing

End Sub

It works! Thanks for the help.
Now to work on transferring the Part number from the main form to the pop-up form. I have found some code for that.

 

[theDBguy]

Private Sub PartNum_AfterUpdate()
If DCount("*", "tblLicense", "LicenseNeeded='" & Me.PartNum & "'") > 0 Then 'Part Number might have a Windows license
    If MsgBox("Does This part have a Windows License?", vbYesNo + vbQuestion, "Windows License Check") = vbYes Then 'Ask if it has a Windows License
    DoCmd.OpenForm "frmWindowsLicenseInfo" ' if it does have a Windows license, open the form to record it
    Else
    End If ' No license do nothing
Else
    'does not need a license
End If ' Do nothing

End Sub

It works! Thanks for the help.
Now to work on transferring the Part number from the main form to the pop-up form. I have found some code for that.

Good luck!

 

[Umpire]

If you are going to allow the record to be saved without a license even if one is required, what is the point?

Thanks for your comments. you bring up some valid points.

Short answer is that an Identical part number may or may not have a need for a Windows license.

I deal with parts from our production line and from our customers. I may get parts from any point in the process. The windows license is applied at step 8. If I get a part from any point prior to step 8 it will not have a windows license. There is a separate process at step 12 that verifies a license has been assigned prior to the parts leaving the factory. I am not part of that process. Any parts I repair are returned to the point they came from. I get it from step 3 it goes back to step 3. I get it from a customer, it goes back to a customer.

Why record incomplete data? Short answer is because management said they want us to start doing this. Suddenly after 5 years they want me to track Windows license numbers as parts move through my area. The easiest way I figured to do it was incorporate it into an existing process. If a part does not qualify for a license, nothing changes. If it does and it has one installed, it only add about 3 fields of data to be entered.

What I suspect is that in the near future, they will be having me assign licenses on those parts that qualify but do not have them. But that is a guess.

Because I am inserting a step in the process of entering data only for those parts that require it, I think checking after the field is entered is better than waiting until al data has been entered. That is not to say that I should not be doing any validation at the form level. I am sure I should. That is a later issue for another day.

I have the advantage of only having to deal with 2 users other than myself (so far) so I have good control over the training and usage of the program. It is not something I should rely on but it is a crutch I will use while I can.

This is a classic case of management dictating something for their own reasons. And I have no choice but to adapt.

 

[Pat Hartman]

again, if the answer doesn't matter, why ask the question? I mention this because you do not want to train the users to simply blow by your warnings/questions. Don't bother them if you don't want them to pay attention. Wait until the point in time when the data will be available if it is relevant. If you can't tell by the part number if it needs a license or not, are you just guessing?

In order to have a coherent process, you need consistent rules. If management doesn't give them to you, you need to dig them out or get management to change the process.

 

[The_Doc_Man]

Not yet. I believe our posts crossed.
That does look like a better option. Then if I do have to add additional part numbers in the future that require a Windows license it is just an entry in one table.

Just to be sure I understand this:
X = DCOUNT( "[License]", "PartsTable", "[License]='" & CStr(Me.License) & "'" )
[License] is the Field I am looking at in the table of parts that need a license
"Parts Table" is the name of the table where [License] resides
"[License]=" is saying I want an exact match
CStr(me.License) is the field on my form I am using to trigger the entire thing. (The part number I want to look for.)
& "" I have no clue what this is for but I am confident it is needed

I hope I have this right.

Well, kind of. You said that the trick was to look at a table of parts where these licenses are found for some parts. Maybe I misunderstood how this is laid out, but the idea is that you would look up a match from a license number on a form (the Me.License parts). The match would be found in a table where some things have license numbers already. So for my example with made-up names, I would look for the license in the [License] field in the PartsTable, and in that table, I want the [License] field to be where I look for the Me.License value.

That syntax you didn't understand is because you have to look carefully at the single and double quotes, and note that I have single quotes INSIDE the double quotes. That construct of "[some field] = ' " & something & " ' " is a form of string substitution using the concatenation operator & to join strings together. I added extra spaces here so you could more easily realize that there is more punctuation involved, though the spaces do not belong in the actual criteria string.

 

[gemma-the-husky]

The other way, is to let them do what they will, and then have an admin query that pulls out all records that ought to have a reference entered, but which don't. Treat it as investigating exceptions.

 

[Umpire]

again, if the answer doesn't matter, why ask the question? I mention this because you do not want to train the users to simply blow by your warnings/questions. Don't bother them if you don't want them to pay attention. Wait until the point in time when the data will be available if it is relevant. If you can't tell by the part number if it needs a license or not, are you just guessing?

In order to have a coherent process, you need consistent rules. If management doesn't give them to you, you need to dig them out or get management to change the process.

I am asking the question because I was told to ask the question. And the answer DOES matter. A NO answer matters just as much as a YES answer. I just respond differently.

" ... get management to change the process." I don't know about your management but mine is not in the habit of of letting low level hourly employees dictate policy and procedures. Heck I have been fighting for months to et them to spend a few hundred bucks to run hardwired network to my area instead of forcing me to use Wi-Fi. (I know. Access and Wi-Fi is a bad mix asking for trouble. I have no other option.)

You are trying to get me to bend to your way of doing something only because you think it is the "right" way to do it. Doing that is ignoring one of the virtues of using Access. It is a flexible program for a reason. You seem to think everything is black and white with no gray in between. That is not the case. Get up from behind your computer and look out the window. There are a multitude of colors out there.

Let me try to explain it this way. You are entering information for a customer. When it comes to phone numbers do you assume everyone has a cell phone? What if someone does not have a cell phone? What do you do? What I am doing is asking a question: Do they have a cell phone? it is a yes or no. If yes, I have a pop-up to record the number. If the answer is no, I just move on to the next field to enter what ever that field is asking for. And continuing with my customer analogy, I know that only customers in certain cities have cell phones. So I only ask that question if the customer is from one of those cities. Or another way, Why would I ask you for information on what your motorcycle's license is if you only own a car?

We are not doing rocket science here. I am fulfilling a requirement that management wants. I am trying to do it in the easiest way for MY situation. What management does with the information I collect is their problem.

I am just a trained Electronics Tech that has been pressed into the role of Access developer. I am not some highly trained software developer that can dictate to the world how things should be done.

And for the record Mr. Hartman, It is attitudes like your "I know better than you so do it my way," that drive those of us trying to learn from the ground up away from this site.

 

[Pat Hartman]

Sorry if I over stepped. I thought the field was required and you could identify which parts required the license. I tried to offer a better way than hard coding strings to identify parts that required a license. Apparently license isn't required and you can't identify the parts anyway. Since the field isn't required then there is no harm in saving a record without it. I was trying to emphasize the point that saving incomplete/bad data leads to other problems. and you are the guardian of the data. If bad decisions are made because of inaccurate/incomplete data,, it will always fall on your head even if it isn't technically your fault. Since you don't need my advice, I'll move on. Good luck.

 

[Poppa Smurf]

I use an unbound form and do my validation when I press the Save button and before I save the data

 

[Pat Hartman]

I use an unbound form and do my validation when I press the Save button and before I save the data

Access is a RAD (Rapid Application Development) tool. If you aren't going to use the RAD features, why put up with the overhead? If you want to code everything for yourself, you get more options with different platforms and much easier distribution channels.

 

[Umpire]

Sorry if I over stepped. I thought the field was required and you could identify which parts required the license. I tried to offer a better way than hard coding strings to identify parts that required a license. Apparently license isn't required and you can't identify the parts anyway. Since the field isn't required then there is no harm in saving a record without it. I was trying to emphasize the point that saving incomplete/bad data leads to other problems. and you are the guardian of the data. If bad decisions are made because of inaccurate/incomplete data,, it will always fall on your head even if it isn't technically your fault. Since you don't need my advice, I'll move on. Good luck.

Sir,
I believe this entire thing comes down to a miss-communication on my exact situation.
Your points were all valid. Unfortunately they did not all apply this time.
I believe I understand the points and theory you were putting forth. Although not all applicable here, I will keep them in mind as I move forward and work with situations and data that I have more control over.

I think I am making progress with management though. This morning they asked if I could use "one of those Q things" to tell him how many items of a certain P/N we processed last month. (Already had the Query and report done. Just had to show him how to select it) Baby steps.

I look forward to your future advise and suggestions.

 

[Pat Hartman]

Good luck