Search Form Result to excel - Item not found - Where to start

Hi, I have a search form and have been given the task of getting the searched filtered records to an excel spreadsheet. I am getting "Item Not Found In This Collection" and do not know where to start to find the reason. I have taken the following and adapted from another working similar application. ' this is to send the forms results to an excel spreadsheet Dim dbsCurrent As DAO.Database Dim qryTest As QueryDef Dim SQL As String Dim QueryStringSelect As String Dim QueryStringFrom As String Dim QueryStringWhere As String 'output filtered data if some filtering is in place

Filter number range causing runtime rror in search form

Hi, If I run the following 'Number example. If Not IsNull(Me.txtPCode) Then strWhere = strWhere & "([Address5] >= " & Me.txtPCode & " ) AND " End If 'Number field example. If Not IsNull(Me.txtPCodeEnd) Then 'Less than the next. strWhere = strWhere & "([Address5] <= " & Me.txtPCodeEnd & ") AND " End If I get a runtime "You Cancelled the previous operation. However if I replace with 'Text field example. Use Like to find anywhere in the field. 'If Not IsNull(Me.txtPCode) Then 'strWhere = strWhere & "([Address5] Like ""*" & Me.

Old Question

On Novembre 04th 2009, I posted one Question labeld "Error 2001, Access 2007, Subform Bookmark", that had some replays from Jeanette Cunningham. Today I looked for them to review again, but they are not anymore available. I googled them I found that other sites still have them, but you don´t. What I have to do to continue with my request. Regards.

Inserting value of an unbound Textbox

hi, i got a form in which a integer value (Number) is being calculated based on the unput the user is giving in the different fields, now i want to insert this data in a field in my table, when the input is saved any bright ideas?

Writing to Excel header w/A2000 VBA

I need to transfer data from an Access table to an Excel spreadsheet for a customer. This was easy until the customer asked me to put the first data in A1 cell and put some text and barcode above it. That forces me to write to the spreadsheet header and I'm not sure how to do that. Can someone point me to a Help topic for this or give me an example to work with. Thanks.

Search Criteria - Yes/No - Allen Browne Form Example

Hi, Not sure if anyone is familiar with this application - wonderful piece of useful that has saved many a problem for searching. Not sure if this question out of left field can be done - here goes, In the example of the yes/no. Say I have series of check boxes that represent Sales people DK SS MM NM TW On one line. And I want to be able to search for the lines that have TW. Is it possible to have the combobox as a drop down that has these initials in it - and once selected it looks at the 5 checkboxes and filters only that which is selected. Its a space problem I hav

list box

I have a list box, upon whose selection determines the contents of another list box. If there is only one value given in the second list box, I would like it to be automatically selected... any advice? Thanks!

run a forms control events

I have a control on a form that runs on the "after update" event. I am using code from another form to add a value to the control, but if the control is updated programmatically, the "after update" event code doesn't run. Why doesn't it run automatically, and what can be done to make it run....

iif expression and time format

I have a table with a time field that displays a long form time (12:00:00 AM) by default. Through a query I want to build a work shift field for grouping these records. Below is an expression, but I know the time format is wrong. What format do I use? iif(([AccTime]between 6:01 and 18:00), "Day", "Night") -- TDC

IMAGE ORIENTATION

How can I determine programmatically the orientation of an image -Portrait/landscape- on my hard drive? Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=33a7aa71-c5a2-439c-83fc-57fa7c1699dd&dg=microsoft.pu

Tool bars

Is there a way to know if a user has certain tool bars enabled? I want to control which tool bars are visible when my application is being used and restore the user's settings when they close my application.

Constants

Hi All, I have a form called frmWeekly on the VBA window for this form, i have a Sub which sends an email, the code pulls of certain information from the forms such as receipient txtTo, subject txtSubject etc. I have a lot of information such as 'Path' information that i use repeatedly through this code and other subs on this and other forms. I would like to declare the path as a constant, i have opened a new module and added: Public Const strPathReports As String = "Y:\Shared\Tsoi01\TEAMDATA\Onstream Management Information\Reports\Current Year\Weekly\" However when i u

