Replies

I have a query called qry-OA_ this_ month’s_ hours and would like to show the total number of distinct full names in a report footer. I have a field called full (which is a concatenation of [Last Name] & [First Name]). Here is my code:

Dim dbsmember As Database
Set db = CurrentDb
Dim rst As Recordset

Dim inttotmems
Set rst = CurrentDb.OpenRecordset(“SELECT distinct [qry-OA_ this_ month’s_ hours].full FROM [qry-OA_ this_ month’s_ hours]”)
inttotmems = rst.RecordCount
Text33 = inttotmems

I am getting a runtime error 3061 “Too few parameters. Expected 2” message. What am I doing wrong?

Thanks, in advance.

Does the “qry-OA_ this_ month’s_ hours” query have criteria? If you are passing criteria to the query via a form, you will get that error. You may need to copy the SQL code (from SQL View) and use it as your recordset source.

Ken

I spoke too soon. It works GREAT in an Access 2003 environment. However, it does not work in a runtime environment. I am not enough of a programmer to say for certain, so this is just conjecture on my part. I suspect that because the code is calling for items on the menu, and since the menu items do not exist in the runtime, that that is creating the problem.

[codebox]’==============================================
‘ Sub: openSecurityDialog( )
‘ Author: Q-Built Solutions; http://www.QBuilt.com
‘ Date: 3 Aug. ’04

‘ Note: This sub requires the Microsoft Office 11.0 Library
‘ as a Reference.

‘ This sub activates the Macro -> Security submenu to open
‘ the Macro Security dialog window for the user who is using
‘ the runtime version of Access ’03 instead of the retail version,
‘ which may need to have the default security level changed in
‘ order to run the code in the modules.

‘ Thanks to Victor Escalera, we have the code needed for the
‘ Spanish version of Access, as well. To use the Spanish version
‘ just replace the lines marked “English version” with the lines
‘ marked “Espanol (Spanish version).” Thanks, Victor!
‘==============================================

Public Sub openSecurityDialog( )

On Error GoTo ErrHandler

Dim CmdBar As CommandBar
Dim CmdBarPopup As CommandBarPopup

Set CmdBar = Application.CommandBars(“Menu Bar”)
Set CmdBarPopup = CmdBar.Controls(“Tools”) ‘ English version.
‘Set CmdBarPopup = CmdBar.Controls(“Herramientas”) ‘ Espanol (Spanish version).
Set CmdBarPopup = CmdBarPopup.Controls(“Macro”)
CmdBarPopup.Controls(“Security…”).Execute ‘ English version.
‘CmdBarPopup.Controls(“Seguridad…”).Execute ‘ Espanol (Spanish version).

CleanUp:

Set CmdBarPopup = Nothing
Set CmdBar = Nothing

Exit Sub

ErrHandler:

MsgBox “Error in openSecurityDialog( )” & vbCrLf & _
“in SecurityFunctions module.” & vbCrLf & vbCrLf & _
“Error #” & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub ‘ openSecurityDialog( )
[/codebox]

I’ve been fighting a similar issue with 2007 – it’s even trickier, but I think this link How to set the macro security level in Access 2003 runtime may provide an answer for you. And I would choose the code approach rather than trying to hack the registry on each user PC.

AWESOME! Thanks. I hope you can get your’s to go as smoothly.

Ken

In answer to your first question, yes you can – but you don’t use either. You manipulate a native Access table using the TableDef properties with VBA. ADO and DAO are methods for working with records in tables, but not for changing the table design.

However, creating or changing export specifications programatically is not a trivial task, and is dependent on the version of Access you are using. In particular, Access 2007 stores them in a different fashion. I’ve never attempted to do that, and I must say my experience trying to create fixed with text files presents some significant challenges. For example, boolean fields don’t seem to behave terribly well, and dates sometimes what to export with time as well. With further web research you may be able to find some information on how to approach it, but my guess is the programing research would take far longer than just creating the export specifications. If you do find a good resource on the subject, please post it so others can benefit from it. Thanks.

Wendell,

