f



To delete a used value from the combobox using SQL code

Hi All

Can anybody pls help me out with this problem.
I have a combo box control which has about 10,000 values in it. These
values in the combo box are called from a different table.
When i use a value(1 out of 10000) in the combo box for a particular
record say Record1, this value should no longer be displayed for the
next new record say Record2 and the combo box should just display only
9999 values. I am trying to do this, just because I do not want the
data entry person to use the same combo value for two different
records.
Is it possible to do this using SQL code?
Any help in this regard will be highly appreciated.

Thanks in advance.

Anita
0
tamilan71 (18)
10/7/2004 11:21:38 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

3 Replies
625 Views

Similar Articles

[PageSpeed] 57

On 7 Oct 2004 04:21:38 -0700, tamilan71@yahoo.com (anita) wrote:

10,000 is way too much for a good user experience. Find a way to
reduce that.
To answer your question: use a rowsource like:
select TheValue from TheSource where TheValue not in (select TheValue
from TheMainTable)

-Tom.


>Hi All
>
>Can anybody pls help me out with this problem.
>I have a combo box control which has about 10,000 values in it. These
>values in the combo box are called from a different table.
>When i use a value(1 out of 10000) in the combo box for a particular
>record say Record1, this value should no longer be displayed for the
>next new record say Record2 and the combo box should just display only
>9999 values. I am trying to do this, just because I do not want the
>data entry person to use the same combo value for two different
>records.
>Is it possible to do this using SQL code?
>Any help in this regard will be highly appreciated.
>
>Thanks in advance.
>
>Anita

0
10/7/2004 1:46:11 PM
anita wrote:

> Hi All
> 
> Can anybody pls help me out with this problem.
> I have a combo box control which has about 10,000 values in it.

WOW!  10,000 rows in a combo?  Needing to requery to combo each time you 
go to a new record?  And you only want to exclude the last record entry? 
  Interesting.

If you are using an autonumber, I guess you can get the last entry made 
when the form is first opened.  Then create would own SQL rowsource and 
use a line like
	Me.ComboRowSource = strSQL

Then store the comboid for each time you are in a new record.  Each time 
a new record is created, create the SQL string to exclude the ID from 
the variable.  Each time the record is saved...in the BeforeUpdate 
event, check to see if the record is new and if so, store the value of 
the combo to the variable.

10,000 rows?  Too much.

  These
> values in the combo box are called from a different table.
> When i use a value(1 out of 10000) in the combo box for a particular
> record say Record1, this value should no longer be displayed for the
> next new record say Record2 and the combo box should just display only
> 9999 values. I am trying to do this, just because I do not want the
> data entry person to use the same combo value for two different
> records.
> Is it possible to do this using SQL code?
> Any help in this regard will be highly appreciated.
> 
> Thanks in advance.
> 
> Anita
0
oil (4047)
10/7/2004 4:45:19 PM
This sounds like a case where using two "linked" listboxes may work - where
you select an item in the left listbox, and it moves the item to the right
listbox, eliminating it from the left listbox.

Roger Carlson created a good example of this.
http://www.rogersaccesslibrary.com/


Darryl Kerkeslager



"anita" <tamilan71@yahoo.com> wrote in message
news:6ea59d1d.0410070321.51b936f3@posting.google.com...
> Hi All
>
> Can anybody pls help me out with this problem.
> I have a combo box control which has about 10,000 values in it. These
> values in the combo box are called from a different table.
> When i use a value(1 out of 10000) in the combo box for a particular
> record say Record1, this value should no longer be displayed for the
> next new record say Record2 and the combo box should just display only
> 9999 values. I am trying to do this, just because I do not want the
> data entry person to use the same combo value for two different
> records.
> Is it possible to do this using SQL code?
> Any help in this regard will be highly appreciated.
>
> Thanks in advance.
>
> Anita


0
kerkeslager (601)
10/8/2004 12:25:57 AM
Reply:

Similar Artilces:

Using Excel To Update records in Access Database
Hello, I have an Access Database that I want to update using an Excel spreadsheet. When it is new reccords, I know how to do it. Nevertheless when I want to complete the information on a certain row of records which already exist, or if I would like to update it (ie: partially change certain records on a row), I do not know how to : identify the record, proceed to the update. Can anybody help me out on that one?? Thanks Arnaud It depends if the record is keyed or not and whether the keys are unique. If it is and they are, you do something like this: (not debugged) ----------------- dim dbsName as Database dim rstMajor As Recordset ' open the database Set dbsName = OpenDatabase("filename") ' open the recotdset Set rstMajor = dbsName.OpenRecordset("tablename") rstMajor.Index = "PrimaryKey" ' just to be sure at the start (it is on open) rstMajor.MoveFirst ' find the first record for a Friday rstMajor.seek "=","Friday" ' find it? if rstMajor.NoMatch then msgbox "No Friday records !' else msgbox "Found one ! endif rstMajor.close dbsName.close ------------------------- Otherwise you can use the rstMajor.FindFirst .FindNext etc. commands. If you just want to step through a dataset use: rstMajor.MoveFirst...

