Reply:

VB Runtime error 3159
I have a combo box that when selected pulls up records in another table. It's worked flawlessly in Access 2003-2007. But in Access 2010 I get the 3159 error, and a notice that a bookmark is invalid. Here's the code in the debugger Private Sub Combo20_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Lname] = '" & Me![Combo20] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub This is the code the debuugger highlights: Me.Bookmark...

VLOOKUP with IF statement
I want to automatically produce a list of all events that will occur between two given dates. I am using VLOOKUP to search the column which has the date for each event. I want to return the names of all the events that occur before the user-defined dates. My formula looks like this: =IF('CH Milestones'!GM5<Sheet1!A$2,VLOOKUP('CH Milestones'!GM5,'CH Milestones'!GL$5:GM$87,2,TRUE),"") GM is the column with the dates. GL is the column with the event names. A2 has a user-defined date entered into it. (In the above formula I was only looking for dates b...

Insert statement with errors
Hi, I am getting the error Too few parameters Expected 1 when i try to run the following SQL statement: strSQL = "INSERT INTO BudgetGST ( Idbranch, Avril )" & _ " SELECT Idbranch, (Avril*cGST)" & _ " FROM GrossMois WHERE GrossMois.IdBranch = " & num Avril is a field from GrossMois that contains a value and cGST is a constant. can anyone tell me what is wrong Thanks Alain What's this: (Avril*cGST) If it is meant to be 2 fields multiplied together, supply an alias for the result: strSQL = "INSERT INTO B...

AfterUpdate/BeforeUpdate and SetFocus
I have some problems with the SetFocus command in a Userform with an AfterUpdate or BeforeUpdate routine. To make it clear to you, I made a very small Userform to demonstrate it. It only contains 8 TextBoxes and this code: Code: Private Sub TextBox01_AfterUpdate() Me.TextBox08.SetFocus End Sub - Private Sub TextBox02_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) TextBox08.SetFocus End Sub - Private Sub TextBox03_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox08.SetFocus End Sub - Private Sub TextBox04_Change() TextBox08.SetFocus End Sub I expected that in ...

Password To Open Spreadsheet Used as Variable in VB Code or Macro
I would like to set a variable to be 1 of 7 values based on what password is used to open a spreadsheet. Does any know if this is possible?? If so, please provide some helpful hints. Thanks for your help. How do you set more than one password to open a workbook? Or do you mean password after opening? Like a password to access a certain sheet in the workbook? Gord Dibben MS Excel MVP On Tue, 16 Mar 2010 14:12:02 -0700, DogLover <DogLover@discussions.microsoft.com> wrote: >I would like to set a variable to be 1 of 7 values based on what password is >us...

VB Question #2
Thanks exactly what I wanted. Got it to work fine, thanks. Gle -- GlenS1 ----------------------------------------------------------------------- GlenS18's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=945 View this thread: http://www.excelforum.com/showthread.php?threadid=26270 ...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

if then statement in VB
I have a cell say A2 that has a number in it .... I would like to return a number value in another cell. I can write an if then else statement: if(a2 = 5 then,1,if(a2=10,2,if(a2=15,3))) etc. but this gets long (i have 30-40 parameters) I know there has to be a way to write it in VB so that I could just go down a list (if I need to change the value later) but for some reason I am having a problem with the language. I would appreciate any help! -- Jackie Is your example true to life, or just an example? If it's true to life, then instead of code you might use just a formula in the...

How to use nested IF statements
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi everyone, <br> was wondering if anyone could help me figure out using nested IF statements in excel 2008.I'm used to using it in windows XP and the procedure is pretty straight forward: to nest a statement you just click on the IF button in the &quot;name bar&quot; when you are in the IF False box.....that doesn't seem to work here....any suggestions would be appreciated,thanks. Dunno what "clicking on IF" does, but just type away -- making sure you know whether you want ...

AfterUpdate
Hello Chaps, In the AfterUpdate code I had the detail back colour change if a check box (tbc) = True. However if I tick the box now it turns the back colour of every form irrespective if that record is ticked tbc or not! Any help on solving this would be great. JAMES Private Sub tbc_afterupdate() If Me.tbc = True Then Me.Section(0).BackColor = 65535 Else Me.Section(0).BackColor = 16777215 End If End Sub If you are using a continuous form, that code won't work. The good news is that you can use Conditional Formatting to make it work. In Design View, select the combo box and fro...

