By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,712 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

For...Each Record Set Question

P: 39
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
May 6 '09 #1
Share this Question
Share on Google+
47 Replies


Denburt
Expert 100+
P: 1,356
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
May 6 '09 #2

100+
P: 675
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtOrderNo_BeforeUpdate
  2.     If DCount("OrderNo", "tWarnExists", "OrderNo=" & txtOrderNo) <> 0 Then
  3.         MsgBox "Some message here"
  4.     End If
  5. End Sub
This code could go on other events if more appropriate. OnAfterUpdate, OnLostFocus, OnChange, or on form events such as OnCurrent.
May 6 '09 #3

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 8 '09 #4

P: 39
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("WorkOrder", "tblTotalTopCoatParts", "[PartNumber] =  WorkOrder") <> 0 Then
  4.        MsgBox "This Part Receives a Special Process"
  5.     End If
  6. 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).

Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("WorkOrder", "tblTotalTopCoatParts", "[PartNumber] = "*"& WorkOrder &"*"") <> 0 Then
  4.        MsgBox "This Part Receives a Special Process"
  5.     End If
  6. End Sub
The error message is a type mismatch. As always, thanks in advance for the help.
May 19 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
Your quotes are wrong.

Try this instead (always separate ampersands (&) with spaces when used for concatenation) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("WorkOrder", _
  4.               "tblTotalTopCoatParts", _
  5.               "[PartNumber]="'*" & WorkOrder & "*'") <> 0 Then
  6.         MsgBox "This Part Receives a Special Process"
  7.     End If
  8. End Sub
May 19 '09 #6

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

Thanks
John Hathcock
May 20 '09 #7

NeoPa
Expert Mod 15k+
P: 31,709
Why don't you post what you have now and we'll see.
May 20 '09 #8

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


Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("WorkOrder", "tblTotalTopCoatParts", "[PartNumber] = " * "  & WorkOrder & " * " ") <> 0 Then
  4.        MsgBox "This Part Receives a Special Top Coat"
  5.     End If
  6.  
  7. 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.
May 21 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 21 '09 #10

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

Thanks for your help on this.
May 21 '09 #11

FishVal
Expert 2.5K+
P: 2,653
@NeoPa
There is a redundant double-quote after [PartNumber]=.
And ... I think wildcards work with "Like" operator only.

Regards,
Fish
May 21 '09 #12

P: 39
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("WorkOrder", _
  4.               "tblTotalTopCoatParts", _
  5.               "[PartNumber]like '*' & WorkOrder") <> 0 Then
  6.         MsgBox "This Part Receives a Special Process"
  7.     End If
  8. End Sub 
May 21 '09 #13

Denburt
Expert 100+
P: 1,356
Slight adjustment:
Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("WorkOrder", _
  4.               "tblTotalTopCoatParts", _
  5.               "[PartNumber] like '*" & WorkOrder & "*'") <> 0 Then
  6.         MsgBox "This Part Receives a Special Process"
  7.     End If
  8. End Sub 
May 21 '09 #14

Denburt
Expert 100+
P: 1,356
Or with only the preceding wildcard and not the trailing one.
Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("WorkOrder", _
  4.               "tblTotalTopCoatParts", _
  5.               "[PartNumber] like '*" & WorkOrder & "'") <> 0 Then
  6.         MsgBox "This Part Receives a Special Process"
  7.     End If
  8. End Sub 
May 21 '09 #15

NeoPa
Expert Mod 15k+
P: 31,709
@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.
May 22 '09 #16

P: 39
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.
May 22 '09 #17

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 22 '09 #18

P: 39
Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("WorkOrder", _
  4.               "tblTotalTopCoatParts", _
  5.               "[PartNumber]like '*" & WorkOrder & "'") <> 0 Then
  6.         MsgBox "This Part Receives a Special Process"
  7.     End If
  8. End Sub
  9.  
May 22 '09 #19

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 22 '09 #20

P: 39
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.
May 22 '09 #21

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 22 '09 #22

P: 39
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
May 26 '09 #23

NeoPa
Expert Mod 15k+
P: 31,709
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).
May 26 '09 #24

P: 39
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.
May 28 '09 #25

NeoPa
Expert Mod 15k+
P: 31,709
@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).
May 28 '09 #26

P: 39
@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.
May 29 '09 #27

NeoPa
Expert Mod 15k+
P: 31,709
@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.
May 29 '09 #28

P: 39
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).



Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("WorkOrder", _
  4.               "tblTotalTopCoatParts", _
  5.               "[PartNumber]Like WorkOrder") <> 0 Then
  6.         MsgBox "This Part Receives a Special Process"
  7.     End If
  8. End Sub
  9.  
Thanks
Jun 1 '09 #29

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 1 '09 #30

NeoPa
Expert Mod 15k+
P: 31,709
I know my earlier version had some dumb errors in it, but try this :
Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("[WorkOrder]", _
  4.               "[tblTotalTopCoatParts]", _
  5.               "[PartNumber] Like '*' & [WorkOrder] & '*'") <> 0 Then
  6.         MsgBox "This Part Receives a Special Process"
  7.     End If
  8. End Sub
Jun 1 '09 #31

P: 39
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
Jun 1 '09 #32

NeoPa
Expert Mod 15k+
P: 31,709
@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.
Jun 1 '09 #33

P: 39
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.
Attached Files
File Type: zip O8I_IDLog.zip (934.9 KB, 78 views)
Jun 1 '09 #34

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 1 '09 #35

NeoPa
Expert Mod 15k+
P: 31,709
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) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("*", _
  4.               "[tblTotalTopCoatParts]", _
  5.               "'" & Me.WorkOrder & "' Like '*' & [PartNumber] & '*'") <> 0 Then
  6.         MsgBox "This Part Receives a Special Process"
  7.     End If
  8. End Sub
Jun 1 '09 #36

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 1 '09 #37

P: 39
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.
Jun 1 '09 #38

NeoPa
Expert Mod 15k+
P: 31,709
A pleasure to help.

PS Welcome Member :)
Jun 1 '09 #39

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub WorkOrder_BeforeUpdate(Cancel As Integer)
  3.  
  4.    If DCount("*", _
  5.               "[tblTotalTopCoatParts]", _
  6.               "'" & Me.WorkOrder & "' Like '*' & [PartNumber] & '*'") <> 0 Then
  7.         MsgBox "This Part Receives a Special Process"
  8.     End If
  9.  
  10. End Sub
  11.  
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
Jun 3 '09 #40

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 3 '09 #41

Expert 100+
P: 1,287
Not positive, but I believe that
Expand|Select|Wrap|Line Numbers
  1. "'" & Me.WorkOrder & "'
should be
Expand|Select|Wrap|Line Numbers
  1. "[WorkOrder]
since it is the name of the field.
Jun 3 '09 #42

FishVal
Expert 2.5K+
P: 2,653
Dear Arli.

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

Kind regards,
Fish.
Jun 3 '09 #43

P: 39
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.
Attached Files
File Type: zip O8I_IDLog.zip (937.5 KB, 70 views)
Jun 3 '09 #44

NeoPa
Expert Mod 15k+
P: 31,709
@ChipR
Not in this case Chip. It's actually the control that's referred to here.
Jun 3 '09 #45

NeoPa
Expert Mod 15k+
P: 31,709
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?
Jun 4 '09 #46

P: 39
That worked. You guys are awesome. I would never have found that.
Jun 4 '09 #47

NeoPa
Expert Mod 15k+
P: 31,709
@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 :)
Jun 4 '09 #48

Post your reply

Sign in to post your reply or Sign up for a free account.