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

Turn on command button wizard Access 2003...

Dököll
Expert 100+
P: 2,364
Jumped for joy last night after learning, indeed, the command button wizard can be switched on or off. Well this occured here, with my Access 2000...an attempt to redo at work failed.

Here's the scenario, quite amusing, and I'll try to sum this up quickly:-)

At home, when the command button wizard (Access 2000) loads and give me some wonderful options/selection tips, the result, code is pre-written for the button when completed.

I found out it is not so at work, so I went to the source. Office 2003 page templates and samples confirmed magic wand like button available in Access turns it on or off, thus reason for jumping into action:-)

my jaw dropped like a stone when I attempted this morning and button was not magic, wand button would not stay pushed in, it came back, rather smoothly by the way, making room for the code screen to pop up instead...

I am smiling now, just as I did just now 5ish. I have a feeling ti's logically possible to fire a nice wizard through Access 2003...loads of information seem to point to it

Side Note: the form/database that I am building seems to have an option to "Convert the Access database from 2000 to 2000 - 2003 Version of Access. I found out today. By then I was drained, I'll look into that later on. We are running 2003, no doubt, says the boss.

This was not as brief as I'd hoped, sorry about that. But what are your thoughts? What do you see happening?
Jun 11 '07 #1
Share this Question
Share on Google+
24 Replies


NeoPa
Expert Mod 15k+
P: 31,754
I'm registered in here now, but a little confused as to what you're asking.
The wizard works on 2003 at home but not on 2000 at work?
I have to admit I don't use the wizards much myself, although I know a number of the experts are experienced with them. I'll try to help though, if you let me know what you need.
Jun 11 '07 #2

Dököll
Expert 100+
P: 2,364
I'm registered in here now, but a little confused as to what you're asking.
The wizard works on 2003 at home but not on 2000 at work?
I have to admit I don't use the wizards much myself, although I know a number of the experts are experienced with them. I'll try to help though, if you let me know what you need.
Excuse the confusion..

It's the other way around, work loads 2003, wizard does not work there (magic wand fires code screen when pushed, with button selected as suggested by Office 2003 site), I disabled wizard here, on my machine with 2000, it interacts correctly to my demands...

Thanks for looking into this, I am on a brief brake form the site actually:

http://office.microsoft.com/en-us/ac...888401033.aspx

Choose "Create a new data access page and a command button that's a hyperlink to that page"

The information suggests wizard can be turned on, but what happens by me is a window pops up and give me a set of options:

(1) Whether to use a the button for open a report, form, run a query, make telephone call, send email, etc...

(2) After all is set, when the form load and button is pressed, the button ges to work.

What this means is the options available to me in 2000, are seemingly scarce in 2003, Or not, I'm still looking...
Jun 12 '07 #3

Dököll
Expert 100+
P: 2,364
Excuse the confusion..

It's the other way around, work loads 2003, wizard does not work there (magic wand fires code screen when pushed, with button selected as suggested by Office 2003 site), I disabled wizard here, on my machine with 2000, it interacts correctly to my demands...

Thanks for looking into this, I am on a brief brake form the site actually:

http://office.microsoft.com/en-us/ac...888401033.aspx

Choose "Create a new data access page and a command button that's a hyperlink to that page"

The information suggests wizard can be turned on, but what happens by me is a window pops up and give me a set of options:

(1) Whether to use a the button for open a report, form, run a query, make telephone call, send email, etc...

(2) After all is set, when the form load and button is pressed, the button ges to work.

What this means is the options available to me in 2000, are seemingly scarce in 2003, Or not, I'm still looking...
Here is a saple code created through Access 2000, command button wizard:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub AddRecord_Click() 
  3. On Error GoTo Err_AddRecord_Click 
  4.  
  5.     DoCmd.GoToRecord , , acNewRec 
  6.  
  7. Exit_AddRecord_Click: 
  8.     Exit Sub 
  9. Err_AddRecord_Click: 
  10.     MsgBox Err.Description 
  11.     Resume Exit_AddRecord_Click 
  12. End Sub
  13.  
  14.  
