For...Each Record Set Question

[Arli]

I have a database that is running with Access 2003. The Database is used to track orders and for putting time stamps on those orders. I need to put a pop up message on the database that alerts the operators when specific order #s come through. The problem is that there are 350 different order #s that require the pop up message.

I think the way to do this is with a recordset (containing the 350 orders). Using the for each statement, I think I can check each record against the current order. Upon finding the order, the msg box will pop up and alert the operator that the order requires more attention.

My question is this: is this the best way to accomplish this?

Thanks for your help. Sorry if this is a repeat question (I searched and found nothing).

Arli

[Denburt]

I am not sure how an order "Comes Through" If someone types in an order number you can use the before or after update event and instead of a for each just use a select statement and if it's not eof then send your message.
Select OrderNo From Orders where OrderNo = me!txtBox

[OldBirdman]

Create a table of those 300+ order numbers that require popup warnings. I called it tWarnExists and have 1 field named OrderNo. OrderNo can be the primary key. I called the entry textbox txtOrderNo.

Then the code is easy:

Private Sub txtOrderNo_BeforeUpdate
    If DCount("OrderNo", "tWarnExists", "OrderNo=" & txtOrderNo) <> 0 Then
        MsgBox "Some message here"
    End If
End Sub

This code could go on other events if more appropriate. OnAfterUpdate, OnLostFocus, OnChange, or on form events such as OnCurrent.

[NeoPa]

I would consider creating a query which links the two tables (nice idea btw, to store the flagged items in a table).

Once you have this query available you can check it easily (DCount()) as well as use it to get the current list if that's required for the error message. Depending on your requirement, the results from the query may need to be filtered. Easy enough with a defined QueryDef object available.

[Arli]

I created a table that houses all of my special process part numbers. I then used the code below to search the table. It works this way.

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("WorkOrder", "tblTotalTopCoatParts", "[PartNumber] =  WorkOrder") <> 0 Then
       MsgBox "This Part Receives a Special Process"
    End If
End Sub

There is a small problem though. the WorkOrder will include more numbers than just a part number. I need to apply a wildcard character to search through all numbers in my WorkOrder for the PartNumber (example: PartNumber =0411174-3 could be in WorkOrder =A005424 0411174-3). I get a run time error when I put the "*" & in the code (see below).

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("WorkOrder", "tblTotalTopCoatParts", "[PartNumber] = "*"& WorkOrder &"*"") <> 0 Then
       MsgBox "This Part Receives a Special Process"
    End If
End Sub

The error message is a type mismatch. As always, thanks in advance for the help.

[NeoPa]

Your quotes are wrong.

