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
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
My formula looks like this:
=IF('CH Milestones'!GM5<Sheet1!A$2,VLOOKUP('CH Milestones'!GM5,'CH
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
What's this: (Avril*cGST)
If it is meant to be 2 fields multiplied together, supply an alias for the
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:
Private Sub TextBox01_AfterUpdate()
Private Sub TextBox02_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Private Sub TextBox03_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Private Sub TextBox04_Change()
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
>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.
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
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
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"fullers" <email@example.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!
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
Operating System: Mac OS X 10.6 (Snow Leopard)
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 "name bar" 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
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.
Private Sub tbc_afterupdate()
If Me.tbc = True Then
Me.Section(0).BackColor = 65535
Me.Section(0).BackColor = 16777215
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.
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
I currently have the file paths for the text files hard-coded into the
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?
John A...Help with VB Code
OK...keep in mind I'm VERY new to VB. Look at this code:
Application.ScreenUpdating = False
If Range("C23").Value <> "4C" Then
.CheckBoxes("Check Box 56").Value = False
msg = "Color is unavailable for this page using the selected pag
MsgBox msg, vbOKOnly, "Runsheet Alert"
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
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',
AND (M2K_Timestamp < CONVERT(DATETIME, '...excel and VB Open Dialog box
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.
I want to add an Open Fi...AfterUpdate Question!
I want to add this to my code , Ony enter date if [DateCheck] (checkbox)
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")
Me.tbDayNo=IIf(Me.DateCheck And IsNull(tbDayNo), _
"Bob V" <firstname.lastname@example.org> wrote in message
| I want to add this to my code , Ony enter date if [D...BeforeUpdate or AfterUpdate ?
I have an unbound form in which I have (amongst others) two comboboxes with
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
Msgbox(“You must choose a value”)
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:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Application.CutCopyMode = False
As u can see the range changes, but I would like to create the sam
name for whatever the range is?
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
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
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.
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"
tail = Mid(n.Name, 6)
Debug.Print Sheets("Records").Cells(row, Range("RECORDS_"
...Problem with data using IF and Nested IF statements possibly???
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.
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?
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
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...