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

VBA Stuff Up

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

Wierd Stuff..

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

Counting Records And Other Fun Stuff

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

Real Basic Stuff I Cant Get Help

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

I'm A Newbie On This Access Stuff...

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

Help With Msys Stuff And Forms

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

Making Buttons Do Fancy(?) Stuff

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

Can I Auto Export Stuff From Word To Access

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

Forms :: Copying Multiple Records From One Table To Another And Other Stuff

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

Controls.Add

Apr 21, 2005

I need create controls with Controls.Add in one forms on event LOAD.

View 2 Replies View Related

Too Many Controls??

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

More Than One Row Of Tab Controls?

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

Two Tab Controls

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

Tab Controls

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

Nested Tab Controls

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

Calculated Controls

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

Creating A Map With Some Controls In It

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

Is It Possible To Group Controls?

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

Active X Controls

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

Calendar Controls

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

Form Controls

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

Tab Controls - Two Forms

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

Startup Controls

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

Question About Tab Controls

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

Form With Two Tab Controls

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







Copyrights 2005-15 www.BigResource.com, All rights reserved