Simple Question - checking for 0 records

[twilbour]

I've been trying to solve this for a while, and I know the answer is easy - but I just can't seem to get it to go. I'm creating a macro that runs when data is input in a form box. The first step is to check the data (its an order number) to see if it has been used before. It has to look in a table called "closed orders" So I created a parameter query, and then a form based on that query. I want to use an if/then in the macro to take action, or not, depending on whether there is a match.

I've tried a multitude of ways and can't seem to make it work. I created a macro that opened the form with the parameter from input form. Works great. On my form I have a count box that counts how many records in a given field. Also great. Always correct. Then I set up If condition in several ways - but none works. One condition is IsNull for the count box - or any other field for that matter. When I run it I just get an "out of string space" error. No clue why. Database is not big. I'm not using fields or counts bigger than 65K.

I tried the VBA route. I was able to run the underlying query and create if then based on if there were any records. My problem in the VBA was how to run the report, and pass the parameter that the query needs.

I'm really, really frustrated. I've set up several other queries and macros. I've written some basic VBA that all seem to accomplish WAY more, but this thing is driiving me nuts!

 

[Beetle]

I'm a bit confused with what you've got going on here. You have a form where a user enters an Order Number. When that number is entered, you then open another form to check if the same number exists in a separate table named "closed orders"? If it exists you want do do something (I'm not sure what - run a report maybe)? If it doesn't exist you want to do something else (again, not sure what)?

Why do you (apparently) have a separate table for "closed" orders? If all orders were in one table, with just a Yes/No field to indicate if they were "closed", then duplicate order numbers could be managed with a simple unique index.

 

[twilbour]

Thanks for the reply. I admit I'm very new at this, and I'm sure my design is not even close to efficient, but it seems to be working for my purposes for now. I'm totally open to other ideas.

Actually - you've got the concept. I have closed orders in a seperate table because my database doesn't own the master order file, and the whole master file is too big to store (I only use a small part of it). So I grab orders from the master when I need them, and then I copy them over to the closed file when I'm done. I check closed first, because I don't want to have to search the master if I've already dealt with the file.

I think I solved my problem last night - after I posted! Again, probably not the most efficient way, but seems to work. Thank you - and thank you all for this forum. I have referenced it extensively as I've been working on my project(s)!