Access Printing Woes.

Relationship Woes

Apr 20, 2005

The database for workers works ok from a solitary table, tblEmployees.

I want to make it a relational db.

I�ve incorporated lookup tables. But I�m lost about what to do next � and if it should be divided further.

I�ve studied many tutorials in relationships, but I don�t find one that resembles this one.

Could a relationship expert give advice please? I've included a sample, hoping this will be simplest way for you to understand the challenge.

View 14 Replies View Related

Alias Woes

Sep 25, 2007

so, I have created a working SQL statement that summarises data from a bunch of different tables (it's a stock monitoring application so it all has to do with levels of stock, numbers of parts processed etc...):

SELECT tblPPIn.BatchID,
tblPartDescriptions.DrawingNumber AS [Drawing Number],
tblOrder.IssueNumber AS [Issue Number],
tblPartDescriptions.Description AS [Description],
tblPPIn.Qty AS [Total Booked In],
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyProcessed) AS SumOfQtyProcessed FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',(SELECT Sum(tblPPProcessed.QtyProcessed) AS SumOfQtyProcessed FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)) AS [Total Qty Processed],
IIf(IsNull((SELECT Sum(tblPPOut.Qty) As sumofQtyOutPass FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'PASS';)),'0',(SELECT Sum(tblPPOut.Qty) As sumofQtyOutPass FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'PASS';)) AS [Total Good Parts Taken],
IIf(IsNull((SELECT Sum(tblPPOut.Qty) As sumofQtyOutUninspected FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'UNINSPECTED';)),'0',(SELECT Sum(tblPPOut.Qty) As sumofQtyOutUninspected FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'UNINSPECTED';)) AS [Total Uninspected Parts Taken],
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyPass) AS SumOfQtyPass FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',(SELECT Sum(tblPPProcessed.QtyPass) AS SumOfQtyPass FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)) AS [Total Passes],
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyFailEtching) + Sum(tblPPProcessed.QtyFailCutOut) + Sum(tblPPProcessed.QtyFailFlatness) + Sum(tblPPProcessed.QtyFailHandling) + Sum(tblPPProcessed.QtyFailOther) AS SumofFails FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',((SELECT Sum(tblPPProcessed.QtyFailEtching) + Sum(tblPPProcessed.QtyFailCutOut) + Sum(tblPPProcessed.QtyFailFlatness) + Sum(tblPPProcessed.QtyFailHandling) + Sum(tblPPProcessed.QtyFailOther) AS SumofFails FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;))) AS [Total Fails],
[Total Booked In]-[Total Qty Processed]-[Total Uninspected Parts Taken] AS [Total Unprocessed Parts Remaining],
[Total Passes]-[Total Good Parts Taken] AS [Total Good Parts Remaining],
[Total Passes]/[Total Qty Processed] AS [Overall Yield]
FROM tblPPIn INNER JOIN (tblPartDescriptions INNER JOIN tblOrder ON tblPartDescriptions.DrawingNumber = tblOrder.DrawingNumber) ON tblPPIn.BatchID = tblOrder.BatchID
ORDER BY tblPPIn.BatchID ASC;

don't worry about the details, it works as it is above...

now I only want to display data for batches where there are some parts left, so I've tried adding the following WHERE clause:

WHERE ([Total Good Parts Remaining] + [Total Unprocessed Parts Remaining]) <> 0

should work fine, right?
nope, we get the (all too familiar) 'Enter parameter value: Total Good Parts Remaining' (and the same for 'Total Unprocessed Parts Remaining'), despite the fact I have used these aliases in the previous SQL with no problem...

Is there any way around this or do I have to create a big-ass WHERE statement complete with the subqueries I've already used previously?

This is seriously annoying me today... any help would be massively appreciated!

cheers,
Bogzla

View 7 Replies View Related

Round Up Woes

Jan 22, 2008

Hi,

I am running a query that has this function

MyRoundedValue: Round([Bonus Total],2)

I am trying to round up the value in Bonus Total.

Currently the result shows 5140.602 and I want it to round up and show 5140.61

Is this possible? For the life of me I cannot figure it out..

Thanks

Fen How

View 1 Replies View Related

Sub Form Woes!

Mar 9, 2005

I have a subform on my main form which I can access properties etc without error, but when I try to set the sourceobject property I get an error saying it can find the object '~sq_cfrmAction~sq_csubPlanetItems'

My form is called frmAction

The subform is called subPlanetItems

The code that errors is this;

Me.subPlanetItems.SourceObject = "qryInPlanetNotInWIMS"

The query runs fine so it is not that?

Help please

View 2 Replies View Related

Username & Password Woes!

Jan 10, 2007

Hello!
I have a problem regarding using usernames and passwords to restrict access to certain tables/forms/reports etc....
I have read around the forum and various other websites but cant seem to get much relating to exactly what i am wanting to do.