See an example through this link:


http://www.microsoft.com/mspress/books/sampchap/2533/f02--07.gif


Once a button is placed on a form such as the above the wizard asks what the next step is. After everything is created, code similar to the above is added...

It looks like Access 2003, wants me to type up the code from scratch
Jun 12 '07 #4

NeoPa
Expert Mod 15k+
P: 31,754
So it seems that 2003 doesn't have that wizard. Maybe it's just not been installed. When installing Office there are options for which wizards to install. I'll see if I can try it out on my PC at home tonight (2003 @home).
Otherwise, just copy and paste the code in. It's not too variable. Just change all occurrences of "AddRecord" to the name you're using for your command button.
I'm not sure there's much more I can tell you about this I'm afraid.
Jun 12 '07 #5

Dököll
Expert 100+
P: 2,364
So it seems that 2003 doesn't have that wizard. Maybe it's just not been installed. When installing Office there are options for which wizards to install. I'll see if I can try it out on my PC at home tonight (2003 @home).
Otherwise, just copy and paste the code in. It's not too variable. Just change all occurrences of "AddRecord" to the name you're using for your command button.
I'm not sure there's much more I can tell you about this I'm afraid.
Truly appreciate your response, I was afraid something's a-miss in 2003. Hopefully all is well near you with your version of Access, if not, oh well. For now, I have resulted to simply bring work home, build buttons here, then send to work via email...get comfortable at least to what the code is saying to us.

Your a gentleman and scholar, Ade.

In a bit!
Jun 12 '07 #6

NeoPa
Expert Mod 15k+
P: 31,754
That reminded me...
  1. In 2003 go to Access window and open form in design mode.
  2. From the View menu select Toolbox.
  3. In the Toolbox toolbar there is a Command Button button. Click on this then click on your form where you want it to appear.
  4. The wizard then starts up with various options.
  5. Play and enjoy.
Jun 12 '07 #7

NeoPa
Expert Mod 15k+
P: 31,754
Truly appreciate your response, I was afraid something's a-miss in 2003. Hopefully all is well near you with your version of Access, if not, oh well. For now, I have resulted to simply bring work home, build buttons here, then send to work via email...get comfortable at least to what the code is saying to us.

Your a gentleman and scholar, Ade.

In a bit!
If you find you need to take work home with you (of any size), then a USB memory stick is often a more practical way to handle that. Much quicker and more convenient (generally) than e-mail.
Jun 12 '07 #8

Dököll
Expert 100+
P: 2,364
Hey! You got it to work, nice...Will try it at work, warp speed. Thanks a million...
Jun 12 '07 #9

Dököll
Expert 100+
P: 2,364
If you find you need to take work home with you (of any size), then a USB memory stick is often a more practical way to handle that. Much quicker and more convenient (generally) than e-mail.
Hello again, I think I've confused things again, at least was not specific.

What I've been doing is make it really messy here, adding buttons all over a dummy form, then copy the code then email that to work. But let me tell you something, you have saved our establishment some server space, we;re talking massive code examples, and this will be an ongoing project.

I thought it to be good doing through email alone, it'll work even better if I can further minimize load to the system. We do a lot via email. Sweet deal, thanks again.
Jun 12 '07 #10

Dököll
Expert 100+
P: 2,364
I knew I wanted to ask you something. At the end of the day today, after my boss tried version 1 of the form; while my form have everything disabled from scroll bars to toolbars, certain menus and so on, then added to the LAN, his machine is showing all toolbars I have deemed disabled at my end, visible. It even showed my form bigger in his office pc and legitimately solid and proper at my desk. Any idea why that is happening...Strange, don't you think!

Admins have regulated all settings same for everyone, truly amazing.
Jun 13 '07 #11