What IF statement
Here is what I would like to do: Don’t know if an IF statement will suffice here or not: In column H I either have S1,000 or it is blank Column J is blank What I would do is if H(?) = $1,000 and L (?) is > than 100% then put $1,500 in Column J Also if H(?) is blank and L(?) is > 100% put $2,500 in Column J However if l(?) is <100% put zero in Column J See reply in microsoft.public.office.misc Please don't post the same question multiple times in different newsgroups - you can post to multiple newsgroups simultaneously, if need be, so that an answer in one newsg...

adding disclosure statements to outgoing email
how to i add a disclosure statements to outgoing email in exchange 2003 This is probably one of the most common questions that gets asked in the newsgroups. You will need a third party product to do this. I blogged a couple of entries about this, if you are interested. http://mostlyexchange.blogspot.com/2004/07/how-do-i-add-disclaimer-to-outgoing.html http://mostlyexchange.blogspot.com/2005/04/much-ado-about-disclaimers.html The second one is a sarcastic look at disclaimers as I'm not all that certain that they are effective. If this is supposedly a legal disclaimer, make sure t...

VB File Save
I have an Excel 97 workbook that produces a sheet of cheque-print data. I have a VB macro that reads data from a text file from another application into the workbook, and another VB Macro that saves data on this sheet to a comma-delimited text file (for input to Word for cheque printing). I currently have the file paths for the text files hard-coded into the VB macro. I would like to be able to select the paths from a standard Windows File Open and File Save box, but the only option I can find is to enter the path in an InputBox, which is not very good. Can this be done? Thanks John A...

Help with VB Code
OK...keep in mind I'm VERY new to VB. Look at this code: Application.ScreenUpdating = False With ActiveSheet If Range("C23").Value <> "4C" Then .CheckBoxes("Check Box 56").Value = False msg = "Color is unavailable for this page using the selected pag count." MsgBox msg, vbOKOnly, "Runsheet Alert" End If End With OK...Instead of range "C23" being limited to "4C", I want it to sa something along the lines of: If Range("C23").Value <> "4C" or "1C" What's the proper way to...

