Hi,
Im using square bracketed parameters in my query designs for user prompts. Now I want a similar feature to tell the query what field I want it to show. Is there a way of doing this please? - or does the fact I need to do imply a db design flaw?
I have a parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.
i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..
I have a field on a report which I am struggling to produce the right query for.
I have a table - tblTillDept which consists of a 4 fields. The first is the PK, which is the number of a till department. The second - fourth fields are names of 3 different branches of the small garden centre I am writing the database for. The values in these fields are the names of the corresponding departments. Not every branch has exactly the same name for each field.
Prior to the report being is run, a small form, frmCentre pops up asking the user to select the Centre they are printing the labels for. They select the name of the branch from a combo box (cboCentre) bound to a field list of tblTillDept. When the report is formatted, the intention is that the value of this combo box selects the field from which the value for the department is taken. I can see that I need to set the control source of this property to some sort of query, but I can't quite work out how to do it.
I've uploaded the database, written in Access 2000, here (http://www.moppy.co.uk/plants.mdb) as it's too big to post as an attachment.
I managed to count the performance of users. The only thing I need to do is to put the names of the users on the form (under different tab) so I can select them and they show their performance. The best would that they are showing up there in the form till I change the names. I use now a text field to write the name, but when I close the database and open, I need to add the name again.
If I could use somehow the names from the users stored in a table, that would be great.
When I send an email from Access with SendObject, how does it determine the user name, the 'from' name. Is it just the user's Outlook name? Do I have any control over that?
View 1 Replies View RelatedOk this might be basic stuff i'm a bit dumb
I have a query showing lots of records of equipment that is in different rooms. I would like to have a form where the user can bash in a room number and all the records from the query that have that room number in it pop up.
Please help the dumb
I have an access 2010 web database, modified from a microsoft template (Desktop inventory database) that several users will need to log in to. The database contains a table of products.
The challenge is, every user needs to only see a subset of these products and never see the whole list.
At the moment i have some code to modify an existing query based on the logged in user's details. As they log in, some tempvars are created and these are used to modify the query criteria.
This works well when the first user logs in, but the moment the next user logs in, the query is modified again and the product list refreshes and now his products are shown and not the first users! Im thinking i need to dynamically create a permanent query for each user on log in?
Here is my code so far:
Button on login form has the following code that collects the user's details
Code:
Private Sub cmdLoginMine_Click()
Dim ID as long, strEmpName as string,strZondsc as string,strgrpdsc as string
ID = DLookup("ID", "Employees", "Login='" & Me.txtUser.Value & "'")
strEmpName = DLookup("FullName", "Employees", "Login='" & Me.txtUser.Value & "'")
strgrpdsc = DLookup("MyGrpdscs", "Employees", "Login='" & Me.txtUser.Value & "'")
strzondsc = DLookup("MyZondscs", "Employees", "Login='" & Me.txtUser.Value & "'")
TempVars.Add "tmpEmployeeID", ID
TempVars.Add "tmpEmployeeName", txtUser.Value
I then call a function that modifies the existing query, populating it with this users details for the criteria
Code:
qryEdit strgrpdsc, strzondsc, ID
Sub qryEdit(strgrpdsc As String, strzondsc As String, ID As Long)
Dim qdf As DAO.QueryDef
Dim qdfOLD As String
Set qdf = CurrentDb.QueryDefs("InventoryQryforDS")
[Code] ....
The results of the query are shown on a form, which is what is currently requerying and showing the wrong data. The data is shown on a form, linked to one of the new style navigation buttons as shown.There cord source property of the form is the query that's populated as described above.
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
hey,
i have a customer form which includes a "Country" combo box.. the reason i did this is because i have a separate table with a list of countries and their VAT (Value Added Tax, you americans call it sales tax i think :)) rates.
i want this country saved in the customers table.. i need the country table to act merely as a source of data. Now ive spent a good hour tying to fix it messing around with the relationship between the two tables and the combo box itself.. but ive only been able to come to two end results
a) access tells me i cant save the new customer record cause a related record is required in the country table.
b) access enters a new line in my country table with the autonumber of the country selected on the add new customer form in the Country field.
im stumped as to where the problem is:// how can i fix it?
How to determine if a field within a table is a primary key or part of a compound key using ADO?
View 1 Replies View RelatedIn a form, the value of any field may determine if the other field will be true or false. For example in my form, inventory, if value in code is equal to 2 then the Field Table will be automatically false.
View 12 Replies View RelatedI have sequence field in query and want it show in form.
Please help me. Thanks
How do I determine which forms, reports or other queries use a specific query in my entire database?
Thanks in advance.
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
Can someone see where I’m going wrong?
Slaughter
slaughter at mizzou dot edu
I would like to know how to do the following:
Query 'X' is a sum query.
How do I determine which other queries use Query 'X'
I want to make some changes to Query 'X' but I want to be sure of which other queries will be affected.
Thanks
I have a database about certain accounting datas from week by week and it's growing. I should make a Crosstab query for see the amounts weekly, it's will be exported to an excel workbook, wich have macro's(this is the problem, because the exported field will be bigger week by week). The difficulty of this query is the future weeks. I want to see all of the weeks in the columns. I made a table wich contains the weeks(Hetek_1.CW) wich I want to see, and the source is also contains the items accounting weeks.
Code:
TRANSFORM Sum(CWall_hetekkel.[Knyv# ssz# kltsg]) AS [SumOfKnyv# ssz# kltsg]
SELECT CWall_hetekkel.[Ktgh# kdja], Oka.Oka
FROM Oka RIGHT JOIN CWall_hetekkel ON Oka.Oka = CWall_hetekkel.Oka
WHERE (((CWall_hetekkel.[Ktgh# kdja])=1250 Or (CWall_hetekkel.[Ktgh# kdja])=1251 Or (CWall_hetekkel.[Ktgh# kdja])=1252 Or (CWall_hetekkel.[Ktgh# kdja])=1253))
GROUP BY CWall_hetekkel.[Ktgh# kdja], Oka.Oka
PIVOT CWall_hetekkel.CW;
I have a form that shows employee license information. I also have a query & form based on that query to show licenses currently suspended. I would like to on my employees form to flash "license suspended" label if their name/record id is on the license suspension query. For all employees whose license isn't suspensed I would don't want anything to show. I can get the label to flash and to appear. I just need help evaluating the query and comparing it to the current record. Example I
if me.id = queryname.id then....execute label flashing, etc.
Can I write code to do this? Thank you
I have a table that consist of pricing for different types of products:
Table name: Costs
ID TYPE EastNew EastRenew WestNew WestRenew
1 500 4100 1000 4400 900
2 501 4100 1000 4400 900
3 600 3400 900 4600 1200
4 700 3700 1300 4900 3300
I have a form that consist of many fields that the user needs to manually input data. 3 fields on that form are the criteria i need to determine which pricing to use back on the table. The first field, which name is LTYPE, is a combobox that the user needs to choose as its type (i.e 500,501,600,700). The second field, which name is EastCoast, is a checkbox (Yes/No) that tells me if it's East Coast (East = yes and West = No). The third field, which name is NewRenewal, is a combobox that the user chooses NEW or Renewal).
Example 1, if the user chooses type 501, checkbox is YES for East, Product is NEW. Then the pricing is $4,100
Example 2, if the user chooses type 700, checkbox is unchecked for NO (which is West), Product is Renewal. Then pricing is $3,300
I would like the unbound field to auto determine the pricing based on the 3 criterias chosen. I read around the forums that Dlookup would be the function for this, however, i tried many times but it did not work.
In forms, I frequenty use the following expression to get the results needed from a field's column:
=EmployeeID.column(1).
However, when I try to use the column function in a query it doesn't like it:
EmployeeName:[EmployeeID].Column(1)
What am I doing something wrong?
Along the same line, if I want to use =EmployeeID.column(1) for an unbound control in a form, why must you put the bound field on the form just to get the info? In this case, I want to do this to display the employee's name - not the primary key - without the scroll bar. Every time I do this, Access performance analyser tells me to use fewer controls, but you can't if you have to have the reference. The same thing seems to be required in queries, i.e. include the unaltered control to get an expression.
Help and advise, as always, will be appreciated!
Christine
I have three tables for an event registration database based on Microsoft’s Event Registration template. The main tables are “Attendees”, “Registrations” and “Events”. “Registrations” is a swing table. Is there any way of creating a query to determine which attendees have not registered for a specific event? I have included a graphic of the table layout. I sure would appreciate some help on this. Thanks.
View 1 Replies View RelatedI am making a query and I need to be able to determine if two number fields are with in 2.5% of each other. How would be the best way to go about doing this. I have two distances and I need to display them if there is a differenece of greater than 2.5% in them.
View 3 Replies View RelatedAll of the types? Including Memo?
View 2 Replies View RelatedI have a query that runs just fine and takes about 1 minute to run on average. I have vba code that uses Dlookup to determine if the query returns any records. The problem is that each time it runs it has to crunch all the data, all I care about is if there is any result at all. Is there a way to get the query to stop after it finds one record to speed things up greatly?
View 2 Replies View RelatedRunning 2007
I never ran into this problem before.
I have a qry, with showing totals (group by). One of the fields is called comments, which is a memo field. I am only seeing partial comments.
When I redesign the query and take out the "totals", I see the entire comments.
Is there a fix to this?
I have a simple query between two tables joined together by common fields. In my first table (Table 1 - tblLocations) I have information about a building i.e. Location Code, address and total sqft. . In my second table (Table 2 - tblAllocatedSpace), this contains details (Location Code, Room ID, SqFt assigned, etc.) of the space allocated in each building. The two tables are joined together when the “Location Code” in both table match.
In my query, I show the location detail from (Table 1 - tblLocations) and related records from (Table 2 - tblAllocatedSpace). My result looks like the following:
Location Code Sqft Address Assigned Sqft
106067 1,000 600 March Rd 10
106067 1,000 600 March Rd 15
106067 1,000 600 March Rd 12
106067 1,000 600 March Rd 20
The location code, Address and Sqft is rebated each time a space is assigned in (Table 2 – tblAllocatedSpace). When a build a report and need to sum the location Sqft, the number is multiplied by the number of related records in (Table 2 – tblAllocatedSpace). In this example by building total space is 4,000 sqft when I only it to show 1,000.
How do I set to only show the location code and sqft once?
I am looking to get a query to show my list of customers "Grouped By" [CustomerName], that show only the single [CurrentBalance] field for each customer based on the "Last or Highest" [RecordID].
Also, each customer can have up to 4 different [StockType]'s but at least 1 [StockType].So my results would look like this:
[CustomerName] - [StockType] - [CurrentBalance]
Customer#1 StockType#1 5
Customer#1 StockType#2 4
Customer#2 StockType#1 5
Customer#3 StockType#1 5