NeoPa
Expert Mod 15k+
P: 31,754
I knew I wanted to ask you something. At the end of the day today, after my boss tried version 1 of the form; while my form have everything disabled from scroll bars to toolbars, certain menus and so on, then added to the LAN, his machine is showing all toolbars I have deemed disabled at my end, visible. It even showed my form bigger in his office pc and legitimately solid and proper at my desk. Any idea why that is happening...Strange, don't you think!

Admins have regulated all settings same for everyone, truly amazing.
It is mainly because the setting up (customise toolbars etc) is relative to the PC and not the database. There are some settings (In Startup etc) which are db relative but I think what you're talking about is mainly PC I'm afraid.
Test it out and see what you find.
Jun 13 '07 #12

Dököll
Expert 100+
P: 2,364
It is mainly because the setting up (customise toolbars etc) is relative to the PC and not the database. There are some settings (In Startup etc) which are db relative but I think what you're talking about is mainly PC I'm afraid.
Test it out and see what you find.
You got a point there, I should probably build everything from the LAN, at least from the folder where the finished product will be housed. I will start there first. I have not done too much in Start up, will read up about it though...Will let you know...
Jun 13 '07 #13

NeoPa
Expert Mod 15k+
P: 31,754
The Startup area is quite interesting. Well worth a look.
Have fun :)
Jun 13 '07 #14

Dököll
Expert 100+
P: 2,364
The Startup area is quite interesting. Well worth a look.
Have fun :)
as expected, it works, it works, hee hee...Not Startup though, hadn't tried this, one thing at a time, know what I mean! But my wizard is now in running order at work. Funny thing, can't figure why it did not work before...Anyway, thanks much! Enjoy your week-end:-)
Jun 15 '07 #15

NeoPa
Expert Mod 15k+
P: 31,754
I'm very pleased to hear that's working for you Dököll.
If we try to keep all our members happy, we try even harder to make sure the other Experts and Mods get the help they deserve for putting in so much themselves :)
Jun 17 '07 #16

Dököll
Expert 100+
P: 2,364
I'm very pleased to hear that's working for you Dököll.
If we try to keep all our members happy, we try even harder to make sure the other Experts and Mods get the help they deserve for putting in so much themselves :)
Guess what, Start up did it!!! I have now greatly reduced user intercaction to our database, have a meeting with the boss in a bit, he'll be pleased. Even added a password to the wizard generated code screen:-) This has been quite the experience having gone a little deeper into it. Thanks much...

Also, and I feel like I am always repeating this, thanks for the umlaut of my screen name, can be quite annoying figuring it out. It's a pitty Lotus Notes cannot add it in on my name which, i am sure you know also carries that.

Anyway, in a bit. Will come back in from home...
Jun 21 '07 #17

NeoPa
Expert Mod 15k+
P: 31,754
Not a problem Dököll. The umlaut is easy, I just copy & paste from your username ;)
Passwords :
If you use them, make sure you can't lose them!

There's very little more irritating than having a very impressive database that you can't develop further, or even borrow ideas / code from.
Jun 22 '07 #18

Dököll
Expert 100+
P: 2,364
Not a problem Dököll. The umlaut is easy, I just copy & paste from your username ;)
Passwords :
If you use them, make sure you can't lose them!

There's very little more irritating than having a very impressive database that you can't develop further, or even borrow ideas / code from.
You're a riot, humorous truly:-)

What did I come here for...

Oh, yes, but I'll see you here from work, just realized I'm home. I am trying to get a handle on using the same feature used to pull reports through a query to do a search and have the results not as a report but as a form:

Just from memory, in a query field such as Category, Date, and Name, the code reads:

Expand|Select|Wrap|Line Numbers
  1. (Query pane)
  2.  
  3. Forms![Myform]![Category]
  4.  
  5. Between #12-Jun-2003# and #12-Jun-2006#
  6.  
  7. Forms![Myform]![Name]
  8.  
  9.  
Something in that kind of a line...

