Check Name in a Specific Customer Record - after update

Hi,

Have to use the after update event of a combox rather than the notinlist 
event to allow for a third option. Combobox is Combo111 which has a control 
source [Contact Name].
The combo has a list of names etc which have already been filtered to a 
company. 

tblCustContacts has records with fields of Last_Name, First_Name, RecordID, 
and CUSTID.
RecordID is unique.

User enters a name "Fred Jones"
Jones is filtered out.
Now I need to check if Jones is in the list associated with that particular 
CUSTID.

If not - user can choose to add to the list adding details , just append the 
name or leave the name as a once off.

I think that I have to check a recordset where the customerID is something
and if no match then do something etc.

I have:

str2 = Right(Trim(Me![Contact Name]), Len(Trim(Me![Contact Name])) - 
InStr(1, Me![Contact Name], " "))
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblCUSTCONTACTS", dbOpenDynaset)
    str2 = rst![LAST_NAME]
    
    rst.FindFirst str2
    
    If rst.NoMatch Then     'The name is unique and may be used
        'ask if the name is too be added
    intpak = MsgBox("Do you wish to add a new contact?" & vbCrLf & _
            "Select " & vbCrLf & _
            " - YES to open Customer Table to Enter / Delete contacts?" & 
vbCrLf & _
            " - No To append Name to customer List without Details" & vbCrLf 
& _
            " - CANCEL Just to accept name as a once off", vbYesNoCancel, _
            "Update Customer")

        
'**************************************************************************************************
                      Select Case intpak


Its the rst.FindFirst str2 WHERE part thats got me.

CustmerID comes form another combobox column.
0
Utf
12/11/2009 12:58:01 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

0 Replies
1056 Views

Similar Articles

[PageSpeed] 12

Reply:

Similar Artilces:

Check box = 1
I'm pretty out of my depth here, so any help would be much appreciated. I'm trying to set up an attendance form to cover a 24 hour perid. i'd like a columnof 14 checkboxes (places available per hour), and at the bottom a total of how many boxes have been checked, and then repeated for the following 23 columns. How do i go about giving a cell the value of "1"if the box is checked, and accumulated the total of checked boxes. I'm struggling to get past changing "true"/"false" values to "1"/"0" values. Also, do i ...

how do you hide a name from the globel address book?
in exchange 2K. Thanks ADUC Properties of the user Exch Advanced Check the Hide from Address Lists box On Fri, 10 Sep 2004 20:34:05 -0400, "Dooma" <Say-no-to-spam@hotmail.com> wrote: >in exchange 2K. > >Thanks > Thanks. Sorry I missed it. "Andy David - Exchange MVP" <adavid@pleasekeepinngcheesebucket.com> wrote in message news:aii4k014g54q5v7dic5a7r568lq31moanr@4ax.com... > ADUC > Properties of the user > Exch Advanced > Check the Hide from Address Lists box > > > On Fri, 10 Sep 2004 20:34:05 -0400, "Dooma" &...

changing a custom toolbar
I have a legacy access database that i'm now working on in Access 2007. I have a custom toolbar set up in there (at least that is what A2007 shows it as on the Add-Ins tab of the ribbon). I need to edit that custom toolbar (menu). How do I do that? Karen Hey Karen, I have spent a lot of time trying to find examples to be able to edit a menubar from a previous version in Access 2007. Finally, I have come up with the following that works! I hope this helps: --- start code --- Function TweakToolbar() Dim cbr As CommandBar Dim cbrctl As CommandBarControl Dim cbrbutt As C...

Q: Referencing named cells in external worksheet ?
Using Excel 2002. I have a workbook with 12 worksheets (one for every month of the year), wherein a lot of the information is looked-up (using VLOOKUP) in simples arrays. I saw no point in implementing the arrays as a 13th worksheet, because I will have a yearly version of my monthly worksheets in one workbook (so one for 2003, 2004, etc). If I change the array(s), I want them to be reflected in all referencing cells. Problem: If my workbook containing my arrays (called "Global") is loaded, I have no problem and the references to it read as: (blabla) 'Global.xls'!Roster ...

Update Prices Online... not updating stock prices
Hi all, I just upgraded from Money 2002 to Money 2004. In Money 2004 when I do a "Update prices Online" to update the prices of my stocks, this task runs to completion with out error but the actual stock prices are not updated. The "Read Call Summary Messages" for this task said it completed successfully. I even tried turning off my firewall. I went to "Update prices manually" and there was no new entries/prices for any of the stock. I verified my stocks still have the correct stock symbol listed. I also selected "Pick Quotes to Download" and...

