Check text field value against underlying table or query of combo box

I have a form on which the user can enter new records or update
existing ones.
If the user enters a new record on the form, I would like to have an
'After Update' event on one of the text fields, which is the item# to
check if the value entered already exists in the underlying table,
tbl_item. How can I call the table from the 'After Update' event of
the text field and check for that value in the table?

Thanks in advance.

A. Crawford

3/16/2007 4:49:24 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 17 wrote:
>I have a form on which the user can enter new records or update
>existing ones.
>If the user enters a new record on the form, I would like to have an
>'After Update' event on one of the text fields, which is the item# to
>check if the value entered already exists in the underlying table,
>tbl_item. How can I call the table from the 'After Update' event of
>the text field and check for that value in the table?

If Me.NewRecord Then
	If DCount("*", "table", "[item#]=" & Me.textbox)= 0 Then
		'  item is not in table
		. . .
		' item already in table
		. . .
	End If
End If

MVP [MS Access]
3/16/2007 8:52:24 PM

Similar Artilces:

Stoping users from saving tables to their local machines...
My question relates to server residing tables being saved locally. While a write protected table cannot be altered on the server by a user (Without write rights) I am wondering if there is a way (Perhaps through Excel macros?) to stop a user from being able to save a given table to his local machine. Thanking all in adv. for your time, and I look forward to your replie(s). hi. your info is a little sketchy. because you posted in excel, i'm assuming that your tables are in an excel file. i don't know how may users you have but you might try something like this..... Private Sub W...

Copy setup tables and changing functional currency
I have a Company database named PRIMARY where the Default currency is USD. The Company has 8 other currencies. I have a brand new Company database named SECONDARY and I want the Default currency to be VEB and not USD. I have read tech articles 872709 and 874208 on using DTS to get the setup data over to the new Company database. This is a 7.5 version of GP. I seem to have an issue with database triggers on the SECONDARY Company database after the DTS. Has anybody been through the process of creating a new Company database along with DTS of the setup tables and changed the functional cu...

Combobox display 1 value, access another
Hi All, I have a combobox that gets filled with the following code: 'load combobox with list of grantee institutions With ComboBox1 For i = 5 To LastRow .AddItem Worksheets("data").Cells(i, 2) Next i End With End Sub The drop down menu contains a list of schools, but the data element I really need access to is the ID number, which is in column A. Is there a way to access this value? Maybe using the offset function? I can't seem to figure it out. Thanks in advance. ComboBox lists can have multiple columns and you can arrang...

Query Too Complex...Sometimes
Hello, This seems to be one of those issues I feel like I am just missing. I have a report that runs off of a query. When the report is called directly from the report objects list, runs just fine. However, when the report is called using a button in a form, I get the infamous "This expression is typed incorrectly, or it is too complex to be evaluated..." Any quick thoughts before I go through a long troubleshooting process? Thanks! B -- Message posted via Why didn't you post the code so that we can help troubleshoot it? On Aug 7, 4:12 ...

Nested or Sub query?
Im a bit lost as to how to go about this, I have 3 queries that i built in access query builder which join 4 tables together in order to generate a report What I would like to do is turn those 3 queries into a single sql statement that can be run from vba. or run all three in vba and get the correct output. so here are my queries Query A SELECT Transdata.Barcode, Transdata.Out, Transdata.[In], Transactions.Usercode, Transactions.location FROM Transactions LEFT JOIN Transdata ON Transactions.Transcode = Transdata.Transcode WHERE (((Transdata.[In]) Is Null)); Query B ...

Pasting Table Info from Word to Excel
Using Office 2003. I have created a table in Word where the first column is address information on 3 lines in one cell (Name, Address, City State Zip) Before going further with this Word table, I decided I really wanted to track the information in Excel. Having trouble pasting the 3 lines of information from the Word table cell into one cell in Excel. It keeps pasting into three cells (i.e. A1 gets the name, A2 gets the address, A3 gets the city state zip). I'd like these three lines of information to paste into one three-line cell in the Excel table. I've tried changing th...

