I've been asked to take a look at a database to look for areas which could be improved. It's not a database I've built myself so I've started by taking a 'walk-through' of the system to see how it works.
What I did notice amongst other things which confused me a little was that, on a specific form, called 'Tenders Sub Form' (tenders stands for builders), there are two combo boxes, each of which is used to enter the same kind of data back to the 'Tenders' table.
I've uploaded a screenshot for you to see. As we work down the 'Tenders Sub Form', we're asked to enter the 'CustomerID', which is simply the ID for the Customer and you'll see from the Relationships I've also uploaded, that this is the Primary Key in the 'CustomersMain' table. This is also an AutoNumber data type. Further down just under 'QuoteID', there is another combo box, which asks for the 'CustomerName'. This is using all the same data as the first combo box, only the first combo box is bound to column 1, the CustomerID and the second combo box is bound to column 2, the Customer Name.
If we then look in the 'Tenders' table - we can see that it's storing the Customer ID and CustomerName is there own fields.
My question really is, would there be a more efficient way to store both the CustomerID and CustomerName in the Tenders table, without the need to use two combo boxes and effectively enter the same data twice.
I'm sure that this was set up with the purpose of being able to see the Customer Name in the table rather than just the Customer ID.
I've uploaded
Relationships
Tenders Sub Form - to see how the combo boxes look
Tenders Table Properties
My database is using data that is entered by the employees to generate Quotations. There is one important piece of information that will not be entered by the employee. The quotes involve metals which are priced based on market price and weight. I am planning on integrating a data feed with this information, but for now I want to enter it manually in a table. The price depends on two combo boxes one for "Precious or Base Metals", and one for "Metal Name". I want those two values to call the price from a table, and automatically fill in the "Market Price" field in the form.Also once that is in I would like to do my calculations. I am planning on using queries to do these. Is that the correct method?
I have two combo boxes containing integers on a form. How can I store the values of these combo boxes in a field of a table separating them by comma or semi colon?
Currently, I have a form with a combobox that lists all of the names of the tables inside my database. Depending on the table selected in the first combobox, I would like to have another combobox which allows the user to choose from the field names inside that table.
I want to add to every row a combobox with a list of towns depending on the value in the field Country ID.how do I filter the comboboxes individually based on a value in another field on the same row?
I currently have 3 tables within a database with student details of three different classes. I need to create a user form that has a dropdown box which I can select a student from one of these tables with a number of text boxes below which brings up all the students details, then once the student has been selected and the correct details are shown then I need to create a button which allows me to move that student from one table to another.
there is a way to convert multiple text boxes to combo boxes all at once, rather than right clicking on them one at a time, and selecting Change to.
I have a form with about 50 fields and most of them need to be converted to combo boxes. I'd always done it manually one at a time up to this point, but I'm trying to build up my learning and look for smarter ways to do things.
I am trying to make a search option in my form header. Right now I have two unbound combo boxes (CboAccountsfilter and cboCourseName) that I can use to filter my records. Currently, I can use the drop down for CboAccountsfilter and a list of accounts will appear. When I select one, the corresponding Course Names will appear in cboCourseName. This works fine...Code below. I would like to take the filtering a step farther and add checkboxes to filter the data. I my form, there currently exist several check boxes (yes/no)...(Priority, Rep Top Target, Manager Top Target, ect). I would like to have the option to use a check box to filter. I.E if I had a checkbox in my header called PriorityFilter, if checked it would only bring up those records that met the two combo boxes criteria and was a priority.
Below is the code I have so far...it doesnt have anything for the checkbox because I am at a lost of how to get started.
Private Sub CboAccountsfilter_Change() Me.Requery Me.cboCourseName.Requery Me.Check178.Requery End Sub
I have several comboboxes (6) on my form.How to populate these comboboxes with values depending on selected value in previous combobox.
Example.Lets say that you select value "Audi" in combobox 1, then available values in combobox 2 should be "A4","A6","TT" etc. and if you selected "BMW" in combobox 1, then available values in combobox 2 should be "3-series", "5-series" etc...
I am a basic/intermediate user of Access. I have been given a small assigment to update a field (the primary id field to be exact) in one of my tables based on the choice selected on my combo box on my form.
Tables: Survey_Answer (N/A, yes, no, Dev) with sur_ans_id being the primary id. (this is the table that I am using to populate or pull information in my combo box. sur_answer: The NA, yes, no and dev.
tblSurvey: has foreign key sur_ans_id.
Therefore, when the user selects yes, no etc. from the combo box , it is supposed to pull the id (1 or 2 or 3 or 4) based on the choice and update table Survey's sur_ans_id.
Currently when the choice is made, it duplicates values (even though i have for my row source for the combo box: SELECT DISTINCT sur_answer FROM Survey_Answer in the Survey_Answer Table.
My Main code to update I have put in On change under the combo box property is:
Public Sub DoSQL()
Set dbs = CurrentDb Dim strSQL As String
strSQL = "UPDATE Survey " & _ "SET Survey.sur_ans_id = (SELECT Survey_Answer.sur_ans_id FROM Survey_Answer WHERE cbosur_answer.Column(0) = Survey_Answer.sur_answer)" & _ "WHERE Survey_Answer.sur_ans_id = Survey.sur_ans_id AND Survey.que_id = Question.que_id" DoCmd.RunSQL strSQL End Sub
However, this is not working: I know it has something to do with my BOUND COLUMN count property but that is mostly where I am stuck. I cannot update Survey.sur_ans_id properly. I understand the concept of BOUND COLUMN but i don't know how to apply it to my SQL statement.
I am sorry for the long narration but any help or guidance will be greatly appreciated.
I have a sub form that I enter date range in 2 text box's txtstartdate and txtenddate this is filtering field "date raised" and I have combo box call cboclient this is filtering field "client name" this code is behind a command button after hitting the command button it open's the report and it show's all records with in the date range and with client name enter in cboclient works great. problem is I have now added another cboclient2 to filter client name twice so now I enter a date range in txtstartdate and txtenddate put a client name in cboclient and a client name in cbocleint2 hit command button it open's the report but doesn't apply the date range to cboclient2 it just show's all records with that client name
Code: Private Sub cmdPreview_Click() 'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long
I have 2 unbound combo box's on a form 1 called cboclient1 and the another called cboclient2. would like to be able to filter field name client name twice by cboclient1 and cboclient2 here my sql from my query
Code: WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null));
This works perfectly for cboclient1 problem comes when I try and add cboclient2
I have tried
Code: WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null))AND(((Assets.Client)=Forms![report gen]!cboclient2)) Or (((Forms![report gen]!Cboclient2) Is Null));[/
I have a couple forms with Combo boxes that look up data in queries. I noticed in testing that I could accidently type a "~" (and other characters) in the field and once I do, it causes a runtime error that shuts down the entire app (as opposed to letting the user backspace out of it or resetting the field). Before I put in some "Before Update" code to prevent the system from crashing, I want to make sure I am on the right track. I have already set the combo box to just show the list, not allow edits, limit to list, etc. Is there a way to prevent the user from doing this either by preventing the keyboard from working on combo boxes (not preferred) or by trapping it before the system crashes? I would think I could validate with a recordset, and create my own path out for the user, but I was hoping the combo box would have sort of done that work for me.
How do i use a combo box to list all the records from a particular field so that when you select a item from the list it then populates other text boxes on the form relating to that particular record?
For example, if i have a customer table and i have 100 records. I want to put all the names in a combo box and when you select one it displays all the other information in text boxes (such as address, tel No, email address...etc).
In the red circled tables, I'd like to use the PK from tblJewelryType, tblCollection, tblDesignName to look up the PK from tblJewelryInventory from three combo boxes on the form.
Each combination of those three PK's may have multiple "subtypes" in tblInventoryLink.
I'm trying to build a form that will be set up so after I select those three combo boxes, the subform for tblInventoryLink is narrowed down to only the specific subtypes that are available.
Do I have to make these cascading for me to accomplish my goal?
So I've got a form set up, and it uses a combo box to find the name of a persons record to populate the form. Simple enough stuff, the wizard takes you through it. It works fine.
However the people that use the database have kinda thrown me a curveball by asking if the search function can search any part of the name. For example, you've got a John Smith. If you enter Smith into the combo box, it won't find the record because it's the second name, you have to type in John.
Is there a way to use wildcards in the combobox so you can type in first or last names and get the same normal combo box effect?
howdy all, ive never touched Access until 2 days ago so my experience is sorely lacking but here is my question:
i want to create a form with a combo box from which a selection is made (data in the combo box is simply a field list from the same table the query is searching, but my stumbling block is that i want to include the query in the same form as the combo box and have it dynamically updates based on the selection in the combo box.
however for the life of me i cant get the query to update based on the input (using [Forms]![Form]![Combo1] as the criteria in the query) i have set this criteria in the CustomerID field of the query (which is also the primary key of the table)
I know I can sort my combo box in ascending or descending order. I have a list of items that I want to order by product code but in a specific way. The order I would like is product code 1 followed by product code 9 followed the rest in ascending order. Without having to change tables or even the product code(!) is there a neat way to do this?
Im working in MS Access 2007.I have 3 combo boxes on a form. My goal is simple I would like the 2nd and 3rd comboboxes to be locked unless the user has already chosen selected an item from the 1rst combo box. The code im trying to get working now is in a On_Current event so that when the first combo box has nothing selected, combo box 2 and 3 are locked.
This is my code where combo box 1 = areabox2 combo box 2 = devbox2 combo box 3 = entitybox2
[code]...
The issue is that the entitybox2 and devbox2 do not lock!
Hi All, Totally new to access, been asked to make a database for a historical society. To make life easy, in descibing the hist. society's numerous items, I thought it best to follow the standard museum catalogue naming system, i.e everything has a primary and secondary classification. So what I have done is create two combo boxes, with the choices in the 'secondary' classification field limited by the choice in the 'primary' combo box. These controls are based on two tables that contain only the names/info on primary and secondary classification. It works nicely. However, because these controls are on a data entry form, when you make your choices in these combo boxes, the data is not recorded on the appropriate data table (presumably as the controls are linked to those other tables). Everything else you type in the form (like the item's location or autonumber) is saved in correct table. Other problem is that every time you go to add a new record, and change the primary/secondary choices, it changes all the records on the form to equal these choices. How do I fix it, or is there nothing I can do (as it may be totally impossible - I don't know!)??
Any help greatly appreciated, but please talk very simply and slowly - I am a complete novice!
I have a form with various input boxes, three of which are combo boxes. These combo boxes need to affect one another. So let's call these cmb1, cmb2 & cmb3.
Hypothetically let's say:
Within cmb1 the list is A, B, C & D
If the user was to choose A, cmb2 would show the options 1, 2, 3, 4, 5 etc.. If the user was to choose B, cmb2 would show the options 2, 4, 6, 8, 10 etc.. if the user was to choose C, cmb2 would show the options 1, 3, 5, 7, 9 etc..
(So, it is possible that if the user chooses A from cmb1, cmb2 would show 1 and if the user chooses C from cmb1, cmb2 would also show 1)
Using the example in the brackets above:
If the user chose cmb1 - A then chose cmb2 - 1, cmb3 would then show the options Option1, Option2, Option3 etc..
BUT
If the user chose cmb1 - C then chose cmb2 - 1, cmb3 would then show the options Random1, Random2, Random3 etc..
At the moment I have a table with 3 columns. Within the table is every possible combination of the drop down, i.e:
-A l 1 l Option1 l -A l 1 l Option2 l -C l 1 l Random1 l -C l 1 l Random2 l Etc...
I have tried to use the example shown on this website but i've had no luck: fontstuff.com/access/acctut10 (If you look at "Example 2", thats what I tried to do)
i have a main form which allows me to fill in Order Information within which is a subform which allows me to fill in Order Lines.My Order Lines contain the following fields:
Extrusion Length Qty etc. etc.
Elswhere in my database (tblLengths) I have set up a list of possible lengths for each Extrusion and therefore when an order is filled out I want the user to be restricted only to those lengths that are possible with the particular extrusion they have chosen. This list contains the ExtrusionID and LengthID so that I can query the available lengths for a particular Extrusion.Within my subform I have set the record source to point at the tblLengths and criteria on the ExtrusionID to point to [Extrusion]. I've also put a requery against the Extrusion field so that I can force the combobox to refresh its list of results.
My problem is that I am getting unexpected results each time I add an Order line into my form and reading up I see that what I am trying to do is a big no-no.
