So, I have a form with 4 combo boxes and 1 field for a date.
I'd like to be able to choose an option from one or more comboboxes and create a report of all of the records matching those criteria.
Will this require VBA Coding or can I do this without it? I don't know VBA
I know I will probably have to use Queries and the Like criteria but I'm not sure where to begin and how to do it.
ok, i have just had a lengthly conversation with my user and i need an advanced search system.
what i would need is a search function, that does not only list the full field details from each record in the combos..
here is what i mean.. i have a quotation form with different locations and prices in a list box. i have a text box at the top of the form with an onchange event that alters the results in the list box.
so if the user types 'a'.. only the axxxxx records are displayed.. if they type 'ab' only the 'abxxxx records are shown.. and so on.
because my customer may not remember the exact address of their pickup or destination, i cant use this system, because i wont know the first letter of the details previously entered..
eg.. a user may have been picked up from '5 house street, new hampshire crescent.
if all the customer remembers is 'hampshire crescent.. then the above system will not reveal my record..
therefore i would need a search box at the top that allows me to search for any word (or identical consecutive characters) within a chosen field.
can this advanced string search be done in access?
I created Advanced search (Multi- Field) form and its work perfect but I want to add a combo box for search [Date Removed] and the options is "Active" and "Inactive".
my table include:
[Location]
[Date Installed]
[Installed by]
[EquipmentTag]
[Date Removed]
...
The users may not enter [Date Removed] which mean its Null ( also which mean in my project company / Combo box its "Active" ).
In another hand, if users enter [Date Removed] which mean it's not Null ( also which mean in my project company / Combo box its "Inactive").
In short, if user chose "Active" it will search for nulls/empty column in [Date Removed]
if user chose "Inactive" it will search for filled column in [Date Removed]
The code is attached:-
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
Const conJetDate = "#mm/dd/yyyy#"
[Code] .....
Please help me if you can. We are in a state of panic right now in trying to figure this out and any help would be appreciated.
Basically I have tried many different angles and have not come up with a solution that works. I have spent 80+ hours trying to figure this out with no luck.
Here is what we need to do: We have a database that has data that needs to be printed on a 3"x6" sized tag. Now there are probably 5 or so different tags that need to be printed. What we need to happen is be able to provide a way to print out all the tags at the same time. The problem is I cannot seem to create one report that will generate all the tags correctly (I could create the code to do this in a form, but I can't get it to work in a report), and I don't know how I would combine multiple reports so that all the tags can be printed at once.
Do you have any ideas on how I can do something like this?
HI ALL,
I Have A Form With A Combobox When I Choose A Record from The Box,
The Matching Info From The Table Populates A Textbox
Example
I Choose The Acct Number From The comboBox And The Company Name Goes In a Textbox and the phone number in another textbox
looks like it is fine on the form My Problem Is That When I Look At The Table The Company Name Is In Its acctName field But The Account Number Field Has The Companies Name Aswell
How Do I Fix This-
and also if you type the number in the acctnumber field on the table it places that number in the acctname textbox of the form there is something that is mixed up here but i dont where
please help
Thanks
Hi all.
I have a form that opens up another form when you click on a button. The button does more than just open the form, it does other checks. I want to wait until the opened form is closed to continue with the subroutine. Is this even possible in VBA? I was wondering if there was a frmFormName.IsOpen or some kind of flag that would tell me if a form is open or not. Any help would be greatly appreciated. Thanks in advance.
Sub open
Checks things
Checks things
checks things
Opens form(frmDetails)
Wait for form to close (THING I DON'T KNOW HOW TO DO IF POSSIBLE)
Checks things
Checks things
end sub
I'm creating an advanced search form which is making use of Like queries and is Null. A working example is below:
Like "*" & [Forms]![Frm_Data_Mine]![Combo_Primary_SIC] & "*" Or Is Null
So this either returns matching values from the field, or basically ignores this if left blank. All good.
I have two other fields:
[Turnover_From]
[Turnover_To]
Both are numeric values. What I would like to achieve is the same as the above, so I can search between a from and to value, or if left null, ignore them. I had a go with the below but it's clearly not right, and I can work how where and how I insert the Like statement which makes the others work.
Between [forms]![Frm_Data_Mine]![Turnover_From] And [forms]![Frm_Data_Mine]![Turnover_From] Or Is Null
I usually use ComboBoxes to show a user-friendly name instead of an ID. For example, I would use a ComboBox of EmployeePK, FirstName + " " + LastName and set the first column as the control source but with 0" width.The issue is these are supposed to be read only. I can prevent the user from changing the field but I don't have a good solution for hiding the ability to select the drop down.
My current method is to create a transparent box over the combobox (so it can't be selected) then hide the dropdown arrow with a background colored rectangle.
In my database you can search on either number or name. I have therefor, two combo boxes. and it works well, except that...
When I pick by number first, and then try to pick another record using the name combobox, the number combobox still shows the first search I did. I would like the two comboboxes to interact with each other...
I am trying to get get 2 comboxes to refresh from one when cascading. The first combobox (Location) works, but the second one (Catches) doesn't. All I get are the Key values of the catches rather than the values...
I am wondering if it my relationship set up of if my VBA needs to be altered to accommodate the requering the two comboboxes.
Code:
Private Sub cboCategory_AfterUpdate()
Dim sCatchLocation As String
Dim sCatch As String
sCatchLocation = "SELECT [tblLocation].[Location_ID], [tblLocation].[LocationCategory_ID], [tblLocation].[Location] " & _
"FROM tblLocation " & _
[Code] .....
I think I've solved a method to have cascading comboboxes on a continous subfrom.
The after update on the first box sets the query the drives the secondbox's rowsource, and blanks out all the boxes below it. And so on.
Dim EventTP As String
EventTP = Me.cbxClass1
Me.cbxClass2.RowSource = "SELECT distinct Class2 FROM tblEventTypes WHERE (((class1)='" & EventTP & "'));"
Me.cbxClass2 = ""
Me.cbxClass3 = ""
Me.cbxClass4 = ""
All of the comboboxes are fed by one table.
Comments?
In my sub form I have two combo boxes named [wps] and [process ], I want to be able to filter process based on the value in wps.
View 3 Replies View RelatedI want to know is it possible to have a combobox change the row source of another combobox with vba code or some other way.
View 3 Replies View RelatedI want to make a form where i have 4 comboboxes in a cscade combobox thing.
I already made 4 tables with info.
- My first table contains devices:
- ApparaatID (Primairy key)
- Apparaat(with 4 entries)
- My second table contains if it is hardware or software:
- TypeID (Primairy key)
- ApparaatID
- Type (where I entered hardware and software in 2 different entries)
- The thirt table contains operating systems:
- OSID (Primairy key)
- ApparaatID
- TypeID
- OS_versie (where I entered all the OS I want to enter)
Now I made a form I called problemen with 4 comboboxes.
- cboApparaat
- cboType
- cboOS
- cboProbleem
1) What I want is that when you select device 1 and 4 that cboType can select both hardware and software but with device 2 and 3 you can only select software.
2) If you have selected software is cboType I want you to be able to select 1 of the 11 OS I have pre-entered in the table.
3) That you get specific problems with the previous made choices. For example if you select Hardware in cboType, thta you only get problems with hardware to select from and the same with software and OS.
4) I want a textbox, that will contain the solution of the problem you selected and that if there and several solutions that you get all of them at once.
Hi,
I'm pretty new to this stuff and I'm having this odd problem:
I've created a tabular form that uses a combobox for each record. The problem is that if I had, say 10 records in the form and I select a value for the combo box of one of the records, all of the combo boxes change to the same value...
Any help would be much appreciated.
TIA
Hi,
I have read a few threads about comboBoxes and what-not, but I guess I'm just having trouble relating the answers to my particular problem. I've done a semester of Access at Uni so I'm a little versed in SQL and basica database theory, and am really just after a point in the right direction as to how to accomplish this:
I have a friend who is sort of like a maintenance worker for a number of places around my city. He wants a database made up which allows him to quickly and easily make a report on the day's/week's/month's jobs he has completed.
Basically, the information in the report should be Premise (the particular building the job was done in), Location (the room in the building), Item (the 'thing' that maintenance was done on), Job Type (eg. Repair, replace, purchase etc), Date and Time.
Ok, so that's the information he wants, and here's how he wants to enter it:
He wants some kind of form which contains a drop box (or combo box) full of the various Premises, and once he highlights the Premise, a second drop box branches out (I think this is what's called a cascading drop box? Maybe not...) which allows him to highlight the Location, then a 3rd allows him to select the Item, and finally the Job Type. Once he selects these things, he wants to be able to type in the date and time, and save the information in a Latest Jobs table.
So in the end, he wants to bring up the Latest Jobs table at some point, and make a report from it. But, once he's made the report, he wants to have the Latest Jobs table append to an All Jobs table, and then clear itself ready for the next wave of Jobs.
So that's the situation, thanks for reading if you got this far :P
I'm not expecting a giant solution to the entire problem, I'm just looking for some tips and strategies on how to acheive this, and some elements of Access I should research in order to help.
Thanks a lot,
Liam
I have two comboboxes on my form. When the value of the first combobox (SourceWH) changes it performs a requery on the second (DestWH) as the drop-down list needs to be filtered depending on the first value. I have this working fine but my problem arises when I need to be able to swap the values of each combobox. I have created a command button which stores the value of each combobox and then swaps them but my problem is that if I click the button the second combobox becomes blank. I guess this is something to do with the requery taking place after the value of the first combobox is updated but am unsure how to rectify this problem.
The first combobox has the following expression in the expression builder:
Action Argument
Requery DestWH
The command button has the following code:
Private Sub Swap_Btn_Click()
Dim SourceValue, DestValue As String
SourceValue = Me.SourceWH.Value
DestValue = Me.DestWH.Value
Me.DestWH.Value = SourceValue
Me.SourceWH.Value = DestValue
End Sub
I use access 2013.
1) How to auto fill in a text box if you select a value from the last of 4 cascade comboboxes.
I have 4 comboboxes where
1 = Apparaat - cboApparaat
2 = Type - cboType
3 = OS - cboOS
4 = Probleem - cboProbleem
With all 4 comboboxes I have the vba code Me.cbo[name combobox].Requery
If I select the last combobox, cboProbeem. I want the textbox underneath to automaticly fill in the solution of that problem. I already tried to use the following vba code
Me.Oplossing = Me.cboProbleem.Column(5)
But it does not work.
2) I want to make a form for customers, where I can fill in customer info, device info and date when there contract started. Now I want to use SUM to fill in the end date of there contract.
I'm making an accounts package.
I've made a graph of revenue by customer but due to the number of customers it's a bit hard to see the customer names.
So I would like to be able to select my own custom group of 5 customers to plot on the graph.
To do this I think I need a query that selects the 5 customers which is populated from 5 comboboxes on a new form that the user can select customer names from but I'm not sure of the VBA that links all this together.
I tried and failed to get this to work using a multiselect listbox..I have a list of departments in tblFunctionalArea...My main table is tblStatic..I want to be able to for each record select multiple departments affected by a record and store them in the tblStatic.After looking around i couldn't find many people successfully maanging to store listbox values in a table...
I decided to create 5 fields in tblStatic and in my form create multiple combo boxes cboFunctionalArea1, cboFunctionalArea2 etc etc which are bound to these fields.I want to be able to ensure the list for any combo box requeries and takes out any selection in the other boxes.
I have this working in a strict cascade fashion i.e. in cbo1 all dept's visible, in cbo2 it takes off whatever was selected in cbo1 etc. But if someone then jumps back and deletes the content of cbo3 then the whole thing breaks or if they amend in the wrong order it breaks
Hello, I'm trying to use information based on items selected in a combobox in the main form. Once the user selects the desired info in the comboboxes on the main form, I want the subform to display the data that matches the search criteria in the main form. For instance, if I select a job number, I want everything that matches that job number to be displayed the subform (along with the corresponding job name, version, etc.). I also want to get more specific in a search where I could select a job number, job name, and job version from the comboboxes in the main form to where all the data that matches the search criteria in the main form will be displayed in the subform.
Attached is what I have so far. The form I'm trying to perform this in is the SearchByResults form and the subform is the qryk95 form. I'm trying to populate this using the K95-Template table. I performed a query (qryk95) to select only the fields I want to display in my subform. Any help would be much appreciated.
I have a report based on a query that I put inside a form. What I want to do is create 4 comboboxes from which users can choose options that will filter the report without leaving the form or opening the report in a separate window. Also, I want to make the filters dependent on each other, meaning if a select option A on combobox 1, the options i will select on combobox 2 will only be based on the earlier filter on combobox 1 ...
So far I'm able to do at least 2 of the comboboxes to filter the report, but they aren't based on which one was used to filter the report first. So if i filter the records by choosing option A on combobox 1, combobox 2 will still filter every record.
here's my 2 codes so far:
Private Sub cboLocation_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboLocation) Then
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery
[code]....
the other 2 comboboxes, I can't apply the same code cause the enter parameter dialog keeps popping out.
I have a tab control on a form, and I want to use "Hotkeys' to get from one page to another (or more specifically, to toggle the visibility of the pages).
So, I set up the tab control with the pages I want hidden set with visible=No. Then I enable the Form.KeyPreview, so that the form will get a chance to look at all the key presses.
Lastly I have a Form.KeyDown handler, that looks like this:
Code:
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
MsgBox "Key pressed (1): Shift value is " & Format(Shift)
' Detect Hot-keys for the pages in the MultiPage wizard, and make them Visible or not visible
' P/D/X/S/R/H/C
If Shift = acAltMask + acShiftMask Then
[Code] ....
This is early on in the design iteration - more will happen with the pages, but this is an easy way of reviewing various aspects of a project.
So what happens?
I put a breakpoint at the first If statement, and sure enough, it picks up the Ctrl key or the Alt key, whenever they are pressed. (I need to use the mouse to clear the msgbox, naturally!) When I press both of them (Ctrl/Alt) I get the required value of 6, but I never seem to get to the second msgbox. In addition, if I comment out the first message box, I also never seem to get to the second msgbox (ie the point where the combination has been detected.
KeyDown obviously has to fire for each component of a HotKey combination, and the Shift parameter has been shown to be cumulative. The only thing I can think of is that somehow I need to turn off keypress processing somewhere else (Used to be possible to use Cancel to do this, I seem to recall).
I'm quite new to using Access 2007 and I'm trying to create a form from different tables which have relationships. I've used the form wizard and added all the fields I need from the different tables, however, when I click form view it only shows the title and nothing else. When I'm in design view everything is showing..
View 1 Replies View RelatedI have a form that displays the quantity of plants that we are growing by genus, (marigolds, impatiens, petunias). each genus has many different varieties that can be used to make up the mix. I need to have some type of worksheet where we can put in the total qty and calculate the quantity of each variety by putting in percentages for each variety, much the same as you could do with a spreadsheet. The results of that worksheet then needs to be saved to a table for future use. Being fairly new to Access I'm not really sure what format to use. Is there a way to display the results of a query in datasheet view minipulate the numbers and update to my table?
View 1 Replies View RelatedI've got two tables - one that works like a cypher and one with all my records.
I have 2 queries. One that is a simple query that extracts data that is not so user-friendly and puts it into a user-friendly format. The Other query is for any records that the first query excludes because the new combination of data (lets just call it a code) is not in the cypher table, but needs to be added.
How do a create a form that will show the records in the 'excluded records' query and allow me to select from a drop-down list a specific set of categories to update the cypher table ?
Example :
Record Table: Cypher Table:
Ford Focus = Sedan
Ford F-150 = Truck
Ford Freestyle = Wagon
Ford Escape = SUV
Now a new model comes in, a Ford Edge which is an SUV but is not in my cypher table.
I have the query to pull in the excluded Ford Edge, but I want a form to show "Ford Edge" in the first column and be able to select from a drop-down list "SUV", and either automatically update my cypher table or require me to press a button to update the table (whichever is easier).