Form Subform Combo box
I want to design a form and on the form I will have a list box with 5 selections, selections 1-5. Each one these selections corresponds to a table of the same name. On my main form if I select one of these selections from my list box, I want my subform to display the corresponding table. Can someone get me started on this? Thanks, Sean First, realize there is a difference between a subform control on a form and the form being used as the subform. The subform control has a property named Source Object. It contains the name of the subform to load. You will need to create a form f...

Convert $ to actual numbers in an imported web query
I use the Web Query function to import tables with data from the internet to my spreadsheet. However, some of the amounts imported are in $ format (e.g. "$7,950.00") and do not function as numbers, i.e. I cannot perform mathematical calculations on them. How do I import $$ values as actual numbers? Thanks for your help. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want ...

How do I get the "Add to Dictionary" box to activate in spellcheck
As I run Spellcheck I want to save unusual names to my 'dictionary' so I don't have to keep ignoring them. However, that box will not activate. Version of outlook and office? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? Outlook 2007: Outlook Tips by email: Outlook Tips: Outlook & Exchange Solutions Center: Subscribe to Exchang...

changing text to numeric
i have two table. one is PO table and one is DI (delivery instruction) table. i have make a form and using DI table as the control source. then i make a combo box to look up the PO no. that combo box i'm using the PO table. cos i want to look into the PO table to see which PO i want to call in the material from the supplier. after select the PO no from the PO table i will keep the data in the DI table. PO no from the PO table was change from number field to text field when in the DI table. can i make the PO no as number field in DI table? how? cos i need using the PO no which is in ...

RE: Check out the important patch from the MS Corporation
--flzixrrheaea Content-Type: multipart/related; boundary="pslicvtz"; type="multipart/alternative" --pslicvtz Content-Type: multipart/alternative; boundary="klihivdso" --klihivdso Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect your ...

