VB Runtime error 3159I 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 statementI 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 errorsHi, 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 SetFocusI 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 MacroI 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 editorJust 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 VBI 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 statementsVersion: 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 "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 ...
AfterUpdateHello 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 statementHere 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 emailhow 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 SaveI 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 CodeOK...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' mathHello,
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 boxHi 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 RangesCan 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 formHey 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 SubFormGoooooooood 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 inexplicablyMy 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 ProblemI 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 CodeDoes 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...