I think i understand the way the access workgroup security works. But does this not just restrict users being able to view the database as read-only or with other certain rights like that? So, firstly would i be able to use this to restrict the database the way i want?

Or is there a way to create a table with names and passwords and create a form at the beginning where the user inputs their details and then according to the details gives/restricts access to parts of the database? Ideally i'd like the startup form to direct them to a different menu according to the user which gives them only the options they are allowed?

Seems a bit of a mouthfull and i sound like a complete newb but i been trying to figure this out all day!!
Thanks

View 2 Replies View Related

Importing From Excel Woes !!

Jan 11, 2007

Hi all,
I have been using Excel data in Access either by importing the sheet as a table or directly linking to the sheet with no problems in the past but today I cannot seem to get the data to import in the right way.

The column on the Excel sheet is mainly populated by numbers but some of the values have letters in them. When I discovered this I formatted the column as text and tried to directly link to the sheet but the datatype kept insisting it is number. I then decided to import the sheet as a table and although it comes in as Text instead of seeing the value A1030573 in comes in as 7.1e+009 and this goes for the values that are purely numbers.

Is this a known bug, does anyone have a solution to to this ?

Thanks in advance,
Mitch....

View 3 Replies View Related

Duplicate Entry Woes

May 30, 2005

I have a table that has (to simplify things) two fields.

[Patient_No] [ID]
BA4206 1067404
BA4206 1067405
BG1013 1067545
BG1013 1067546
BG1013 1067547
BG1111 1078432

I want to be able to count the number entries where the Patient_No is the same and output the query like this:

[Patient_No] [ID] [COUNT]
BA4206 1067404 1
BA4206 1067405 2
BG1013 1067545 1
BG1013 1067546 2
BG1013 1067547 3
BG1111 1078432 1

Hopefully this is just a simple query but for some reason i cannot get it to work.

Any help is much appreciated.

Thanks in advance

View 7 Replies View Related

Record Saving Woes

Mar 16, 2006

Ok, here is my scenario.

I have several FE's each saving in their own BE. They are all the same but for different areas of our business. Each has around 20-30 users. I recently introduced another form into the FE that has linked tables to another different BE in another folder. Each FE out there with its own BE now has this form and all of the new forms save to the same (one) BE. Ok, got it?

Now, sure its possible to have them all together and I would anticipate someone suggesting this. Its split up to keep sizes down and performance up among other reasons.

In any event, here I am. All of the new form introductions went smoothly without a hitch. The last one however is causing trouble. On submit an e-mail is sent, the user is asked if they want to submit another form, and if yes a new record is created. If no the form is closed.

E-mails are always sent, no problem. The trouble is saving the record. All except one of the different FE's I have out there in different folders save the record into the table just fine. There is one though that does not do this. The mail is sent and the program behaves normally with no errors but the record is not saved. This does not happen to all users of this FE either. There are some users that work fine and the record is saved in the BE yet other users do not save the record. You can see the .ldb appear and disappear. Our IT dept has been through the permissions time and time again and tell me they are ok. The FE and main BE are in one folder while the BE that the new form talks to is in its own folder. Remember, this works fine for several other instances, its just the one that doesn�t save the record.

I did have a chance to see the permissions the other day and it seems a mess to me. They have users in individually, as they were set up initially and the same users are also in there in a group and maybe 2 groups. The permissions look a mess to me� they really do�. but that doesn�t necessarily mean they would not work and can be blamed. I have no control over this part and it drives me nuts!

What I have done...... copied a working FE from another area into the troubled folder. No good. I have removed the new form along with its linked tables and the record is saved. Put the form and linked tables back in and it stops saving again. The code compiles just fine. I have done compact and repairs.

Any thoughts or ideas out there??? If you need more info ask. I understand what I wrote but you might not LOL

View 8 Replies View Related

ODBC Driver Woes!

Apr 29, 2005

I'm sure you've all seen this before - but, it's killing me. It's for a university project which is being demonstrated soon, and I can't get it working consistently!

When pages are loaded, I get the error -

Provider Error - 80004005, Unspecified Error /db/update_select.asp line 33 (line 33 is the connection to the database)

or

Internal Server Error 500 - Page cannot be displayed

And sometimes I get one about "Jet" "Threads", and ODBC stuff.

I'm guessing it's a driver problem, but I have no idea where to go from here! I'm running Windows 2000 Adv. Server, IIS 4 (I think) and my db is access 97 :O (I would have put a newer version on, but the CD-Rom is so old on the PC it won't take 700MB CDs! Argh.)

Any help would be greatly appreciated!

Ally
www.ally.nu

View 1 Replies View Related

Relationship Woes With Specific Query

Mar 9, 2006

I have a pretty standard relationship set up, with the following tables:

