General :: Rewriting Repeated Blocks Of Code For Combo Boxes
May 10, 2013
I just recently started programming in MS Access. I have some forms with lots of combo boxes. I do not want users to type anything in the boxes, but to only select stuff that is contained in the drop down menus.I found this useful code and I am using it in my combo boxes on the key down event to do what I want.
Private Sub cmb_a_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode <> vbKeyTab And KeyCode <> vbKeyReturn Then KeyCode = 0
End Sub
Private Sub cmb_b_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode <> vbKeyTab And KeyCode <> vbKeyReturn Then KeyCode = 0
End Sub
Private Sub cmb_c_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode <> vbKeyTab And KeyCode <> vbKeyReturn Then KeyCode = 0
End Sub
The issue is because I have so many combo boxes, is there a better way to rewrite the code so I don't have to repeat the same code in 20 places?
I have a pair of combo boxes which are n a frm and work fine. The code is:
Private Sub cboSubjectSelector_AfterUpdate() Me!cboOneBookSelector.Requery Me!cboOneBookSelector.SetFocus End Sub Private Sub Form_Current() Me!cboOneBookSelector.Requery End Sub
I want the combo boxes to be repeated five more times so that the same options are given within the same form. Every time inset a copy of the same combo box get the folowing error message:
'Microsft Access can't find the macro 'requery'' I am ot sure what I need to do nrder t remove the error message. I did not create a macro called 'requery' tis part of the code above. I have alos tried to recopy the code above and then retnamed the cbo in the form adn then modified then copied and modified the code to see if that helps - it doesn't. Any ideas - please help.
And other time it wont work with On change but only with After Update Event (code is same).
Another thing this morning happens was that when I tried to add new record trough form where combo box and tex boxes are located, MS Office suddenly stopped working after selecting combo box selection (with message Microsoft Office has Stopped Working). It is 2013 version.
I got it work after deleting and re-inserting VBA code to autopopulate text box at After Update Event.
I have an Application with several users that share tables located in a Design Master file at a server. The tables are shared in several computers with a Replica of the Design Master in each computer. When we try to synchronize any Replica with the Master Design, it gives back an error message stating that the "Number of blocks of shared files are exceeded" And the message ends stating that I must increase the MaxLocksPerFile in the Register.
Im using the following vba Code to try and populate a text box with multiple line based on selections from a list box.
Private Sub Precautions_AfterUpdate() Dim Criteria As String Dim ctl As Control Dim Itm As Variant
Set ctl = Me.Precautions
For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then
Criteria = ctl.ItemData(Itm) Else Criteria = Criteria & "," & ctl.ItemData(Itm) End If Next Itm Me.description = Criteria
End Sub
Its working to the extent that it adds the options that are selected from the list box but I actually want it to put the 2nd column of the list box which is hidden into the text box. How do i edit this code to add column 2 rather than column 1 when an option is clicked?
Any help would be appreciated
Ive attached the database (sorry its very simple) Open form tester to see what i mean.
Hi all... and thanks for your help to this very frustrated moderate Access user. I have been using an example for cascading combo boxes in a timesheet form, but I simply cannot get it to do what I need.
I have a table called "Entry", with a form called EntryForm based on it. That table has the follwing fields:
Name Date Hours Set* cboSet Subset* cboSubset Item* cboItem
The fields with asterisks are the ones I am trying to create cascading combos for. I have a table called "Sets" which has the same fieldnames in it, which I am trying to use as the sources for these fields. The form has "cbo" in front of the names. I have attached my code in a document.
It looks like all this does is act like an Excel autofilter. Can someone help me with the sourcing for these fields and how I get the Entry table to populate as I make entries. All my code does is push me to edit the Sets table records.
my search form has a combo box with "make"names. i can add new makes via a other form. so the combo box needs te run via a query so that it can list new makes (so that i can also search them). but, since some makes repeat them selves in the table, sometimes up to 15 times (see atachment 1), the combo box will show 15 times the same make. i just need it to show the make 1 time, all of the makes just 1 time. only way i can do that is to type them my self (see atachment 2). but then new makes that are added later wont be shown in the list.
so: i want that the make is only shown once in the list, and that the list is updated with new makes that are added later. how do i do that?
note: a "make" E.G = Philips, Apple, LG, Microsoft, creative etc (just for the lesser skilled english readers like me )
I am new ussing access already with issues surprice no.I have four tables Section, Ducument, Description, Origin.I nees to Synchronize four Combo boxes, my first combo box code work fine
Private Sub cboSection_AfterUpdate() Me.cboDocument.RowSource = "SELECT DocumentName FROM" & _ " [Document] WHERE SectionID = " & Me.cboSection & _ " ORDER BY DocumentName" Me.cboDocument.Enabled = True End Sub
When I select a category from my first combo box the secon combo box get populated with the correct Data.But when I select the second box the third combo does not get populated with any data here is my code for the second combo box.
Private Sub cboDocument_AfterUpdate() Me.cboDescription.RowSource = "SELECT DescriptionName FROM" & _ " [Description] WHERE DocumentID = " & Me.cboDocument & _ " ORDER BY DescriptionName"
What I am trying to do is, for example, I have 2 combo boxes, you can select a value in the first one and search and your results match the criteria, or you can select a value in the first AND second one and the records dispalyed will match the first AND second combo box criteria.
I am creating a small database for a budget and Im trying to create 3 cascading combo boxes on a form (areabox2 , devbox2, entitybox2) . I have it right now so that that
devbox2 is based on areabox2 and entitybox2 is based on devbox2 BUT!!!!!!! I want entitybox2 to be based on both areabox2 and devbox2
Parameters are :
Table = Budget Info areabox2 data = [Budget Info].[Project Area] devbox2 data = [Budget Info].Development entitybox2 data = [Budget Info].Entity
I have designed a main form which have buttons for about 30 different companies name to open, each button opens an individual company form. Now the companies list is growing day by day so i want to create a combo box which select the company name and open the relevant company's form.
Do I need to create a table which lists all the companies and then add a combo box on a main screen which select the company name and open the form but i need a VBA code for that.
I am using a combo box to filter a 2nd form upon clicking a button. I posted on this topic the other month and was given some code that works. I am attempting to tweak it for another part of my database.I am receiving a Run-time error "3464': Data type mismatch in criteria expression.
I'm trying to Identify a particular entry in my table for editing via combo boxes. The same serial number will come in multiple times but a different job number each time it comes in. To identify a record for editing I need the user to identify the serial number in combo1 and then the job # in combo2 (cascaded combo boxes).
The issue I'm running into is that combo1 has dozens of serial number duplicates (combo2 is acting as it should). When I change some settings around I'm able to get combo1 to eliminate duplicates but now combo2 only shows 1 job # when there should be dozens for that serial #. I need all like serial numbers to show their job # in combo2 and I have not had any luck surfing around the net/forums/experimenting.
I'm pretty sure I have to use a Junction Table but I haven't messed with that and I'm not 100% sure that's the best/only way to do it.
I have a table with the following 5 fields. (Service Type), (Valve Name),(Size),(Rating),(Description).
I want to do two thing:
First: I want to select the required information from the first 4 fields using combo boxes and get the last field (description) based on the selected 4 fields. In other words, i want the record to be filtered using first 4 fields to give me the last field info.
Second: I want to store the filtered record (all 5 fields) in another table.
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?
What I am trying to do is create a master table which references the every other table.But the primary key references another primary key.I can get the combo box to display all the options, buy shows options already selected for other records.in other words...
I have 4 reference IDs from table A. (ID1, ID2, ID3, ID4)
Master_Table has a combobox to select between the IDs. if record 1 has ID_1 Record 2's combobox will show all 4 IDs If I only have 1 ID that can be put into record 4, it's combobox still displays all 4 IDs.
What I want is: If ID3 is selected for record 1, it wont be displayed in the comboboxes for the other records.
The Database is coming along nicely now, but in need of guidice with this one:
On the main table of the contacts I have what you could call a date of 01/01/06 and a combo box next or below that that I select what risk levels to asign the user.
Each user would have a no of year review based on the risk level: High = 1 Year Med = 2 Years Low = 3 Years.
There is another date box that is blank at the moment, what I am looking for query wise or another way is on change/update the risk level is to add the start start in this case 01/01/06 (if user is Med Risk - then add 2 years) and adding risk level giving me a new date of 01/01/08 in the blank date box (this option will make the main table update.
Can anyone point me in the right direction for this one?
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.
I'm trying to build an database for aircraft operators. I've got the basic tables structure and relationships but I'm stuck on building an search form to filter records by user input.I've got following controls on my form (unbound):
1. AircraftType (combo box) from tblAircrafts 2. CompanyName (combo box) from tblListOfAircraftsOperators 3. TeailNumber (text box) from tblAircraftOperators 4. AirportNameSearch (combo box) from tblAirports 5. PassengersNumber (text box) from tblAircraftOperators 6. ManufactureYear (text box) from tblAircraftOperators 7. SourceSearch (combo box) from tblInfoSource 8. CountrySearch (combo box) from tblCountry 9. CategorySearch (combo box) from tblAircraftCategory 10. EamilToOperator (text box) from tblAircraftOperators 11. InteriorPhoto (Bound object frame) from tblAircraftOperators 12. ExteriorPhot (bound object frame) from AircraftOperators
I need to enable users to search for aircrafts based on those criteria. As I mentioned I'm new to Access and I don't have any advanced coding skills. I have a query build to perform the search and this is the code I've managed to write so far:
Attached I have a database that I've been working on which has a form called "frmCriteriaSearch". It is based off of the qryCriteriaListBoxUpdate query. I am trying to get the listbox in the second tab of the results section to work. It queries fine for the checkboxes, but I cannot get the comboboxes to affect the query (unless a checkbox has already been selected)
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'm having a problem getting a report textbox to display the average value that I want.
Background:I have a DB that tracks student grades. The course is organized into blocks, with several tests in each block. Students can take the same test multiple times, in cases of failure, or if they get rolled back in class. The DB tracks whether or not each test is a retest or audit.
The tests are also weighted. For example, the Geo Quiz could be worth 10% of the grade for a block, and the final exam 90%.
Goal:My report needs to display the class number, student name, block, test, and associated scores; it needs to show the total grade for each block; and it needs to display the student's average grade across all blocks. I need to be able to generate the report at any point in the course, not just after all blocks and tests have been completed.
Setup:Since I want to display the assigned grade, but calculate the weighted grade into the block grade, I set up a query (qryWeightedGrades) to calculate the weighted grade for each test (e.g. Score of 98%, weighted at 10% of block, results in a 9.8 for the weighted grade). When it comes to calculating the block grade, I just sum the weighted scores.
In the report (based on qryWeightedGrades) I have the grouping levels set up for Class#, Student name, then Block; the test name, score, weight, retake info, audit info, etc. appears together on a row.
How to do, get the average of all the block grades. I can't get the textbox in the Student Name group level footer to average the Block grades in the Block group level footer, without getting an error. I've thought of creating another query that averages the block grades for each student, but how to incorporate that into the footer of the Student Name group level.
Here's an example of what I want the report to look like:
I've been tasked with coming up with a table structure that will allow me to make a form that lists the available static ip's in my various network range blocks (we have multiple off-site locations and use different ip ranges at each site).
I have the vision in my head of the form, you would select the block range from a drop down list, then see all the available addresses in that range. From there you could assign mark one as used and it is no longer listed on the form (I'm thinking a simple yes/no checkbox). What my co-workers would love to see is if there were entering a new PC assignment and put one of those available IP addresses into that forms record (different table too) then the ip would get deleted from the available pool as well.
Hi guys, I have a related databes with about 20 tables.
My main table stores the data linking with most of the other tables. This main table stores football match records with player line ups. Initially I had a problem linking the 11 player fields in the main table to the player table, Icould only do it with 1, so someone at work suggested to link it using the lookup function. This worked brilliantly. However, now I am designing a GUI with forms but the forms have combo boxes where the lookup function was used and when i change these to text boxes, the players names are replaced with their ID numbers.
Is there any way of creating the form without the unsightly combo boxes, as they won't be needed, the GUI will be read-only. I look forward to your help!!
