If greater than 0 then...

[wilbr73]

I want to have a query that looks for orphaned records when importing data from an excel spreadsheet. I have everything working from the import to the count orphaned records, to count the number of records, a message box that pops up and alerts the user of orphaned records... I am able get everything to work as long as I put in the exact number of records that are orphaned. What is the correct way to say "greater than 0" so access will open up the message box for any number of orphaned records?

Thanks,
Will

[June7]

Where are you putting in the 'exact number of records'?

Criteria under the appropriate field: >0

Show the query SQL statement and code for analysis if you need more help.

[wilbr73]

Here is how I have it set up...

I have an unmatched query that tells me when I have orphaned records, I have a form built on that query to display the records and on that form is an unbound box that I have set up with the count function to display the number of records. I have an If then macro built that looks at the calculated field. If there is a value I want it to display the message box alerting the user of the orphaned records. If there are none than I want the macro to just close the form/query. When I put in fake records so that I know exactly how many orphaned records I have and I put in that value in the if then statement, the macro runs just fine. If I put in >0 then it skips and goes straight to closing the form/query.

Here is a screen shot of what I have... If I put in 5 orphaned records, for example, and I type in =5 into the highlighted area then the macro runs just fine. If I put in >0 as it is right now, the macro just skips over the message box and closes the window.

The screen shot is below.

[wilbr73]

[wilbr73]

So the screen shot won't work so here is attempt #2. Here is how the macro looks in the macro builder.

if [Forms]![frmUnpairedPlateLayoutSamples]![Text56]>0
Message box
Message: There are samples on the layout that are not associated with a sample in the database.
Beep: yes
Type: none
Title: Samples Not Present
Else
CloseWindow
Object Type: Form
Object Name:frmUnpairedPlateLayoutSamples
Prompt: Yes

[June7]

Looks good to me. If =5 works then >0 should, so should >=1. But I don't use macros, only VBA.

[wilbr73]

I will try the >=1 trick and see if that works.

Do you have any suggestions for references to get started in VBA? I know that is where the action is for really taking the database to the next level. I am a complete beginner when it comes to VBA.

Thanks.

[June7]

I never got any books on Access and VBA (I had a mentor to guide me) but I am sure many good ones available. I did get some old books on Visual Basic which is very similar to VBA. This book looks interesting: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc.

Lots of tutorials on web, just Google it. This one looks promising http://www.access-vba.net/introducti...ccess-vba.html

[Missinglinq]

Here's a site I usually recommend as a great starting point, having 28 well written, well defined, clearly named chapters.

http://www.functionx.com/vbaccess/index.htm

Linq ;0)>