UtterAccess.com Access Forums UtterAccess Access Forums http://www.utteraccess.com/forum/home.html Fri, 30 Jul 2010 12:23:29 -0400 15 <![CDATA[Printing "DUPLICATE COPY" on a report]]> http://www.utteraccess.com/forum/Printing-DUPLICATE-COPY-t1949640.html
]]> Fri, 30 Jul 2010 12:18:12 -0400 http://www.utteraccess.com/forum/Printing-DUPLICATE-COPY-t1949640.html Autonumber ID field jumped 8 figures http://www.utteraccess.com/forum/Autonumber-ID-field-jumpe-t1949639.html
I have a table of people that has about 8500 names and the Autonumber ID field has been fine until this week. Users are added on a regular basis and usually the ID justs increments by 1.
I just noticed that the last 10 or so people have ID numbers of 8 figures (46208554 for example) assigned instead of the usual (around 8500).

I have been doing maintenance on the DB but don't remember anything about this table.

Any ideas on how this could have occurred and whether it will pose a problem? I have related records in other tables so not sure how easy it is to change the numbers down lower again.

Thanks fo rany help in advance,

Julia]]> Fri, 30 Jul 2010 11:31:33 -0400 http://www.utteraccess.com/forum/Autonumber-ID-field-jumpe-t1949639.html Setup help http://www.utteraccess.com/forum/Setup-t1949638.html
Each Contract has many Task Orders under it. Every Task Order Number will fall under a Contract Number, but every Contract Number may not have Task Orders under it.
Basically, the Contract is between McDonalds and my company and states that we will provide services to McDonalds for 5 years. A Task Order might be to supply 6 drive-thru workers, 4 fry cooks, and a manager to supervise them to 2 local McDonalds restaurants for 3 months.

The drive thru workers and fry cooks are Laborers within the Contract or Task Order, and are a different matter entirely. However, a Contact is the person in my office that is overseeing the entire Task Order. The McDonalds manager at each location who is overseeing our employees on thier end is also a contact. The McDonalds executive overseeing the Contract is a contact as well.

Right now, I have one table for Contact (tblContacts) which contains fields such as FName, LName, Title (at thier company, not as related to the Contract or Task Order), Phone Number, Email. I did it this way because whether the contact works for us or the contract client, the same data is needed. In addition, many contacts could serve in multiple positions in multiple Contracts or Task Orders, particularly our employees.
I also have a separate table for Contact Type (tblContactTypes), which is a listing of positions the Contact could hold in a Contract or Task Order (such as Contract Manager, Project Manager, Site Lead, ect).

When I add a new Contract or Task Order, I need to be able to also add a Contact (from a list- if they are not in the list the user will be prompted to "add Contact"), and then choose thier Contact Type (a second list), and tie these together (contact and position within this contract) with the Contract Number.

What I would like is when a user views a specific Contract or Task Order, I can set that form up where there is a subform that contains each contact associated with the Contract or Task Order, along with thier phone, company, and Contract/Task Order position (Contact Type). I will do this by creating a query with the fields I want and the criteria being the Contract number (which is not the primary key for tblContracts, but is a unique number).


I'm not sure if I should have the user add contact information as part of frmAddContract or frmAddTaskOrder (as noted above), or if I should have the user pull up contacts separately, and select the contract number they are assigned to, and then select thier "contracttype" as associated with that specific contract number.

I am also not even sure if I need to do this- if I want to know who the project manager was on this contract, I could just "view" the contract and have the data available. I could also query this data with the contract number as criteria as mentioned above. However, if I ever wanted to know what contracts and positions a person has served on/in, I believe I would have to have this data saved somewhere in my tables.

As I'm thinking this through and testing things in my db, I would appreciate any feedback or thoughts anyone has as to this setup.]]> Fri, 30 Jul 2010 11:26:54 -0400 http://www.utteraccess.com/forum/Setup-t1949638.html DCount to include blank fields? http://www.utteraccess.com/forum/DCount-include-blank-fie-t1949637.html
I want t display a quick DCount on our form. I want it to count the number of records where the status of "quotestatus" is set to one option, or is left blank.

