I have a field countyID which is a code field and a lookup table County
I want to show the text for county on the datasheet, should I base the datasheet on just the company table and use a combo to get the county text - or should I have query that brings in the county text.
Could the experts tell me the "correct" way of doing this?
What are the performance issues of the two methods. Is which method I choose dependent on how many records are in the lookup table?
I am creating a PDF version of a Report so that I can send it as an attachment. I use the following code to do this:
Private Sub EmailWorkOrderQuote_Click() On Error GoTo Err_EmailWorkOrderQuote_Click
Dim stDocName As String Dim mFilename As String Dim mEmailAddress As Variant Dim mCompanyName As String
[Code] .....
I first create the report with a specifically generated name and save it to a folder.
I then do a SendObject to send the actual email. Unfortunately, it generates its own name for the file that it attaches and I don't think it is possible to specify an attachment.
Anyway, how to include the name of the PDF report in the actual report preferably in the footer.
Im trying to do the following query work. I have a form, with combos , text fields which are filled in with parameters. The query I want to make is complex. For example we shall use field1, field2, combo1 , combo2 for explanation. I want the field1 param AND the field2 AND combo1 AND combo2 to be evaluated for a result. At the same time, I want the user to fill in only field1 and combo1 or like that and the result to be right. Is there any possibility to make them all work at the same time, individually, or in combinations ?
Sounds too Complicated ?
I ve managed to make it work only by filling in individually the fields or combos and have the right results. The other ways didnt.
I thought that my previous request on how to create a dependent combobox based on selections from two previous comboboxes, but the example provided only worked if the fields were all in the same table. Here is an explanation of my problem...I have 3 tables:
-FoodType -fruit -vegetable
Color green red yellow
FoodName apple banana cabbage green beans kiwi lettuce lime raspberries beets spinach star fruit strawberries squash tomato
I have 3 comboboxes on a form: cboFoodType, cboColor, cboFoodName.I would like to filter the value lists in cboFoodName based on the selections from cboFoodType and cboColor.For example, if I select "fruit" from cboFoodType and "red" from cboColor, I want the value list available in cboFood Name to consist of "apple;raspberries;strawberries".Likewise, if I select "vegetable" from cboFoodType and "green" from cboColor, I want the value list available in cboFoodName to consist of "cabbage;green beans;lettuce;spinach".
I have two questions. I have a form with 2 combo boxes. The first pulls from a simple list. Once you make a choice, the second combo box is then filtered by the first. This works correct, except that once a choice is made in the first, the filter is locked. If you change the first combo box's value a second time it does not effect the second box. The query that I am using for the second combo box has two columns that pull the first 50 characters of a pair of memo fields. Quote: SELECT MSSS.SS_ID, Left([Application_Name],50) AS Expr1, Left([Description],50) AS Expr2, MSSS.Site_Code FROM MSSS WHERE (((MSSS.Site_Code)=[Forms]![Edit or Delete Requests]![Combo6])); These fields are then used to populate 2 text fields using an event procedure. Quote: Private Sub Combo10_AfterUpdate() ' Display Partial Application Name and Description based on choice Me!txtApplication_Name = Me!Combo10.Column(1) Me!txtDescription = Me!Combo10.Column(2) End Sub This is working fine, but it starts the next problem.
I need to change the form to include the entire memo field into a text box. I was using the columns of the second combo box to fill them in. Since the combo box is limited to only 50 characters, i could only grab part of it. I am guessing there is a much better way to accomplish what I need, but I am too inexperienced with forms and vb to come up with it. Below are the fields I need from table MSSS Business_Name Application_Name Description Acronym Level_1_Support Level_2_Support Escalation_process Troubleshooting Priority Links Modified Disabled
I need to figure out the amount of days between two dates that do not exist in my database...I use this calculation to do waivers...I was able to build a table and put in the two dates and then build a query which calculates the days between the two but I wanted to know if I could build a query that prompts on a [start date] which I would enter and then prompts on an end date [end date] which I would enter to get the number of days between...is that possible?
I had...
WaiverDays: [Enter start date]-[Enter end date] but it did not let me run...I added () and it still did not run...
I am trying to find the best way to compare estimated expenses to actual expenses in Access. I am working with vessel and port call information, so each port call has a series of costs associated with it. I have two tables, each with multiple fields (around 100). Each of the fields contains a cost type that goes to a particular cost code. Each row of the table is associated with one particular port call. The call is estimated right after it happens, but the invoices are not all recieved for up to three months. Once all of the invoices for this port call have been recieved, we enter the actual expenses for the call. I am trying to find the best way to analyze the costs through finding the variances for one particular port call for each code, and also for multiple port calls to see which cost item has the largest/smallest variance for further investigation into how to estimate it more accurlately. I am thinking that I may have to move into Excel to do this, but would like to stay in Access if possible.
Including check boxes (representing the query fields) in an interactive access form, in order to decide which fields should be visible.
I think the solution is building an invent in VBA for each check box, however I'm not an expert in Access VBA and don't know how to write the code.
In the example that I've uploaded, in the Form1, when I run the query, all fields are displayed, i.e. VENDOR, REGION, CUSTOMER and MATERIAL are displayed. How can I manage it in the form with a listbox to display only the REGION or MATERIAL for instance.
Hi, first post here - may be a simple question, but it's been a bit of a while since I used Access, so my skills are getting rusty...
I have a form with a number of checkboxes writing to Yes/No fields in the underlying table. I then want the data to populate a report via a query. However, in the report, I would like to show only those fields where values equal TRUE/YES. In other words, for each record, only the fields where the user has checked the boxes should be displayed in the report. If possible, this should involve variable height for each record in the report, but this is not a must if too complex. It is, however, imperative not to show fields where the value is FALSE/NO. In other words, if all the fields has to go into each record in the visual layout of the report, is there a way to make the fields with negative values not visible? Any ideas how I can achieve this? I'm not sure if it can be done with just a simple Query, or with the SQL builder, or if it has to be done with VBA scripting (which would perhaps be applied to the report object rather than in the query?).
PS. If you know the answer, but are wondering how much you may need to dumb it down for my benefit, I have used Access a bit in the past, but not professionally. I have never bothered to learn VBA for Access, but have coded in VB and VBA for Excel, though, and know a bit of SQL and Java, which may give you a bit more of an idea what level to pitch it at :o).
I have a form with a combobox whose list items are taken from a table of currencies (called tblCurrencies)
Row Source = SELECT [tblCurrencies].[Ccy] FROM tblCurrencies ORDER BY [Ccy];
Problem is, there may come a time when the currency they want to select is not yet part of the tblCurrencies table, and needs to be added. For simplicity, I'd like to have an additional option in the dropdown (perhaps at the bottom) to specify a new currency.So an additional list item (e.g. 'Add New...') which when selected could prompt a subform where the user could specify the new currency (and any other relevant details specific to that currency) which can be programmatically added to tblCurrencies.how do I get that extra option into the list items for the combobox?...
I have a DB of books and a form to search for multiple fields including ranking (combo box). The way I have it now is to pick "3 stars", "4 stars", "5 stars". What I want to do is have it so that if I pick "4+", it will show all records that have 4 stars and 5 stars and "3+", it will show records with 3, 4, 5 stars.
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 on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is : Private Sub cmd_mailreport_Click() Dim office As Object Set office = Checks.office DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _ "info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _ True, "" End Sub
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
Access 2007-10 Listbox created: List62 (I know I need to rename it, but for now) Multi-select: Extended Row Source Type: Field List Row Source: qryFieldList Open Query: qrySelectedFields (I added primary key to first column, just to have at least one destination field).
Goal: to select multiple fields within listbox, click on command button to open query "qrySelectedFields" with selected fields from list box.This is the code I have on the command_click:
Dim varItem As Variant Dim strSQL As String If Me.List62.ItemsSelected.Count = 0 Then MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT" Me.List62.SetFocus End If
[code]....
It does absolutely nothing - doesn't add the fields to "qrySelectedFields", doesn't open the query, notta.
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!!
I have a table that has client names and addresses. I have designed a form to be able to invoice these clients and everything is fine however what I'd like to do is have the address of the client appear automatically.
I have set the client names in a combo box and would like their address to appear in either a text box or sunken label automatically from the table. Is this possible and how do I do it.
Please keep in mind that I've done basic programming so please be kind to this newbie :D
Hi everyone, I have been making progress with customizing a MS Access program, but one major problem is that I have been trying to make a List Box or Combo Box that I can use to enter data in the TABLE, but I find that I get a pull-down list that has the list of values from only the parameter that is primary key. More specifically, the program is set up as follows: I have two tables in this program: One that is called "invoices" and one that is called "items". In both of these tables, there is one common parameter, which is "Item Number". Item number is the primary key, and I used the "relationships" function to tie this parameter to itself between the two tables. I was successful in setting up a list-box for the "Item Number", but when I try to set up a list-box for another parameter that is supposed to display a person's initials, the pull-down list displays the list of item numbers instead of the list of people's initials. In fact, I don't know if there is an extra step I need to take so that the database stores a list of people's initials. Instead, I just fill in the initials in the field for each record for which the "Item Number" is the primary key. How can I get the list-box to pull down a selection of different people's initials, or in other words how can I get all the people's initials to be stored so that the list of initials can be looked up. Just so you know, I have tried different choices of entries in the "Lookup" tab in the Design mode of Tables, including Display Control, Row Source Type, Row Source, and Bound Column, but the outcome is that the only parameter that I get get in the pull-down list is the primary key, which is Item Number. I appreciate any help you can offer in explaining how to correct this. On a separate note, one of the parameters is "Date", and on the reports, I'm trying to figure out how to filter a specific date range so that I can limit each report to a specific month. Please advise me on this procedure as well. Thanks.
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...
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)