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

Visual Basic in MS Access '07?

P: 6
I just installed MS Office Enterprise on my system today and jumped right in to MS Access. Because I am familiar with Access I had very few problems navigating around. Here is the big question.

Where is the Visual Basic Code???

I made a form and placed an Add Record button on the form. When the user clicks this button, I want to focus to go the the first field on the form. I usually do this with the setfocus command in visual basic. When I clicked on the Visual Basic icon on the ribbon, the Visual Basic window pops up, but there is no code at all except for the command Option Compare Database.

What is going on?

Any help would be appreciated.
Feb 20 '08 #1
Share this Question
Share on Google+
16 Replies


mshmyob
Expert 100+
P: 904
You need to activate your PROPERTY sheet. It will appear to the right of your form. If it is not there then RIGHT CLICK on your form and at the bottom you will see the word PROPERTIES in the popup window.

After activating your PROPERTIES sheet you then select the EVENTS tab and then click on the little button that has 3 dots in the ON CLICK event and choose CODE BUILDER.

regards,

I just installed MS Office Enterprise on my system today and jumped right in to MS Access. Because I am familiar with Access I had very few problems navigating around. Here is the big question.

Where is the Visual Basic Code???

I made a form and placed an Add Record button on the form. When the user clicks this button, I want to focus to go the the first field on the form. I usually do this with the setfocus command in visual basic. When I clicked on the Visual Basic icon on the ribbon, the Visual Basic window pops up, but there is no code at all except for the command Option Compare Database.

What is going on?

Any help would be appreciated.
Feb 20 '08 #2

P: 6
I have no problem getting to the VB Window. When I used the wizard to place a button in Access '03, there was code automatically placed in the VB Window for that control. When I look at the VB Window there is no code at all.

Here is the simple code that I am trying to put in for the cmdAdd button:


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAdd_Click()
  2. On Error GoTo Err_cmdAdd_Click
  3.  
  4.     DoCmd.GoToRecord , , acNewRec
  5.     LnameEng.SetFocus
  6.  
  7. exit_cmdAdd_click
  8.     Exit Sub
  9.  
  10. Err_cmdAdd_Click:
  11.     MsgBox Err.Description
  12.     Resume exit_cmdAdd_click
  13.  
  14. End Sub
  15.  
When I type in the doCmd line visual basic acts like I would expect and as soon as I type the two commas, the window pops up and gives me the choices to select such as asNewRec or acFirst or acLast. No problem there.

When I type in the line: LnameEng. I do not get the options I would expect like Setfocus, the only option I get is .value. This would lead me to believe that I had typed the name of the control in wrong, but I double checked the control name and LnameEng is correct and it is a text box and its tab stop value is set to Yes.

The biggest thing here is, I should not have to type in any code for the cmdAdd button because the Wizard should be doing that for me which it is not.

When I get the completed code in and I go back to the form and try the button I get this compile error:

"Method or Data Member not found"

and the .setfocus is highlighted in yellow?

What is going on???
Feb 20 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
I don't run 2007, but if I remember correctly, the command button wizard in this version creates embedded macros rather than the VBA code you're used to finding and modifying after creating a button in this manner. Take a look at this tutorial and see if it helps:

http://www.databasedev.co.uk/access2007macros.html

Welcome to TheScripts!

Linq ;0>
Feb 20 '08 #4

mshmyob
Expert 100+
P: 904
Linq is correct that if you use the Wizard to create an Addnew record in Access 2007 it uses a macro. But if you click on the drop down arrow on the ON CLICK event you will get 2 options. 1 for the macro and 1 for Event Procedure (which is the code). Select the Event Procedure and then you will be able to put in your code and the Macro will be void.

I have no problem getting to the VB Window. When I used the wizard to place a button in Access '03, there was code automatically placed in the VB Window for that control. When I look at the VB Window there is no code at all.

