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.
Hi all, I have 2 tables, “DATA-CUSTOMER” and “DATA-MAIN”“DATA-CUSTOMER” has the following fields:Rep_code, Area_code, Name, Update (yes/no)“DATA-MAIN” has the following fields:Rep_code, Area_code, NameI have a form which has 3 combo boxes, looking at the following fields: rep code, area code, name,. These combo boxes are picking the data up from querys which look at the table: “DATA-CUSTOMER”I am trying to enable the user to select from the 3 combo boxes these 3 fields and it should update the table “DATA_MAIN” with these details. It appears to update the "DATA-MAIN" with one record but keeps overwritting the first record, and the combo fields in the form already have fields selected, i would like them to start of blank when entering the form.Any help would be appreciated, attached is test database that I cant get to work properly.cheers, Kevin
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 have a form that i want to stamp with a date when it was last updated. if a change to the form is made it will be stamped with today's date. i used the BeforeUpdate event so that you could choose to save the edits or not so if you didn't mean to edit a record then the date would not change. the code is: Dim myVar As Byte myVar = MsgBox("Changes to this issue have been made. Would you like to save the changes?", vbYesNo + vbQuestion, "Save Changes?") If myVar = vbYes Then [LastUpdate].Value = Date Else Me.Undo End If
my problem is that when i change the combo box values it will run this event and ask to save. i think the reason this is happening is because the comboboxes save when you change them. how do i accomplish the same task as above but allow the combo boxes to act like txt boxes?
also is there a way that when i ask if the user wants to save or not, to have a cancel button that will take no action, leaving the record open the way it was (new typing and everything) before the user tried to close the form without saving that info?
I have a combo box that get its values from another table the problem i am having is when a user don't see the info they have in the combo box then enter the new data into the combo box field but it don't update my table with the entered values. How can i fix this to update my table if the user add new data in the combo box field.
Ok So I've created a Database to track phone calls coming into a call center. (Originally created in Open Office, but then we switched to Access)
I've got a form that has buttons to run multiple reports.
The Reports pull data from a table based off dates found in another table (Something I used in Open Office)
Table 1 has all the phone calls
Table 2 has a From date and a to date
The report pulls the phone calls in table 1 between the dates in table 2. In open office I used Subforms (Which were seemless) and combo boxes to update the dates in Table 2 before running the report.
Is there an easier way to accomplish this in Access? If so How? If Not, is there a way to make a seemless subform in Access?
GOAL: select the IC number from the combo box and have 5 text boxes auto fill.
I have a table made for the IC number drop down. The columns represent the fields that need to be auto filled.
Making some kind of query to link the combo box entry to the text boxes? Some VBA where the control source is equal to my query? I have tried changing the text box control source to equal columns from my drop down but that did not work.
NOTE: the blue font in the first image represents the text boxes that need to auto fill when I select the IC number from the drop down. The second image is my table from which my combo box is generated. The acronyms are for the two tests on this form.
I have a form that our operators use to do their hourly quality audits on. This is getting to be a huge burden on them because right now, they enter the date, the week ending date, the month every time they have to do an entry, and for me it is a nightmare because they can still enter the wrong information. So, what I was wondering is if there is a way that I can have them select the date from a combo box (easy enough), but from that, have the week ending date and the month fields automatically update as well. Any advice? I would really appreciate it! Thanks so much!!!!
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
I have a table that has 2 fields. One field referes to data in another (using combo box pointing to a DEPARTMENT table). When a value is selected I want the next field to only choose the values that refer to that department sub classifacations. In other words.
If I have 3 departments and 5 job classifacationd in each department. When I select a department I only want the choice of the 5 job classifactions to be listed.
I have the DEPARTMENT table and the JOB CLASSIFACATION tables linked correctly. If you look at the DEPARTMENT table you see the + isign on the left and if you select the + sign you can see all the JOB CLASSIFACATIONS listed.
My problem is that I can get the DEPARTMENT column to list the departments, but when you choose one and go to the JOB CLASSIFACATIONS I either see all 15 job classifactions or a text box asking me for the department, or nothing at all.
I was able to create a Form to do this, but I can't figuer out how to put this in a table so that I can make it useful and link it to an employee.
I have wasted too much time and am in over my head. Need some real help.
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));[/
Checked the FAQ on this but doesn't apply to what I need.
Basically, I have a Product Class, and Products that are in the class. My database is for a computer component business, so the clsses are Processors, Mainboard etc.. and the products that fit into that class. The Class and products are listed in the Products table, and I need a way to have it on my subform, so I can choose the product class from one combo box, and then have another combo box to view the products in that class.
Right now I have it :
SELECT DISTINCT Products.[Product Number], Products.[Product Description] FROM Products
and that lets me select ALL the items in my products list, but I also have a Product Class Combo in the form that does nothing yet - So I thought adding : WHERE (((Products.Product Class)=(!FORMS![Orders Subform1]![Product Class])) ORDER BY Products.Product Class
To the end would sort it, but it doesn't and gives me the error : "Syntax Error (Missing Operator) in query expression"
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 that contain data from one table.
table has two fields: Name and ID
cboName cboID
I would like both combo boxes to update each other.
Example if start typing in the cboName box it fills in after update I would like the cboID to be updated with the correct value and vise versa if i start typing the ID in the cboID box when selected the cboName should be updated.
example table ID NAME 1 joe 2 jane 3 mark
So if i type in or select 1 in the ID combo box it should put 'joe' in the Name combo box.
Or if i type in or select jane in the NAME combo box it should put '2' in the ID box.
I have read how to cascade combo boxes but that is not what i think i need.
I'm building my first Access DB for a while, so am a little rusty, but I can't seem to work out this problem.
I've bound my form, and all of the other fields are saving and populating the correct table, but I have two combo boxes, both of which populate a series of other text boxes based on the selection (eg. I select Joe Bloggs in one box, and it automatically puts Joe Bloggs' phone number and email address in two following boxes).
This all works, however when I save the record, or move to the next one, the data entered in the combo boxes and the related populated fields doesn't save - they just show up as blank in the record created in my table.
As an aside, how do I get the combo boxes & related text fields to show a default blank value until a value is selected.
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 have two tables, tblCountry and tblLocation. With the following structure
tblCountry ID Name Text
tblLocation ID Country_fk Name Text
As you can guess tblCountry lists all of the countries, tblLocation lists all of the locations in each country, the tblLocation.Country_fk field is linked to tblCountry.ID.
I want the user to be able to edit [tblLocation].[Text] using a form. They simply select the country and then the location using combo-boxes and then add or edit the content using a textbox.
In my form I have a combo-box that displays the country names, the RowSource is set to SELECT [tblCountry].[ID], [tblCountry].[Name], FROM tblCountry ORDER BY [Name];
I then have a second combo-box that lists the locations for the selected country. This uses an AfterUpdate() procedure to select [tblLocation].[ID] using an SQL query based on the value of the country combo-box. I.e:
SELECT [tblLocation].[ID],[tblLocation].[Name],[tblLocation].[Text] FROM tblLocation WHERE [Country_fk] = " & Me.country_box.Value & " Order By [Name]"
I want to be able to have a textbox that then displays [tblLocation].[Text] for the selected location. Thats where the problem arises. I can't find a way that will let me display any content thats available for the selected location AND let me edit it. I've tried using UpdateAfter procedures, different bindings (tables, queries based on the value of location combo-box).
Can anyone suggest how I can display [tblLocation].[Text] based on the value of the selected country/location and be able to update the information via a textbox?
I'm jumping to the forms development and going to try what I need via queries and SQL. I'm trying to do a series of cascading combo boxes which have worked out fine, right till I hit the first junction table.
I will include the VBA code below as well as a screenshot but here's how it goes.
REGION cascades down to COUNTRY cascades down to PORT and then to CARRIER.
This is for a shipping program.
When it gets to PORT and are trying to cascade to the various CARRIER's thats where it hits a junction table of PORT_CARRIER.
Here is REGION to COUNTRY:
Me.cboCountry.RowSource = "SELECT COUNTRY.CNTRY_ID, COUNTRY.COUNTRY FROM COUNTRY " & _ " WHERE REG_ID = " & Nz(Me.cboRegion) & _ " ORDER BY COUNTRY" Me.cboCountry = Null
COUNTRY to PORT :
Me.cboPort.RowSource = "SELECT PORT.PORT_ID, PORT.PORT FROM PORT " & _ " WHERE CNTRY_ID = " & Nz(Me.cboCountry) & _ " ORDER BY PORT" Me.cboPort = Null
PORT to CARRIER:
Me.cboCarrier.RowSource = "SELECT PORT_CARR.PORT_ID, PORT_CARR.CARR_ID FROM PORT_CARR " &_ " WHERE PORT_ID = " & Nz(Me.cboPort) & _ " ORDER BY CARR_ID" Me.cboCarrier = Null
PORT to CARRIER is where the problem is.
It populates the combo box, but with the ID numbers instead of actual CARRIER names.
(the Junction table are two PK fields and are lookups to PORT in PORT table and CARRIER in CARRIER table.)
Is there a magic spell for cascading combo boxes when you hit a junction table?
I need a table structure that will allow me to have a repair log data entry form with 3 cascading combo boxes on the repair log data entry form. There can be many repairs for a specific job but most of the time there will be one repair per job and 1% of the time two or more repairs for a specific job.
I am pulling a report based on a query that will show the repaired location, facility type, repaired item, repaired component on that item and other details related to the repair.I uploaded an empty database with the structure.
Cascade levels
I. Facility Type II. Item by [I] III. Component by [II]
I'm creating a form that has combo boxes pertaining to each of the following fields from table "Documents": DocumentID (primary key), DocumentTitle, DocumentAuthor, and DocumentYear. No combo box is used with any priority over the others. The working form will allow a user to retrieve the full document record (data for all fields) by using any combo box they want, as well as any combination of combo boxes. This means that if a selection is made in one field's combo box, the drop-down lists in the other boxes need to update based on that preliminary selection. The filtered results for each field, based on any and all combo box selections, are always shown in a single datasheet on the form.
The kicker is that when a user starts filtering records by making selections from the drop down list in a combo box, but then decides to TYPE in another field's combo box, I want the combo box they typed in to do two things: 1) filter the records for that field based on what they typed, if any records meet that criteria (e.g. they typed "B" so only records beginning with B are shown), and 2) if no records match the typed criteria, the search starts all over (at the top of the cascade), filtering all library records based only on what they typed in the most recent combo box.
Does that make sense? Any tips welcomed.
I don't know how to cascade combo boxes in this way.
I have read the FAQ on cascading combo boxes and have managed to apply the theory to my DB's data input form (frmDataEntry) which is very cool and prevents a lot of errors however in doing so it no longer populates the underlying table (tblProductionDetails).
I am at a loss as to how to correct this as you can see from the example I need the customer and description field to be populated with the correct data rather than the fields I am using to make the cascading combo boxes work.
I have a form setup that has 5 combo boxes where a user will select one item from each combo box. Each combo box is from a differant table. Once this is done I want to be able to append or update the results into a new table I created. My problem is I don't know how to submit the data from the combo boxes to the new table. Can anyone help me with this?