Recognize which form is calling the code

Hi - I have a contacts form that can be used to enter a new contact into the database. I have many forms throughout the system that may call this form at any time. I am writing some code and I want to save the name of the form that originally called the contacts form in a variable so I can pass control back to the original form. Can anyone help me with this? Thanks, Elizabeth

INSERT a Table (32 items) into another Table - Re subform

Hi, I have a form with a subform. The subform is linked parent to child. The subform has a field that has a combo box. From the combo there are two choices. On the after update if the selcetion is equal too "INSERT ALL ITEMS - 32 LINES" the idea is to take the contents of a separate table line by line and insert into the subform / subtable. I Have the following code If Me!cboProject_Notes.Column(0) = "INSERT ALL CHOICES - 32 LINES" Then 'do the input from the table tblProjectNotes_Default Set db = CurrentDb Set rst = db.OpenRecordset("tblProjectNotesDefault_1"

Why I can't send email from Access?

I have written some programming codes to email an Access form via Outlook, i.e., DoCmd SendObject acSendForm, "frmInformation", etc. There is a do while loop to send emails out depending on a condition. When the program is executed, the Outlook email interface does pop up. When I click on the Outlook's "Send" button, it pops up an error message, "The operation failed b/c of network or other communication problems. Verify your connections and try again". Can you help me to solve this problem? Thanks!

How To Convert Access 2003 Application To Access 2007 Version

This office is converting to Office 2007. I have been asked to convert a current Access 2003 Application scheduling volunteers into something compatible with Office 2007. I have been given a virtual machine with the application loaded. I do not have any idea how to look at the VBA I wrote, run diagnostics or step thru the code in Office 2007. Can anyone suggest a book or web site that covers this topic. I am slightly above beginner status, so it will have to be fairly basic and step-by-step. As an example, how do I stop execution at the Current Event for my main page. Some

Crosstab

How can I create a crosstab query that shows fiscal year from October to September on a report? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201003/1

VBA query trouble

I think I'm going blind. I have programmed SQL statements many times in the past, and for some reason I simply cannot get anything to work right now... I've reduced my code down to the most basic statement in order to get some kind of error code I can comprehend. My code, at the moment, is: docmd.runsql "Select * from MLS Sales" Can't get much simpler than that. However, when I try to run that command I get an error that says "A RunSQL action requires and argument consisting of an SQL Statement." Originally I did not use the "*" and had all my field identified. When

Help to create and Insert New Target Date record in Subform

