X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

> Microsoft Access Help Forums
The premier Microsoft Access help forum with Visual Basic, SQL Server, Office and more! Members are able to post questions and help others with problem solutions. UtterAccess has been seen in over 160 countries, recording over one billion hits to date. Microsoft Access Tables, Queries, Forms, Reports, Pages, Macros, Modules...you'll find all the free samples and answers here!

The efforts of everyone from Simon Wolf's "A3" shall carry on here and with thanks to these past efforts and those to come, UtterAccess.com is the leading source for Access and other products problem solutions available on the Internet.

Registered members have access to all the file attachments that UA is pleased to provide. An added bonus along with many other features such as Access sample databases, professional Access code samples, file uploading, private messaging and forum subscriptions...all this for those who take a moment to sign up...(Your personal information is kept completely confidential and everything is truly free of charge!)

Over 250,000 members have signed up at UA to enjoy well over 2,500,000 posts - proving UtterAccess.com is the only source for help with Microsoft Access...and more!
> Additional UA Navigation
Discussion ForumsCode Archive Table of ContentsThe Microsoft Access WikiNewcomer's Reading List
Today's Active TopicsHow to post a good questionContact UADifferences in Access Versions
> Automating Formatting

Given the attached sample file,
1. In my receiving report I’d like to rename receipt date and store name as in my order report: order date and supplier.

Order Report --- Receiving Report
Order Date --- Receipt Date (should be named Order Date)
Supplier --- Store Name (should be named Supplier)
Account Code --- Account Code
Order Number --- Order Number
Blanket Order ---
Order Type --- Order Type
Distribution Amount --- Distribution Amount

2. Delete the remaining columns in both order and receiving reports so in the end I’m left with the following:
Order Number --- Order Date --- Order Type --- Blanket Order --- Supplier --- Account Code --- Distribution Amount

3 Reconcile the two reports in a new worksheet: combine duplicate on the basis of order number and distribution amount values for both reports, then
compare and determine outstanding distribution amounts i.e. order – receiving

can a macro/VBA be used to accomplish the above manual process?

Attached File(s)
Attached File  sample.zip ( 18.52K )Number of downloads: 1
> Docmd.outputto Acoutputreport Hanging

This code has been working perfectly but now it hangs on the DoCmd line. I get the 'Printing - Now outputting' dialog box but the PDF file is not being written to disk and the last line of code does not execute.

' create PDF file
msOutputFile = fGetSystemTempPath & TMP_PREFIX & "_" & rsC!InvoiceNo & ".pdf"
Debug.Print clsAM.ReportForInvoice, msOutputFile
DoCmd.OutputTo acOutputReport, clsAM.ReportForInvoice, "PDFFormat(*.pdf)", _
msOutputFile, , , , acExportQualityPrint
Debug.Print "A"

Any suggestions would be greatly appreciated.


> Can You Create A Tab Control With A Table Layout?

I am creating a data entry form and I want to display the controls in multiple tabs each arranged like tables, with the field names as column headings over rows for a history of entries. The table layout is greyed out when I select the tab pages. Can you put a table in a tab sheet?

> Referencing Fields In A Query
I have imported a spreadsheet with a lot of fields into a table. I would like to check for multiple fields being null. is there an easy way to do this without having to set criteria in each field?
thank you in advance.
> Php Date("m/d/y") Works Wierdly?
I am trying to assign todays date to a php variable I am using $date=date("m/d/Y") bit that returns tomorrows date anyone understand why this is happening?
> Current Event In Form To Display Shifted Controls
My data entry form is in single form view. I have codes that shift the controls up and down on my form depending on the selection from the comboboxes, including making textboxes appear and dissappear. When a record is saved and I navigate to it, I can't see the results of some of those hidden textboxes nor are have the controls shifted when the user entered the data originally. I did have success in coding to make the hidden textboxes appear when applicable, but it is overlayed over other textbox fields, because the controls have not shifted. How do I make the current event in my form say to look at the modules (where I have my codes that shift the controls on my form) and say to run them applicable for each record??