Tutorial; Accessing MS SQL Database using C++!
hey! Im looking for a db tutoral which covers accessing a Microsoft SQL database on windows. I will be using VC++ if it matters. NO details just a basic introduction! Oracle DB access using C++ is also of interest! Thanks! -- Majk majk <msa_pub@softhome.net> wrote in message news:<BB7F5CCB.6D61%msa_pub@softhome.net>... > hey! > > Im looking for a db tutoral which covers accessing a Microsoft SQL database > on windows. I will be using VC++ if it matters. NO details just a basic > introduction! > > Oracle DB access using C++ is also of interest! > >...

Problem while retrieving values from MS Access from jsp using some condition using date functions.
hi i want to get record from database(MS Access) which are entered in specific month. i've one html file with: <select name="MonSur"> <option value="Jan" selected>January <option value="Feb" >Feburary <option value="Mar">March <option value="Apr">April <option value="May">May <option value="Jun">June <option value="Jul">July <option value="Aug">August <option value=&...

Unable to display values in ComboBox using SQL query from Access table
I am trying to get 2 comboboxes working by using values from a single table each combobox using one field each. SQL Query which shows up fine in datasheet view but nothing shows up in Form Combobox for either one. <snip> SELECT DISTINCT Source.dr FROM Source ORDER BY Source.dr; </snip> Once I get it working I would like to restrict the second combobox using values picked from first one. This is similar kind of code I have found examples of listed below. Would thos work. <snip> ' ***** Code Start ***** Private Sub cboPersonID_AfterUpdate() Me.txtPersonName.Value ...

Ms Access frontend / Oracle backend problem
Hi all, I'm developing a database using Ms Access 97 as the frontend and Oracle 8.17 as the backend under win2k os. Using Oracle ODBC to link the tables in Ms Access. This database is supposed to be used by 10 users at anyone time. Everything else is working as I expected but one. The one problem is, everytime I update or insert records using SQL*Plus, the changes or the new records do not show when I open the linked table in Ms Access frontend. I had to restart my computer before the table in Ms Access showed the correct records/data. Has anyone else had the same problem? Anyone knows how to overcome this? Regards, Sid ...

Accessing MS ACCESS database using Data Environment
HI, I have developped a program in Visual Basic 6.0 using MS Access database. I have created all my reports through Data Environment where I have entered the location of my database file (for e.g c:\project\app\test.mdb) in the Connection properties. I have installed my application on another PC using Package & Deployment wizard. When viewing the reports on the other PC, I have to enter everytime the new location of the database. How can I solve this problem, please? nadia wrote: > HI, > I have developped a program in Visual Basic 6.0 using MS Access database. >...

How do I a smallint SQL database value using the Database tools?
I'm using the Database Toolset with Labview 7. I'm building up a parameterized Query to a Stored Procedure in my SQL Database. It doesn't appear that smallint output types are supported because zero is always loaded into the variable. Is this correct? I'm I doing something wrong? Feedback welcome... thanks! It's probibly not an issue with datatypes since LV has far more datatypes than most databases. What exactly are you trying to do? Are you getting the 0 when running the stored procedure? Could you perhaps post a bit of code (LV and SQL) that demonstrates the issue?...

Using Access to 'access' an sql database on a webserver
Is this possible? I know about a bit about ODBC and found how to import an ODBC database stored on the computer, however I am after accessing an SQL database stored on a webserver. I'd like to keep it up to date, but that could probably be done with a macro. At the moment I'm just considoring possible options, so just need to know if it can be done, how easy and a rough idea of how. Any help would be much appreciated! Flic On 6 Oct 2006 03:59:25 -0700, "Flic" <FelicityP@gmail.com> wrote: I typically create an ADP, then connect to such db specifying the IP address...

Rebuilding SQL code from MS Access SQL to SAS/SQL VS Salvaging the code to SAS/SQL.
Hello SAS'ites, I have a MS access application which generates a/some reports using some operations in SQL/DATABASE. I am being requested to recode the whole thing using SAS/SQL probably to extract some statistical info out of it (My guess). So my question is, should I rebuilt the whole code or should I cut/ paste and tweak the code into SAS/SQL to generate the same dataset? If anyone of you have worked on any operation like this shoot me your advice/ thoughts. Thanks in advance. Makwana I think there is no standard answer to the type of question. It depends on your local situation....

