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.
thutchinsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Nz means if the first argument is NULL, return and use the second argument.
0
mbizupCommented:
<< 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.
0
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: http://office.microsoft.com/en-us/access-help/nz-function-HA001228890.aspx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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?
0
mbizupCommented:
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.
0
thutchinsonAuthor Commented:
Thanks to mbizup who told me the screenshots didn't get attached.  Here they are (hopefully)
Code-to-create-new-record-not-wo.pdf
0
mbizupCommented:
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )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".
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
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:
http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

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.
0
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.
0
mbizupCommented:
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.
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.