Here is the simple code that I am trying to put in for the cmdAdd button:


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAdd_Click()
  2. On Error GoTo Err_cmdAdd_Click
  3.  
  4.     DoCmd.GoToRecord , , acNewRec
  5.     LnameEng.SetFocus
  6.  
  7. exit_cmdAdd_click
  8.     Exit Sub
  9.  
  10. Err_cmdAdd_Click:
  11.     MsgBox Err.Description
  12.     Resume exit_cmdAdd_click
  13.  
  14. End Sub
  15.  
When I type in the doCmd line visual basic acts like I would expect and as soon as I type the two commas, the window pops up and gives me the choices to select such as asNewRec or acFirst or acLast. No problem there.

When I type in the line: LnameEng. I do not get the options I would expect like Setfocus, the only option I get is .value. This would lead me to believe that I had typed the name of the control in wrong, but I double checked the control name and LnameEng is correct and it is a text box and its tab stop value is set to Yes.

The biggest thing here is, I should not have to type in any code for the cmdAdd button because the Wizard should be doing that for me which it is not.

When I get the completed code in and I go back to the form and try the button I get this compile error:

"Method or Data Member not found"

and the .setfocus is highlighted in yellow?

What is going on???
Feb 20 '08 #5

P: 6
Thank you for the replies to this question. Linq, you link to the information page gave me some good information and I also read some other pages that are getting me in the right direction.

Right now I can get to the VB Window just fine, but still there is limited functionality with VB.

When you use the wizard to create a button, it does write a macro and not VB. To convert it into VB you have to use a tool on the ribbon to manually create VB Code. I have no problem doing this.

When I go to the VB editor, i still cannot usede commands that were available in access '03, namely the Setfocus Method.

Is there something that is prohibiting VB. Is this prohibition something I can change to allow more code to be used.

According to the Microsoft webpage, they are trying to limit the use of code for security reasons and they have tried to make the Macros do the same thing, which they are no where near doing.

Has anyone else encountered this problem.

The biggest reason for me going to Access '07 is the ability to use access with sharepoint.

Thanks

Bob
Feb 20 '08 #6

mshmyob
Expert 100+
P: 904
I use Access 2007 and have created a number of apps with it and have not run into the problem yet. If you can send the file I could test it on my computer to see if I get the same problem..

Thank you for the replies to this question. Linq, you link to the information page gave me some good information and I also read some other pages that are getting me in the right direction.

Right now I can get to the VB Window just fine, but still there is limited functionality with VB.

When you use the wizard to create a button, it does write a macro and not VB. To convert it into VB you have to use a tool on the ribbon to manually create VB Code. I have no problem doing this.

When I go to the VB editor, i still cannot usede commands that were available in access '03, namely the Setfocus Method.

Is there something that is prohibiting VB. Is this prohibition something I can change to allow more code to be used.

According to the Microsoft webpage, they are trying to limit the use of code for security reasons and they have tried to make the Macros do the same thing, which they are no where near doing.

Has anyone else encountered this problem.

The biggest reason for me going to Access '07 is the ability to use access with sharepoint.

Thanks

Bob
Feb 21 '08 #7

P: 6
mshmyob,
I really appreciate the offer although I do not know how to send it to you. My contact email is <<Removed due to site rules >> if you would like to contact me to give me instructions on how to get it to you.

Again Thanks

Bob
Feb 21 '08 #8

mshmyob
Expert 100+
P: 904
See at the bottom of your last post it says 'Edit/Delete'. Choose that and then scroll down and at the bottom of that page you will see an ATTACHMENT link. You can attach a ZIPPED file to your message there.

If you don't get that to work I will contact you via email. Let me know if you got it to work.

mshmyob,
I really appreciate the offer although I do not know how to send it to you. My contact email is <<Removed due to site rules >> if you would like to contact me to give me instructions on how to get it to you.

Again Thanks

Bob
Feb 21 '08 #9

NeoPa
Expert Mod 15k+
P: 31,768
Bob,