Then:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.OpenReport "MyWOnderfulReport", acPreview, acEdit
  3.  
  4.  
Works fine for reports, although don't quote me on that (I believe Date info should have the Start and End date fields added), I'd have to look for total accuracy, but that's the ticket for reports.

Now I attempted to simpy replace DoCmd.OpenReport to ...OpenForm, but acForm, rather than Preview. Access is unhappy with me...

I intend to give it another whirl today. I could not seem to grab helpful hits here on this, at least for this type of search criteria.

In a bit!
Jun 25 '07 #19

NeoPa
Expert Mod 15k+
P: 31,754
Dököll,

I have to admit to struggling a little to understand your post, so forgive me if I sound confused or get this wildly wrong. I know English is not your first language.
You cannot open a report as a form. Only forms can be opened with DoCmd.OpenForm, and only reports with DoCmd.OpenReport.

-NeoPa.
Jun 25 '07 #20

Dököll
Expert 100+
P: 2,364
Dököll,

I have to admit to struggling a little to understand your post, so forgive me if I sound confused or get this wildly wrong. I know English is not your first language.
You cannot open a report as a form. Only forms can be opened with DoCmd.OpenForm, and only reports with DoCmd.OpenReport.

-NeoPa.
Quite the contrary, right on point, I need to stop doing this to people:-) I apologize, I sometimes translate from French in my head, that be the reason I've confused you...

What I meant was, yes the report is opened with DoCmd.OpenReport. I wanted to keep the same code that pulls in reports and change DoCmd.OpenReport to DoCmd.OpenForm. Now, the report code looks like:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.OpenReport "MyWonderfulReport", acPreview, acEdit
  3.  
  4.  
Wanted to change it to:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.OpenForm "MyWonderfulForm", acForm, acEdit
  3.  
  4.  
Like that...

I am just toying with the idea. I can query the darn thing and get the results I want. I just need my VBA code to do it through my start up form using a command button then add the results to MyWonderfulForm.

I needed the button that fires such a code to reside on the start up form, and only when the code meets the condition set forth in the back end should MyWonderfulForm emerge.

Within the query however, say it were:

Expand|Select|Wrap|Line Numbers
  1.  (Query)
  2.  
  3. SELECT * From ThisTable
  4.            Where Category Is Not Null
  5.            And Date >= #01-Jun-2003#
  6.            And Date <= #01-Jun-2007#
  7.            And Status ="Open"
  8.            And User Is Not Null
  9. ORDER By Category
  10.  
My VBA code, similar to what above should achieve, would also include an error/message, "Try again!" something like that, in the start up form code, to keep MyWonderfulForm invisible or, again, form runs wild if data are found.

Getting pretty close though, I am going to attempt writting it up in VB, then plug it in, and make it yell at me once more, see what pops up.

It has got to at least tell me what I need to fix...

Hope this makes more sense!
Jun 26 '07 #21

Dököll
Expert 100+
P: 2,364
Great news!

An example is available in the Office site:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SendToForm_Click() 
  2.     Const cInvalidDateError As String = "Please enter data in proper format." 
  3.     Dim strWhere As String 
  4.     Dim strError As String 
  5.  
  6.     strWhere = "1=1" 
  7.       If Nz(Me.Category) <> "" Then 
  8.         strWhere = strWhere & " AND " & "MyForm.Category = '" & Me.Category & "'" 
  9.     End If 
  10.  
  11.     If Nz(Me.User) <> "" Then 
  12.         strWhere = strWhere & " AND " & "MyForm.User = '" & Me.User & "'" 
  13.     End If 
  14.  
  15.     If IsDate(Me.DateFrom) Then 
  16.         strWhere = strWhere & " AND " & "MyForm.[DateFrom] >= " & GetDateFilter(Me.DateFrom) 
  17.     ElseIf Nz(Me.DateFrom) <> "" Then 
  18.         strError = cInvalidDateError 
  19.     End If 
  20.  
  21.     If IsDate(Me.DateTo) Then  
  22.         strWhere = strWhere & " AND " & "MyForm.[DateTo] <= " & GetDateFilter(Me.DateTo) 
  23.     ElseIf Nz(Me.DateTo) <> "" Then 
  24.         strError = cInvalidDateError 
  25.     End If 
  26.    If Not Me.FormFooter.Visible Then 
  27.             Me.FormFooter.Visible = True 
  28.             DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height 
  29.     End If 
  30.         Me.MyWonderfulForm.Form.Filter = strWhere 
  31.         Me.MyWonderfulForm.Form.FilterOn = True 
  32.     End If 
  33. End Sub 
  34.  
  35. Function GetDateFilter(dtDate As Date) As String 
  36.     GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY") & "#" 
  37. End Function 
  38.  
  39.  
No mess, no fuss...

This may be helpful to others. Of course, I'll need to modify to a form and not a subform but that's the least of our worries.

In a bit!
Jun 28 '07 #22

NeoPa
Expert Mod 15k+
P: 31,754
I'm pleased to see that you're making progress, even when I'm away for a bit. Keep it up :)
Jun 28 '07 #23

Dököll
Expert 100+
P: 2,364
Great news!

An example is available in the Office site:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SendToForm_Click() 
  2.     Const cInvalidDateError As String = "Please enter data in proper format." 
  3.     Dim strWhere As String 
  4.     Dim strError As String
  5.     Dim stDocName As String
  6.     Dim stLinkCriteria As String
  7.  
  8.     strWhere = "1=1" 
  9.       If Nz(Me.Category) <> "" Then 
  10.         strWhere = strWhere & " AND " & "MyForm.Category = '" & Me.Category & "'" 
  11.     End If 
  12.  
  13.     If Nz(Me.User) <> "" Then 
  14.         strWhere = strWhere & " AND " & "MyForm.User = '" & Me.User & "'" 
  15.     End If 
  16.  
  17.     If IsDate(Me.DateFrom) Then 
  18.         strWhere = strWhere & " AND " & "MyForm.[DateFrom] >= " & GetDateFilter(Me.DateFrom) 
  19.     ElseIf Nz(Me.DateFrom) <> "" Then 
  20.         strError = cInvalidDateError 
  21.     End If 
  22.  
  23.     If IsDate(Me.DateTo) Then  
  24.         strWhere = strWhere & " AND " & "MyForm.[DateTo] <= " & GetDateFilter(Me.DateTo) 
  25.     ElseIf Nz(Me.DateTo) <> "" Then 
  26.         strError = cInvalidDateError 
  27.     End If 
  28.    'If Not Me.FormFooter.Visible Then 
  29.             'Me.FormFooter.Visible = True 
  30.             'DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height 
  31.     'End If 
  32.         'Me.MyWonderfulForm.Form.Filter = strWhere 
  33.         'Me.MyWonderfulForm.Form.FilterOn = True 
  34.     'End If 
  35.  
  36.     stDocName = "MyWonderfulForm"
  37.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  38. End Sub 
  39.  
  40. Function GetDateFilter(dtDate As Date) As String 
  41.     GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY") & "#" 
  42. End Function 
  43.  
  44.  
No mess, no fuss...

This may be helpful to others. Of course, I'll need to modify to a form and not a subform but that's the least of our worries.

In a bit!
Many thanks for your support, I was coming over to report yet another discovery. I will comment out the last bit of code to reflect my new finding.

Using the form's "OpenForm" code, I can open up MyWonderfulForm with my results rather than having it in the footer. See above for the modified portion...

I think we've nailed this one:-)

In a bit!
Jun 29 '07 #24

NeoPa
Expert Mod 15k+
P: 31,754
Nice one Dököll.
In case anyone isn't clear :- The amended code is in the "Quoted" part of post #24.
Jun 29 '07 #25

Post your reply

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