I have this so far but it is not counting the blanks.. just the ones set to the option.

CODE
=DCount("[Quote]","Jobs","[QuoteStatus]='Low Bid - Order Pending' OR [QuoteStatus]=''")


Not sure what I'm missing, I tried various combinations of the above but it never returns more than the number of "Low Bid.." status'.]]> Fri, 30 Jul 2010 11:23:11 -0400 http://www.utteraccess.com/forum/DCount-include-blank-fie-t1949637.html Counting Problem http://www.utteraccess.com/forum/Counting-Problem-t1949636.html
For example: One address appears in the final union query 7 times. However when I do a count of the address, each time the address appears instead of 7 appearing as the count#, a 1 appears instead. I have attempted to use my count query to link the union query, but it only works on the fields with relationships in the logic flow. IE. First Logic only requires the name and address to match, so a count total on those works, but the ssn count returns the total for the entire original table not just the final results.

I am assuming the reason is due to how many queries it is taking to build the final union query. I have three logic flows that make up 3 separate results which are put into the union query. dazed.gif

So let me give you what i've got so you know exactly what i'm dealing with here.

I have a count query that counts totals of Name, Address, & Unique Key of table Test Orders.

Then i have my logic flows that build off of this count query as follows:

First Logic: Step 1, First Logic: Step 2, First Logic: Results
Second Logic: Step 1, Second Logic: Step 2, Second Logic: Results
Third Logic: Step 1, Third Logic: Step 2, Third Logic: Results

Then i have a union query that combines the three results queries to remove any duplicates.

In excel this would be a piece of cake, just a =countif(columnname:columnname, currentcell), but in access I know of the IIF function, but is there anyway to get it to count without specifying a specific text or # to count?

Any ideas?]]> Fri, 30 Jul 2010 11:17:38 -0400 http://www.utteraccess.com/forum/Counting-Problem-t1949636.html Update Rank Field with Number http://www.utteraccess.com/forum/Update-Rank-Field-Number-t1949635.html
I saw a little on the web on this topic but nothing that was straight forward, or I did not think of what to search on. I have two links to an excel document with two worksheets. The results are already in descending order.

Link one (Worksheet 1 - This year)

Client A 3000
Client B 2000
Client C 1000

Link two (Worksheet 2 - Last year)

Client C 4000
Client B 3000
Client A 2000

I need to compare ranks from this year to last year for client fees.

Query Results:

Rank 1 Rank 2 Client This Year Last Year
1 3 Client A 3000 2000
2 2 Client B 2000 3000
3 1 Client C 1000 4000

Currently I add a column in Excel in each worksheet where I drag down the numbers and save and it works perfectly, but that is not elegant. Is there a way to insert these into two Access tables (without bringing in the ranks) and update the rank fields accordingly in Access itself?

Please note that there are actually more comparisions so I would prefer these ranks actually updated in the fields rather than doing something fancy in various reports. All my queries work once the ranks fields are there.

Please assist.]]> Fri, 30 Jul 2010 10:55:30 -0400 http://www.utteraccess.com/forum/Update-Rank-Field-Number-t1949635.html have calculated elapsed time - now need to total elapsed time http://www.utteraccess.com/forum/calculated-elapsed-time-t1949634.html The report uses an Instant If to display the elapsed time for each event grouped by the person participating.

I need to total the elapsed time and have it displayed as HH:MM.

I have a Running Total box in the group footer, but it's not giving me accurate totals.

Here's an example:
Name date start end elapsed
Doe, John 1/1/10 1000 1100 1:00
1/2/10 1415 1430 0:15

total 0:15

Next Group

My Running Sum is set to Overall.
What is the secret to adding time?
Thanks in advance!

Erick]]> Fri, 30 Jul 2010 10:53:13 -0400 http://www.utteraccess.com/forum/calculated-elapsed-time-t1949634.html pseudo Subform in a continuous form http://www.utteraccess.com/forum/pseudo-Subform-continuo-t1949633.html
The only way to give the desired overview is to use a continuous form. For each order item, there may be several despatch note references which I would like on a subform but i cannot put subforms into continuous forms. However, the following data can be displayed.