Problems after kb890830 and kb978542 updates
After windows Vista installed kb890830 and kb978542 I noticed 4 problems so far. 1. MSN Service not available, error code: 800706b5. (This happened with previous updates and I fixed the issue by doing a system restore to a previous date.) 2.When I started internet explorer (ver 8.0.6001.18904) I got a message my default search engine was corrupted by another program. 3.I tried the usual system restore and found there were no restore points available 4. I had some unknown problem trying to log into this site. It kept giving a site not available message. I am running windows vis...

When form closes it creates a blank record in the table
I have a form that links to a table called 'Suppliers'. Whenever that form closes it automatically adds a blank record to that table. Is there a way to prevent that from happening? Thanks in advance <MichaelJohnson168@hotmail.com> wrote in message news:f63293f9-caa7-4450-bacf-e844c6d5b9c4@e4g2000hsg.googlegroups.com... >I have a form that links to a table called 'Suppliers'. Whenever that > form closes it automatically adds a blank record to that table. Is > there a way to prevent that from happening? If it only happens when you had the form on a new (blank...

Cannot add customer statement email address in Smartlist
On the Debtor Maintenance Options screen you can chose the checkbox to Send Email Statements to a customer and then enter the To, CC or BCC email addresses. In Smartlist under the Customer folder, while you can choose to add the Send Email Statements column you cannot select to add the To, CC or BCC email address fields. You should be able to do this so you can easily view the information. These columns should be standard columns that you can select for Customers. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most vo...

Check Boxes and Radio buttons on Worksheet
I just discovered that I can drop check box directly on a worksheet. I can make it do what I want, in a single instance, but I was wondering there was a way to make them act like formulas, in that I would like to attach the checkbox directly to a cell physically, and, incorporate it's TRUE/FALSE output in a formula, then be able to copy a whole row or column of cells containing the checkbox and formula ...and other data perhaps in a very general way. in other words havethe control behave as if it were the contents of a cell...Make sense? I don't want to have to write code or ...

Pivot tables, linking to a named range as a source to a pivot table
I have created a main worksheet within the same workbook of many pivot tables, and I want to use this same worksheet as the source of information to these different pivot table sheets off of which I create charts. I want to use a named range because there are over 4,000 rows in this main worksheet. I am not sure if I need a "=" to start the reference or what to do. I thought I should just use nameofworksheet!database if I name the range "database", but when I point and click to the sheet, it is just inserting the name "database" without the name of the...

HOW to COUNT THE FREQUENCY of specific CHARACTER WITHIN a CELL?
How can a frequency of a specific character be counted with in a cell. Ex -" #4 Bluebirds, #6 Aquatic" is in cell B2 - how do I count the number of "#" that appear in cell B2? =LEN(B2)-LEN(SUBSTITUTE(B2,"#","")) HTH Jason Atlanta, GA >-----Original Message----- >How can a frequency of a specific character be counted with in a cell. Ex -" >#4 Bluebirds, #6 Aquatic" is in cell B2 - how do I count the number of "#" >that appear in cell B2? >. > ...

M05 update fails.
Uninstalled M04 and installed M05 over a week ago. M05 said an update was available. I let it run and it said "Update Complete". Now, EVERY time I shut down Money, I get "Money is attempting to install an update. To complete, restart Windows, and then Money". In the last week, I tried the following to no avail. I've re-run the update several times, deleteted the mnyupdate.log several times and restored the registry to several previouse dates. It still gives me this msg EVERY time I shut Money down. Any Ideas or should I just go back to M04???? Frank ...

ERROR: "The record you are requesting is currently unavailable"
CRM 3.0, using Web client, Windows Server 2003 SP1 for SBS. Have found no good solution for this problem I've been getting since installation couple days ago. When trying to create a new appointment, upon save get the error: "The record that you are requesting is currently unavailable. Either the record was not found or you do not have sufficient security permissions to view it." Upon clicking "Ok" I get the error: "General failure in scheduling engine" with a "Ignore and Save" button which will save the appointment, but still marks the appointm...

Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...

Insert Trigger to Update Value of Column in Inserted Row
I am trying to write a SQL Insert trigger which would populate the value of a column in the inserted row with the value of a column from another database table. The table into which the row is inserted does not have a primary key match with the other database table. The two tables can be related through a join to a third table. "CarlC" <CarlC@discussions.microsoft.com> wrote in message news:834D03E6-84CA-4CF3-9781-12FA18713940@microsoft.com... >I am trying to write a SQL Insert trigger which would populate the value of >a > column in the inserted ...

turn off "Do not check spelling or grammar" in outlook
In doing a "Spell Check" in Outlook, I get the following message: "Spelling and grammar check complete. Text marked with "Do not check spelling or grammar" was skipped." I never turned this on or indicated any text not to check, I'm working in a newly installed version of 2007. Thanks Bill Note: someone did answer this on Friday, but when I click on the link, I get a blank page, both at home and at the office? tinman wrote: > In doing a "Spell Check" in Outlook, I get the following message: "Spelling > and grammar check complete. Te...

COUNT /COUNTIF for Multiple Column Defined Dynamic Named Range
Hi, Would like to use Dynamic Named Range "RESULTS" in Formula. The Dynami Named Range "RESULTS" spans 30 COLUMNS and many ROWS. Need help with Formula to COUNT specified criteria in each separat COLUMN of the Defined Dynamic Named Range "RESULTS". Looking for on Formula that can be adapted with an OFFSET / INDEX to work on the othe COLUMNS. Example Results required from Formula: COLUMN 1 in Dynamic Named Range "RESULTS" - COUNT instances of numbe 75 COLUMN 2 in Dynamic Named Range "RESULTS" - COUNT instances of numbe 75 COLUMN 30 in Dy...

Tools.Customize.Commands.Modify Selection (doesn't)
I have a macro (I wrote) that is listed with the project name (and the project name appears on my button) and despite following (what I believe are) the instructions for renaming it the "Modify Selection" button in the Tools.Customize.Commands dialog box is never enabled and I can't rename it. It's Outlook 2000 if that matters. Thanks, Tom ...

MS Money won't update some accounts
This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C66FBD.344D48A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable OS: Windows XP Pro, all patches from MS up to date. ZoneAlarm firewall; = McAfee AV Sent the following message to MS support this afternoon: Purchased MS Money Premium 2006 off of the MS website about a year ago. = All was working well until about 4-6 weeks ago. Symptoms and = observations: --MS Money can not update some bank accounts (Pentagon Federal Credit = Union, ETrade) and=20 some credit cards ...

Searching Recipients' Names for Emails
How do I set Outlook 2002 so that the names in the "Select Names" box will be listed last name first when I'm trying to find names to address my emails? See your answer to the later question posted. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, bioflyer7@yahoo.com asked: | How do I set Outlook 2002 so that the names in | the "Select Names" ...

Should I create a custom report?
I would like to be able to run a report of best selling items per for select vendors. We are trying to reduce our inventory and carry only the best sellers. ...

Get rid of legacy 2003 custom toolbars in 2007
I have a Word 2003 template for which I created a custom toolbar with 5 buttons. The client migrated to 2007, so I did as well. Some of the buttons don't work. I want to either delete some buttons or the entire toolbar but I can't figure out how. Someone please help. I'm near bridge jumping. I am thinking that if you use File>Open to open the template and then save it in either dotx (or dotm if it has macros that you still need) format, the toolbars may be deleted from it. Alternatively, if you still have access to Word 2003, you could open the template in that ...

Editing the name box
I entered a name in the name box for the top left cell of a form but when I clicked on the name, the cell showed up at the bottom right of the screen. I was successful in creating a name for a different form (the starting cell shows up at the top left of the screen), but I can't get the first form's starting cell out of the bottom of the screen. Now I have four different names for the same form in the name box. How do I clear the name box of useless names? How do I get the starting cell at the top left of the screen? Insert/Name/Define, then find the name, then click Delete....

customers from the drop down ist will not transfer to invoice
My invoice has tabs for the invoice and customers, my invoice has the drop down list that i select and it places the company on the invoice to section. My new girl in the office hand typed the company in the invoice to section, and now , when i select a company from the drop down list, it will not transfer the company's billing info to the invoice, how do I correct this ? Thanks ...

The Microsoft Exchange Information Store service terminated with service-specific error 0 (0x0).
I'm currently running an exchange 2003 sp2 on a Windows 2003 SP1 Domain Controller with Mcafee virus scan 8.00 Its been running fine for about a year. Recently the information store terminates intermittently with the following error. I can restart the Microsoft Exchange Information Store service Event Type: Error Event Source: Service Control Manager Event Category: None Event ID: 7024 Date: 19/09/2006 Time: 21:54:43 User: N/A Computer: ICCUK-MAIL2 Description: The Microsoft Exchange Information Store service terminated with service-specific error 0 (0x0). I've installed Exchang...