Thanks in advance.
> Is Possible To Use Shellexecute On A Accdr File?
I have code like the following that can be called from a button and it works.

Dim Retval As Variant
Retval = ShellExecute(0, "open", "C:\Data\Test01.accdB", "<arguments>", _
"<run in folder>", SW_SHOWMAXIMIZED)

However, if I change the name of the file to Test01.accdR and change the code to read

Retval = ShellExecute(0, "open", "C:\Data\Test01.accdR", "<arguments>", _
"<run in folder>", SW_SHOWMAXIMIZED)

It does not work.

I would have guessed a runtime version of a given database could be opened with this command, but maybe not. Is this just the way it is, or is there a another way to open an accdr file?

> Trouble With Referencing Controls On Form And Subform
I downloaded an account ledger database that I am customizing for a friend. There is some duplication of data entry with using the original layout of the "Transactions" form with the addition of client/supplier and horse controls thus the reason I am trying to go this route to minimize data entry.

The issue I am having is trying to get a control value on a subform, that I have set to display as a datasheet, to show on the main form.

Main form: Contact Details
Sub Form: Transactions Subform1

The end result I would like to have is similar to what's on the "Transactions" form.

Attached File  DEC_Financials.zip ( 297.56K )Number of downloads: 2
> Odometer

I am trying to create an odometer in Access which increments the number. My problem is I need digits 0 - 9 as well as characters A - z (excluding I & O).

In effect, every increment by number 1 should produce following results -

009 + 1 = 00A,
00Z + 1 = 010,
00H + 1 = 00J (remeber, I and O are included from the permissible digits)
0ZZ+ 1 = 100,

In effect, I need number system to the base 34.

Any clue.

> Outlook Crashes With Duplicate Names In Distribution
On an exchange server.

Last week, my boss had me add some names to a distribution list which I did blindly (apparently). I didn't use the list until this morning. I sent off a number of mails to other distribution lists without problem, but when I came to using this list, Outlook crashed.

Upon re-launching Outlook, we had an error message that one of the McAfee security plug ins was what caused it to crash. I decided to get a screen shot of the distribution list, remove it and recreate it from scratch. When I looked at the picture I took, I noticed that two of the names were duplicated. When I recreated the list without these duplicates, the mail went fine.

One would think that Outlook would be robust enough to simply send the mail to each email address, duplicated or not. At least it should warn you that you have duplicate addresses. It shouldn't crash the whole application and worse yet, lie about what caused it to crash.

Just posting this in case someone else ever sees this issue.
> Autoincrement By Count Of Records
Good Afternoon,

I have received a very unusual request and I do not even know if it is possible. I am hoping someone here might be able to tell me whether it is or isn't. I have field where a specific value can appear as many as six times. For each of these values they want the dataset to automatically numerically increment each. For example, if store number 2342 appeared 5 times, they'd like a separate field that returns, 1,2,3,4,5 for each one. Is there a simple function that could do that?

Thanks in advance for any suggestions,

> Handle Outlook Security Warning With Sendobject
Hi there,

I have a bit of code to send a report to a specified individual via Outlook email (MS exchange server). It works fine, but the Outlook security warning comes up making the user allow or deny the action. I don't want this to happen, as the email send is in the background where users don't see it. This would just take up more of their time, and confuse them. Is there are way to accomplish the task without that warning?