Try this instead (always separate ampersands (&) with spaces when used for concatenation) :

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("WorkOrder", _
              "tblTotalTopCoatParts", _
              "[PartNumber]="'*" & WorkOrder & "*'") <> 0 Then
        MsgBox "This Part Receives a Special Process"
    End If
End Sub

[Arli]

@NeoPa
I followed the direction above and I still receive a run time error.

Thanks
John Hathcock

[NeoPa]

Why don't you post what you have now and we'll see.

[Arli]

When I put the following code into my form I get a runtime error '13' type mismatch.

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("WorkOrder", "tblTotalTopCoatParts", "[PartNumber] = " * "  & WorkOrder & " * " ") <> 0 Then
       MsgBox "This Part Receives a Special Top Coat"
    End If
 
End Sub

I put the proper spaces between the ampersand (&). As I view the code from the above posts. Without the Wildcard (*) and ampersands (&) the code works perfectly. When I add the wildcard and ampersand, that is when I have the problem.

[NeoPa]

The trouble with trying to duplicate something manually is that it relies on you getting everything precisely right and noticing every detail.

It appears that you have extra spaces surrounding your string in place of single-quote characters (').

I strongly suggest you copy the code from my earlier post exactly as it is and paste it into your module. I suspect you'll see it simply works.

Let me know how you get on.

[Arli]

I copied your code exactly and I get a Compile error stating that I have a syntax error.

Thanks for your help on this.

[FishVal]

@NeoPa
There is a redundant double-quote after [PartNumber]=.
And ... I think wildcards work with "Like" operator only.

Regards,
Fish

[Arli]

I tried it as listed above (removed the redundant double qoute) and replaced the = sign with a Like statement. The program works...until I put the wildcard and ampersand in place.

the code is:

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("WorkOrder", _
              "tblTotalTopCoatParts", _
              "[PartNumber]like '*' & WorkOrder") <> 0 Then
        MsgBox "This Part Receives a Special Process"
    End If
End Sub 

[Denburt]

Slight adjustment:

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("WorkOrder", _
              "tblTotalTopCoatParts", _
              "[PartNumber] like '*" & WorkOrder & "*'") <> 0 Then
        MsgBox "This Part Receives a Special Process"
    End If
End Sub 

[Denburt]

Or with only the preceding wildcard and not the trailing one.

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("WorkOrder", _
              "tblTotalTopCoatParts", _
              "[PartNumber] like '*" & WorkOrder & "'") <> 0 Then
        MsgBox "This Part Receives a Special Process"
    End If
End Sub 

[NeoPa]

@FishVal
Well spotted Fish. Both points spot on and I apologise for the mistakes.

Denburt's post (#14) shows how it should have been after fixing these mistakes as explained.

Let us know if this works for you.

[Arli]

It works when I only enter the PartNumber (0411174-3 like 0411174-3), but when it checks (0411174-3 like a005424 0411174-3) it does not work. It does not give me any kind of error message at that point (so we have that fixed).

To clarify: My WorkOrder field is likely to have extra characters at the begining (followed by a space usually, but not always) like a005424 0411174-3 (where the bold and underlined number is my PartNumber)

Again, you guys are awesome.

[NeoPa]

It should be working for anything where that string of characters is found, whether at the start or otherwise.

Can you post your latest code again.

[Arli]

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("WorkOrder", _
              "tblTotalTopCoatParts", _
              "[PartNumber]like '*" & WorkOrder & "'") <> 0 Then
        MsgBox "This Part Receives a Special Process"
    End If
End Sub
 

[NeoPa]

Normally there is a space before "Like", but I tested and this won't cause a problem.

The only other points that come to mind are both unlikely, but should be pursued I guess.

1. There is no "*" after the value (as is found in post #14 but not #15). This is unlikely to be a problem, but there may be some unprintable data after the WorkOrder value in the field for all we know.
2. If using Access version 2003 or later there is an option to use ANSI-92 compatibility (ANSI Standards in String Comparisons). This would cause a different wildcard character (%) to be the correct one to use.

[Arli]

I checked both of those options and still get the same result. I was wondering is there a way to alter the code to so that the WorkOrder is like Partnumber? At this point, my head is hurting from thinking at this for too long. I wonder if changing the order or the way it is coded could fix it.

Thanks again.

[NeoPa]

The construct A Like B can only work if A is >= B.

IE B must be a string subset of A. Reversing this will only ever work in the restricted case of A=B (when it would generally be pointless anyway).

It can be coded that way, but I doubt it will help much.

[Arli]

I thought about this another way. Can I use code to only check the PartNumber against the WorkOrder After the first 8 characters (using the ######## to represent the first 8 characters). In essence, I would be filtering out the first part of the WorkOrder altogether and only checking the remaining number. If I can get that to work, it will then only be comparing the Part Number to the WorkOrder for an exact match.

Thanks

[NeoPa]

That's possible, but it's more complicated and gives you nothing extra. You can use Mid() or Right() functions - even from your Jet SQL.

The previous idea works perfectly if properly implemented.

Have you tried debugging your code (Debugging in VBA).

[Arli]

I have been debugging the code. It appears to work as it needs to. The only thing that appears to be of is that maybe the wildcard (* and &) characters need to be associated with the the PartNumber field.

When I set a watch on the PartNumber field I get this when the code gets to that part: Watch : : tblTotalTopCoatParts : <Expression not defined in context> : Empty : Form_frmIDLog.WorkOrder_BeforeUpdate

A watch set on the WorkOrder field returns exactly what is in that field (whether it is 0411174-3 or 92095 0411174-3).

Any help is greatly appreciated.

[NeoPa]

@Arli
Allowing for the typo (of for off) I still have no idea what you mean by this.
@Arli
This looks like you're trying to set a watch on your table. This won't work. It must be a reference to an object accessible to the code (variables etc).
@Arli
That looks good. We now need to follow what's going on when the records are found (or not).

[Arli]

@NeoPa
Sorry about the typo. What I mean by that is that when I hover over the Variable (WorkOrder), it is exactly what I need to see. That leads me to think that maybe I need to put the Wildcards on the PartNumber (just a thought--I tried it and it did not work)

@NeoPa
I understand exactly what you are saying here. Would that also apply to the PartNumber field because the code is comparing the variable (WorkOrder) to the field in the table? Could that be our problem then?[/quote]

@NeoPa
The only value that shows at the break is the number assigned to the WorkOrder field. It shows the whole number as entered. That part works perfectly.

Thanks for your time on this.

[NeoPa]

@Arli
I simply mentioned it to clarify that it was not the cause of my confusion. Typos are found everywhere. Of course it's good to be careful, but don't worry if you let one slip through.
@Arli
I think I need to see your latest code again. It's hard to know what you're referring to with your debugging without your exact code close by to refer to.

When we have that we can decide what we expect to see, trace through the code until we (you actually at this point) see something unexpected. At this point we focus our attention on where it diverges from our expectation until we see the problem.

[Arli]

Here is the current code. I removed the Wildcard in order to kind of start from fresh. I put a watch on the WorkOrder variable. It reads as it should with the number that is input (i.e. 0411174-3 or 0523106-13). The program then returns the message box as programmed. Because the WorkOrder field is recorded 'as is,' I do not need the wildcard there (it will be set to whatever the field is: i.e. a 005424 0411174-3 or 92095 0523106-13--either of which should produce the message box, but do not).

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("WorkOrder", _
              "tblTotalTopCoatParts", _
              "[PartNumber]Like WorkOrder") <> 0 Then
        MsgBox "This Part Receives a Special Process"
    End If
End Sub
 

Thanks

[NeoPa]

I'm not sure why you would think that Arli. A Like compared to a value with no wildcard characters is equivalent to an =.

I can't see that the error message would ever come up with that code as it stands.

[NeoPa]

I know my earlier version had some dumb errors in it, but try this :

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("[WorkOrder]", _
              "[tblTotalTopCoatParts]", _
              "[PartNumber] Like '*' & [WorkOrder] & '*'") <> 0 Then
        MsgBox "This Part Receives a Special Process"
    End If
End Sub

[Arli]

That works, but only if the number entered into WorkOrder matches exactly with the PartNumber (i.e. PartNumber is 0411174-3 and WorkOrder is 0411174-3. If the WorkOrder is 92095 0411174-3, the code finishes without prompting for the message). Is there a way that I could filter out 8 characters on the front side of the WorkOrder variable? That may work.

Thanks

[NeoPa]

@Arli
What works exactly? Are you using the code from post #31 verbatim?
@Arli
I cannot see this, unless you're still using your previous code. If this is still working as you say with my recently posted code I'd like to see a copy for myself. This just makes no sense to me.
@Arli
Yes, but as that is less reliable in just about every way, let's try to nail the problem first.

[Arli]

Yes, that code is verbatim. Attached is a copy of the database. When you open it goes straight into the switchboard. The form you want to use is the frmIDLog. Once you are there, if you enter the number 0411174-3 or 0523106-13 into the WorkOrder field, you will get the appropriate pop up message. if you put in s039915 0411174-3 or kc25835 0523106-13 (those extra eight characters), nothing happens. I need it to pop up either way.

Hope this helps.

[NeoPa]

Someone with the sense to include an explanation of how to reproduce the problem with an attached database. I see this will be easier than I've become used to expecting :)

I will download and check later when I get home, as I don't ever download at work.

[NeoPa]

Ah.

Looking at the database and working out what's going on it now seems clear to me that you are looking for when [PartNumber] is within [WorkOrder], rather than the other way around.

This is much less straightforward, but actually SQL can handle it quite well really. Try this (I did & it worked fine) :

Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
    If DCount("*", _
              "[tblTotalTopCoatParts]", _
              "'" & Me.WorkOrder & "' Like '*' & [PartNumber] & '*'") <> 0 Then
        MsgBox "This Part Receives a Special Process"
    End If
End Sub

[NeoPa]

You may also want to bear in mind that this message is just that. A simple message. This does not force the different processing that you require.

[Arli]

That worked perfectly!

The message is only a 'heads up' to the users to prevent that process from being missed.

Thanks for your help with that.

[NeoPa]

A pleasure to help.

PS Welcome Member :)

[Arli]

I ran into a problem when implementing and testing that code. What I have is listed below:

 
Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
 
   If DCount("*", _
              "[tblTotalTopCoatParts]", _
              "'" & Me.WorkOrder & "' Like '*' & [PartNumber] & '*'") <> 0 Then
        MsgBox "This Part Receives a Special Process"
    End If
 
End Sub
 

When the code is ran it pops up on all entries regardless of whether the WorkOrder is Like the PartNumber. I notice that there is a wild card before the tblTotalTopCoatParts reference, I wondered if that was what would cause that.

Thanks

[NeoPa]

I doubt it. I can't see any reason for the failure.

Why don't you attach another copy of the db & I'll look at for you later.

[ChipR]

Not positive, but I believe that

"'" & Me.WorkOrder & "'

should be

"[WorkOrder]

since it is the name of the field.

[FishVal]

Dear Arli.

Your [tblTotalTopCoatParts] table has en empty entry.
Sure - empty [PartNumber] value concatenated with "*" wildcard will be like everything.

Kind regards,
Fish.

[Arli]

Here is a sample of the database with the code 'as is.' When the code runs, it pops up the message box for anything entered. If you type random symbols into it, it will pop up the message.

[NeoPa]

@ChipR
Not in this case Chip. It's actually the control that's referred to here.

[NeoPa]

Fish was spot-on Arli.

Your program is fine. Your data doesn't support what you want to do. An empty record will always match whatever you put in, so the code will always show the message. Quite correctly according to your stated requirement.

Remove that one record of course, and all should be well.

Does that make sense?

[Arli]

That worked. You guys are awesome. I would never have found that.

[NeoPa]

@Arli
Well, this solution was a little way off the beaten track as it were. Getting your head around it may well be a little difficult.

It was actually a very clever spot by Fish to see it. We've got used to that from him though :)