Null Value Error in recordset

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - Why does (VBA Excel) Solver becomes slower when executed in a for next loop more than 1000X? Problem: Solving problems becomes slower the more often I call the SolverOK function in a VBA Excel 2002 code. I am using the solver add in my program and I was wondering if that will use up a lot of memory and I want to make sure that I release the memory properly. The solver is called several 1000 times in this routine and it could possibly produce a big mess if it does release the memory correctly. Any help is appreciated. Code example 'Open solver library Application.Run "Solver.xla!Auto_Open" 'Clear solver contraints SOLVERReset For i=1 to 100 'Set solver contraints SolverAdd CellRef:="$H$114", Relation:=1, FormulaText:="$H$103" SolverAdd CellRef:="$H$119", Relation:=3, FormulaText:="$H$103" 'Call solver and calculate result SolverOk SetCell:="$H$101", MaxMinVal:=2, ValueOf:="0", ByChange:="$H$103" SolverSolve UserFinish:=True SolverFinish KeepFinal:=1 'reset solver after the job is done SOLVERReset NEXT ' close solver library Application.Run "Solver.xla!Auto_Close" Tag: Null Value Error in recordset
  - 2
    - Macro To Delete Spreadsheet Columns if no data in column This will delete the entire row if there are no values in col A and B Sub Delete_Unused_Rows() iMax = ActiveWorkbook.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row For i1 = 2 To iMax If LenB(Trim$(ActiveWorkbook.Sheets(1).Cells(i1, 1).Value)) = 0 And LenB(Trim$(ActiveWorkbook.Sheets(1).Cells(i1, 2).Value)) = 0 Then ' iMax = iMax - 1 ActiveWorkbook.Sheets(1).Rows(i1).EntireRow.Delete i1 = i1 - 1 iMax = iMax - 1 End If If i1 > iMax Then Exit For Next i1 End Sub It will take sheet 1 as default. Please modify if necessary Cheers Shasur Tag: Null Value Error in recordset
  - 3
    - What is the VBA code to insert an Object into an Excel File You can use the Add method of the OLEObjects collection for that Code Snippet Sub Insert_OLE_Object() Worksheets("Sheet1").OLEObjects.Add Filename:="c:\temp\sample.pdf", Link:=False, DisplayAsIcon:=False, Left:=40, Top:=40, Width:=150, Height:=10 End Sub Cheers Shasur Tag: Null Value Error in recordset
  - 4
    - Excel VBA open/select text in Word? Hi My first tip is to try doing it in Word first, and then add the code to Excel. When you get to Excel VBA you need to reference the Word Library (Tools/References/Microsoft Word Object Library) The following is an imperpect example of getting everything out of all tables in to the Activesheet. Sub test() Dim xla As Excel.Application Dim wda As Word.Application Dim tbl As Word.Table Dim dc As Word.Document Dim cl As Long Dim rw As Long Dim rwE As Long Dim strFN As String Set xla = Excel.Application Set wda = New Word.Application strFN = xla.GetOpenFilename("Word Document (*.doc), *.doc") If strFN = "False" Then Exit Sub Set dc = wda.Documents.Open(strFN) For Each tbl In dc.Tables rwE = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 For cl = 1 To tbl.Columns.Count For rw = 1 To tbl.Rows.Count Cells(rwE + rw, cl) = tbl.Cell(rw, cl).Range.Text Next rw Next cl Next tbl dc.Close wda.Quit MsgBox "Done" End Sub Regards Peter Mo Tag: Null Value Error in recordset
  - 5
    - Read data from excel through VB.NET Hi Prasanth, Here's some information from our support engineer: The partner is wonder why he got the abc, not the 汐汕污﹝ It is designed by EXCEL. When the cell data is 汐汕污 in the EXCEL, you will find that the font of the cell is symbol and the exact data showed in the fx above the booksheet is abc, not the 汐汕污 . So when you get it from the OLEDB, you will get the abc, not the 汐汕污 . As the font can not be got through OLEDB provider, we can not convert the data by its font. In order to resolved the issue. We have two choices. We can input 汐汕污 in the EXCEL through the IME, not using the font of the EXCEL. We can use the EXCEL automation model to read the data in EXCEL and convert them to a special data. When the font is symbol and the data is a, you can convert it to 汐 . I have given the reason why the data you read is not you expected, and some suggestions to work it around. Please let me know, if your issue is resolved or you have any question. -brenda (ISV Buddy Team) Tag: Null Value Error in recordset
  - 6
    - Vba - Directories / Folder tree Hi, you can try this code that i've found somewhere.... Apath = GetDirectory(ThisWorkbook.Path, True, "Caption text") Public Type BROWSEINFO hOwner As Long 'Handle to the owner window for the dialog box. pidlRoot As Long 'Address of an ITEMIDLIST structure specifying the location 'of the root folder from which to browse. Only the specified 'folder and its subfolders appear in the dialog box. 'This member can be NULL; in that case, the namespace 'root (the desktop folder) is used. pszDisplayName As String 'Address of a buffer to receive the display name of the folder 'selected by the user. The size of this buffer is assumed to 'be MAX_PATH bytes. lpszTitle As String 'Address of a null-terminated string that is displayed above 'the tree view control in the dialog box. This string can be 'used to specify instructions to the user. ulFlags As Long 'Flags specifying the options for the dialog box. 'See constants below lpfn As Long 'Address of an application-defined function that the dialog box calls 'when an event occurs. For more information, see the 'BrowseCallbackProc function. This member can be NULL. lParam As Long 'Application-defined value that the dialog box passes to the 'callback function (in pData), if one is specified iImage As Long 'Variable to receive the image associated with the selected folder. 'The image is specified as an index to the system image list. End Type Public Const WM_USER = &H400 Public Const MAX_PATH = 260 'ulFlag constants Public Const BIF_RETURNONLYFSDIRS = &H1 'Only return file system directories. 'If the user selects folders that are not 'part of the file system, the OK button is grayed. Public Const BIF_DONTGOBELOWDOMAIN = &H2 'Do not include network folders below the 'domain level in the tree view control Public Const BIF_STATUSTEXT = &H4 'Include a status area in the dialog box. 'The callback function can set the status text 'by sending messages to the dialog box. Public Const BIF_RETURNFSANCESTORS = &H8 'Only return file system ancestors. If the user selects 'anything other than a file system ancestor, the OK button is grayed Public Const BIF_EDITBOX = &H10 'Version 4.71. The browse dialog includes an edit control 'in which the user can type the name of an item. Public Const BIF_VALIDATE = &H20 'Version 4.71. If the user types an invalid name into the 'edit box, the browse dialog will call the application's 'BrowseCallbackProc with the BFFM_VALIDATEFAILED ' message. This flag is ignored if BIF_EDITBOX is not specified Public Const BIF_NEWDIALOGSTYLE = &H40 'Version 5.0. New dialog style with context menu and resizability Public Const BIF_BROWSEINCLUDEURLS = &H80 'Version 5.0. Allow URLs to be displayed or entered. Requires BIF_USENEWUI. Public Const BIF_BROWSEFORCOMPUTER = &H1000 'Only return computers. If the user selects anything 'other than a computer, the OK button is grayed Public Const BIF_BROWSEFORPRINTER = &H2000 'Only return printers. If the user selects anything 'other than a printer, the OK button is grayed. Public Const BIF_BROWSEINCLUDEFILES = &H4000 'The browse dialog will display files as well as folders Public Const BIF_SHAREABLE = &H8000 'Version 5.0. Allow display of remote shareable resources. Requires BIF_USENEWUI. 'Message from browser to callback function constants Public Const BFFM_INITIALIZED = 1 'Indicates the browse dialog box has finished initializing. 'The lParam parameter is NULL. Public Const BFFM_SELCHANGED = 2 'Indicates the selection has changed. The lParam parameter 'contains the address of the item identifier list for the newly selected folder. Public Const BFFM_VALIDATEFAILED = 3 'Version 4.71. Indicates the user typed an invalid name into the edit 'box of the browse dialog. The lParam parameter is the address of 'a character buffer that contains the invalid name. 'An application can use this message to inform the user that the 'name entered was not valid. Return zero to allow the dialog to be 'dismissed or nonzero to keep the dialog displayed. ' messages to browser from callback function Public Const BFFM_SETSTATUSTEXTA = WM_USER + 100 Public Const BFFM_ENABLEOK = WM_USER + 101 Public Const BFFM_SETSELECTIONA = WM_USER + 102 Public Const BFFM_SETSELECTIONW = WM_USER + 103 Public Const BFFM_SETSTATUSTEXTW = WM_USER + 104 Public Const LMEM_FIXED = &H0 Public Const LMEM_ZEROINIT = &H40 Public Const LPTR = (LMEM_FIXED Or LMEM_ZEROINIT) 'Main Browse for directory function Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long 'Gets path from pidl Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long 'Used by callback function to communicate with the browser Declare Function SendMessage Lib "user32" _ Alias "SendMessageA" (ByVal hwnd As Long, _ ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Any) As Long Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, _ hpvSource As Any, ByVal cbCopy As Long) Public Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal pv As Long) Public Declare Function LocalAlloc Lib "kernel32" _ (ByVal uFlags As Long, _ ByVal uBytes As Long) As Long Public Declare Function LocalFree Lib "kernel32" _ (ByVal hMem As Long) As Long ''The following declarations for the option to center the dialog in the user's screen Public Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long Public Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long Public Const SM_CXFULLSCREEN = 16 Public Const SM_CYFULLSCREEN = 17 Public Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Public Declare Function MoveWindow Lib "user32" (ByVal hwnd As Long, _ ByVal x As Long, _ ByVal y As Long, _ ByVal nWidth As Long, _ ByVal nHeight As Long, ByVal bRepaint As Long) As Long ''End of dialog centering declarations Dim CntrDialog As Boolean Function GetDirectory(InitDir As String, CntrDlg As Boolean, Msg) As String Dim bInfo As BROWSEINFO Dim pidl As Long Dim lpInitDir As Long Dim Flags As Long CntrDialog = CntrDlg ''Copy dialog centering setting to module level variable so callback function can see it Flags = BIF_RETURNONLYFSDIRS 'Flags = Flags + BIF_EDITBOX 'Flags = Flags + BIF_VALIDATE Flags = Flags + BIF_STATUSTEXT ''''''''''' PARA PODER SELECCIONAR FICHEIROS 'Flags = Flags + BIF_BROWSEINCLUDEFILES Flags = Flags + BIF_NEWDIALOGSTYLE With bInfo .pidlRoot = 0 'Root folder = Desktop .lpszTitle = Msg .ulFlags = Flags lpInitDir = LocalAlloc(LPTR, Len(InitDir) + 1) CopyMemory ByVal lpInitDir, ByVal InitDir, Len(InitDir) + 1 .lParam = lpInitDir 'If Val(Application.Version) > 8 Then 'Establish the callback function .lpfn = BrowseCallBackFuncAddress 'Else ' .lpfn = AddrOf("BrowseCallBackFunc") 'End If End With 'Display the dialog pidl = SHBrowseForFolder(bInfo) 'Get path string from pidl GetDirectory = GetPathFromID(pidl) CoTaskMemFree pidl LocalFree lpInitDir End Function 'Windows calls this function when the dialog events occur Function BrowseCallBackFunc(ByVal hwnd As Long, ByVal Msg As Long, ByVal lParam As Long, ByVal pData As Long) As Long Select Case Msg Case BFFM_INITIALIZED 'Dialog is being initialized. I use this to set the initial directory and to center the dialog if the requested SendMessage hwnd, BFFM_SETSELECTIONA, 1, pData 'Send message to dialog If CntrDialog Then CenterDialog hwnd Case BFFM_SELCHANGED 'User selected a folder - change status text ("show status text" option must be set to see this) SendMessage hwnd, BFFM_SETSTATUSTEXTA, 0, GetPathFromID(lParam) Case BFFM_VALIDATEFAILED 'This message is sent to the callback function only if "Allow direct entry" and '"Validate direct entry" have been be set on the Demo worksheet 'and the user's direct entry is not valid. '"Show status text" must be set on to see error message we send back to the dialog Beep SendMessage hwnd, BFFM_SETSTATUSTEXTA, 0, "Bad Directory" BrowseCallBackFunc = 1 'Block dialog closing Exit Function End Select BrowseCallBackFunc = 0 'Allow dialog to close End Function 'Converts a PIDL to a string Function GetPathFromID(ID As Long) As String Dim Result As Boolean, Path As String * MAX_PATH Result = SHGetPathFromIDList(ID, Path) If Result Then GetPathFromID = Left(Path, InStr(Path, Chr$(0)) - 1) Else GetPathFromID = "" End If End Function 'XL8 is very unhappy about using Excel 9's AddressOf operator, but as long as it is in a ' function that is not called when run on XL8, it seems to allow it to exist. Function BrowseCallBackFuncAddress() As Long BrowseCallBackFuncAddress = Long2Long(AddressOf BrowseCallBackFunc) End Function 'It is not possible to assign the result of AddressOf (which is a Long) directly to a member 'of a user defined data type. This explicitly "converts" it to a Long and 'allows the assignment Function Long2Long(x As Long) As Long Long2Long = x End Function 'Centers dialog on desktop Sub CenterDialog(hwnd As Long) Dim WinRect As RECT, ScrWidth As Integer, ScrHeight As Integer Dim DlgWidth As Integer, DlgHeight As Integer GetWindowRect hwnd, WinRect DlgWidth = WinRect.Right - WinRect.Left DlgHeight = WinRect.Bottom - WinRect.Top ScrWidth = GetSystemMetrics(SM_CXFULLSCREEN) ScrHeight = GetSystemMetrics(SM_CYFULLSCREEN) MoveWindow hwnd, (ScrWidth - DlgWidth) / 2, (ScrHeight - DlgHeight) / 2, DlgWidth, DlgHeight, 1 SetWindowText32 hwnd, TWNAME ' SendMessage hwnd, BFFM_SETSTATUSTEXTA, 1, "TESTE!!!!!!!!" End Sub HTH JR Tag: Null Value Error in recordset
- Next
  - 1
    - LCS 2005 SP1 Server-Side Contact Lists Not Populating within Communicator 2005 Per the support engineer: It seems you are facing some issue while using the LCSAddContacts.wsf script. However I am not very clear on what happens when you run the command with the user and contacts file as parameters. It will be great if you can provide me more detailed explanation of what is failing and I can help you with it. Also, in the case the command you have mentioned is ※CScript LCSAddContacts.wsf /userfile:contacts.txt /contactsfile:contacts.txt§, however the command should be as follows: ※cscript lcsaddcontacts.wsf /usersfile:contacts.txt /contactsfile:contacts.txt§ Looking forward for more clear explanation so that I can help you accurately. -brenda (ISV Buddy Team) Tag: Null Value Error in recordset
  - 2
    - run an exe file in excel using VB What do you mean by - "run an exe file in excel" Do you want to open an excel file or do something else Are you using vb.net (VB express) or are you using VBA in excel Tag: Null Value Error in recordset
  - 3
    - Some questions about WER Take a look at my reply to this post around processing delays. Kind regards, -Jason Tag: Null Value Error in recordset
  - 4
    - public variable between sheet code and userform Hi, You need to reference your public variable via the sheet object. For example in sheet1 code module you have a public variable MySheetVar and on the sheet a commandbutton with the following click event code, Code Snippet Public MySheetVar As Long Private Sub CommandButton1_Click() MySheetVar = 123 UserForm1.Show End Sub Userform1 has a commandbutton with the following click event code to display the value Code Snippet Private Sub CommandButton1_Click() MsgBox Sheet1.MySheetVar End Sub Alternatively place your public declaration in a standard code module rather than the sheet code. Then you can just use the variable name. Tag: Null Value Error in recordset
  - 5
    - Help Deploying VBA changes across hundreds of Word Temlates Hi, Thats a nasty one mate, but it could be possible. Your going to need to write something that selects the word template, or ideally a group of templates, and then opens each one and modifies the VBAProject. Have a look at this excellent article http://www.cpearson.com/excel/vbe.htm It gives examples of how you can write VBA code that modifies VBA code. Select a group of templates that you want to modify the same way, open each template, modify it's VBA to whatever you need, and then save it. Hopefully that should do the trick. Tag: Null Value Error in recordset
  - 6
    - A question from a beginner... welcome to vba programming world. vba is suitable to the novice as like you. i 'd like to tell a reference from my bookmarks. plz refer to http://www.fontstuff.com/downloads/index.htm#excelvba best regards sjoo Tag: Null Value Error in recordset
  - 7
    - About iexplorer 7 error. I get this error as well but i cannot figure out how to fix it but i have EI 6 not 7 Tag: Null Value Error in recordset
  - 8
    - Excel Pivot Table Hi, I am populating the Excel Pivot table from the sql OLAP cubes . I am having 2 items in the Data Field list and I dragged and droped those two in the pivot table and every thing is working fine .But the problem is when i am selecting two rows and asking to group by right clicking the Data field , It is grouping the rows but it is showing only one column insted of two and the 2nd column is disappering and it is also disappering from the Data Field List and coming back when i say unGroup. I could able to figure out if it is a front end (Excel ) issue or it is a backend (OLAP) issue. Can any one help me on this and also let me know if I am missing any thing in setting the properties. Tag: Null Value Error in recordset
  - 9
    - VBA Word Can't find project or library Hi, RTrim is in the VBA library C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL You could get the client to check there references. Within the VBE use Tools > References missing entries will be displayed at the top of the list. Tag: Null Value Error in recordset
  - 10
    - Excel: Public variable doesn't work Hi, Thisworkbook is a special class rather than a code module. So the Public declaration here means the variable is visible to all routines in Thisworkbook rather than all routines in the project. Either move the declaration of the variable to a standard code module. You can leave the routine ModuleA in thisworkbook if you want. Or change the way you reference the variable within the userform. ThisWorkbook.PsResult = "derived value" Tag: Null Value Error in recordset
  - 11
    - Inserting Comments in Excel...from a Beginner Here's one possible solution. It assumes that, by comments, you are referring to the yellow post-it type comments that pop up when you hover over a cell (I'm actually not aware of any other type of comment anyway, but just in case!). I have made bold the particular part of the code that answers your question. The rest is just a suggestion. Sub AddComments() Dim dataRange As Range Dim cell As Range 'Prompt user to define range containing values in Sheet2 and set dataRange accordingly Set dataRange = Application.InputBox("Select range containing the values to convert into comments", Type:=8) 'Loop through each cell in the specified range, ignoring blank cells For Each cell In dataRange.SpecialCells(xlCellTypeConstants).Cells 'Add a cell comment in the corresponding cell in Sheet1 Sheets(1).Range(cell.Address).AddComment (cell.Value) Next End Sub Tag: Null Value Error in recordset
  - 12
    - Time Addition hello Walkenbach's a web page would be of help. plz refer to http://www.j-walk.com/ss/excel/files/timesht.htm Tag: Null Value Error in recordset
  - 13
    - Excel/Word VBA IDE - Additional Controls Dialog Box refuses to display I have a problem trying to add additional controls to the Controls toolbox The two methods I've tried 1. Right click on the controls toolbox , click Additional controls 2. Menu, Tools, Additional controls Neither of the above produce a dialog box containing the list of additional controls to choose from. Things I have tried so far.... Office Update, Reinstalling Office 2000, uninstalling Office 2000 and installing Office XP (2002) Please help any ideas Tag: Null Value Error in recordset
  - 14
    - Excel Time Help hI Have a look at the datediff function, you can get the return value in minutes for time difference. Tag: Null Value Error in recordset
  - 15
    - Excel import I am probably not the one to answer your question my friend but is all of your code too long to post, can we get a better feel for what you are importing, readable text (sentences), numbers that are previously formated as text (string on a .doc), words that will populate your xlcells one at a time I use an external Macro Recorder to do exactly the same task over and over, it also clicks on the screen at specific X and Y coordinates, to copy data from a .doc, paste it in the next previously selected xlcell, I dont think it will do your work in under a minute though and I would like to work on your VBA needs first if possible. I am only a beginner, still, been struggling with VBA for some time. Tag: Null Value Error in recordset
  - 16
    - Use of InStr() function Sounds like you'd have to loop through each character in the string and examine if that char is what you want, and if it is, exit the loop. Tag: Null Value Error in recordset

Roborean

I have encountered a problem when returning a record set which contains a Null value. Please can any one help

I am trying to determine if a date field against a client is empty but if it is empty, when I try to get the value, I get an error message when it gets to dteDateCreated - "Invalid Use Of Null"

Dim SQLText As String
Dim rs As Recordset
Dim strClientCode
Dim dteDateCreated As String
Dim strCode As String

strClientCode = [Forms]![frmClient].[ClientCode]

SQLText = "SELECT tblClient.ClientCode, tblClient.DateCreated " & vbCrLf & _
"FROM tblClient " & vbCrLf & _
"WHERE (tblClient.ClientCode) = " & strClientCode & ";"

Set rs = CurrentDb.OpenRecordset(SQLText)
strCode = rs.Fields("ClientCode").Value
dteDateCreated = rs.Fields("DateCreated").Value

It works OK if there is a date present.

I am using Access 2000 on Windows XP.

Top

Re: Null Value Error in recordset

ADG

Checkout the NZ function, chnage your code to

dteDateCreated = nz(rs.Fields("DateCreated").Value,0)

Top

Re: Null Value Error in recordset

Roborean

ADG

Thanks for the suggestion. I will give it a go.

I got round the problem by

If IsNull(dteDateCreated = rs.Fields("DateCreated").Value) Then .......

Top