I found this forum discussion (http://www.UtterAccess.com/forum/index.php?showtopic=2005517) that solves the problem, but if this is the only method, I will need some help because I'm not sure where it should go.

Currently, I have the following code sitting at the end of an OnClick event for the command that closes the form:

  DoCmd.SendObject acSendReport, "rpt_ShowNewIssues", acFormatRTF, "myemailaddress", , , "New issue", , 0

Does exactly what I want, only brings up the security warning. Any advice would be greatly appreciated!

Thanks! smile.gif Jess
> Percentile By Group

myField/ myGroup
5 A
4 A
2 A
7 A
1 A
8 A
6 B
7 B

95th percentile for group A is 7.75, for group B is 6.95

The following ranks the values:

SELECT myField, DCOUNT("myField","myTable", "myField <=" & myField & " AND myGroup='" & myGroup & "'") AS Rank, 1/(DCOUNT("myField","myTable","myGroup='" & myGroup & "'")-1)*([Rank]-1) AS [Cum Rank], myGroup
FROM myTable
GROUP BY myGroup, myField
ORDER BY myGroup, myField

the result is
myField/ Rank/ Cum Rank/ myGroup
1 1 0 A
2 2 0.2 A
4 3 0.4 A
5 4 0.6 A
7 5 0.8 A
8 6 1 A
6 1 0 B
7 2 1 B

the following gets the percentiles
DCOUNT("myField","myTable", "myGroup='" & myGroup & "'") AS fieldCount,
DLOOKUP("myField", "Query Rank", "myGroup='" & myGroup &"' AND Rank=" & [Rank]+1) AS myNextField,
myField + (myNextField-myField)*(0.95-[Cum Rank])/(1/([fieldCount]-1)) AS percentile, *
(SELECT * FROM [Query Rank] QR WHERE myGroup=? AND [Cum Rank] = (SELECT MAX( [Cum Rank]) FROM [Query Rank] WHERE [Cum Rank] <= 0.95 AND myGroup=QR.myGroup))

the results is: 7.75 for 'A' i.e. one group at a time as the query is parameterized

fieldCount/ myNextField/ percentile/ myField/ Rank/ Cum Rank/ myGroup
6 8 7.75 7 5 0.8 A

Instead, how can I get the percentiles for each group without parameterization or VBA as in

fieldCount/ myNextField/ percentile/ myField/ Rank/ Cum Rank/ myGroup
6 8 7.75 7 5 0.8 A
2 7 6.95 7 6 0 B

Many thanks,

> Write Conflict
I am building an inventory system and for some reason it started giving me an error message whenever I try to edit any information in the form. I'm using SQL Server 2012 as the back-end for the tables. I have the only copy on my computer, so no one has access to the database yet, so I'm 100% sure no one else is using it. Why am I getting a Write Conflict error?

Attached File  error.JPG ( 113.85K )Number of downloads: 10

> Make Passwords Case Sensitive

I have the following code when a user clicks a button to login to the DB but need help modifying it so that passwords are case sensitive (i.e. b, B), currently it's not:

Private Sub btnlogin_Click()

If IsNull(Me.quserid) Then
MsgBox "You must enter a User ID", vbInformation, "UserID Required"

If IsNull(Me.qpassword) Then
MsgBox "You must enter a Password", vbInformation, "Password Required"


'process the job

    If (IsNull(DLookup("UserID", "11_Authorized_Users", "UserID = '" & Me.quserid.Value & "'"))) Or _
    (IsNull(DLookup("Password", "11_Authorized_Users", "Password = '" & Me.qpassword.Value & "'"))) Then
        MsgBox "Invalid UserID or Password"
    DoCmd.OpenForm "Procurement Log Form", acNorma1
    End If

End If
End If
End Sub
> Recommended sites and books
Access Team BlogMicrosoft Access Small Business Solutions
Access Dev CenterProfessional Access 2013 Programming
Access 2013 Inside Out
> Online
327 users active in the past 60 minutes
14 members and 313 visitors
andycutts, Ayiramala, DanielPineault, datAdrenaline, ghubbell, Google.com, GroverParkGeorge, MikeGSuppe, orange999, RJD, Rose_gardener, samarpondas, shlongmeister, sid989, yourjacky
RSSSearch   Top   Lo-Fi    26th January 2015 - 10:21 PM