Pivotables, Date Controls And Other Stuff
Mar 11, 2008
Can someone please, please help me on here?
If there are any aspiring `idiot´s guide` writers out there, now is the time to put some practice in!
I have almost completed my database, I just need to get the reporting section done.
I need to produce a monthly report on our casework which basically means totalling certain fields each month, which are exportable to access so I can produce comparison graphs.
I know that you cannot ´total´ text fields except for when you produce a form based on a pivotable. Is there anyway of producing a pivotable from a user-selected date range, and then being able to export that pivotable to Excel for the monthly report? Also, can I put a user-selected field filter in the pivotable?
So for example, my user wants to know how many cases there were for each subject in the month of March i.e. how many health enquiries, how many benefit enquiries, how many pension enquiries and so on. Each of these different types of enquiry are coded in a field-list called ´subject´. Therefore, can I produce a pivotable based on the selection of a date range and the field called ´subject´?
I am against a desperate deadline to get this done now. If anyone can spend time spelling this out in little words for me that would be great. This is the first time I´ve used Access and I ain´t done half bad, but this is completely stumping me!
Thanks in advance.
View Replies
ADVERTISEMENT
Oct 16, 2006
In trying to write code to automatically hide database window I have completedly stffed up and now have code that closes the database window everytime I startup, hence closing the database.
Does anyone know how to disable VBA or somehow keep the database open??
View 1 Replies
View Related
Oct 25, 2005
Recently I've been having a problem with Access (2003) that I can't seem to get around. If I click on "NEW" to create a new query, do some editing, then go to close, it prompts me as to whether or not I want to save, which would be ok, if it would let me choose NO!!
I only get OK or Cancel options. I've NEVER had this problem before.
I have not done any updates added any addons. Is there some hidden trick here or some way to get around this cause its really annoying.
TIA,
Tim
View 3 Replies
View Related
Dec 5, 2005
Hey all,
I am building a database to help my unit in Iraq. Here are the fields I have so far.
Field 1: Date
Field 2: Time
Field 3: Location
Field4: SIGACT (Significant Act)
Well that is the basics look like. I would like to be able to count the number of times a type of SIGACT occured in durring the day, week, and month. I would like to have this come out in a report I could then take to Excel and graph. The SIGACT has a couple different options like IED, and SAF. Thanks for any help you can provide
View 5 Replies
View Related
Mar 21, 2007
trying to join two tables but get message
"relationship must be on the same number of fields with the same data types"
data types are same, numbers.
Primary table has just jobnoID that are all unique (primary table)
ie 6907, 6908, 6909 etc
second table "jobs by order" (secondary table)
has
unique orderno(order numbers) in column one say 69071 and 69072, 69073
and jobno in second coloumn say 6907, 6907, 6907 for all the above.
trying to link JobnoID primary table (one to many) to Jobno secondary table (many)
Also need to note. This database is a purchase order database. so am trying to load in all past data from excel. data is in already. so primary table above with jobno is an auto number so will generate the new jobno for us. but had to copy and paste blank fields into table to get records upto the number we are upto now, ie job number 7112. I have only put data in secondary table for
jobno's 6885 through to 7112, and even some of these have blank spaces.
Any ideas?
Also how do i do a screen capture and dump in here so you can see relationships etc, which would be a whole lot easier to explain. thanks heaps
Alastair:rolleyes:
View 4 Replies
View Related
Mar 23, 2006
I have a table (Master) that contains columns such as MsgLocation, MsgName, MsgScript, and MsgTxt. I want to create a form where the user would have an area to type in a 'string' and when they hit enter (or select a button), a (background) query would execute a lookup on master "where MsgTxt LIKE %string%"... For example, if a user wants a list of all MsgTxt values that contain "Please hold" they would type in a box "Please hold" and the query would use %Please hold% in the search criteria.
I don't have a clue on the first thing to do to get this done. And I'm new at this...
Thanks!!!!
Mary :)
View 9 Replies
View Related
Aug 26, 2004
I am trying to create a Switchboard of my own that will do everything the database window will, but restrict certain users from accessing certain areas of the switchboard. The only part i am needing assistance on is Adding, Deleting, Modifying and Running my database items from the form.
I already have used that MSysObjects code to create list boxes for my Tables, Queries, Forms and Reports. Now, I'd like to setup 4 custom command buttons on the form that say:
Code:Private Sub AddTable_Click () [listTables.Value = New Table, prompt user for standard New table wizard]End Sub Private Sub DeleteTable_Click () [listTables.Value = Delete this table (with normal "Are you sure" prompts)]End Sub Private Sub ModifyTable_Click () [listTables.Value = Open this table in Design view]End Sub Private Sub OpenTable_Click () [listTables.Value = Open this table in Datasheet view]End Sub
Now, I know that this code is by far not the correct code, but I hope it helps you understand what I'd like to do. Basically, I want the button to correspond with whatever is selected in the list box for that item (except for the add button, it really doesn't matter what the listbox says when the add button is pushed). As far as my list boxes go, i'd like 2 functions for it to do:
1. If a table/query/etc. is double clicked, that item is opened.
2. Allows multiple selections to delete. (i.e. the user can select multiple tables and click the Delete button, and all the tables that are selected are deleted.)
Let me know if all of this can be done from my form. It would be great to get all this working by tomorrow. I appreciate all of aspfree's users help. You guys rock!
View 3 Replies
View Related
Nov 22, 2005
I am trying to add a 'keypad' to an access form. The aim is to have an entry system similar to the tills you get in some pubs where when you click on the relevant number it enters that number in the selected field on the form.
I know this is probably a basic question for most of you but your help would be very much appreciated.
Derf
View 2 Replies
View Related
Jan 16, 2006
we run a family business and i do all the quotations.
i have an access database in which i record these quotations and whether or not we got the job or not, and i have to input all the data manually.
i wondered if i could set a word document so that it would automatically export certain areas of the quotation (like the date, who it was to, what work we are quoting for, how much it would cost, etc) into the correct fields in my database.
can it be done??
any replies will be much appreciated.
thanks
View 5 Replies
View Related
Sep 5, 2013
It basically is used to create, manage and track Customers, Parts, Work Orders and Suppliers, payments and employees.You simply create a Customer and then create a Work order that contains a list of parts and labour items.
What I want to do is to create a "Kit" of parts so that the user does not have to select each part individually when they create a Work Order. For example, if I use a mechanics workshop as an example, the mechanic may sell his "Signature 1916cc Engine" that consists of 40 different parts and some other items such as labour and other services. In this scenario, instead of the user individually selecting 40 different parts, all he needs to do is select the Kit called "Signature 1916 cc Engine" from a drop down list and all of the 40 parts and other items would automatically populate the correct Work Order Parts/Labour tables.
I would also like the user to be able to select another "kit" that would either replace all parts and labour records or append to what is already there.
I have changed the form called "Workorders" to have 2 buttons. One called "Load Kit" the other called "Save Kit".If the user presses "load Kit" a form will be displayed that enables them to select a "kit" from a list and whether they want to replace all of the existing parts and labour records or if they want to append to the existing records.
In order to create a new kit, the user can simply manually create a new work order by individually selecting each part and labour item and then pressing the "Save Kit" button. The user will then be asked to provide the short name of the Kit and the long description (I have created table called tKit). The new kit will then be saved.
It appears that the "Entered by" field that is linked to the Employee table must not be blank other wise the Workbook is NOT saved....I have tried to figure out why this is happening but can't!
The Work Order Parts are stored in "Work Order Parts" and the Work Order Labor is stored in a table called "Work Order Labor". I have created 2 extra tables called "tKitWorkOrderParts" and "tKitWorkOrderLabor" that I assume will contain the items that make up the kits..
View 14 Replies
View Related
Apr 21, 2005
I need create controls with Controls.Add in one forms on event LOAD.
View 2 Replies
View Related
May 11, 2005
I have a form in my Access database that after trying to add another field to it today, I get the error "Microsoft Access can't create any more controls on this form or report."
I'm guessing I reached some type of limit that I was not aware of. How can I get around this?
View 10 Replies
View Related
Jul 19, 2005
I read somewhere that you can have more than one row of tab comtrols on a form. I have searched the forum for any threads or posts on the issue but can't find anything. Does anyone know how to do it?
View 2 Replies
View Related
Aug 17, 2005
is it actually possible to place a tab control within another tab control? every time i try to put one on it ends up beneath? would be good to have because even with one tab control my forms still look very busy!!
View 1 Replies
View Related
Jan 27, 2005
Is it possible to put a tab control on a page of a tab control?
And if so, how?
Everytime I try to fix another tab control on an already existing tab control, it places it on the form, not the control.
View 1 Replies
View Related
Oct 3, 2005
I have placed a tab control within a tab control (which I have done before). The problem is that the sub tab control appears on every page of the main tab form (encountered this before). I fixed it in the past by placing code on the on open event to make the sub tab control not visible unless the appropriate tab is selected. That worked fine.
I have tried this in the new program and find that when I set the value of the main tab form to 4 (the page index of the tab) the sub tab form will not display at all. However if I set it to zero it will display regardless of which tab is selected. Confusion has now set in :confused: . Any thoughts out there on what I have done incorrectly.
[/CODE]Private Sub Form_Open(Cancel As Integer)
'Me.OrderBy = "RankNo desc"
'Me.OrderByOn = True
If TabCtl44.Value = 4 Then
TabCtl144.Visible = True
Else
TabCtl144.Visible = False
End If
End Sub[CODE]
View 5 Replies
View Related
Jan 24, 2005
I have a main form which shows customer personal details including their budget and a subform which shows the costs for each course they book. The sub form has 4 boxes which I use to capture course fee, travel fees,subsistance and other expenses (along with other details such as course code etc). These cost details are stored in the table study_leave_recs in the respective 4 fields. I show on the subform the total spend for each course being booked using a calculated control. On the main form I use four dsum statements added together to show the total spend per customer.
=DSum("[COUR_ACT_FEE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_TRAVEL]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_SUBSISTANCE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")++DSum("[COUR_ACT_OTHER_EXPEN]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")
It works ok but is there a better way of doing this? Also is there any way I can use the resultant output to calculate / show the remaining budget (the budget is held in the general table "people"? I'm having trouble using the above statement to subtract the calculated spend from the budget box shown in the main form!!
I would appreciate any help, I seem to have spent ages on this on! Thanks in advance. Regards Peter
View 3 Replies
View Related
Jan 28, 2005
I wanna know if it's possible to use an access 2002 form to create some controls(icons) on top of a picture (a map) so that i can click those icons to display some info. The trick part is that those icons positions are generated by x,y coordinates from the db itself. Plus a zooming control wold be a plus...
So what are your opinions on my little project.
Thank you all in advance.
JT
View 1 Replies
View Related
Apr 19, 2005
Hi,
I have a pretty standard form to enter data about contracts. Depending on the type of contract, the user will answer a yes/no type question, and the result will determine what set of information needs to be entered next. Since the variables are grouped intrinsically, I would like to make them visible or not depending on what is necessary. I know how to do this with just one control, but not several at a time. Is there a way to treat them as a group, and have the form make the group appear/disappear as needed, or do I have to list out each control everytime i need to do this? Any help would be much appreciated.
Carsie
View 2 Replies
View Related
May 3, 2005
I have application made in MS Access 2000
I got crazy with one Active X Control on one PC. On every other PC works fine but on this one I have error "Object is missing or does not exist".
I tried delete - copy - paste couple times, unregister and register control couple time, but error is still there.
Reference looks fine, I put control on the form and everything looks great.
I tried delete control from the form and put again.
When I try to assign some data to the control I have error "Object is missing or does not exists".
It's look like my Access programm see just as read only or something like that.
Does anybody have idea why my Active X Control does not work on this PC?
View 2 Replies
View Related
May 18, 2005
I am currently using a popup Calendar in Access "Calendar Control 9". I mousedown on a field and the Calendar pops-up. I select the date and it populates field1. So far, so good. After I select the date I need I would like for field2 to populate with a date that is exactly six months out from the date I selected for field1. Any suggestions.
I hope this can be done as it would help immensely.
View 1 Replies
View Related
May 28, 2005
Hi guys,
please give me some directions.
Recently I designed a form with several controls including both TextBox and Combox. Before I submit data by clicking the button, I use VBA to validate the values of those controls (textbox and combox). If the value is not valid, the system will eject a msgbox and automatically set the focus on that component. In order to tidy the code, I use a GoTo statement, which refers to a group of code to display the message due to the value of parameters
But it seems VBA doesn't recongize the control I set through the parameter.
The code as the following:
private function validation () as boolean
Dim ErrorMessage as string
Dim ErrorComponent as String
......
ErrorMessage="Please select the shop Name"
ErrorComponent="ShopName" // ShopNameis a combox
GoTo ExitFunction
.....
......
ErrorMessage="Please set the start date"
ErrorComponent="StartDate" // StartDate is a Textbox
GoTo ExitFunction
.....
ExitFunction:
msgResult = MsgBox(ErrorMessage, vbOKOnly, "Error Message")
Me(ErrorComponent).SetFocus
validation = False
Exit Function
=============
As I tested for seveal time, I'm sure there is something wrong with
"
msgResult = MsgBox(ErrorMessage, vbOKOnly, "Error Message")
Me(ErrorComponent).SetFocus
"
but how can I fix it?
Many Thanks
View 4 Replies
View Related
Jun 2, 2005
Hello,
I want to use a tab form where the user can click add, search, and edit records all from 3 tabs. The problem is I only want to be able to add data on one form, edit data on another, and just read only on the search. This seems to cause a dilemma, how can I specifiy them to add, edit, and read only on one form using tabs?
Mateo
View 1 Replies
View Related
Sep 2, 2005
Hey, I was messing about with tools>startup because I wanted a form to appear whent he database was opened.... I unchecked far too many options in there, and now I cannot design and modify my database anymore... it just goes straight to the form, but i cannot get anything back in design view.. is there a way to fix this??
View 2 Replies
View Related
Dec 15, 2005
In my program I have a tab control with three tabs in it, how can I tell which tab currently has focus (which tab is activated) in VB? I checked all the properties of the tabs, and there's no GetFocus or Value or anything like that, so how do I do it?
View 3 Replies
View Related
Aug 29, 2006
I am new to Access and have not used VBA.
I have searched the forum on this, but I can't find an example of this problem.
I have a data entry form with a two-page tab control. One is for company info and the other for contact info. The recordsource is a query based on the two tables. Initially I had the page set up as a rather large form with a subform and decided to convert to the cleaner looking tab control (I used copy and paste to transfer the controls).
This form worked initially, but now I have changed some setting so that it doesn't work anymore: I can enter data properly on the first tab, but not on the second.
I have tried changing the various form properties but I must be missing something obvious. All my tab-less forms work the way I expect them to.
What I don't understand is why one tab works and not the other.
View 14 Replies
View Related