It's not allowed to share e-mail addresses publicly in the forums (Check out Help for full explanations). It's probably best if you send your e-mail address to Mshmyob via PM.

Admin.
Feb 21 '08 #10

missinglinq
Expert 2.5K+
P: 3,532
I know that in order for run VBA code under 2007 you have to declare the folder that the db resides in to be a trusted location. This is why apps from previous versions stop working when opened under 2007. I suppose it's possible Access won't let you write code unless the same thing is true. To do that:

Open A2007, click the "Office" button in the upper left, go to Access Options / Trust Center and add the source file of the db to the list of trusted locations.


Linq ;0)>
Feb 21 '08 #11

mshmyob
Expert 100+
P: 904
Hello Linq,

The trusted location annoyance - i mean setting that MS has added stops Macro's and Add-Ins from automatically running. If you open a database that isn't in the trusted location you get the MS standard message 'Do you want to enable macros ... etc. etc".

It doesn't stop you from adding code. Just FYI.

Cheers,

I know that in order for run VBA code under 2007 you have to declare the folder that the db resides in to be a trusted location. This is why apps from previous versions stop working when opened under 2007. I suppose it's possible Access won't let you write code unless the same thing is true. To do that:

Open A2007, click the "Office" button in the upper left, go to Access Options / Trust Center and add the source file of the db to the list of trusted locations.


Linq ;0)>
Feb 21 '08 #12

missinglinq
Expert 2.5K+
P: 3,532
I don't run 2007, as I've stated, but according to multiple reports I've seen for months and months now, it stops code from running, without any warnings. Buttons with code behind them simply don't work until the db's folder is declared as a trusted location.

I'm not as clear on the warning you cited, about Macros, but I thought that started with ACC2003 and had to do with the level of security you set, not Trusted Locations.

Linq ;0)>
Feb 22 '08 #13

P: 6
Linq,
I have made the location of my files a trusted location but I am still haivng problems with the code.

If I make a macro through the macro wizard and then convert that to VB Code, The code will appear in the VB window and will act like I expect. But if I try to add lines to the code (i.e. textbox.setfocus) I get an error on that line telling me that the Setfocus method cannot be found.

I am making progress albiet very slow progress. I am working this program for a friend of mine and would not use '07 except that is what he has on the system he is using.

Does anyone else believe that Microsoft has gone backward with Office '07?

Bob
Feb 22 '08 #14

missinglinq
Expert 2.5K+
P: 3,532
Can you post the all of the code involved here (the entire sub) and the exact error message?

As to ACC2007, I look at it as more of a different app than a new version of an old app. I think it's going to go the route of VB6/VB.Net. Micro$oft thought that VB6
would be left by the wayside and everyone would jump on the VB.Net bandwagon, but that hasn't happened.

All these years later, VB6 is still chugging along, and I think what we're going to see is ACC2003 being used for years and years beyond when it would have been retired, if ACC2007 hadn't been such a departure..

I can see the reasoning behind the major changes in moving from VB6 to .Net, but I can't for the life of me see the reasoning behind interface/infrastructure changes they've made to Access in ACC2007!

Linq ;0)>
Feb 22 '08 #15

mshmyob
Expert 100+
P: 904
Airborne if you want you can PM me and I will give you my email to send the file to me so I can look at it.

Cheers,

Linq,
I have made the location of my files a trusted location but I am still haivng problems with the code.

If I make a macro through the macro wizard and then convert that to VB Code, The code will appear in the VB window and will act like I expect. But if I try to add lines to the code (i.e. textbox.setfocus) I get an error on that line telling me that the Setfocus method cannot be found.

I am making progress albiet very slow progress. I am working this program for a friend of mine and would not use '07 except that is what he has on the system he is using.

Does anyone else believe that Microsoft has gone backward with Office '07?

Bob
Feb 22 '08 #16

P: 34
Are you using me.textbox.setfocus?
Feb 22 '08 #17

Post your reply

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