Item Description Order Qty Price DeliveryNoteNr Delivered Qty Invoice Qty
1 Small Widget 100 2.50 68 80 78
1 Small Widget 100 2.50 78 20 20
2 Large Widget 100 3.50 68 40 40
2 Large Widget 100 3.50 78 20 20
2 Large Widget 100 3.50 91 40 40

Is there a way of hiding the text boxes in red (based upon the item number being the same as the row before) and only displaying them where it is a new item number. I also would like a dividing line only where there is a change in item number.

This would give the appearance of a sub form without repeating the description many times which, although functional, looks terrible!

Any suggestions welcome.

Regards

John]]> Fri, 30 Jul 2010 10:48:04 -0400 http://www.utteraccess.com/forum/pseudo-Subform-continuo-t1949633.html <![CDATA[MySQL & Access Required Fields]]> http://www.utteraccess.com/forum/MySQL-and-Access-Required-F-t1949632.html In Access if I design view the linked table it lists some fields as required, seemingly without rhyme or reason.
I never set these fields specifically as required in MySQL so I do not know why they are showing up.
Does anyone know a way to get Access to stop thinking these fields are required?
Or to mark them as Non Required in MySQL if I somehow marked them inadvertently?
MS Access 2007 and MySQL 3.1.
Thanks!
]]> Fri, 30 Jul 2010 10:40:44 -0400 http://www.utteraccess.com/forum/MySQL-and-Access-Required-F-t1949632.html Date Picker http://www.utteraccess.com/forum/Date-Picker-t1949631.html Fri, 30 Jul 2010 10:22:37 -0400 http://www.utteraccess.com/forum/Date-Picker-t1949631.html Formatting report with multiple subreports for print or pdf http://www.utteraccess.com/forum/Formatting-report-multip-t1949630.html
I have created a report with 4 subreports. I have nested the subreports, one within the other, as Access doesn't seem to like Growing/Shrinking subreports properly on a consistent basis, and I end up with subreport overlap.
Now, the Report looks great in Report View, but when I attempt to print or export the report to a .pdf, the formatting changes drastically.

The Main Report is empty. It is only used to house the 4 sub reports. They are: Daily Issues, Active Issues, Recent Accomplishments, Upcoming Events. Each subreport is based upon a query that returns the list of Issues that correspond to the subreport criteria.
They are nested in reverse order so they display properly.......

The Upcoming Events subreport is nested in the Details section of the Recent Accomplishments subreport.
Recent Accomplishments is nested within the Details section of Active Issues.
Active Issues is nested within the Details section of Daily Issues.
Daily Issues is located at the top of the Main Report's Details section.

All subreports are set to Can Grow/ Can Shrink.
In Report View, Daily Issues are listed above Active Issues, which are above Recent Accomplishments, which is followed by Upcoming Events.

However, when I change to Print Preview, or if I export the report as a .pdf file, everything gets skewed.
The Daily Issues subreport is listed alone on page 1. (It is currently only 2 lines of text.)
Active Issues is listed alone on page 2. (Currently 19 rows of text)
Recent Accomplishments spans 3 pages. (100+ lines of text)
Upcoming Events is listed immediately following the Recent Accomplishments. (4 lines of text)
(Why didn't Upcoming events get shifted to it's own page?)

When all is said and done, I do not want to segregate the subreports in this manner....it should be one report that lists the sections one immediately behind the other.

Any ideas?
]]> Fri, 30 Jul 2010 10:10:03 -0400 http://www.utteraccess.com/forum/Formatting-report-multip-t1949630.html Code to lock project http://www.utteraccess.com/forum/Code-lock-project-t1949629.html
Hello,

Using Access ’03…

I recently built a form object for a series of preexisting mdb files. These files are located at various satellite locations and I have no access to them and they cannot be replaced.

I plan on sending this form to users via e-mail or on a CD; they simply open my mdb (containing a single form) and export the form to their mdb.

My problem, however, is that I’d like to lock the VBA code!

If I ship the form with the VBA locked, the users cannot export the form. Also, as you know, you cannot export a form from an mde file.