Customers: A row/Cust ID for each customer
WorkOrders: each customer can have multiple work orders (linked to Customers by CustID, individual ID is WOID)
Jobs: each work order can have multiple job records attached (linked to WorkOrders by WOID, individual ID is JobID).

As far as I can tell, they're fine and all other forms etc work, updating no problem, referrential identity is enforced etc...

Problems: When I go to make a "job allocation" query for subsequent form, I select: Customers, Work Orders and Jobs table (I then filter by location, but
this problem remains unfiltered also). They link up fine. The query isn't nonsensical either - it lets me add new data. BUT instead of showing all the customers and work orders for the location due for the location, it will ONLY display the records that have details in the Jobs table (the lowest in the relationship chain). Basically, If the jobs sections are empty or "unattempted", then NO details AT ALL will show up in the query. Which is a problem, because I want to see ALL the "unattempted" jobs in the area to allocate them, including customer data and work order numbers. I also need to have the jobs table present, so I can allocate a job date, a contractor etc.

I'm not sure what to do. As far as I can see, the set up I have IS very standard tables/relationships-wise. I have to work it out, otherwise I'm going to have to revert to some kind of 'super-table' (like in our old system) - which I am at loathe to do - I don't want to merge Work Orders and Jobs because that means a lot more typing for me.

Would cascade update in the relationships do anything? (it is checked).

I've been away from Access for awhile and I'm just drawing a blank on this one. Any help greatly appreciated. Thank you.

View 2 Replies View Related

Lookup Field / Autonumber Woes

Jun 21, 2006

So, basically my database has quite a number of lookup fields in tables as there is much repeated data, each one references to a seperate table with an autonumber field (ID) and a text field (Value)

Currently I have column one (ID) as the bound column in such cases, as this is what the guy who started me on access said to do, and it's fine until I try and export any query with such a field to Excel.

I have found, through experimentation, that setting the 2nd column (Value) as the bound column allows me to export the values rather than numbers, but I don't know if this is a sensible thing to do? (ie, I don't know what other unexpected effects this might have)

Any guidance here would be very welcome, thanks,
Bogzla

View 8 Replies View Related

Update/amend Querie Woes!

Mar 23, 2007

Hello,

I have a table exported from excel. I intially imported this in to access to form a list of due dates for services.

Every week I get and e mail with the updated version.

I am trying to figure out how to update the dates in the 1st table with the new weekly ones.

I can't get it to update. Also there may be the addition of new services as well.

Any tips would be grealty recieved.

Many thanks in advance.

View 2 Replies View Related

Null / Non Existent Values And Query Woes.

Aug 12, 2006

Hi there.

I've attached my db in the hope someone can help my head scratching.

I have got a Sales summary table with several other related table. Most notably, a table with the items in the sale and one with the costs. Because each sale might contain many different items and many different costs, I thought seperate tables were the way to go.

All I'm trying to do is make another query that gets the total sales (That's adding each line item * quantity) - (Each cost line item*it's quantity) and then finally the margin made on the deal.

However, if there are no costs or sales involved in the sale the query ignores it. (Sounds odd, but some transactions might be cost free, and some may not involve any revenue - so I have to bear it in mind).

I tried the Nz function, but It's either not what I need or I'm doing it wrong.

How do I get this to work?

Many, many thanks!

View 2 Replies View Related

Modules & VBA :: Printing Code Slowing Down Printing Reports?

Feb 25, 2014

I was having trouble just setting each report with a particular print method - for some reason they just kept forgetting their individual settings and resorting to default on the machine.

This meant reports were printing on the wrong paper, or the wrong size paper, the wrong orientation and some times refusing to print if it couldn't find the paper (which is useful in runtime as it doesn't display error messages)

So I used Reports(rpt).printer properties (I forgot where I found this) to hard code the printer properties into each print command... this meant I had to use another function to insert the variables.

So all I had to do was say:

Code:
PrintMe("Invoice","InvoiceID",iID)

and a report would print to exactly how I wanted... but it's just too slow!

See attached for full code, I have a niggling feeling it may be the function: PrinterOK, to make sure the printer exists or not.

Code:
Function PrinterOK(sPrinterName As String) As Boolean
Dim MyPrinter As Printer
PrinterOK = False
For Each MyPrinter In Printers
If MyPrinter.DeviceName = sPrinterName Then
PrinterOK = True
Exit Function
End If
Next
End Function

I know it's the printing code, because if I stop the printing and just preview then it shows up almost instantly.

View 1 Replies View Related

Printing From Access

Jul 25, 2005

Hello,
I have one machine that gets this error when trying to print or e-mail a report:
the microsoft jet database engine could
not find the object

Any other machine can print from this db.
Access 2003. XP Pro, SP2.

View 7 Replies View Related

Printing In Access

Nov 18, 2004

