Access Data Project - Selecting Data

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - runtime 2465 can't find field "|" referred to in your expression runtime 2465 can't find field "|" referred to in your expression. Here is the code. I know that the fields exists in the table and the object exists on the form. Private Sub Command32_Click() Dim db As Database, Tools As Recordset Set db = CurrentDb() Set Tools = db.OpenRecordset("tblCodedCuttingTool", dbOpenDynaset) Do Until [tblCodedCuttingTool]![ToolCode] = Forms![frmCodedCuttingTool]![ToolCode] Or Tools.EOF If [tblCodedCuttingTool]![ToolCode] = Forms![frmCodedCuttingTool]![ToolCode] Then MsgBox "Tool Already Exists", vbInformation, "Error" Tools.MoveNext End If Loop End Sub Tag: Access Data Project - Selecting Data
  - 2
    - asynchronous communications Group, I need to be able to have a top level gui in excel make calls asynchronously to another excel where I am doing my modeling. During this calls, I need to keep the gui alive and well for interaction. I can't find much info on this. I am an experienced multi-thread programer in C++ and Java, but I need to do this in VBA. Can you point me to some examples in Visual Basic for Applications in Excel where I can cut my teeth Thanks, The elephantwalker Tag: Access Data Project - Selecting Data
  - 3
    - vlookup Hello can anyone help me with how to write the excel vlookup function in vba the function in excel looks like this VLOOKUP(O2,'[Consolidated list of supplier.xls]Sheet3'!$A$5:$F$218,6,FALSE) how can i get something similar working in a macro. thanks namrata Tag: Access Data Project - Selecting Data
  - 4
    - Mouseover event on shapes (Excel 2003) Can I set a mouseover event in VBA on a shape (copied picture) I have a module that copies an inserted image from one sheet to another sheet based on cell values denoting the placement of the copied image. The copy is then handled as a shape to set Name, Alt Text, and so on and the sheet is saved as a web page for browser viewing. Is there a way to set a mouseover event for a Shape object so that when someone points at the image on the web page, the image changes Code Snippet ' Go to the Display sheet and paste the copied image into the cell referenced Sheet1.Activate Sheet1.Range(pRange).Select ActiveSheet.Paste Application.CutCopyMode = False ' Set the AltText and Name to match the server network name Sheet1.Shapes(Sheet1.Shapes.Count).Name = sRange & "-" & namePicture Sheet1.Shapes(Sheet1.Shapes.Count).AlternativeText = namePicture ' Format picture by calling washPicture() from module3 washPicture ' Put the picture name in the cell one over to the left and hyperlink it ActiveCell.Offset(0, -1).Range("A1").Select ActiveCell.FormulaR1C1 = namePicture ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ namePicture, TextToDisplay:=namePicture Tag: Access Data Project - Selecting Data
  - 5
    - VBA ADO Error Handling Method I am using VBA in Excel to take the information in Excel, place it in an array, and insert it into a database. The operation completes successfully, however, I'd like to record any ADO errors that come up in the operation. The code is shown below. It first opens the database connection, then it creates a worksheet to add any errors that come up during the add new operation (errors if a record in another table does not exist or if duplicates exist in the array). The code then adds the data in the array into the table. If an error occurs, it should put the information into the 'Errors' worksheet, including the error description. During execution, the data is correctly inserted into the database, however, the 'Errors' tab ends up with twice as many records in it than it should. The information as far as which records show up there are also off. It seems as though it is not returning to the loop correctly, although I've never tried to build error handling in before. Any help is greatly appreciated. ' Open tblUser_Roles to add records rs2.Open "tblUser_Roles", conn, adOpenKeyset, adLockOptimistic, adCmdTable ' Row Counter Dim MaxRow MaxRow = 1 ' Worksheet Tab for Errors ActiveWorkbook.Sheets.Add After:=ActiveSheet ActiveSheet.Name = "Errors" Set q = ActiveWorkbook.Sheets("Errors") ' ADODB Error Handling On Error GoTo myErr: ' Add Data to Database from the Array For r = 1 To p_maxRole rs2.AddNew rs2("AUserID") = Data(r, 1) rs2("RoleID") = Data(r, 2) rs2("Priority") = Data(r, 3) rs2.Update Next r 'Error Handling Procedure myErr: If Err <> 0 Then q.Cells(MaxRow, 1).Value = Data(r, 1) q.Cells(MaxRow, 2).Value = Data(r, 2) q.Cells(MaxRow, 3).Value = Data(r, 3) q.Cells(MaxRow, 4).Value = r q.Cells(MaxRow, 5).Value = Err.Description MaxRow = MaxRow + 1 Resume Next End If Tag: Access Data Project - Selecting Data
  - 6
    - text parsing in excel I am collecting data that another person has done in excel to be used in a access database. The problem is that a lot of the cells have 5 spaces before the actual text. how do i write a macro to first check if there is a space at the beginning and then to delete the spaces. If there is a space, it is always 5 spaces. Tag: Access Data Project - Selecting Data
  - 7
    - Combobox behaving oddly! I have a combobox embedded in an Excel worksheet, who's source is a named range. The named range is dynamic and can change in size if data changes. I have set my combobox to pick up this range by setting its ListFillRange property to the range name. This is done at design time, rather than in code. It has been working fine up until the range changed size. At this point, the values in the combo were all over the place, even containing blanks, duplicate values etc. I managed to cure this by setting the AutoLoad property of the combobox to True. However, what I find is still happening is the ListCount of the combo doesn't change if the range gets smaller. It displays the available values in the newly sized range, but the 'slack' is taken up by blanks. I hope I'm making sense. Can anyone help Tag: Access Data Project - Selecting Data
  - 8
    - Possible to run code after Word closes? Hello, I have a macro that saves the current word document on the users c:\ and then emails it to me. I am aware of the kill function to delete a file, but I would need to close the file first in order to delete it - which can easily be done in the macro. But it appears that once Word closes the macro stops running. Thanks for any help! Tag: Access Data Project - Selecting Data
  - 9
    - how to get the hyperlink value from an excel sheet cell hi All I have an excel sheet that have some cells with text and under the text there is a hyperlink to another files to the network driver Does any one knows how to extact the value of the hyperlink (not the text value that how up). I tried many variations like =CONCATENATE("link ",T(HYPERLINK(A1))) but it didnt help !! Thanks for participating Aws Tag: Access Data Project - Selecting Data
  - 10
    - Printing using the Microsoft Office Document Image Writer printer and C# Hi all, I am trying to print, without user interaction, a PDF file to the Microsoft Office Document Image Writer. If use System.Drawing,Printing.PrintDocument, I'm able to set the destination file but when it gets to printing the file, I can't since it has to know how to render the PDF file. Now, I tried using the print command in the command prompt but when I want to specify the Microsoft Office Document Image Writer it says it can't initiliaze the printer. Can anybody give me a hand on this one I want to do it either way. For those who don't know the print command do the following: Go to Start Click on Run Type cmd if you are using XP Type print in the command line Thanks in advance, Tag: Access Data Project - Selecting Data
  - 11
    - Complex report generation from datatable..challenging one plz help I have created datable which normally has 5000 records upto 1 lakh records depending on runtime value....i want to display rows of datatable to user in particular format... 1] odd rows with yellow back ground 2] even rows with green back ground 3] if value of cell in odd rows is not eqaul with value of cell in even row then both cells's text should be bold and with red color given pic is view that report should look like right now i am doing by putting that data in excel programatically using excel object but it is very slow....it takes hell lot of time ....15-20 minutes just for 300 rows only then i tried to write those data as html table format and generated .html file and i opened it with excel....got happy that it is done in some seconds but when opening with excel i found that it gets hanged if it has more than 10000 rows....so now i am totally stuck is it possible to get it done using xslt transformation and how will color font color in xslt at run time by matching data or any other way to represent table content with same formating but in any other file format please help.... Tag: Access Data Project - Selecting Data
  - 12
    - importing data from an ODBC datasource using a filter How can I use a filter on an import table into my Access database Do I use SQL statements in a module or VBA Tag: Access Data Project - Selecting Data
  - 13
    - VBA help with a conditional Subscript out of Range Hey everyone, Hopefully this one will be an easy one. I am asking VBA to pull information from an access 2003 database, and place it in an array. There are cases when I know that I will get a "Subscript out of Range" Error, and have to ask for a different set of values, due to having 0 values in the database for the parameters I asked for. IE no values in this table for that date range. So I will have to query a second table. Is there a way that I can make VBA catch that with an IF statement I tried If(ResultsTrend), and If(Not ResultsTrend). But neither of those worked. Basically I need it to analyze wether the array got populated with anything at all, and if not, go do this instead. Here is my code so far for this block. Code Snippet ResultsTrend = GetTrendData(Server, Username, Password, TrendStartDate, TrendEndDate, 0, 0, OverrideTemp(k).Range("F1").Value, Status) If (Not ResultsTrend) Then ResultsTrend2 = GetTrendData(Server, Username, Password, TrendStartDate, TrendEndDate, 0, 0, OverrideTemp(k).Range("F2").Value, Status) For i = 1 To size OverrideTemp(k).Cells(EventStartTime.Row, EventStartTime.Column).Value = ResultsTrend2(i) OverrideTemp(k).Cells(EventStartTime.Row, EventStartTime.Column).HorizontalAlignment = xlCenter OverrideTemp(k).Cells(EventDuration.Row, EventDuration.Column).Value = ResultsTrend2(i + 1) OverrideTemp(k).Cells(EventDuration.Row, EventDuration.Column).NumberFormat = "0.0" OverrideTemp(k).Cells(EventDuration.Row, EventDuration.Column).HorizontalAlignment = xlCenter Set EventStartTime = OverrideTemp(k).Cells(EventStartTime.Row + 1, EventStartTime.Column) Set EventDuration = OverrideTemp(k).Cells(EventDuration.Row + 1, EventDuration.Column) Next Else Tag: Access Data Project - Selecting Data
  - 14
    - 2 Small Macro problems. Hi all, Problem 1: I am trying to delete a sheet within a workbook, as part of macro. Here's the code: Sub DeleteInstructions() ' ' DeleteInstructions Macro ' Macro recorded 3/13/2007 by Gary Beckwith ' ' Sheets("INSTRUCTIONS").Select ActiveWindow.SelectedSheets.Delete Sheets("DATA").Select End Sub When I run it, I get the prompt "Data may exist in the sheet for deletion. To permanently delete the data, press delete." I have to click OK. How can I get the macro to do this for me Problem 2: I have just sorted and then copied text from another file into this one. I want to go back to that file I just sorted and save and close it. Here's the code: Sub CloseOtherFile() ' ' CloseOtherFile Macro ' Macro recorded 3/13/2007 by Gary Beckwith ActiveWindow.ActivateNext ActiveWorkbook.Save ActiveWindow.Close End Sub It goes to close it, but then asks if I want to save it. How can I get the macro to do this for me Thank you, Gary Tag: Access Data Project - Selecting Data
  - 15
    - MSDN Download Hi, I found out at microsoft download site that the latest version of MSDN could be downloaded free of cost. Has any body tried downloading the image file and is the MSDN fully functional Or is it an evaluated version which will expire after some days Please find the link to the page below. http://www.microsoft.com/downloads/details.aspx familyid=94596AF5-CC58-45AF-A14B-DF627A31E783&displaylang=en Tag: Access Data Project - Selecting Data
  - 16
    - some mp3 play some others don't I have a powerpoint presentation and inserting an mp3 files to play across the slide, but for some reason my mp3 file would not play at all. that mp3 was created by a windos media player and will not play in powerpoint at all, but play just fine in my windows media player. if I create the file with soundforge then that mp3 will play fine in powerpoint. can anybody tell me, what I'm doing wrong here Tag: Access Data Project - Selecting Data