The only workaround that I can think of is to set a password in the properties of the database and then (in the form’s code) set the locked property to true when the user executes the form.

The user will have access to the VBA until the form is opened, but that’s better than nothing.

Any thought?
alex
]]> Fri, 30 Jul 2010 09:43:30 -0400 http://www.utteraccess.com/forum/Code-lock-project-t1949629.html up level button for OLE Web Browser Active X Control http://www.utteraccess.com/forum/level-button-OLE-Web-Br-t1949628.html I have an ActiveX control on my form. The control is the Microsoft Web Browser. It opens the windows explorer with the project file folder. Can I add a button on the form that will navigate up a level in the control? Fri, 30 Jul 2010 09:43:05 -0400 http://www.utteraccess.com/forum/level-button-OLE-Web-Br-t1949628.html installing 2010 http://www.utteraccess.com/forum/installing-2010-t1949627.html
I keep running into error messages and the program will not load.

Any ideas?

g.j.m.]]> Fri, 30 Jul 2010 09:40:46 -0400 http://www.utteraccess.com/forum/installing-2010-t1949627.html calculating controls http://www.utteraccess.com/forum/calculating-controls-t1949626.html
In the form footer, I have a control which has a formula on it called which reads "=count([nd])"

Up until recently I would get the count of the records now it only shows "error"

I also get the same message on another control which has a formulat on it "=sum([unitsordered])

Why am I getting these error Messages?

tia]]> Fri, 30 Jul 2010 09:37:32 -0400 http://www.utteraccess.com/forum/calculating-controls-t1949626.html Normalising Relationships - Help with Multiple Options http://www.utteraccess.com/forum/Normalising-Relationships-t1949625.html
Basically, I have mutiple Update Types (such as Registration, Testing, etc). Under Update Types, I have multiple topics (for example under registration I have Idea, Documents, Business Requirements, etc). and under topics I have stages (such as completed, not required, in progress, etc).

I want only certain topics to be available for each update type. Likewise, I want only certain phases to be available for each topic.

I've been trying to figure out a way to do this, I have figured out a couple - but they aren't very practical - I want a normalised, orginized way of doing this.


Any help is much appretiated!

Thanks,

- Shane]]> Fri, 30 Jul 2010 09:30:29 -0400 http://www.utteraccess.com/forum/Normalising-Relationships-t1949625.html Detect and Repair http://www.utteraccess.com/forum/Detect-Repair-t1949624.html
TIA
PLHagan]]> Fri, 30 Jul 2010 09:26:20 -0400 http://www.utteraccess.com/forum/Detect-Repair-t1949624.html Copy field value from one for to another http://www.utteraccess.com/forum/Copy-field-t1949623.html
Private Sub Form_Load()
Me.Company = Forms("Add Customer").Controls("Company")
DoCmd.Close acForm, "Add Customer"
End Sub

If nothing else is there a way to just ignore or bypass the error message?

]]> Fri, 30 Jul 2010 09:13:54 -0400 http://www.utteraccess.com/forum/Copy-field-t1949623.html Printing only one letter from control source to report http://www.utteraccess.com/forum/Printing-letter-contro-t1949622.html
Example: I need four blocks on the report and each block contains a character from the control source.

If control source value = 1234, On report, block one contains 1, block two contains 2, block three contains 3, block four contains 4.

Your assistance is appreciated.]]> Fri, 30 Jul 2010 08:22:38 -0400 http://www.utteraccess.com/forum/Printing-letter-contro-t1949622.html Formatting a calculated date in a report http://www.utteraccess.com/forum/Formatting-calculated-da-t1949621.html
I have a text field in a report that contains a letter. In this letter is a date that is caluclated based on a control on the main switchboard.

....letter text" & " " & ([Forms]![Main Switchboard]![QueDays]+[ArrivalDate]) & "letter text.....

I want this to show up as the long date format (eg 10 July 2010) not in the default format (e.g. 13/07/2010 08:11:55).

How do I achieve this guys?

Thanks in advance

Adam]]> Fri, 30 Jul 2010 08:09:05 -0400 http://www.utteraccess.com/forum/Formatting-calculated-da-t1949621.html