Access Compile Error

[SeanD]

Trying to do the most simple VBA code and can't get it to work. Tried the two methods below (and every different way you could think of doing it using these two methods) and got the same error message

Compile Error: 
              Expected Function of variable

Can anybody please help?

   Dim dbs As Database
    Dim RegionCode As String
    Dim strSQL As String
 
    Set dbs = CurrentDb()
 
    strSQL = "SELECT MAX([HeadOfficeRegionCode]+0.1)FROM   tlkHeadOfficeRegion"
 
    RegionCode = CurrentDb.Execute(strSQL, dbFailOnError)
    Me.HeadOfficeRegionCode.Text = RegionCode
 
******************************************************************************************
 
   RegionCode = DoCmd.RunSQL("SELECT MAX([HeadOfficeRegionCode]  +0.1)  FROM tlkHeadOfficeRegion")
 
    HeadOfficeRegionCode.Text = RegionCode

Thx

Sean

Email address deleted per site rules!

[missinglinq]

Site rules prohibit the posting of email addresses, and so yours has been removed from your original post.

Perhaps if you could explain your problem in a little more detail we could be of some assistence. Your code leaves me somewhat baffled as to exactly what you're trying to do, especially this portion:

MAX([HeadOfficeRegionCode]+0.1)

Linq ;0)>

[SeanD]

Site rules prohibit the posting of email addresses, and so yours has been removed from your original post.

Perhaps if you could explain your problem in a little more detail we could be of some assistence. Your code leaves me somewhat baffled as to exactly what you're trying to do, especially this portion:

MAX([HeadOfficeRegionCode]+0.1)

Linq ;0)>

************************************************** ******************************************

Thanks Linq, I wasen't aware of the email rule.

All i'm trying to do is select the largest value in the "HeadOfficeRegionCode" field of the table tlkHeadOfficeRegion, add .1 to it and return that value to a textbox called HeadOfficeRegionCode.

Get Lagest value from HeadOfficeRegionCode
Add: .01
Return to HeadOfficeRegionCode textbox


Hope that clears things up,

Thanks

Sean

[Denburt]

Well I don't see any error trapping so when you receive the error you should have a button that says debug. This will highlight the offending line and may help us resolve your problem.

There are so many ways this can be written but this may help.

Dim dbs As Database
    Dim RegionCode As String
    Dim strSQL As String
Dim rs as recordset
    Set dbs = CurrentDb()
    strSQL = "SELECT MAX([HeadOfficeRegionCode]+0.1)FROM   
tlkHeadOfficeRegion"
 
set rs = dbs.openrecordset(strSQL)
    RegionCode = rs!MaxOfHeadOfficeRegionCode
    Me!HeadOfficeRegionCode.Text = RegionCode
 

Let us know if that helps you any.

[Denburt]

Also FYI don't forget to close the recordset and release any objects.

rs.close
set rs = nothing

[missinglinq]

I have merged your duplicate threads of this problem. When posting a question, please show a little patience as it sometimes takes 20 or 30 seconds for a post to go thru. Double posting makes it very hard for people to answer you especially if there are answers happening in all the threads you have started because they have to read 2 or more threads in order to see what has already been said.

Thank you for your attention in this matter.

Linq ;0)>

[missinglinq]

SeanD, you have posted this question three times within the past 24 hours! Your third post, like your second one, has been deleted! It was explained to you why you should not do this, and you have been asked politely to refrain from doing so in the future. If you continue to ignore this request, we will be forced to ban you from this site.

Please take the time to read the Posting Guidelines

Linq

[NeoPa]

Sean,
A post count of 3 and you're already on my radar. Nice going!

Let's start again here. There are site rules that you've already been directed to so I won't repeat that. What I will say, however, is that simple courtesy demands that you reply to those who take the time to respond, even if simply to inform them that they were not what you were after.

Further, I only have the one copy of your question to go on but it's clear that you weren't very bothered to post it correctly. The error message is either a very strange one or you meant 'or' instead of 'of'. This is not the only example of writing the question wrongly that you clearly didn't check. This sort of laziness possibly saves you a couple of seconds but can add a fair amount of wasted time to everyone who tries to assist. If you seem to treat their time with such disrespect, do you really think they will be bothered with your problem when they see that?

It is very early days yet so this situation can be changed around if you would like it to. If this type of behaviour stops then we can very easily forget it ever happened. Alternatively, as you've already been told, much more of this type of posting will get your account banned and we'll never have to worry about each other again.
I sincerely hope we see the former and are able to provide you with lots more help in the future.

-NeoPa
Access Forum Leader.

[SeanD]

Thank you Denburt, that solves my problem. I didn't realize that you needed to use a DAO object when working with a local database.

Neopa - You're right, I should have used an or in the compile error statement. I'll try to proof read my posts more diligently next time.

Sean

[NeoPa]

Thank you Denburt, that solves my problem. I didn't realize that you needed to use a DAO object when working with a local database.

NeoPa - You're right, I should have used an or in the compile error statement. I'll try to proof read my posts more diligently next time.

Sean

That's OK Sean.
I can understand the frustration of trying to wait for a solution when the problem is urgent.
There's always a "post and wait" element to getting new help from a web site though (by its very nature - however frustrating that is). The only immediate solution there is finding a problem that's already been answered.

Good luck with your project :)

[Denburt]

Thank you Denburt, that solves my problem. I didn't realize that you needed to use a DAO object when working with a local database.

Neopa - You're right, I should have used an or in the compile error statement. I'll try to proof read my posts more diligently next time.

Sean

Glad I could help, as I said there are so many ways this can be accomplished yet I have been using this method for so long it is usually the quickest way for me to spit it out.

As a quick note if you stick around for a while and try to share some of your knowledge with others then you will find your response times tend to get much shorter since you have an established reputation and the people you have tried to help will be much more inclined to respond as quickly as they can. Not that any one here put people off or try to make them wait it is just a busy, busy place with so many people the more people you get to know the better.

Good luck hope to see you round.

Also FYI and mentioned in