Combining two tables and separating duplicates
Hello, I have two different mailing lists, one from company A and one from company B. To prevent company A & B from sending duplicate invitations, I would like to combine the two lists in Access using e- mail addresses as a unique identifier. How do I query to find and separate the duplicates into a separate table. The desired end result would be one final mailing list (the duplicates identified and Company A's mailing information kept) and one list with all the duplicate contacts? Thanks in advance! ...

Replacing cell with text
Hi, I want to know if it is possible to do the following: Imagine I have a legend in which I say which text corresponds to whic letters ex: John -> J Harry -> H Susan -> S and what I want to do is when entering J, H or S in a cell, it wil change to the corresponding text. Is there a way of doing it without having to run a macro ? Thanks in advance Pedr ----------------------------------------------- ~~ Message posted from ~~View and post usenet messages directly from Go to Tools > AutoCorrect Options (XL2002) and insert new...

How do I make numbers become text?
I am trying to create a spreadsheet where numbers entered in one location become text in another. I tried the Help option, but I am still lost. Please help, and thank you. From earlier postings: How to Convert a Numeric Value into English Words;EN- US;140704& and: (courtesy of a cut and paste from a Tom Ogilvy post): If you want an addin that provides a worksheet function that does this, download Laurent Longre's free morefunc.xll addin found here: It is downloaded in a zip file which also conta...

empty from field
hi after last fresh install of Office I always have to manually select my name from the Contact list to use in the From field or else it's empty and the mail server wont send my email. I cannot find anywhere how to tell Outlook who's the default sender = me !?! thx, David Enter that information in the Properties of your mail account. -- Russ Valentine [MVP-Outlook] "Dav�� ��risson" <> wrote in message news:%23XJXAhHzDHA.2448@TK2MSFTNGP12.phx.gbl... > hi > after last fresh install of Office I always have to manually select my name > from the Con...

Extract some fields from a Row to a column
Hi! Apreciate your help in advance. This is my problem I have a Database w/ four columns but in my firs column starts row A, Co. Name, RowB Address, Row C Phone #. I Columns # 3 and 4 have their City Name ,State Zip Code respectivibly I'll like to put this rows in columns. Thanks VicV PS From Sunny South Florid -- Vic ----------------------------------------------------------------------- VicV's Profile: View this thread: Hi not really sure how your ...

Query for specific characters in a spreadsheet
I used Excel to track student attendance for a class this summer and used one character to indicate attendance, another absence, a third for cancellation, etc... Is there a way to query how many of each of these characters appear in the spreadsheet? =COUNTIF(range,"A") Where range would be a column or row that would contain the character.A for absence. You will need a formula for each character. Gord Dibben MS Excel MVP On Wed, 16 Dec 2009 14:10:01 -0800, Vicki B <> wrote: >I used Excel to track student attendanc...

query to delete negative numbers in HQ
Hello all, I have some negative items trapped in my HQ that are not in any of my stores inventories. I believe this happened when the supplier was changed. Is there a simple query to drop these negative numbers out of HQ?. This is a multi-part message in MIME format. ------=_NextPart_000_008C_01CA5950.12193CA0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Patrick, How about running a 501 worksheet and then a 190 task. --=20 Jeff=20 Check Point Software Search this newsgroup -- (Stolen from DRS) =3D=3D=3...

Combo box for column
How do I make a combox box throughout an entire column? For example, if each row is a new record, I want a combox box each time I get to column F. Have you thought about using Data|Validation with a list on a different worksheet? Select your range of cells in column F and then Data|Validation. Debra Dalgleish has some very nice notes if you've never used this feature before: Kmarie wrote: > > How do I make a combox box throughout an entire column? For example, if each > row is a new record, I want a combox box each time I get to c...

Text Box 05-21-10
I have the following event procedure on a text box: Private Sub txtNotesOnTransaction_BeforeUpdate(Cancel As Integer) Debug.Print "User: " & CurrentUser() & " " & Me!txtNotesOnTransaction End Sub It is suppose to stamp the user name in the text box but it does not work. Any suggestions? Thanks PJ - The debug.print will just display (in the immediate window of the code pane) the name and the Notes. If you want the notes saved with the name, do this: Me!txtNotesOnTransaction = "User: " & CurrentUser() & " " &a...

Pasting Values #2
Hi, I have developed a quoting tool which creates an output sheet detailing all the info, What I would like to be able to do is take a copy of this and paste the values (and Format to a new workbook). I'm currently doing this manually and I unsure how to automate it! I have never used VB which I'm guessing is the only way of doing it, so please be gentle!! Thanks, Sam. -- sammy2x ------------------------------------------------------------------------ sammy2x's Profile: View this thread: http://www.excelf...

Picklist Values start at 200,000?
Has anyone else come accross an issue where, after upgrading to CRM 4, any new picklist values you enter begin with the number 200,000? I have a Lead Source picklist that left off at number 12 in CRM 3.0. When I went into the attribute to add another item (which should have been number 13), it began with number 200,000.??? Thanks. AMCK This is normal behavior in CRM 4 - the custom values start there in a model similar to NAV, AX, etc. That way there is no conflict of new SYSTEM attributes (until they get to 199,000 new attributes that is :) ) on upgrades, sps, etc. If you want a clea...

Size of the drowpdow list of a combo box
Below is my attempt at creating a combo box. The item is correctly displayed, but the dropdown list has a size of zero. How can I change this so I can see the options? Thanks for your help. Miguel HWND hWnd = GetSafeHwnd(); CWnd *dialog = CWnd::FromHandle(hWnd); RECT ctlRect; ctlRect.left = 130; = 50; ctlRect.right = 220; ctlRect.bottom = 80; outputVolumeCB = new CComboBox(); ret = outputVolumeCB->Create(WS_VSCROLL|CBS_DROPDOWN|CBS_DISABLENOSCROLL|CBS_HASST RINGS,ctlRect,dialog,102); outputVolumeCB->ShowWindow(SW_SHOW); outputVolumeCB->AddString(&qu...

changing text case
Is there a way to change the text case in Excel? Ex. from all caps to Sentence case, etc. See: ************ Hope it helps! Anne Troy "Linda B" <> wrote in message > Is there a way to change the text case in Excel? > > Ex. from all caps to Sentence case, etc. Excel supports conversion to upper, lower or proper case, but it has no sentence case. I created this ...

Running a Select Case procedure off a ComboBox Value
I am trying to Run a Select Case procedure off a combo box value. When one value is chosen I want to open a query. When a different value is chosen, I want to open another query. My Case Else is a message box that appears. What happens is that when this procedure is executed, the message box appears, regardless of what has been selected in the combo box... any ideas? Thanks -- Message posted via Barkley via wrote: > I am trying to Run a Select Case procedure off a combo box valu...