By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Macro conditions not being executed, even when met.

100+
P: 118
Hi folks,

I have a system for an auctioneer and have on the form I have the fields: lot (the item), reserve price ([Reserve]) and minimum successful bid so far ([Min]). There is then a text box for the user to input their bid ([Bid]). I have set up a macro for this:
Expand|Select|Wrap|Line Numbers
  1. mcr_addBid:
  2. Condition               Action           
  3. isNull([Lot])           MsgBox
  4. isNull([Lot])           Stop Macro
  5. isNull([Bid])           MsgBox
  6. isNull([Bid])           StopMacro
  7. [Bid]<=[Reserve]    MsgBox
  8. [Bid]<=[Reserve]    StopMacro
  9. [Bid]<=[Min]          MsgBox
  10. [Bid]<=[Min]          StopMacro
  11. [Bid]>[Min]            MsgBox
  12. [Bid]>[Min]            StopMacro
  13.  
at the moment I've only set it up for msg boxes to test it, but it works fine if either Lot or Bid is empty but then any value in bids doesn't run any of the latter actions, it's not that it gets stuck on one, it doesn't do any of them at all.

Am I doing something wrong with the conditions? (I obviously am doing something, but I don't know what).

Thanks,
Sam
May 16 '08 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Sam, I should warn you that, although some of our members do know a bit about using macros, it's very rarely used professionally.

Most of us use VBA exclusively in our projects.
May 16 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
I did try to go through the logic of it and I found it difficult without any data posted.

Perhaps if you posted a set of data for all the controls you refer to and explain how it behaved when you tested with that data someone could see the problem more easily.
May 16 '08 #3

100+
P: 118
Ok fair point.

Let's say I have a lot for a 1 bedroom apartment, when the user chooses this (from a combo box, 'Lot') the values of 'Min', 'Reserve' change accordingly.

So they choose 1 bedroom apartment and the value appears in Min as 109,400 and Reserve as 90,000.

There is then a text box for them to enter their bid in, which is named 'Bid'. When they press the "Make Bid!" button it should execute the macro. If there is no Lot selected they get an error message saying "Please choose a lot to bid for"; that works. If there is a Lot but no value in Bid, or the Bid is less than 1000 then they get an error message saying "Please enter a substantial bid"; that also works.

However as soon as they enter a number over 1000 nothing at all executes. Even if it's 1001 or 1,513,500.

Any ideas?
May 18 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
Like most here, I never use macros with the exception of AutoExec and AutoKeys, but the one thing I see glancing at this, and it relates to the portion of the macro that's bombing, is that you have a field named Min. Min is a Reserved Word in Access, and needs to be changed, whether it's causing your problem or not.

Linq ;0)>
May 18 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
I did try to go through the logic of it and I found it difficult without any data posted.

Perhaps if you posted a set of data for all the controls you refer to and explain how it behaved when you tested with that data someone could see the problem more easily.
When I suggested this I had no idea you'd find it complicated.

I'm afraid your latest post leaves more questions than answers. I can't see any correlation between the figures and macro code on the one side and 1,000 on the other.

As before, a full set of data that makes sense would be really quite helpful when trying to guess what may be going wrong.
May 19 '08 #6

missinglinq
Expert 2.5K+
P: 3,532
I keep reviewing this and like NeoPa, my head's swimming. One thought did occur to me; when comparing Numerical/Currency data and the comparisons fail to function as one would expect, one of the first thing to look at is the Datatypes of the data. Are you sure that all of your fields, bid, reserve and min (and you really need to change the name of min to something else) are either Currency or Numerical? Having one or more of them defined as Text would foul up the comaprison operations.

Linq ;0)>
May 19 '08 #7

100+
P: 118
I usually do use VBA but for this I have to use Macros. The 1'000 part is using exactly the same macro only I have

Expand|Select|Wrap|Line Numbers
  1. IsNull([Bid]) Or [Bid]<1000
  2.  
Which works fine.

I think I've sorted the problem now though. The values for each field was found by a query linked to the rowsource of the fields. I've now made the Control source as DLookUp or DMin to get the same values. Now each step of the macro works.

Thanks for the help, guys.
May 19 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
So the code you're actually testing with is different from the code you posted and we can see :S

Am I missing something here?

Clearly if you have any lines which check against a value of 1,000 then that's where you'd need to look for the logic flaw. We'd help, but it's not easy when you don't have the same code as you're testing with.
May 19 '08 #9

100+
P: 118
You did have the same code I was testing, only with that 1 minor change, but as I said from the beginning that part of the code executed; with the <1000 or without it. So the problem was within the latter part, which you had exactly what I was using. =)

The row source/control source part has fixed it now so it works perfectly.

Sam
May 20 '08 #10

NeoPa
Expert Mod 15k+
P: 31,186
I'd better get my degree in Mind-Reading sorted out then I suppose :(
May 20 '08 #11

missinglinq
Expert 2.5K+
P: 3,532
Like I've said to OPs before, the fact that many of us here are out of our minds doesn't mean that we're in their minds!

I guess we need to get MS Access-Ouija Board Edition!

Linq ;0)>
May 20 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.