Issues with Select statement doing 'simple' math
Hello, I have tried numerous solutions to what should be a simple issue, but I have had no success. I am trying to gather some simple math values from a single table, and I cannot create the proper select statement. The below query works and provides the correct values: SELECT TOP (100) PERCENT MAX(DistCode) AS DistCode, MAX(DistName) AS DistName, MAX(CustServRep) AS CustServRep, COUNT(DistCode) AS AllOrders FROM dbo.[TABLE OrderHistory] AS OH WHERE (M2K_Timestamp >= CONVERT(DATETIME, '2010-03-01 00:00:00', 102)) AND (M2K_Timestamp < CONVERT(DATETIME, '...

excel and VB Open Dialog box
Hi guys, I want to add an Open Dialog Box in an excel file, so the user can browse for a csv file and then add it as a worksheet in the original excel file. It's going to be like this: 1. The excel file (called main.xls) has 4 Open Dialog Boxes. 2. When the user clicks on the 'Browse' button beside the first dialog box, he can browse for a csv file. 3. Let's say he chose a csv file named file1.csv... this csv file will be saved in a worksheet of main.xls. How do I go about this? Please start with how I can create a Dialog Box. Thanks! Hi guys, I want to add an Open Fi...

AfterUpdate Question!
I want to add this to my code , Ony enter date if [DateCheck] (checkbox) is -1 Any help would be great..............Thanks Bob Private Sub tbAdditionCharge_AfterUpdate() If tbDayNo.value = "" Or IsNull(tbDayNo.value) Then tbDayNo.value = Format(Now, "dd-mmm-yy") End If End Sub Hi Bob, Me.tbDayNo=IIf(Me.DateCheck And IsNull(tbDayNo), _ Format(Date,"dd/mm/yy"),tbDayNo) Regards Jacob "Bob V" <rjvance@ihug.co.nz> wrote in message news:%23xApdzD7HHA.4880@TK2MSFTNGP03.phx.gbl... | | I want to add this to my code , Ony enter date if [D...

BeforeUpdate or AfterUpdate ?
Hi all, I have an unbound form in which I have (amongst others) two comboboxes with mandatory data. Although I present a combobox, limited to the choices to pick out, people manage not to fill in those data and to jump via the TAB-key to the next field. Of course I’ll have NULL value for this combobox, but how can I force the user to look for the correct row ? In the BeforeUpdate or in the AfterUpdate event ? I have some code like : Private Sub CboAfterUpdate() if isnull(me.Cbo) then Beep Msgbox(“You must choose a value”) Me.cboAfter.setfocus endif end sub But this does not se...

VB Code for Naming Ranges
Can somehere tell me how to name a range in excel using VB code. Here' how I get the range: Range("L1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False As u can see the range changes, but I would like to create the sam name for whatever the range is? Thanks. -- Message posted from http://www.ExcelForum.com the code you can use is ActiveWorkbook.Names.Add Name:="YO...

Click ok to MessageBox in vb.net triggers form.activate in calling form
Hey guys, We're in the process of converting a vb6 app to vb.net 2008 and are having some minor issues. One of these is that it appears that the form.activate code is fired after clicking ok to any messageboxes that we display. Is this normal? Is there any way to call a messagebox in a way that won't fire the form.activate when clicking ok? We have much of our form setup code in there which we don't want run again. Sure we can code around this with flags but I thought maybe there's a way to call the msgbox with an optional param or similar? Thanks in A...

using vb code to move the record selector of a SubForm
Goooooooood evening, Im using MS Access 2000. I have FormA and FormB....FormB has a subform on it called "SUBformB" which is in datasheet view...If a user clicks a button on FormA i'd like FormB to open up and the Record Selector on SUBformB moved to a specific record. In short, how would i move SUBformB's Record Selector using vb code running from FormA? As always, any help is appeciated. WebDude. MS Access 2000 note to self: http://groups.google.ca/group/microsoft.public.access.forms/browse_thread/thread/2e2cf264744126fd/2ec3e97b8810b604?lnk=gst&q=highlighting+subf...

VB exiting silently and inexplicably
My vb code is mysteriously aborting without any error or warning. It's got me puzzled in the extreme. The following code copies values from cells from one sheet into the columns of a row on a second sheet (with debugging stuff added): For Each n In ScanSetup.Names If Left(n.Name, 4) = "EDIT" And n.Name <> "EDIT_ClientTitle" Then tail = Mid(n.Name, 6) Debug.Print Sheets("Records").Cells(row, Range("RECORDS_" + tail).Column).Value Debug.Print Sheets("Edit").Range(n.Name).Value ...

Problem with data using IF and Nested IF statements possibly???
Afternoon All I am attempting to analyse data from multiple worksheets from numerous people the incoming data all has one thing in common column A this is a certain frequency a job is done. The problem is that there are many ways of entering the data ie 12 months or 52 weeks or 365 days all essentially meaning the same thing. My idea is to collate the data in col A and then using the Helper column as the standard frequencies ie if cell A1 = 12 months, closes frequency in helper is 52 weeks therefore value in C1 = 52 weeks. By using an IF statement I can change the value of one frequen...

AfterUpdate Problem
I need to flash an "RE-ORDER ALERT" message after inventory level falls below re-order level. The message only flashes up when I move to a next record and back after a DoCmd.Save. I need the message to flash immediate after a Save. Note. The message is a flashing label on the form. Thank you. Perhaps you could code it to actually flash before DoCmd.Save is processed. Once you're into that code string, it will be run. Or, maybe consider a Message Box. I like this idea because it forces the user to interact with the reorder message. No one can say "I didn'...

VB Code
Does anyone have a sample bit of VB vode I could use in a modified form to open a new window via a push button on a modified form? TIA Hits -- Do you mean you wish to activate another stock button on a GP form that opens another window? Just set that button equal to one private sub CallSave me.Save = 1 end sub The above would activate a form's Save button if it has one (and that button has been added to the VBA module. I have used Extender to create a form that is linked to the Item Maintenance Form that is normally accessed via the Extras option in the main toolbar, and I h...