copying data from MS-SQL to MS-Access using VB.Net
Hi there, I'm trying to use MSAccess as a "container" to move data around from one MS-SQL server DB to another. This is basically already a design decision that has been made for a lot of reasons and can't be changed by me. What I was wondering is what is the bet way to populate the MS-Access DB. The DB will only contain 6 tables which will have a very small amount of data in them. I have tried to use ADO.Net to read the data from MS-SQL into a DataTable and then save it into the MS-Access DB using again using a DataTable populated from the first, but I can't seem to ge...

ComboBox using SQL code
Hi, I've been working on trying to use a combo box to filter my records for a while now, and can't get it to work. Right now, I have SQL code written into IfThen statements on the afterupdate for the combobox. I tried using the docmd.runSQL but from looking at previous posts, it seems that I need to use DoCmd.OpenQuery instead since I do not have an action SQL statement. The short term problem is that when the user selects from the combobox, Im getting a "type mis-match error." The longer term problem is that once the records are filtered, I need to use the procedure numbe...

Accessing linked tables in MS Access database using JDBC ODBC Bridge
Does anyone know if JDBC can access linked tables in a Microsoft Access? We have a Mictosoft Access database that contains linked tables that are physically on a network drive. On a windows machine, We set up a java servlet that uses JDBC to connect to a microsoft access database using a DSN name set up in the ODBC administrator. We are able to run a query using a table that is physically on the database but not on tables linked into the database. The linked tables are comng from another Microsoft Access database on a mapped network drive. The java servlet and the database contain...

Accessing a Microsoft SQL 2000 database using Labview Database Connectivity Kit
Hello people,,,... i am new to Labview Database Connectivity Kit. Currently, I Have created a Database using Microsoft SQL 2000, but i am not sure how to let my labview program read and write to this database i have created.... So far, I have only suceeded in using the Database Toolkit to read/write to a Microsoft Access DataBase, but not a Microsoft SQL 2000 Database. thanks a lot... terence There's an online PowerPoint presentation that can help you get started using the SQL commands for the Database Toolkit in LabVIEW, which you can access <a href="http://zone.ni.com/devz...

java.sql.Date value truncated using JDBC in MS-SQL Server
Hi, I'm trying to write a java.sql.Date to a database and the minutes/ seconds etc. are not being written. I've seen and tested many examples found via searches and still have come up with nothing. Here's what I have: java.sql.Date formation - recommended constructor forms: java.sql.Date entryDttm = new java.sql.Date(System.currentTimeMillis()); OR java.sql.Date entryDttm = new java.sql.Date(new java.util.Date().getTime()); // prepared statement insert pstmt.setDate(1, entryDttm); // what is written to database 2007-02-07 12:00:00.000 // what needs to be w...

Accessing linked tables in MS Access database using JDBC ODBC Bridge #2
Does anyone know if JDBC can access linked tables in a Microsoft Access? We have a Mictosoft Access database that contains linked tables that are physically on a network drive. On a windows machine, We set up a java servlet that uses JDBC to connect to a microsoft access database using a DSN name set up in the ODBC administrator. We are able to run a query using a table that is physically on the database but not on tables linked into the database. The linked tables are comng from another Microsoft Access database on a mapped network drive. The java servlet and the database contain...

Retrieve (Get) SQL Username from SQL Server using VBA code and Access 2003
Hi all I am trying to retrieve the SQL username for the currently logged on user from my backend SQL Database so i can populate a form. I have the following 2 procedures. However obviously its always returning the username SQL as per the connection string. Is there a way of doing this for the CURRENTLY LOGGED IN USER?? if i remove credentials from the connection string the procedure fails. Also if i use 'CurrentProject.connection' this also fails as it refers to a JET connection Public Function DB_Connection() As Variant 'DB_Connection = CurrentProject.Connection Dim s...

Compacting an Access database using SQL
I've been using the Matlab Database toolbox to build and update several PC based Microsoft Access .mdb files using the ODBC bridge. When I perform a major update, I'd like to issue a "Compacty Table my_table" SQL comand that is equivalent to the "Compact & Repair" tool that is in Access. Thus avoiding the manual intervention. I have ACCESS 2003 and Matlab 7.04. The "Compact function" seems to run using conn = database('db6','my_userid','') cursor= exec(conn, 'Compact Table Table1') close(conn) its just not compacting ...