Hi

Anyone who can help me out?

I have a report in access.
I have designed the report such that. When a user wants to print
there is a prompt for the user to enter the order number.The report displays
all the orders for that order number.A number is assigned for that print out.This
number is unique for every order number the user enters.

How do i capture the generated print out NUMBER, store it in a table so that when
there is another printing for the same order number No NEW PRINT OUT NUMBER is
displayed.The user should also be prompted that he/she is printing an already printed orders.
However in case of a new order number, the generated print out number should be differnt which
should also be stored for future refencing.

Please help

View 3 Replies View Related

Printing With Watermark In MS Access

Aug 29, 2005

I want to be able to create a background image as a watermark for Access Reports when they are printed. Now, as I can figure, the Report has a "Picture" property which I can use to set this.

The catch is, the contents of the background image is not static, it must be generated on-the-fly. (for example, it can contain the date of report generation) So, I think, that's ok. I'll need to create an ActiveX object that will generate the image, then I'll assign the image to the picture property using VBA.

Now, my concern is, can I set it up such that even if users create a new report in the database, this behavior can still be automatically done? Is there a way?

Also, would it be possible to set a background image for query results also?

View 2 Replies View Related

MS Access 97: Printing PDF Files

Sep 14, 2004

I would like to be able to print PDF Files from MS Access 97. I have a table with records containing all the information necessary to create full pathname of the file (i.e. strPathName). But how do I get it to print (not view) the file once the record is selected from a drop-down box. Do I have to create a module to do this? Or is there some simple trick? If anyone has come code for doing this, it would be much appreciated. Thanks!

Malcolm
Tulsa, OK

View 1 Replies View Related

Printing A Letter From Access

Aug 18, 2004

Dear All:

I have created a print command button on a form. When this button is pressed, It should print a Word document.

I have composed this document in Word already.

Any ideas on how to do this?

Many thanks in advance,

Dion

View 2 Replies View Related

Printing A Letter From Access

Aug 20, 2004

Dear all:

Code:

Private Sub Command167_Click()
On Error GoTo Err_Command167_Click
Dim objWordApp As Object
Dim objWordDoc As Object
Set oApp = CreateObject("Word.Application")
'make word not show up to user
objWordApp.Visible = False
set objWordDoc = objWordApp.Documents.Open(C:Documents and SettingsUserDesktopEXPRESS DIPLOMA LETTER.doc)
objWordDoc.PrintOut
'quits word
objWordApp.Quit
Set objWordDoc = Nothing
Set objWordApp = Nothing
Exit_Command167_Click:
Exit Sub
Err_Command167_Click:
MsgBox Err.Description
Resume Exit_Command167_Click

End Sub

I am trying to print a letter from access when a comand butting is pressed. It returns syntax error on this line:set objWordDoc = objWordApp.Documents.Open(C:Documents and SettingsUserDesktopEXPRESS DIPLOMA LETTER.doc)

HELP!!!

Many thanks,

Dion

View 2 Replies View Related

Printing Report To PDF In Access

Oct 22, 2015

I have an Access Database installed on a server that is accessed remotely by numerous users. I have a button to print out a specific report. The problem is that in order for a user to print the report their print driver must be loaded on the server. Due to the number of users, and the numerous types of printers available, this is impractical. The current code on the button is currently:

Private Sub Command PrintReport Click()
Dim strWhere As String
strWhere ="[Property Number] = " & Me.[Property Number]
DoCmd.OpenReport "RESERVE REQUIREMENT REPORT", acViewNormal, ,strWhere
End Sub

I would like to know if there is some code that would send the report to PDF, so that any remote user could then print the report from PDF.

View 2 Replies View Related

Printing Sequence Of Access Report

Sep 6, 2004

Hi,

I have 2 single-page Access reports. The first report has the following fields:
FirstName, LastName, StreetName, HomeNumber. The second report has the following fields:
MerchantA, MerchantB, MerchantC.

The data for the first report are taken from tblBuyers, the data for the second report are taken from tblMerchandise.

I need to print 2 copies of the first report and 4 copies of the second report for the first Buyer, then 2 copies of the first report and 4 copies of the second report for the second Buyer and so on. Can I help on this problem ?
Rgds

View 1 Replies View Related

General :: Printing Reports To PDF Access Only

Jun 30, 2014

I have limited users to not show the ribbon, but I need them to be able to print of reports or export them as pdf files how do I give them access only to do this ?

View 3 Replies View Related

How To View Access Report Before Printing It

Aug 20, 2014

I have an Access program that prints various reports with a button.I would like to view the report in full screen prior to printing, how do you do this?

View 6 Replies View Related

Printing From A Locked Access Database

Mar 4, 2013

I have an .accde database that will not let my print button to work. How do I print from an .accde database?

View 2 Replies View Related