- Next
  - 1
    - Vlookup in VBA Hey eveyone, I'm have a lot of difficulty using a vlookup in a macro I'm creating. If anyone could help it would be greatly appreciated. Here is the code: '********************************************************************** Sub proScoreCard1() Dim rng As Range Dim tradeDate As Date Dim confirmDate As Date Dim t1mDate As Date Set rng = Range("H2") tradeDate = rng.Value confirmDate = rng.Offset(0, 4).Value Do Until IsEmpty(rng) t1mDate = Application.VLookup(tradeDate, _ Worksheets("Lookup").Range("A2:E62"), 3, False) If confirmDate <= t1mDate Then rng.Offset(0, 5) = "X" Else rng.Offset(0, 5) = "" End If Set rng = rng.Offset(1, 0) Loop End Sub "*************************************************************************** The error I am receiving is "Runtime error 13: Type Mismatch" Thanks in advance! Tag: Access Data Project - Selecting Data
  - 2
    - common dialog not returning file names. Hi everyone. i have a vb script that is imbeded in an html page (hta). when i use the common dialog flags = cdlOFNAllowMultiselect the file list is present in the filename object and are separated by a space. however when i use dialog flags = cdlOFNAllowMultiselect Or cdlOFNExplorer the files that i selected on the dialog box, are not present in the filename object. the only thing that's there, is the path to the files. here's the code: Const cdlOFNAllowMultiselect = 512 Const cdlOFNExplorer = 524288 Set ObjFSO = CreateObject("UserAccounts.CommonDialog") ObjFSO.Filter = "All Files|*.*" ObjFSO.FilterIndex = 1 ObjFSO.InitialDir = "c:\" ObjFSO.Flags = cdlOFNAllowMultiselect or cdlOFNExplorer InitFSO = ObjFSO.ShowOpen selected_files = ObjFso.FileName msgbox selected_files the variable "selected_files" should have the file names. but it only shows path. any ideas why this is and how can i get the files names thanks so much. Tag: Access Data Project - Selecting Data
  - 3
    - Writing result in Excel Hello, I have the folowing code: Public Function DgrMinSec2DecDgr(Angle As Double) As Double ' * -------------------------------------------------------- ' * Program not yet working ' * -------------------------------------------------------- ' * Variables Dim grd As Integer Dim min As Integer Dim sec As Integer Dim calc As Double ' * Values grd = Angle \ 1 calc = (Angle - grd) * 100 min = calc \ 1 calc = (calc - min) * 100 sec = calc \ 1 DgrMinSec2DecDgr = grd + (min / 60) + (sec / 3600) DgrMinSec2DecDgr = Round(DgrMinSec2DecDgr, 4) ' should I round this result ' * Write - problem in writing in cell 'Debug.Print Round(DgrMinSec2DecDgr, 4) 'ActiveCell.Formula = "Write" & DgrMinSec2DecDgr ActiveCell.Select: Selection.FormulaR1C1 = calc '"Write Sel" '& DgrMinSec2DecDgr End Function I will be extremely gratefull for your answers. Thank you. V. Tag: Access Data Project - Selecting Data
  - 4
    - Inserting Comments in Excel...from a Beginner Hello everyone. I'm new to VBA programming, but I have done a little. I have a new project at work that I'm trying to accomplish via VBA, but I'm not sure how to get it done. Thanks in advance for your feedback. My goal is to take data from a cell on Sheet2 of a workbook, and place that data in a comment field in the exact cell on Sheet1 in the same workbook (data in A10 on sheet 2 will be a comment in cell A10 on sheet 1). I will have to create a loop to do this in several columns and rows, but I'm pretty sure I know how to do that. But how do I insert that type of comment, if it's possible at all Thanks! Tag: Access Data Project - Selecting Data
  - 5
    - About SAP MM and SD Hi, I'm a Mechanical Engineer from Chennai, India, with about 2 years experience in Project Engineering. I worked with the order management team of an MNC and handled inquiries on design, material and manufacturing, co-ordinated with vendors and clients, and have also done post shipment analysis. I just want to know if studying SAP - MM or SD will be a good career option for a mechanical engineer. I also want to know if I'll get a job immediately after studying SAP and what are the technical skills that IT companies expect when they recruit people. It would be nice if someone with a similar background explains his/her experience about the selection process, the interview and career growth pattern that one can expect in this field. Tag: Access Data Project - Selecting Data
  - 6
    - Excel-Capture the sum of Highlighted Range I'm trying to capture the sum of a highlighted range in an Excel Marco without using a loop. After selecting a range, I can capture the cell count with the following command: X = Range(Selection, Selection.End(xlDown)).Count Wondering if there is way to the sum as well. Excel can show the sum highlighted range, so it seems I should be able to replecate in VB. Thanks, Neil Tag: Access Data Project - Selecting Data
  - 7
    - LCS 2005 Not Showing Options in Administrative MMC I recently installed Live Communications Server 2005 Standard on a Windows 2003 Server which is a member of our domain. Everything seems to be working correctly with Messenger 5.1 however when I try to Use the Administrative snap-in it only shows the text Live Communication Server 2005. It does not give me a tree structure with options and nothing appears under the actions menu options. I logged in as the domain administrator to see if maybe it was a permissions problem but still no luck. Any suggestions Tag: Access Data Project - Selecting Data
  - 8
    - adding text to cell with vba script I've done this before but I am completely blanking on what I am doing wrong with adding text or in this case a formula to a cell. The line I have and am getting an error on is Workbooks("DSL Annual Rollup").Worksheets("DSL Annual Rollup").Cells(i, 3)="January!D"&i&"February!D"&i&"March!D"&i&"April!D"&i&"May!D"&i&"June!D"&i&"July!D"&i&"August!D"&i&"September!D"&i&"October!D"&i&"November!D"&i&"December!D"&i Tag: Access Data Project - Selecting Data
  - 9
    - How to test Com Addin for ISV test We are a product based company and want to go for Microsoft Platform test for ISV of Veritest. How can we make sure that our COM add in will pass through the test Does any body know what sort of tests we can conduct for Com Addin. Tag: Access Data Project - Selecting Data
  - 10
    - Dear DMan1 could you just be a little more explicit about how I name the Sub1? Hi folks, I've written several Subs on different worksheets in Excel but I'd like to set up a batch file to run them all. Is there a command that allows a "stack" of subs to be run one after the after I am sure there must be. Beats me. Hamish. Tag: Access Data Project - Selecting Data
  - 11
    - VBA + Bloomberg Hi All, i am building a macro in which i retrieve info from Bloomberg. i have to compare these data with other values. Before comparing i would like to copy and paste special value the data retrieved from bloomberg. How can i do to perform the copy paste special value only once all fields are updated (i.e. how the macro can know that the fields are ok and it is time to copy and paste ) Thnaks a lot Tag: Access Data Project - Selecting Data
  - 12
    - Stuck on logic for Excel problem... Hi I am stuck on the logic for the following problem. I have a list of employees and each has a manager attached. I want to write a macro that will loop through all the employees on the sheet and compile a list of the managers with all employees reporting in to them. I've attached an example as this may be hard to visualise! http://i102.photobucket.com/albums/m82/sc0ttb_2006/Sheet1.jpg http://i102.photobucket.com/albums/m82/sc0ttb_2006/Sheet2.jpg I realise this will involve a for loop and then an IF statement or two and I know to use concatenate to add the names together into the one cell but I just can't get my head round the exact way to do it, so any help is greatly appreciated! Many thanks Tag: Access Data Project - Selecting Data
  - 13
    - how do re-enable marco after it stopped? Hi, I wrote some vba code within the Worksheet_Change sub. It supposedly fires whenever a cell value has been changed. Problem is...sometimes it fires, sometime it doesn't. Whenever it doesn't (probably because it encounted bug within my code), no matter what I do, I can't re-enable the marco again. So far, the only way for me to go around that copy my code into a clipboard, close out excel (not save). Re-open excel and paste my code back in. I'm sure there must be ways I can go around that without having to do what I've been doing ( tried hitting the square button but still doesn't work). How can I re-enable marco after it stopped Thank you. Tag: Access Data Project - Selecting Data
  - 14
    - Solver in VBA - variable "Bychange" value I started by programming a simple loop which identifies the cells which I want SOLVER to calculate. Let the variable VAR1="$H$9;$H$10;$H$11;$H$12;$H$13" Then I try to execute the code: SolverOk SetCell:="$K$1", MaxMinVal:=3, ValueOf:="0", ByChange:=VAR1 However, it does not work. If I write Bychange:=VAR1, VBA does not assume this. Another problem. If I write: SolverAdd CellRef:="$H$9 H$18", Relation:=3, Formulatext:="0.1" or SolverAdd CellRef:="$H$9 H$18", Relation:=3, Formulatext:="0,1" when i open solver, it has assumed FORMULATEXT= 1 ($H$9 H$18>=1) instead of 0.1 Can somewone help me with this Tag: Access Data Project - Selecting Data
  - 15
    - I need help modifying Sharepoint I am trying to modify a few things in Sharepoint and I can't seem to figure out how. First off, is there a way to change the categories that are displayed on the homepage. I currently have announcements, links, and discussions displayed. I am wondering how I can have a list of my documents added as well. I also have a calendar but it isn't displayed on the home page. Please let me know if you can help. Mike Tag: Access Data Project - Selecting Data
  - 16
    - MODI Exception - Running out of memory on Set_Document method of MODI document object Hi, I am using the Microsoft Office 2003 Document Imaging libraries with Visual Studion 2005. I have the following VB.NET code which processes many tiff files and then at some point it comes up with exception. At which point, it corrupts something because i can not use the VS IDE to open the main form in design mode saying that it encountered an error most likely indication that memory is corrupt. In any case, i have the MODI from 2003 version 11 installed and used the toolbox and added a reference to my form. The form contains a button which starts a new thread to process files. The part of the processing of files which requires looking into tiff file content requires calling this function below. The code is expected to run through 9749 TIFF files, it comes up with exception on my laptop running windows XP Office 2003. When i use Office 2007, this does not happen (i.e. i don't get the exception). Is there a difference between MS Office Document imaging in 2003 and in 2007 that could cause memory corruption problem I have included the excerpt from the code that does the modi processing, i think that i may also not be releasing COM object handles in the right order. Can anyone help in this forum or do i need to post to another Out of memory exception on Set_Document of the MODI document object. What i suspect is that because i don't correct the object handles correctly the first time an image runs through this code, the consequtive times for the remaining images, it fails on tiffDoc.Create(fileName). Any help would be appreciated. Ibrahim at bramoin@yahoo.com Code Block Public Sub TiffEvaluate(ByVal fileName As String, _ ByVal startingPageNum As String, _ ByVal endingPageNum As String, _ ByVal ocrCheck As Boolean) Handles loadFileCheck.TiffEvaluate CheckTiffImage(modiTiffView, fileName, startingPageNum, endingPageNum, ocrCheck) End Sub ' This routine is called from another thread, that explains the Me.Invoke logic. ' The function of the remainder of the logic is to use the MODI object and read ' the tiff image compression and after telling it to OCR the document search the ' resultant text string for text matching those specified by starting and ending ' page numbers. These two strings will be different for multiple image TIFF files. ' ' SPECIAL CONSIDERATIONS: ' The OCR software in MODI will sometimes see '1' as 'I'. As a result, we ' need to search for both 'something1' and 'somethingI' Private Sub CheckTiffImage(ByVal uiElement As Control, _ ByVal fileName As String, _ ByVal startingPageNum As String, _ ByVal endingPageNum As String, _ ByVal ocrCheck As Boolean) Dim tiffFile As String = fileName Dim startPage As String = startingPageNum Dim endPage As String = endingPageNum If uiElement.InvokeRequired Then Dim d As New CheckTiffImageCallback(AddressOf CheckTiffImage) Try Me.Invoke(d, New Object() {uiElement, tiffFile, startPage, endPage, ocrCheck}) Catch ex As Exception End Try Else Dim result As Boolean = False Dim left As Integer = 0 Dim top As Integer = 0 Dim tiffDoc As MODI.Document = Nothing Dim tiffImages As MODI.Images = Nothing Dim imageLayout As MODI.Layout = Nothing Try ' the following lines are for ui update to let the user know what is going on. tiffDocumentCount += 1 lblTIFFDocCount.Text = tiffDocumentCount.ToString() lblTIFFDocNumber.Text = startPage lblTiffBoxNumber.Text = tiffFile.Substring(tiffFile.IndexOf("BOX"), _ (tiffFile.LastIndexOfAny("\") - tiffFile.IndexOf("BOX"))) ' CAUTION: There are two ways to load the MODI ActiveX Viewer object: ' one way is to assign the filename of the viewer object, that proved buggy since ' the document object is realized when the image is displayed. And because the display ' of the image immediately after the Show method below is not garanteed, it caused ' file mis-read. ' In our implementation, below we create the document first and then assign it to the object. ' This seems to bring the memory usage of the application and the IDE environment down to a ' more stable level. tiffDoc = New MODI.Document If Not tiffDoc Is Nothing Then ' opens the tiff file tiffDoc.Create(tiffFile) ' assigns the MODI viewer object the document object created modiTiffView.Document = tiffDoc ' displays the MODI viewer object modiTiffView.DocViewMode = MODI.MiDocviewMode.miDOCVIEWMODE_SINGLEPAGEVIEW modiTiffView.FitMode = MODI.MiFITMODE.miByWindow modiTiffView.Show() tiffImages = tiffDoc.Images ' count of images in the tiff file Dim imageIndex As Integer = 0 ' utility for storing part of a string Dim strItems As String() ' number representing the current page Dim currentPageNum As String = "" Dim docNum As Integer = 0 Dim imageEnum As IEnumerator = tiffImages.GetEnumerator() While imageEnum.MoveNext ' get the image in the MODI Document's Images object Dim image As MODI.Image = imageEnum.Current If ocrCheck = True Then ' modiTiffView.SetScale(2, 2) ' Adjust the image to view lower right corner in the window ' Dim height As Integer = image.PixelHeight ' Dim width As Integer = image.PixelWidth ' left = width - Panel1.Width ' top = height - Panel1.Height ' modiTiffView.MoveRectIntoView(left, top, width + left, height + top) If Not image Is Nothing Then ' first check the image compression If image.Compression <> MODI.MiCOMP_TYPE.miCOMP_TIFF_LZW Then UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIF Compression-Check successful for: <" & _ tiffFile & "> found: <" & _ MODI.MiCOMP_TYPE.GetName(image.Compression.GetType(), image.Compression) _ & "> for image number <" & imageIndex.ToString() & ">.") Else UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIF Compression-Check processing error: <" & _ tiffFile & "> found: " & image.Compression.ToString() & " compression " & _ "for image number <" & imageIndex.ToString() & ">.") UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "...TIF Compression-Check processing error: <" & _ tiffFile & "> found: " & image.Compression.ToString() & " compression " & _ "for image number <" & imageIndex.ToString() & ">.") image = Nothing Exit While End If ' This will cause it to OCR the current image image.OCR(MODI.MiLANGUAGES.miLANG_ENGLISH, True, True) ' form the currentPage string, by taking the last part of the ' sartPage string and then adding the imageIndex strItems = startPage.Split("-") If strItems.Length > 0 Then Dim docSequenceNum As String = strItems(strItems.Length - 1) ' converted string will be stored in docNum If Integer.TryParse(docSequenceNum, docNum) Then docNum += imageIndex ' the problem here is that the leading zeros are lost docSequenceNum = String.Format(docSequenceNum, docNum) ' include the dash (that is why we have +1 below) currentPageNum = startPage.Substring(0, startPage.LastIndexOf("-") + 1) currentPageNum += docSequenceNum Else If tiffImages.Count > 1 Then UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "...TIF image error: <" & _ tiffFile & "> can not convert docNumber <" & _ currentPageNum & "> for images.") End If End If End If ' Layout is an object created by the OCR process imageLayout = image.Layout result = SearchOCRForNumber(imageLayout.Text, currentPageNum) imageLayout = Nothing If result = True Then UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIFF image OCR was successfully verified for: <" & _ tiffFile & "> image number <" & imageIndex.ToString() & _ "> and document number <" & currentPageNum & ">.") tiffOcrCount += 1 Else ' Depending on the orientation, the OCR text may not contain what we ' are looking for. So rotate it 90 degrees, OCR and search again. image.Rotate(90) image.OCR(MODI.MiLANGUAGES.miLANG_ENGLISH, True, True) imageLayout = image.Layout result = SearchOCRForNumber(imageLayout.Text, currentPageNum) imageLayout = Nothing If result = True Then UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIF image OCR was successfully verified for: <" & _ tiffFile & "> image number <" & imageIndex.ToString() & _ "> and document number <" & currentPageNum & ">.") tiffOcrCount += 1 Else UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIF image OCR check failed for: <" & _ tiffFile & "> image number <" & _ imageIndex.ToString() & "> and document number: <" & _ currentPageNum & ">.") UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "...TIF image OCR check failed for: <" & _ tiffFile & "> image number <" & _ imageIndex.ToString() & "> and document number: <" & _ currentPageNum & ">.") End If End If End If Application.DoEvents() ' release the reference to the image object image = Nothing imageIndex += 1 End If End While Else UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "...Error: MS Office Document Imaging was unable to create a TIFF document for <" & _ tiffFile & ">.") End If Catch ex As Exception UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "An exception occurred: <" & ex.Message & ">, this error happened when " & _ "Procedo was trying to process file: <" & tiffFile & ">.") Finally ' Do more clean up If Not imageLayout Is Nothing Then imageLayout = Nothing End If If Not tiffImages Is Nothing Then For Each tiffImage As MODI.Image In tiffImages tiffImage = Nothing Next End If If Not tiffImages Is Nothing Then tiffImages = Nothing End If If Not tiffDoc Is Nothing Then tiffDoc.Close(False) ' False means don't save changes modiTiffView.FileName = "" tiffDoc = Nothing End If End Try lblTIFFOcrCount.Text = tiffOcrCount.ToString() End If End Sub Tag: Access Data Project - Selecting Data

MGillanders

What are the best pratices on selecting single values from SQL Server tables (ADO) in VBA code in an Access Data Project

The command.execute method returns a ADODB.Recordset so a SQL SELECT statement can be executed and moving to the first record of the recordset to get the value. But seems a little extreme to lookup one value.

The DLoookup methods used for Access tables (DAO) are not available for ADO.

In .NET there is a sqlcommand.executescalar which returns a single object.

What is the recommended method to select a single value from a SQL table

Top