Using using
I want to plot column 2 against col. 1, but subtract a constant from col. 2. I tried, in a test script, plot 'test.dat' using 1:($2 + 1) w p pt 7 but gnuplot complains Warning: empty y range [1:1], adjusting to [0.99:1.01] although the data file contains 1 1 2 2 3 3 4 4 5 5 What am I doing wrong? I read this in the manual: plot 'file' using 1:($2+$3) "%1f,%1f,%1f" which led me to use the above. I assume the format stuff is not needed. If I am doing this wrong, how do I modify a given column to be plotted arithmetically? -- Dieter...

Using USER value in SQL code
I need to be able to drop a table after a user is done with it. I have tried something like, DROP TABLE USER.tblEducation_SAP1 but I get an error. Can someone suggest the way I should be using the USER value in this instance? Thanks! (billharrison9@gmail.com) writes: > I need to be able to drop a table after a user is done with it. I have > tried something like, > > DROP TABLE USER.tblEducation_SAP1 > > but I get an error. And the error message is? Of course, if that is the exact command line you would get a syntax error since USER is a reserved word in T-SQL. But I...

Access the database using VBA code
Private Sub tbDate_AfterUpdate() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset rst.CursorLocation = adUseClient Set cnn = CurrentProject.Connection rst.Open "SELECT * FROM Table1 ORDER BY StudentID ASC", cnn, adOpenStatic, adLockReadOnly, adCmdText rst.Close cnn.Close End Sub I have been trying to sort the database through the VBA code. The above SQL statement works very well when I do it in the SQL view or Design view of Query. Somehow I am not able to access the database through VBA code; I don't understand where I am g...

when to use ; and when to use ,
Any statements like printf("abc"); scanf("%d",&x); a=20; can be replaced by printf("abc"), scanf("%d",&x), a=20; So when should we use ; and when sould we use , In article <1181971596.941107.318810@o11g2000prd.googlegroups.com>, Ravi <ra.ravi.rav@gmail.com> wrote: >Any statements like > printf("abc"); > scanf("%d",&x); > a=20; >can be replaced by > printf("abc"), scanf("%d",&x), a=20; > >So when should we use ; and when sould we use , Use ; when you want ...

when to use == and when to use is
I know this question has been answered: http://stackoverflow.com/questions/6570371/when-to-use-and-when-to-use-is , but I still have doubts. Consider the following code: class A: def __init__(self, a): self._a = a #def __eq__(self, other): # return self._a != other._a obj_0 = A(0) obj_1 = A(1) obj_2 = A(2) obj = obj_1 if obj == obj_0: print(0) elif obj == obj_1: print(1) elif obj == obj_2: print(2) if obj is obj_0: print(0) elif obj is obj_1: print(1) elif obj is obj_2: print(2) Both if statements wo...

Using Crystal Reports in MS-Access & Use of timer event
Farid Khan wrote in an e-mail message to me: > dear sir would you tell me how to use the crystal reports > in ms access and the use of timer. > thank you. Alan Webb ...

Playing & recording WAVE-type sound in MS Access
I've followed the instructions found on Microsoft's site... http://support.microsoft.com/default.aspx?scid=kb;en-us;149119&Product=acc97 Here are those instructions... To play and record sounds, follow these steps: Create the following new table in Design view: Table: Messages ----------------------- Field Name: Message Data Type: OLE Object Save the table as Messages, and then close the table. Create a new form in Design view based on the Messages table. Add the following controls to the form: Bound Object Frame: Name: Message ...

Web resources about - To delete a used value from the combobox using SQL code - comp.databases.ms-access


Stack Overflow
Q&A for professional and enthusiast programmers

dBase - Wikipedia, the free encyclopedia
dBase II was the first widely used database management system (DBMS) for microcomputers . It is a relational database management system. It was ...

热门搜索 - 我的异常网
我的异常网之热门搜索专题提供权威的各类程序问题解决方案,欢迎您的到来。

Watermarked edit controls
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using ...

Flash Builder Plug-in for SAP Netweaver Gateway - Adobe Labs
Search Navigation Labs Home Wiki Home Technologies Alchemy Blueprint Camera Profiles and DNG Profile Editor Cirrus ColdFusion 10 DNG Codec Fireworks ...

AutoIt Forums
The AutoIt community forums.

3. Automation
Albert Gareev Education Experience Blog Archives Content policy Copyright Testing Stories Articles Weekend Testing Automation Chapters Problems ...

Does Windows 8 programming lack creativity?
... is a feature called OwnerDraw. What this means is that a number of UI elements (controls) in the operating system, such as a Listbox or Combobox ...

Restore Wallet Instuctions
Restore Wallet Instructions Introduction A wallet.aes.json downloaded from blockchain.info or emailed to on sign up contains everything needed ...

Resources last updated: 2/28/2016 12:10:25 PM