Basic Programming question

I am trying to modify a contacts template from Office online and I am running into a problem when I try to change the main table.

Could someone please tell me what NZ in the statement below refers to and how I would find where this value is set?  It its referred to over and over again in the applet.

Where Condition ="[ID]=" & Nz(DMax("[ID]",[Form].[RecordSource]),0)

Attached is a screenshot of a the full command.  Thanks.
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I agree with mbizup - you  seem to be searching for a record. If you want to add a Record, then just open the form as mbizup has shown.

However, you must somehow "relate" that record back to the parent (assuming this is a Contact record which must be related back to another entity, like a Company or Account). I believe that's what the "search" section of your macro does. Like miriam I haven't used macros in a very long time, so I can't really tell you how you'd do that. If I was doing this in VBA, I'd do this:

DoCmd.OpenForm "MyFormName", , , , acFormAdd
Forms("MyFormName").ForeignKeyField = Me.IDField

This would open the form in the "Add" mode, and would set the value of the Foreign Key to that of it's "parent".
Nz means if the first argument is NULL, return and use the second argument.
<< Where Condition ="[ID]=" & Nz(DMax("[ID]",[Form].[RecordSource]),0) >>

So in your case, if the DMax returns a NULL (no records meeting the criteria), use Zero instead.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In addition to mbizup's comments:

Your DMax function is referring to "Me.Recordsource", which essentially grabs the "recordsource" of the form (or report) in which the code is running. So if I have a Form where the Recordsource is the table named "Customer", that DMax function essentially asks:

"Give me the largest value in the ID column of the Customer table. "

The Nz function essentially asks:

"Give me the value returned from the DMax function. If there is none, give me a zero."

Here's the Microsoft Office writeup on the Nz function:
thutchinsonAuthor Commented:
Thank you for helping me to understand.  I really appreciate it.
The command is supposed to open a form with blank fields so I can add a new record to the contacts table.  Unfortunately, the form opens without any empty input fields at all. Attached are the screenshots.

Will you please take a look LSM or mbizip?
Your screenshots didn't get attached... either here or in your original post.

I'm calling it a night, but just posting this for your info - to help you get a quicker response.

Give it another try... if you have attached the file correctly, you should be able to see it in your own comment as soon as you post it.
thutchinsonAuthor Commented:
Thanks to mbizup who told me the screenshots didn't get attached.  Here they are (hopefully)
Are you simply trying to create a macro for a custom button to open a form to add new records?

The macro you have seems to be geared towards searching for records.

I practically never use macros - prefer VBA by far - so I am not very familiar with them, but I would think your macro should look more like this (change the form name to match your database, of course):
Macro to add a new record
mbizupConnect With a Mentor Commented:
IF as Scott suggested, you have a one-to-many relationship between companies and contacts (I think he's hit the nail on the head about that), you should also look into using a subform for your contact details.

This is a good tutorial, that clearly lays out how to do this:

A subform, if it is set up and linked to the main form correctly will automatically include the correct ID linking the contact records to the company records when new records are created.

Subforms are specifically designed to handle relationships like that.  It would involve some design changes that would go beyond the scope of this question, but depending on your application it might be worthwhile.
thutchinsonAuthor Commented:
Thank you for these excellent suggestions.  

Yes, there is a one-to-many relationship between Companies (one) and Contacts (many).

 I discovered why none of the procedures worked when I tried to add a contacts subform to the companies form and Access couldn't identify the relationship.  There was a single duplicate value in my company list.  After I removed it everything worked!

I wish the errors in Access were a little more descriptive.  Anyway, I learned something today.

Thanks for your help guys.
If/when you move from developing macros to developing VBA code, you will find the error messages to be something you can really work with... much more meaningful.

That's one of the major reasons I prefer VBA to macros.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I wish the errors in Access were a little more descriptive.
Some are quite cryptic, but when you become more experienced you'll be able to discern what is happening from the error. Many of them are necessarily vague, since they cover so many things.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.