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]

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

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]

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 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