I am a newbie to programming in MSAccess. (I'm using Access2007). I have a mainform (ContentAreas) and a subform (UpdateDates). I also have a database table (DateSchedule). The Mainform contains a field: UpdateFrequency The Subform contains two fields: TargetDate and ActualDate The DateSchedule table is a list of dates and a "Usable" Yes/No field (to tell me if that date is a valid date for use) What I would like to do is when I update the "ActualDate" in the subform, I would like to calculate the next "TargetDate" by adding the "UpdateFrequency" to the current record's "T

appending unbound combo query data to new record

This may be covered elsewhere, but my understanding of how to phrase the question is limited at best. To set the scene - this is a dB for a weekly running club. I have a table "RunDetails" (holds details of runners in an event) which has a lookup field (the date) bound to table "Runs" (details of the event itself). Form PersData displays the details of runners (name, etc). On this form I have two unbound texboxes (Bib and Distance) and a date combobox (cbo.rowsource is query from table 'Runs') and a button to enter their ID, and the contents of these unbound boxes into tabl

Compile Error ??

I used some code from Allen B. and it works fine in Access 2K3. However, it will not compile in 2K7. It fails at .Edit. Member or data object not found. What might I do to change/alter to comply with 2K& standards? Any help appreciated. Function UpdateGroupAlias() On Error GoTo UpdateGroupAlias_Err Dim strErrMsg As String 'For Error Handling Dim strSQL As String Dim intAlias As Integer Dim bytLevel As Byte Dim lngMakeID As Long Dim bytMaxColumns As Byte Dim pbytNumColumns As Byte Dim db As Database Dim rs As Recordset strSQL =

calculating value in a query

how would i build a function that would calculate value which is basically aggregate summary per group ? Here is an example: Table Demand Item Qty Date aaa 10 03-mar-2010 aaa 7 05-mar-2010 aaa 5 12-mar-2010 ccc 8 06-mar-2010 ccc 12 09-mar-2010 Table On Hand Item Qty aaa 12 bbb 9 ccc 6 the result should be table Net Demand Item Qty Date (how to get column Qty) aaa 0 03-mar-2010 (12-10, left 2) aaa 5 05-mar-20 (7-2, left 0) aaa 5 12-mar

VBA for emailing to all involved poeple

I need to email Access forms that have informaton about a specific student to his teachers (T) and his teachers' superviors (S) of a specific Unit. All of T&S email addresses are in the same table. (T) are grouped into 3 Units 1,2, and 3 controlled by (S). As if it's not complicated enough, the stud infor's form is a form that have its own query. I need to VBA codes to be able to pull out email addresses of T and S of a specific Unit based on that studentID and send the form to his T and his T's S. Any suggestions would be greatly appreciated.

Event Procedure

Hi, I am looking to insert [Event Procedure] for a form's event through VBA. Ideas on how it should be done welcome and appreciated. Thanks. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201003/1

Unprotect a Word Template

I posted something similar a few days ago, but it remains unresolved and has run out of steam. I will be more specific this time in the hope that someone can give me a quick and precise answer. How do I unprotect a Word Template (I know the password) from within Access (2003) so I can complete fields, and then re-protect the document so users cannot change anything? This does not need high security - it is simply a means of preventing changes after the data has left the database. The internal users will not dig deeply to bypass the password protection, and a copy of the print

Retrieving Active Directory User Name into Access 2003 table

It seems like there would be an easy VBA call to do this, but I have not found it yet... I am trying to grab the Active Directory User Name (the user name that is used to log into WinXP professional by the user) in order to populate the user name in a transactional database. Is there some simple (or not simple) VBA code that is out there to accomplish this? Perhaps something internal to Access 2003?

DSN-less Connections

This article discusses two methods by which a DSN-less connection can be made to SQLServer. http://support.microsoft.com/kb/892490 One uses the CreateTableDef the other uses DAO.RegisterDatabase. Any suggestions as to which would be the preferred approach? Any opinions about either? I'm inclined to stay awy from the first since it captures user name and pwd.

Link to Outlook folder

access 2002: Is there a way to create a command button to go to a specific email folder in outlook?

Help with Transactions

Hello: I am trying to add new records from a Quotes table and associated QuoteDetails table to a WorkOrder table and associated WorkOrderDetails table. I thought transactions but, perhaps I am wrong. The WorkOrderDetails table will not allow the addition of records unless there is an associated redord in WorkOrders. So, the transaction fails. If I commit the single record from WorkOrders and there is some error after in the WorkOrderDetails, how would I then recover from this situation? I hope that I have adequately explained the problem and I look forward to some discussio

Form Code HELP ??

I am have been trying to eliminate having to delete and then redefine my query in the code below. Can anyone help or suggest how I would modify? Private Sub Text0_AfterUpdate() On Error GoTo Err_Text0_AfterUpdate Dim stDocName1 As String Dim stDocName2 As String Dim MyQueryDef As QueryDef Dim db As DAO.Database Set db = CurrentDb() If ObjectExists("Queries", "qryUpdateBRSales") = True Then db.QueryDefs.Delete "qryUpdateBRSales" End If If ObjectExists("Queries", "qryUpdateStatus") = True Then db.QueryDefs.Delete "qryUpdateStatus" End If

Split database into smaller db based on values

I have a database with multiple tables. I need to break the database into smaller files based on states. I've been able to do this one at a time by using the transfer Database function in a Macro but i'd like to set this up to loop through the different state values in my Locations table and save each new database into a different folder. for example: run query for orders where state = CA then transfer the query data into a table in a file of the same name but directory C:/CA/mydata.mdb then OR C:/OR/mydata.mdb. any help would be appreciated

How do I make my 2003 access file to be compactable with access 20

I have written an Access 2003 program which runs fine on. I just installed Access 2007 and database is showing up. How do I make my 2003 access file to be compactable with access 2007? Thanks a lot.

how to reference a uniqueidentifier

I have an access 2007 based application and I need to be able to open a form based on a uniqueidentifier, the guid is used here as the primary key. I have this now: Dim strcriteria as string strcriteria = "[ID]= '" & Me.ID & "'" DoCmd.OpenForm ("myform"),,, strcriteria When I open the form I get this error: Syntax Error: Converting from a Character String to Uniqueidentifier Does anyone knows what the correct syntax should be in this case?

CODE HELP!

The following Sub is working, but I am looking to "Clean it up". I have an unbound form with a list box. I make a selection(s) from the list box which builds the Where string of my query (qryCoverage). From here, I build another query (qryCoverage1). I have to delete the existing qryCoverage1 or I get an error message that it already exists. Is this necessary? I then build a make table query and use that table to open yet another query to display the results. How might I simplify this process? Any help appreciated. Private Sub cmdOK_Click() On Error GoTo Err_cmdOK_Click

Can Access 2000 be installed on Windows 7

A client has a 2000 Access database that needs to be modifed, and we don't know if they're ready to be pushed into a version upgrade at this time. I know that I can save an Access program in the ver. 2000 format, but I would prefer to have the actual environment so I don't accidentally use a property or command that doesn't exist in the old version. Can Access 2000 be installed on my PC (OS Windows 7) without screwing up Access 2007? If so, can Access 2000 still be downloaded from Microsoft?

Can you help me make this string shorter?

I get an error message saying that my string is too complex. Can anyone see a way to streamline it?? =IIf(IsNull([costcode]) Or IsNull([costtype]),0,IIf([costcode]="013210" And [costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],IIf([costcode]="020110" And [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],IIf([costcode]="064201" And [costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],IIf([costcode]="061301" And [costtype]="05320",[txtEstimatedCost]/[txtQAQCSuprvLabEst],IIf([costcode]="061101" And [costtype]="05310",[txtEstimatedCost]/[txtSiteTeamLabEs

Access 2007 link table login

Hi I am migrating my Access 2003 to 2007. There a lot of link tables from Oracle in my mdb file. when my applicaiton running, I have to code to login Oracle: Dim wsp As Workspace Set wsp = DBEngine.CreateWorkspace("xxx", "xxx", "xxx", dbUseODBC) Set dbs = OpenDatabase("", False, True, strConnect) ' connect via regular ODBC 'Close the database. The login is still cached so that it is available for any 'Access object that needs it during the same session. dbs.Close This code works great in 2003 but not in 2007. How I can do it in 2007? Thank you a lot

Manipulating the Access 2007 User Interface

1) How can I programically eliminate, hide or minimize that damned Ribbon? 2) How can I hide the Navigation bar? In short, how can I lock users out from accessing the underlying objects unless I want them to have that access?

Mail Merge using Access then save as PDF

In my organization, we send out letters by the thousands, ususally about 500 at a time, due to the time constraints of the current method. Currently, we process the database, which places the letter contents in a table called Archives. Then we open the Merge Template Word document, and process the mail merge to print each individual letter. Next we have to save each individual letter as a PDF file (Acrobat 8.2 Installed) for record keeping and quality assurance. What we'd like to do is find a way that this can all be done from within Access programmatically. References:

Count the Number of Files in a Folder

I’m sorry if this is a repost, I posted the same question yesterday but it never appeared on the site. Could someone please show me an example or examples of how to return the number of files in a folder? I don’t need anything except the total number of files in the folder. I’d like to use VB but, I’m open to any solution… Thanks in advance, Jim

kindly, before I crash this computer against the wall..

....does someone know why when I write this declaration: dim idx as Index (or even DAO.Index) it changes into: dim idx as index (or DAO.index) ....and obviously it screams errors if I run the code. References seem fine.

Application Deployment Question

I have developed an application using Access 2k running on Windows XP that I would like to make available to users by downloading from a web site. After reading about “Inno” in an earlier post, I am intending to use it to install the application. 1)Would my application work with all the versions of Access that users may have on their machines? 1a) If it would not work with versions earlier than 2k, what could I do for these users? 2)Would my application work with Access2007 runtime for users that do not have a version of Access already installed? 2a) If yes, wo

Looking for a better way to Parse out data from alphanumeric field

Dear all, I need to parse out the text from a mixed alphanumeric field. I recieve ship-to addresses from a client, which do not always conform to the standard I need. Specifically they occasionally drop the postcode. I need to grab the first series of characters in the address, up to but not including the first numeric. This will allow me to match the address to a table of addresses and postcodes. Fortunately, due to the Japanese way of writing addresses, I can guarantee that it will be *near* the form "Prefecture City Town Division" (maybe with or without spaces) where

VBA Code to Save Excel Sheet to XML

I'm working on a project that uses VBA to send data to Excel from Access. The spreadsheet has a number of tabs with charts that reflect the data. At the end of the Access code, the spreadsheet is saved to a specific folder. The next step would be to create an XML file for the contents of the "Data" tab. When I create the XML file manually from Excel, I get the xml list which is <20k. When I try to save the Excel file as xml, using VBA in Access, I get a file that is >300k and contains the whole spreadsheet. Can someone show me, or lead me, to VBA code that I can use in Access

Internet Explorer Object, Navigation, Hiding

Hi, I use the IE object to navigate to a website and pull some information. I've run into an issue faced at a client that I have not experienced when testing it out (the worst kind of error, in my opinion). Here's the code: Dim objIE as Object ' late binding Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = False .Silent = True .Navigate myurl Do While .ReadyState <> 4 'etc. - code to bomb out if the site can't be found Loop mytext = .Document.Body.InnerText etc. When I tested the code, Inter

=RowNum([Form]) - equivalent in a report?

I am running Access 2003 and found the following function: Public Function RowNum(frm As Form) As Variant On Error GoTo Err_RowNum 'Purpose: Numbering the rows on a form. 'Usage: Text box with ControlSource of: =RowNum([Form]) With frm.RecordsetClone .Bookmark = frm.Bookmark RowNum = .AbsolutePosition + 1 End With Exit_RowNum: Exit Function Err_RowNum: If Err.Number <> 3021& Then 'Ignore "No bookmark" at new row. Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description End If RowNum = Null

display the result

I have the following programme.module for generating all possiible combination of in a group of 3 items and it works like this Private Sub Command1_Click() Dim rs As ADODB.Recordset Dim stocknames() As String Dim i As Long Dim j As Long Dim k As Long Set rs = New ADODB.Recordset rs.ActiveConnection = CurrentProject.Connection rs.Open "select stockname from stocknames order by stockname", , adOpenForwardOnly, adLockReadOnly i = 0 ReDim stocknames(0) Do While Not rs.EOF ReDim Preserve stocknames(i) stocknames(i) = rs.Fields("stockname") i = i + 1 rs.MoveNext Loop rs

Hide page on tab control

Hello, Is there any way to hide a page on a tab control based on what the user selects from a drop down box? Thanks in advance Martin

declaring a variable as "Index" turned out into "index"

Hello, it can sounds weird but I'm stocked into trying to declaring a variable as index. Immediately after digitizing the statement it changes "Index" into "index" and the code will not work. I need the variable to iterate through the Indexes collection of a Dao recordset. This recordset is opened as dbOpenTable and until now I have created one index only, the primary key, when I built the table. The DAo 3.6 library has been correctly referenced. What's wrong? Thanks, Rocco

Bind object to OLE Bound Frame

Hi, Is it possible to bind an oleBoundFrame object in a form to value in a DAO.Recordset using vba? Thanks Sanya