Thanks for the follow up. I am using Access 2003. I have found a snippet of code I am testing for creating the tables. The master source table I have contains like 200+ fields with specs, and I need to create several tables from the source. If I have any luck, I can share the resulting code.

As for the date and boolean fields, in the past when I had to create text files, I used queries and formatted the date fields using something like ExprtDate:format(MyDate,”YYYYMMDD”) as the field to export. And something like YesNo:IIF(Fldname=true,”Y”,”N”).

I’ll research and test creating an export spec via code and share any positive experience.

Thanks!

Ken

Play around with Export from the File menu. This only allows you to do one at a time but gives you all standard formats, excel, text, csv, other database formats. You can use the records within a table to make another one by using a Make Table query. If you want to do more than one table at a time, you would have to use code. A lot depends on what it is you need to do with these tables in terms of determining the most efficient way to do it.

Peter,

Thanks for the reply. I need to be more specific.

I have table A with fields named:
Name: Type: Caption : Precision
where Name = the name of the field to be created
Type is the type of field
Caption is the caption to use for the field
Precision is the decimal places for numeric values

Sample Records in the table :
txtFirstName : Text : First Name :
txtLastName : Text : Last Name :
intAge : Integer : Age : 0
PayRate : Currency: Rate : 2
Weight : Single : Weight : 2
DoB : Date/Time: Birthdate :

These records would create fields in a table with the above specs.

I hope this better explains my goal.

Thanks!

Ken

Where are the macros and modules located in Access 2007. I am really struggling with Access 2007 interface.

http://office.microsoft.com/en-us/access/HA102388991033.aspx is a kewl tool to help map 2003 to 2007.

Try “Database Tools” on the ribbon and then Macro is on the left

In my haste (or haze) of trying out Apple’s Safari to access Woody’s I overlooked Hans’ suggestion.

Thanks for pointing it out to me.

Ken

Does Hans’ suggestion regarding Named AutoCorrect help?

Hans,

Thanks for the suggestion. I have made the change on this end, but will have to wait until later in the week before I can put it on the user’s pc. I’ll keep you posted.

THANKS again for all your help.

Ken

Reputedly, having “Track Name AutoCorrect Info” turned on in the General tab of Tools | Options… may cause reports to lose their page setup, apart from slowing down performance in general. So I’d turn this feature off, then distribute the database again (the setting is stored with the database).

The most common reason for this issue is that the Printer Names (including the port) are not the same as your PC. That is especially true if you are specifying a printer that the report must print to. A second issue that sometimes occurs is that you are using a paper size that isn’t available on the default printer – in that case Access wants to default to Letter and Portrait, or so it seems.

Wendell,

Thanks for the heads up. I don’t believe this to be the issue, as the specific printer in this case is an Acrobat pdf, and both my machine and the users have Acrobat printers. This has been going on now for nearly three years and we have just lived with it. They now have a new user who is less patient, and in all honesty it is something I should have researched sooner.

In all cases, the paper size is 8 1/2″ x 11″ (Letter).

I also have code that changes their default printer to Acrobat pdf during the print process and then switches it back when done. That is a relatively new addition and the problem was occurring prior to that update.

Thanks again for your help.

Ken

I have just got notice that we are upgrading to Access 2007. I have a lot of databases that need to be converted. Is it as simple as opening the database in Access 2007 and the database will be converted? What are the issues that I may run into? Is the Access interface as changed as Excel?

For what it is worth, among other entities, I work with two large state universities with custom Access applications. One university’s IT dept resisted for months going to Office 2007, then eventually succumbed to pressure from a few department heads and took about three days updating the staff and some students. They then spent the better part of four weeks undoing it and going back to 2003.

The other university offered training on 2007 to anyone interested. About one third of staff attended. Of those attending, about one half decided to “upgrade”. Of those that “upgraded” about half of that group asked to go back to 2003 after a few days to weeks. From my involvement with these guys and a few others, it seems like experienced users do not like 2007 and the newbies do like it. For me, it has created a headache because I had helped some of the users create custom toolbars which do not show up in the 2007 version.

