I have a combo box that is pulling data out of table that I created with possible selections for the users of the database. I also have another table where the data is saved after the user has completed the form. My question is...I cannot restrict the users to the options I came up with in the table, so how do I set up the combo box so that users can either select from the existing list, or add an item and have it saved to the list for subsequent users?
I created an append query that does this, but I don't know how to make the query run automatically, every time a new item is added. I've seen some postings on the AddItem property, but I haven't seen how the code works.
Any help would be greatly appreciated!
4 3403
There is a property for the combo box called "Limit to List". Set it to "No".
comteck
PEB 1,418
Recognized Expert Top Contributor
Hi,
If you want to Save every introduced value in the combo box in the after update property you have to assign a Macro that runs your Query!
For your Macro You can use RunsSQL Macro and copy your SQL from your query to your Macro!
So in this case you don't have to write a code and you will use only Macros and SQL :)
Well the SQL did what I wanted it to do, but the Append query is not the way to go. First of all, it adds duplicate entries to my table. I know there's a way to prevent this, but the major issue, is that I want the new option to appear in the combo box as well as in the table when it is saved, and the append query only saves the options to my table.
Is the AddItem the way to go? and if so how does the code work?
PEB 1,418
Recognized Expert Top Contributor
To escape from the repetitive add of items you have to change your Append query. I'll give an exemple how to do it!
1. You have to create a table for exemple Connection
2. In this table yu insert only one record! The information for this record doesn't matter! The columns of this table also doesn't matter!
3. Imagine that you save the New Values in a table COMMUNES in the filed COMMUNE and the new value is "ARBIGNY"
So your SQL should seems like:
INSERT INTO COMMUNES ( COMMUNE )
SELECT "ARBIGNY" AS Expr1
FROM [Connection]
WHERE ((("ARBIGNY") Not In (SELECT COMMUNE FROM COMMUNES)));
In this SQL you have to change "ARBIGNY" with your Form Control and change the table Names and fields
So you shouldn't have a dublicates in your table!
When you want that the new value appears in the combo box you should refresh or requery the combo box or the form!
So to do this you have to replace the macro in after update with event procedure
your procedure is:
DoCmd.RunMacro "Your macro name"
Me!ComboBox.Req uery
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Paul Edwards |
last post by:
I am writing in c# with Visual Studio 2003. I am creating
a form that shows one record from a Dataset that is
populated with only one record from SQL Server 2000. I am
using bound controls, both text boxes and Combo Boxes
(populated successfuly from same data source).
When I use the fill command for the SQLDataAdaptor I can
get the data by querying the dataset but the controls on
the form do not show the data. If I set a combo box to...
|
by: Robin S. |
last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope
I've done a better job of explaining myself below.
I am trying to produce a form to add products to a table (new products).
Tables:
tblCategoryDetails
CategoryID
SpecID
|
by: edworboys |
last post by:
I have a form (Prospect) with a subform (Document).
The result I am looking for is this:
The user selects a prospect which is made up of Prospect Name, Country,
Company and Prospect Type. They can then use the subform to add records
into the database with the above information already there.
At the moment I have four combo boxes in the Prospect part of the form.
|
by: MS |
last post by:
What's the best way to "store" and display a value in a text box that
changes from day to day. An example of this would be where the name of the
user is manually typed in after using the datbase, along with (say) the
date.
ATM, I have a table with (say) 4 fields. Each field is linked to a column in
a combo box. The combo box is invisible on the form. The text boxes display
previous data using code something like this...
Private Sub...
|
by: Shawn Yates |
last post by:
It has been a while since I have done anything on MS Access and I seem
to be a bit rusty. I hope someone could help me solve my issue. I have
a form that has multiple combo boxes on it. Each box has 4 possible
answers to a question. Each of the 4 possible answers is also assigned
a value: answer 1 is worth 1 point, answer 2 is worth 2 points....
What I need to do now is add togethor the points of all the combo
boxes. So far I have given...
| |
by: consonanza |
last post by:
I am working on a report filter form. It has 2 combo boxes
(cmboSelectSubject and cmboSelectCategory) to select criteria.
Selecting an entry in combo 1
restricts the options available in combo 2.
The row source for combo2 is:
SELECT DISTINCT tblComplaintCategory.fldComplaintCategory,
tblComplaintCategory.fldComplaintCategoryID,
tblComplaintSubjects.fldComplaintSubjectID
|
by: ljungers |
last post by:
Hi to all and hope someone may have an answer for me.
I have a Form named Cust_lukup_Form that has 3 text boxes and a click button that uses a OnClick to call Cust_lukup_Macro that runs an OpenQuery named Cust_lukup_Query using the text box(s) input data running in print view mode. The Cust_lukup_Query has the code setup to query the 3 fields using 1, 2 or 3 of the boxes or selecting all if there blank.
What I need to do is add another...
|
by: chixor1 |
last post by:
Good afternoon All I have got over some hurdles on my current project and now I am stuck again.
Im now having issues with writing the code that takes the calculations from my txt boxes and inserts them as a new row in my access database.
I used the Visual Studio 2005 Wizard to create my DB connections, so im struggling to work it all out.
Here is the code for my form:
|
by: =?Utf-8?B?Y2RtdW5veg==?= |
last post by:
I have created a sub routine to programmatically add an indefinite number of
combo boxes to a form. The combo boxes are bound in code to a view for
displaying the available options. This routine works good until you add the
second combo box. At that point it changes the first combo box's selected
item. A third changes the second and first and so on. I've tried setting
the combo box name and tag to a unique name, but that doesn't help. ...
|
by: jgoodnight |
last post by:
Hi,
I have a form with three combo boxes: Area, Sub-Area, and Factor. I've set up the Sub-Area combo box to list only those areas that are part of the selected area. I've set up the Factor combo box to list only those factors that are part of the selected sub-area.
For example, if I select area 1.0, the Sub-Area combo box displays 1.1, 1.2, and 1.3 as options. It does not show 2.1, 3.1, etc.
If I select area 2.0, the Sub-Area combo box...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |