I have developed a project in Access 2003 and it works absolutely fine. There is a switchboard form too and that works fine as well. But now in our company we are moving all the projects developed in 2003 to 2010. And so I have noticed the switchboard form doesn't work in Access 2010 the way it works in 2003. The Switchboard form doesn't display all the options in 2010.
View RepliesI'm trying to figure out why I can email a report as a PDF, but not save it as a PDF using the code below? When I save, I get the dreaded error 2501.
The searching I did for this said to uninstall and reinstall printers (I've done that - seems to have no effect), or took issue with filtering the report with a where clause (but if that was an issue, why does it email a PDF just fine?).I'd just like to be able to save a report as a PDF to a specified location.
CB
Developing in: Access 2010; Win7
For use in a mixed 2007/2010 XP/7 environment
Code:
Private Sub EmailDietCardBtn_Click()
On Error GoTo MyErrorHandler
Me.Refresh
Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim stEmailMessage As String
Dim stCaption As String
Dim myPath As String
[code]...
I have a process which runs lots of slow append queries, so I want to reassure the user of progress, by changing the font colour of labels to green when the relevant queries have finished. This is the code I'm using
Code:
'Update the older data if that option is selected
If Me.Menu_YearOption.Value = 1 Then
DoCmd.OpenQuery "2-10 Append FY1112"
Me.lbl1112.ForeColor = 32768
Me.Repaint
DoCmd.OpenQuery "2-12 Append FY1213"
[Code]...
Each query takes about a minute to run, as it gets data from a sharepoint server, but the labels don't turn green one at a time as expected. Instead, I get the spinning wheel until all the queries have run, then all the labels turn green together.
Is there something else I should be doing rather than Me.Repaint ?
I have a query that sums up the number of parts used. This works fine.
I want to be able to limit this query to parts used after a specific date.
I have in my report
DoCmd.OpenReport "Part Totals Report", acViewPreview, , "[Part Date])>= " & SQLDate
The report is bound to a query that has 2 group by fields, 1 count field a a further field, a date field ([Part Date], that I put a default criteria on. This field is not displayed. If I don't put a criteria on this field disappears when I close and open again.
I pass a date to the program via a form and this ultimately ends up in SQLDate. When I run this I get promted to enter [Part Date] even though I'm setting it equal to SQLDate above. I can out garbage to a proper date in here either way the report picks up the default date entered in by the query.
1. Get rid of all of the parameters off the query.
2. Then you can use the Where Clause of the DoCmd.OpenReport code to specify the parameters based on your variables.
1.Not sure what this means but when I get rid of the criteria for the parameter the field disappears (I'm setting the show field to no as I don't want totals group by date). Getting rid of the field gives me all parts used.
2.I think I'm doing this in the above but will bow to superior knowledge!!
or is it I can't pass a parameter to a report run by a query that is grouping fields together to produce a count.
Incidentally once the report has been run (albeit with the wrong parameters) and I go into design mode and look at the property sheet for the report the correct filter is there (i.e., the date that has been input) but it quite clearly ignores this.
My database is used by a few users on a shared drive in "read-only" mode so they can't change any data.I set a scheduled task to copy an updated version of the database (which I edit) every morning.
The problem is that when the users forget to close the database file I can't overwrite it.For this purpose I wrote a small code that quits the application at 00:00 using Application.Quit.The code WORKS when I test it on my computer, but every morning when I try to open the file I see that it is opened by another user since I also open it as "read-only" - meaning the code didn't work...getting the code to work properly OR get a better solution to be able to overwrite the file even though it's opened by other users.
I have a form that worked when I left work Tuesday. It is supposed to look at all the information in my "courses" table in descending order by year, ascending order by date, in edit mode. There is also a look-up list that allows me to select the date of the course and jump to that record.
I came in today and it doesn't work! The form LOOKS like it's in add mode--completely empty. the data is still in the table. I checked all the properties and they all look right. The query still looks like it did when I left. It just quit! When I use the look-up list the dates appear in the list, but nothing happens when I select it.
I know I'm not giving you much to go on, but any ideas?
I am using Access 2007. I have had an application running now for about 1 year and now getting an error when user opens the database. (Have one for front end and one db for backend)
Here's the error message:The form name Switchboard is misspelled or refers to a form that doesn't exist.Also, multiple users are accessing the database at the same time.
I have a DB: This DB was sent to me via eMail (dropbox). It consist in a BE and 3 FEs. In each of the FEs I have a started form with some code activated by the Open event.
The issue: Access do nothing when I open the FEs. Note please that the DB is designed by me and it work very well on the user"s computers.
I use A2007 on Win7
The DB is in a trusted folder. Compact and Repair is not useful.
I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":cboMatterTypeIDcboMatterIDcboAttyIDcboPlglID Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.
Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."
So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:
tblLeadData.AttyID
refAtty.Atty
tblLeadData.PlglID
refPlgl.Plgl
tblLeadData.AssignmentNote
Expr1: [Atty] & " " & [Plgl] & " " & [LeadAssignmentNotes]Criteria: Like "*" & [Who?] & "*"
The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.
When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.
As for my cascading combos, here are the settingsMatterTypeIDRow Source:
SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType]; On Change Event:Me.cboMatter.Requery
MatterIDRow Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter;
On Change Event:Me.cboAtty.Requery
[Code] ....
I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter
data 3 times:Enter Parameter Value: Atty
Enter Parameter Value: Plgl
Enter Parameter Value: Who?
Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.
I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.
I created a small database with a user login form, along with a password reset form. Running it in my sandbox as an accdr file, the password value resets just fine, but on my test machine with the Access 2010 runtime installed, the password reset form doesn't reset the password. I will try to describe the state of things as of this point.
1. The LoginF presents with a combo box for the user to choose their name, and a text box to enter their password. The code below is on the After Update of the password text box.
Code:
Private Sub txtPassword_AfterUpdate()
'Check if user has been chosen
If IsNull(Me.cboUser) Then
MsgBox "Please Select User Initials", vbInformation, "Warning"
[Code] ....
2. The password reset form has two text boxes for the new password and to re-enter the same, as well as a command button with this code.
Code:
Private Sub cmdResetPassword_Click()
If Me.Password = Me.PasswordTest And Len(Me.Password) & "" > 0 Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
DoCmd.OpenForm "LoginF"
[Code] ....
As I say, this works as an accdr file, but not in the actual runtime environment.
basically am creating a booking system, i have a add a room form. my form should check whether i already have a room number in my table, which works when the form is filled in. however when my form is null, then i press add new room button, i get this error rather than " please fill your form in"
Error: runtime error '3075' syntax error (missing operator) in query expression 'Room Number ='.
room number is a number field, integer but has primary key. i cant keep autonumber, as my requirement is to add new room number, but the roomnumber has to be unique.
here is the dlookup;
If DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber) > 0 Then
MsgBox "This number already exists."
Else
p.s it unbound form
I know stupid question. When I open my database I want the switchboard to open automatically, but it doesn't. What do I do to fix this?
Thanks
Biz
i am trying to run an append query in SQL which appends a calculated value into another table 50 times incrementing the day by one day each time. When I run it it asks me for the parameter [NewDay] each time. It is obviuosly not picking up the variable.
Can anyone tell me why ?
Dim NewDate As Date
Dim n As Integer
For n = 0 To 50
NewDate = Date + n
DoCmd.RunSQL "INSERT INTO InventoryEvolution ( SAP, Stock, [Date] ) SELECT UK_Product_Estimate_Live.[RE SAP Code], ((Sum([Estimate01])+Sum([Estimate02]))/50)*-1 AS Stock, NewDate From UK_Product_Estimate_Live GROUP BY UK_Product_Estimate_Live.[RE SAP Code] HAVING (((UK_Product_Estimate_Live.[RE SAP Code])=513450))"
Next n
Hi all,
I get this error "You tried to execute a query that does not include the specified expression 'SITENAME' as part of an aggregate function."
The query is:
SELECT o1.name AS SITENAME, o1.vertical_loc/10000000 AS LAT, o1.horizontal_loc/10000000 AS LON, c.CELLGLID AS CELLID, 'traffic_total' AS Expr1, sum((MEBUSTCH_HR+MEBUSTCH_FR)*period_duration)/sum(period_duration) AS Traffic, 'traffic_hr' AS Expr2, sum(MEBUSTCH_HR*period_duration)/sum(period_duration) AS Traffic_HR
FROM objects AS o1, objects AS o2, c_bts AS c, p_generic_cell AS p
WHERE c.int_id = o2.int_id and
o2.parent_int_id = o1.int_id and
p.bts_int_id = o2.int_id
GROUP BY SITENAME, LAT, LON, CELLID, Expr1, Expr2;
I know the problem is with the group by clause. In MySQL it just works using "group by p.bts_int_id. I learnt in access i have to include all non-aggregated fields in the group by clause, and that's what I've done!! WHY!!Please help me!!!
Hallo,
The following what I do not understand:
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT par1 FROM tblparameters Where tblparameters.gcnf = 'XMLexp' AND ((tblparameters.ccnf) Like 'ExpTijd*')"
Set rec = cmd.Execute()
Do While rec.EOF = False
MsgBox rec("par1").Value
rec.MoveNext
Loop
I don't get any result back. If I changed it likt the following:
tblparameters.ccnf = 'ExpTijd1' , in the query, I get one record back.
So my conclusion the query is right but the Like doesn't work in these circumstances?
thanks in advance.
Nico
hi folks,
I am designing an application for an assignment for college, all seems to be going ok except that I have to teach myself access from a book. When I update a record on a form and go to another page it works fine, but if I attempt the same action ( for example creating an appointment then moving to a new form to confirm they have arrived) a second time I am getting an error which tells me I can't save the record. I think it is to do with updating the recordset when the new form is opened. I have an update record button on the form (created by the wizard) but it wont update on the second attempt. Could someone please tell me the code to update a recordset on the formload procedure. Ive tried me.recordset.refresh and me.recordset.requery but I still can't solve the error. Please help I've got to hand this assignment in in 2 weeks. thanks in advance
Chris
Hi,
Does anyone know if I can just show the switchboard only so that it looks like a program, or if I can use an existing MDB to create an .exe??
I'm trying to run an update query to trim a field to the first 8 characters.
I've used this in the past, but now it wont work. I'm stumped.
Left(Trim([IMIE_MSISDN_IMSI]![IMEI]),8)
Now when I try the records remain unchanged, i.e. 13 characters.
Any ideas?
Hi! I'm relatively new to Access (2003) . I had to modify a shared network app so I copied it off and renamed it, made my mods and everything is fine. I then went to another app that connects to the original app to obtain data for display and synchronization, modified the call to open the new app including the new mdw. When it hits the new mdw, it can't open it because it says it's being used exclusively or the workgroup file is missing. It's not being used exclusively and it doesn't appear to be missing. The network path is correct. If I use the mdw from the original source app, it works fine. Please advise! :(
View 1 Replies View RelatedHello everbody,
I'm sorry if my thread is in wrong section, cause I'm first time on this great forum :(! I hope you'll undrestand me ;)
My problem is with my database for my coffee bar, man who made database is still, and I don't have somebody to repair my database unfourtanetly :(
I formatted my C:, cause I installed new OS Windows XP PR SP2, and everything is deleted, but I save this .mdb file, but I don't have instalation CD for this program, cause man who installed me this database didn't give me instalation file :(!
And I saved this .mdb file, and now I try to run this POS.mdb,I can open "POS KASA" in english POS CASH, and I see articles - (ARTIKLI), and value of them it account values of them, just program doesn't count for me like before "sume" or "total" of EX. one count, for example coca cola 2.5 KM, fanta 2.5 KM = 5 KM, and now he doesn't count sume - total in program total is "UKUPNO", if somebody to know to repair this, or something another what is importing to work this database like before, please help me, database is on bosnian, if you need translating just ask me, cause I don't know to edit this file, and to have access to change words :(!
1'st colone in program interface (bosnian - english)
Sifarnici -something like codes
Artikli = Articles
Ulazi = Inputs
Storno racuna = storno count
Pregled storno racuna = review storno count
2-nd colone in program interface
Dokumenti - Documents
POS kasa = POS cash register
Dnevni promet = daily exchange
Trenutni saldo kase = current saldo of cash register
3-rd colone in program interface
Reports
Printanje prometa po broju = Printing exchange by number
Printanje prometa od datuma do datuma = Printing exchange from date to date
Printanje trenutnog prometa = Printing current exchange
IZLAZ = EXIT :)
Actually for me the most important is POS CASH REGISTER, cause it doesn't count total value of one count , total = ukupno doesn't work, sume doesn't work, and I can't give on my guests count, and I have problem with inspection cause I don't have counts :(, please help me and if you can repair this; IN ADVANCE TNX MUCH!
Greetings to all, from Sarajevo, Bosnia and Herzegowina!
I'm sorry really on my bad english, and I hope you'll understand me :(!
I need to do the price in table [price] multiply by 1.20 if the price is higher then 150. If the price is between 75 and 150 it have to multiply by 1.25. Continue... continue... continue...
expr1: IIF([Price]>"150",[Price]*1.20,IIF([Price]>75,"[Price]*1.25",IIF([Price]>50,"[Price]*1.28",IIF([Price]>30,"[Price]*1.35",IIF([Price]>15,"[Price]*1.45",IIF([Price]>0,"[Price]*1.6"))))))
Error Message:
Run-time error '-2147024769 (800700f)':
Method 'Connection' of object' _ CurrentProject'failed
Debug:
' Open the table of Switchboard Items, and find ' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection 'error is to left as indicated by Access)
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
Something must be screwed up in the program. A reinstall did not help.
Can you please?
If I hold down the SHIFT-key and click on my access database the database boots up with the startup options! I can't get into my database even if I hold down the SHIFT key!! Help me, please
View 2 Replies View RelatedI am trying to take a string from an input box and place it in a table. I was told to use ado. I have tried but it seems to have problems with Access Data Projects. see http://databaseadvisors.com/pipermail/accessd/2003-April/005252.html
Does anyone know how to get this to work in an .adp database?
I have an Access 2003 database which is soon going to run out of space when we hit the 2Gb limit for the linked MDB that holds our biggest table of 2.5 million records.
So, management decided that we had to migrate the table to linked-Oracle - however, the performance of the large queries that depend on this Oracle linked table is abominable - they take up to 15 times as long to return as when using an internal Access table - hours instead of minutes.
So management have decided to migrate the whole application to Oracle - but in the meantime until this new system goes live, I have to make a cut-down version of the original Access table with only the fields necessary for the queries that are slow under Oracle, and populate it from the Oracle table as a kind of cache. This also saves relinking all the other databases and spreadsheets that refer to the original Access table.
However, there seems to be no way that Access can get these records without taking a ridiculously long time, or raising errors that are so STUPID that it implies Microsoft didn't bother testing the Access Import methods (probably programmed by an intern) before releasing the product.
PLEASE can anybody help?
___________________________________
Here are the methods I have tried:-
METHOD 1.
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN={DSN as shown in ODBC Administrator};UID={uid};PWD={password};" & "DATABASE={Oracle Service name}", acTable, "{Oracle Table Name}", "tbl_ORACLE"
First, it returned the following error, as I hadn't created a target table (I thought it would auto-create one with the correct schema):-
"The Microsoft Jet database engine could not find the object 'tbl_ORACLE'. Make sure the object exists and that you spell its name and the path name correctly."
So, I used Copy/Paste (structure only) to make a blank target table 'tbl_ORACLE' with exactly the same schema as my linked Oracle source table. It then gave this error:-
"The Microsoft Jet database engine could not find the object 'tbl_ORACLE1'. Make sure the object exists and that you spell its name and the path name correctly."
So, it's added a "1" on the end of the name of the table I explicitly supplied ... and then said it couldn't find it!!! So, I created a copy table named 'tbl_ORACLE1' - it then said it couldn't find 'tbl_ORACLE2' !!!
METHOD 2.
I tried writing a simple SELECT...INTO statement, where I insert the linked-Oracle fields that I want to keep into the blank target Access table. 14 hours later overnight, and it still hadn't finished!!!
METHOD 3.
The Oracle programmers also have the data in a text flat-file format, so I tried importing using the
DoCmd.TransferText acImportDelim, "ORACLE_Import_Spec", ...
method, which works if the flat-file is empty apart from field definitions.
However, if there is any data in the Oracle flat-file, Access returns inexplicable "Numeric Overflow" errors (with no helpful debug text like row number, field name, etc) if there are any non-skipped numeric or date fields in the Import Spec - despite my spending about 2 working days trying all possible variations of datatypes in the Access Import Specification dialog.
METHOD 4.
In desperation, I decided to try loading the data the hard way, using LINE INPUT to get each line in turn from the flat-file and rs.AddNew, etc, to add the records one-by-one to the Access table.
However, since Microsoft didn't bother putting an optional "input date format" parameter on the CDate() function, I have to use the Format() function to convert the entirely normal "YYYYMMDD HH:MM:SS" date format in the flat-file to something that Access can understand with no possibility of confusing US (MM/DD) and non-US (DD/MM) date parameter orders.
So, I hit the bug in the Format() function where it always returns "Jan" regardless of what month you supply. Try this:-
Debug.Print format("09", "MMM")
or with any month you like instead of the "09".
Likewise, the other bug in the Format() function where it returns random dates. Try this:-
Debug.Print format("0109", "DDMMM")
You won't get 9th Jan or 1st Sept...
If I copy paste the SQL from a query (View->SQL) to a macro it doesn't work.
Why?
I've tried removing quotes,Caps, adding quotes, parenthesis nothing worked.
Any ideas?