As one of the experienced users, I find the interface cumbersome and not at all intuitive. Going from Office 97 through all versions up to and including 2003 transitioning was a breeze for me and anyone else I know. I am sure that Microsoft, in all their wisdom have a great reason for such a departure from the previous standards. But they make me wish I was using a Mac again. LOL

Good luck.

Ken

For some reason, when I move reports created in an Access 2003 Application from my machine to another, the reports designed for landscape print and preview in portrait. The user consistently has to preview the report, then File:Page Setup:Landscape before printing. The weird thing is that when they get to the File:Page Setup:Landscape it is already in Landscape, so the user clicks on OK, and it prints and previews fine. Is this something anyone has experienced before?

Would I be able to run this module immediately before printing or previewing the report and force it to landscape at run time?

I also have another app that results in similar problems. It also is created on my machine, and then moved to the same computer as above. If the user prints or previews without going to Page Setup menu, it prints the fonts at something like 72 point making the reports unprintable. However, if they go to the PageSetup for the report before printing all is well.

One other point of significance is this occurs on four pc’s at the same business. Office is not loaded on their company pc’s individually, but instead they have an image that is used to make all pc’s have the same programs and configurations initially. So if I can discover what causes this, I will need to get their IT gurus to change their image. However, since I don’t have a reason that this occurs, it makes it difficult to communicate with their IT folks and discuss a solution.

Any ideas are greatly appreciated.

Thanks,

Ken

In Access 2002 and later, you can specify Landscape orientation for the printer. You could create a function in a standard module:

Public Function SetLandscape()
Application.Printer.Orientation = acPRORLandscape
End Sub

and call this function from a macro named AutoExec. But the Printer object is not available in Access 2000. For forms and reports, you could use PrtDevMode, but that doesn’t apply to queries.

You shouldn’t let the end user open queries anyway, they should only use forms and reports.

Does anyone know the easy route to get the runtime for MS Access 2003 where a user can run a program without having Access installed. I am going round in circles with the MS website. Thanks

I know this is an old thread, so I hope I am not violating protocol. However, I did find Office Access 2003 Developer Extensions (English) is available at their BizSpark site.

You have to sign up in order to access the programs.

Just thought I would mention it.

Ken

You cannot assign a value to a constant dynamically. Change it to a variable: instead of

Const IDLEMINUTES = 15 ‘ or intTo

use

Dim IDLEMINUTES As Integer

and asssign the value in the procedure where you read the value from the table:

IDLEMINUTES = intTo

Thank you Hans. I will make the change.

Once again you come to the rescue.

Ken

See How to detect User Idle Time or Inactivity in Access 2000 (works in later versions of Access too). In that example, the code just puts up a message box after a specified time of inactivity (defined as: the user hasn’t moved to a different control or to a different form), but you can have it perform another action such as quitting Access.

Note: the example uses a hidden form with an On Timer event and a TimerInterval of 1000, i.e. 1000 ms = 1 second. I’d make the TimerInterval considerably larger, for example 30000 (=30 seconds) or 60000 (=1 minute) to minimize the overhead.

I took the above link and followed the instructions. Very kewl! However, I tried setting it up to read the time interval from a table so that I can change the time out dynamically.

I tried switching “Const IDLEMINUTES = 15” to “Const IDLEMINUTES = intTO” where intTO is dim’d as an Integer. I get “Constant expression required” when compiling.

Any ideas are greatly appreciated.

Ken

Ken:

Don’t know if this matters but if the user is entering data for some type of order and you always want to send the order plus extra rounded to the 1/16 you may want to use the RoundUp function otherwise the customer will get less than expected. On the other hand if its always important to send the customer less rounded ot the 1/16 because that is what the customer expects (the item will fit in the desired space) you would need to use RoundDown function.

Tom Duthie

Hans, Franz and Tom,

THANKS! The Round function worked fine with a single exception. If the sixteenths was over 15.5 it resulted in 16/16ths. Added some IF(,,) to address it and it works fine.

Thank you all.

Ken