Look up Customer in table, if not there then notify user.

[Lanie]

Is there any way to enter the customer number in the invoice form and have it look up in the customer table to see if it is in the customer table and if not, then notify the user to add the customer record?<br><br>I bet this is an easy one, but need a quick and hopefully easy to do solution.<br><br>Thanks for your help.&nbsp;&nbsp;Lanie <p>Lanie<br><a href=mailto:etroidl@conaxfl.com>etroidl@conaxfl.com</a><br><a href= > </a><br>

 

[DougP]

Well the easiest way is make a listbox which has all of the users in it.<br>Then look for them in the list.<br>If not in the list then add user and requery the list to add the new record to the list.<br>If they are there then click on them and sync listbox to form.<br><br>OK<br>

 

[jgarnick]

You could try putting a dlookup statement in the after update property (perhaps the on change, on exit, or on lost focus properties too) of the field that the user is inputting the customer number.<br><br>If IsNull(DLookup(&quot;[customernumber]&quot;, &quot;customertable&quot;, &quot;[customernumber]=&quot; & Me![customernumber])) Then<br>&nbsp;&nbsp;msgbox (&quot;Customer not found.&nbsp;&nbsp;Please enter customer.&quot<br>endif<br><br>It would be nice to add code to allow the user to add the customer right then! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[gol4]

Have them enter data via a combo box. Use ON not in list event to launch a macro that opens a msg box and then your customer entry form you can even auto fill some fields for them.

 

[Lanie]

Thanks to all of you, I can't tell you how much your help means.... <br><br>What I did was to change to the field in the table to a lookup (customer table). add that to the form, set Limit to list to yes, set auto expand to yes and set required field (in the table design) to yes.<br><br>I also created a message box and got it working.&nbsp;&nbsp;However - I am getting the system message as well as the message I created.&nbsp;&nbsp;I think one message is enough and would like to get rid of the system message - how can I do that?<br><br>I've looked in the properties, but can seem to locate anyplace that would allow me to do this.&nbsp;&nbsp;<br><br>Sure would apprecite your help.&nbsp;&nbsp;Lanie<br><br> <p>Lanie<br><a href=mailto:etroidl@conaxfl.com>etroidl@conaxfl.com</a><br><a href= > </a><br>

 

[CharlesDFW]

Lanie,<br><br>The trick is in changing the response parameter that is passed in the first line of the &quot;Sub. . .&quot;<br><br>There are constants that tell Access what to do when a record is added. You need to include the following:<br><br>Response = acDataErrAdded<br><br>Access will automatically requery the combobox, and since the record has been added, it is in the list and all's right with the world. You need to be sure that everything that's required to add the record is put in using your code.<br><br>

 

[Guest_imported]

I was just reading this thread, and something about problem defenitions was well illustrated here. I am not being critical - it is just interesting.<br><br>The original problem definition was peppered with solution.&nbsp;&nbsp;When we think about our troubles, if we take a step back before we solve the problem, we may save time?<br><br>What if the problem had been stated &quot;I want to make sure that the user enters a known Customer, and has the ability to enter a new customer if necessary.&quot;&nbsp;&nbsp;which may be closer to what you were really seeking.<br><br>Sometimes I spend a lot of time working on the wrong problem, trying to fix what I started.<br><br>Did you add a &quot;New Customer&quot; button too?&nbsp;&nbsp;It might be cool if you attached the call to add a Customer to the doubleclick event for the listbox.&nbsp;&nbsp;Many use a special record in the target table.

 

[Lanie]

JohnatOBI<br><br>Thanks for your insite.&nbsp;&nbsp;<br><br>I do have the Add New Customer button in the main form and I now have changed the properties Not on list to yes, added the message which includes an Open Customer Form in add mode when OK is clicked.<br><br>This should meet his needs.&nbsp;&nbsp;<br><br>Thanks to everyone for your help once again.&nbsp;&nbsp;You have helped me tremendously.&nbsp;&nbsp;<br><br> <p>Lanie<br><a href=mailto:etroidl@conaxfl.com>etroidl@conaxfl.com</a><br><a href= > </a><br>