Turn on command button wizard Access 2003...

[Dököll]

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?

[NeoPa]

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.

[Dököll]

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...

[Dököll]

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:

 
Private Sub AddRecord_Click() 
On Error GoTo Err_AddRecord_Click 
 
    DoCmd.GoToRecord , , acNewRec 
 
Exit_AddRecord_Click: 
    Exit Sub 
Err_AddRecord_Click: 
    MsgBox Err.Description 
    Resume Exit_AddRecord_Click 
End Sub
 
 

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

[NeoPa]

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.

[Dököll]

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!

[NeoPa]

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.

[NeoPa]

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.

[Dököll]

Hey! You got it to work, nice...Will try it at work, warp speed. Thanks a million...

[Dököll]

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.

[Dököll]

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.

[NeoPa]

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.

[Dököll]

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...

[NeoPa]

The Startup area is quite interesting. Well worth a look.
Have fun :)

[Dököll]

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:-)

[NeoPa]

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 :)

[Dököll]

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...

[NeoPa]

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.

[Dököll]

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:

(Query pane)
 
Forms![Myform]![Category]
 
Between #12-Jun-2003# and #12-Jun-2006#
 
Forms![Myform]![Name]
 
 

Something in that kind of a line...

Then:

 
DoCmd.OpenReport "MyWOnderfulReport", acPreview, acEdit
 
 

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!

[NeoPa]

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.

[Dököll]

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:

 
DoCmd.OpenReport "MyWonderfulReport", acPreview, acEdit
 
 

Wanted to change it to:

 
DoCmd.OpenForm "MyWonderfulForm", acForm, acEdit
 
 

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:

 (Query)
 
SELECT * From ThisTable
           Where Category Is Not Null
           And Date >= #01-Jun-2003#
           And Date <= #01-Jun-2007#
           And Status ="Open"
           And User Is Not Null
ORDER By Category
 

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!

[Dököll]

Great news!

An example is available in the Office site:

Private Sub SendToForm_Click() 
    Const cInvalidDateError As String = "Please enter data in proper format." 
    Dim strWhere As String 
    Dim strError As String 
 
    strWhere = "1=1" 
      If Nz(Me.Category) <> "" Then 
        strWhere = strWhere & " AND " & "MyForm.Category = '" & Me.Category & "'" 
    End If 
 
    If Nz(Me.User) <> "" Then 
        strWhere = strWhere & " AND " & "MyForm.User = '" & Me.User & "'" 
    End If 
 
    If IsDate(Me.DateFrom) Then 
        strWhere = strWhere & " AND " & "MyForm.[DateFrom] >= " & GetDateFilter(Me.DateFrom) 
    ElseIf Nz(Me.DateFrom) <> "" Then 
        strError = cInvalidDateError 
    End If 
 
    If IsDate(Me.DateTo) Then  
        strWhere = strWhere & " AND " & "MyForm.[DateTo] <= " & GetDateFilter(Me.DateTo) 
    ElseIf Nz(Me.DateTo) <> "" Then 
        strError = cInvalidDateError 
    End If 
   If Not Me.FormFooter.Visible Then 
            Me.FormFooter.Visible = True 
            DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height 
    End If 
        Me.MyWonderfulForm.Form.Filter = strWhere 
        Me.MyWonderfulForm.Form.FilterOn = True 
    End If 
End Sub 
 
Function GetDateFilter(dtDate As Date) As String 
    GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY") & "#" 
End Function 
 
 

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!

[NeoPa]

I'm pleased to see that you're making progress, even when I'm away for a bit. Keep it up :)

[Dököll]

Great news!

An example is available in the Office site:

Private Sub SendToForm_Click() 
    Const cInvalidDateError As String = "Please enter data in proper format." 
    Dim strWhere As String 
    Dim strError As String
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    strWhere = "1=1" 
      If Nz(Me.Category) <> "" Then 
        strWhere = strWhere & " AND " & "MyForm.Category = '" & Me.Category & "'" 
    End If 
 
    If Nz(Me.User) <> "" Then 
        strWhere = strWhere & " AND " & "MyForm.User = '" & Me.User & "'" 
    End If 
 
    If IsDate(Me.DateFrom) Then 
        strWhere = strWhere & " AND " & "MyForm.[DateFrom] >= " & GetDateFilter(Me.DateFrom) 
    ElseIf Nz(Me.DateFrom) <> "" Then 
        strError = cInvalidDateError 
    End If 
 
    If IsDate(Me.DateTo) Then  
        strWhere = strWhere & " AND " & "MyForm.[DateTo] <= " & GetDateFilter(Me.DateTo) 
    ElseIf Nz(Me.DateTo) <> "" Then 
        strError = cInvalidDateError 
    End If 
   'If Not Me.FormFooter.Visible Then 
            'Me.FormFooter.Visible = True 
            'DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height 
    'End If 
        'Me.MyWonderfulForm.Form.Filter = strWhere 
        'Me.MyWonderfulForm.Form.FilterOn = True 
    'End If 
 
    stDocName = "MyWonderfulForm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub 
 
Function GetDateFilter(dtDate As Date) As String 
    GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY") & "#" 
End Function 
 
 

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!

[NeoPa]

Nice one Dököll.
In case anyone isn't clear :- The amended code is in the "Quoted" part of post #24.