How to create a button which opens Form again and which starts parameters again?

[moke123]

heres adams sample with the filter procedure in it. Its very simple. just click your sample button and input the name.

 

[KissBambi]

Hello Moke...I should not use a query for the form? Is it possible to know where I should write on VBA? Problem is that I have an aversion for VBA, if possible I would like to use a simple button but if you explain me with screenShots where I can replace query by VBA, I think I could try to make something which has a sense on Access. I would like to make something easy and simple to use, to create different forms, bound in one single Final Form. This means different forms with different criterias of search based on simple SQL queries

 

[KissBambi]

bambi,

moke's post might be too tough for you to follow, which I assume it is. so, in that case, look at this attachment. this is the FIRST step for you. look at the query "Anzeige Abfrage". you will notice that there is a "IS NOT NULL" statement in the WHERE clause. what this does is force a 0-record result set if a user tries to bypass the parameters by not putting anything in the popup box. tell me if this is something you want first, and then we'll do the next step regarding the 2nd filtered form you want.

This is not what I want this should allow NULL value. I don't know where I can write VBA code in Access and how to make the same as you with Example button. Will try to change the query

 

[vba_php]

This is not what I want this should allow NULL value. I don't know where I can write VBA code in Access and how to make the same as you with Example button. Will try to change the query

give it a try and then upload what you can't get to work and specify again what you want. if you include the SQL statement in your post that you "want" to work and the one you try that "doesnt" work, that will help quite a bit.

 

[KissBambi]

The query ANzeige Abfrage is wrong.


The good query is Anzeige Berufsuche

 

[vba_php]

The query ANzeige Abfrage is wrong.

The good query is Anzeige Berufsuche

specify who you are talking to my dear.

 

[KissBambi]

Hello Adam, please can you send me a zip file DB with your solution? I have to reorganize my work. I can't find again on that thread the good zipped file where Example button is based on VBA.


Can you send me a full solution with screenshots explaining where I have to go on Access to write VBA code bound to a button in a form?

 

[vba_php]

Can you send me a full solution with screenshots explaining where I have to go on Access to write VBA code bound to a button in a form?

weren't you able to follow the post by MOKE? I didn't think you could, so apparently I was right.

I have to go to the hospital right now for a short appointment for a head cold, but I'll look at what you've got and get back to you in about 2-3 hours. But keep an eye out during that time cuz someone else might post here to help you too.

 

[KissBambi]

No I wasn't able to follow the post. I hope your cold is going to heal and that you can take care of yourself Adam. I go back home and will be back to Office tomorrow morning, my head is going to explode....thank you for your precious help Adam and Moke....not easy but very interesting, a little bit scaring for me....

 

[moke123]

Bambi,

the query for your form Anzeige Berufsuche Formular2 should just be

SELECT Anzeige.Beschreibung, Anzeige.AnzeigeNummer, Anzeige.Kundenname, Anzeige.Publikationname, Anzeige.Position, Anzeige.Pensum, Anzeige.Rubrik, Anzeige.PLZ, Anzeige.Ort, Anzeige.Datum, Anzeige.Kontakt, Anzeige.Sprache, Anzeige.[E-mail], Anzeige.Webseite, Anzeige.Telefonnummer
FROM Anzeige;

as you can see I removed the Parameters in the query as well as the criteria in the query. This returns the full set of records. When you click your button it is constructing and applying a filter to those records. if you dont enter anything in the inputbox it returns the full set of records.

where I have to go on Access to write VBA code bound to a button in a form?

If you look at the property sheet for the button where you had it saying embedded macro, it will now say Event Procedure. Click the elipsis(...) next to it

 

[vba_php]

ok bambi,

regardless of what everyone else has posted in here, I think I've got a solution for you. see attached docs. and if you're wondering why I'm crazy enough to go through all of this and help you out, it's more about repaying others for the help I got from people like the veteran who helped me in this PHP thread figure out code that was written incorrectly by the supposed "geniuses" of the world:

https://www.phphelp.com/t/setting-smtp-in-php-ini-to-make-mail-function-work/30444

and the solution was found 96 posts later and after 2 days of conversation:

https://www.phphelp.com/t/setting-smtp-in-php-ini-to-make-mail-function-work/30444/96

In my database attachment for you, notice that both forms "Anzeige Berufsuche Formular" and "Anzeige Berufsuche Formular 2" have a recordsource of the table "Anzeige". It is not practical to bound your main form to a query that's pulling virtually every field of data from the table anyway. And there are 2 examples of how to do what you want:

1) using 1 form by applying a filter (like MOKE showed you earlier).
2) using 2 forms like I suggested earlier.

If you don't want to filter your records on the field I have specified in my attachment, change it to your liking. You will find in the attachment, step-by-step instructions in the .docx file on how to produce both methods by writing code. You don't need the image files because they're embedded in the word document, but I included them anyway, as that's standard practice for distribution to a developer like yourself. I hope this works for you my dear.

 

[moke123]

Bambi,

I dont see any reason why you would use two identical forms for the task at hand.
Forms are very flexible by design for several reasons. For instance you may want to open a form for data entry only or open it to display existing data to edit. To do so you use arguments in the DoCmd.OpenForm method.
The syntax is - DoCmd.OpenForm (FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

DoCmd.OpenForm "MyForm", acNormal, , , acFormEdit
DoCmd.OpenForm "MyForm", acNormal, , , acFormAdd

You can use some or all of the arguments when opening a form.

Another reason not to use identical forms is a maintenace issue.

I know you say you are adverse to using Vba and the purpose of this post is to hopefully demonstrate to you how it actually makes things easier. When I write code I always try to make it as generic as possible so that it can be used in many places in my project.

So for your task at hand we wrote this code. This would go in a standard module (not the form module) Google those terms if you dont understand the difference.

Public Sub FilterMe(frm As Form, strField As String, Optional Prompt As String = "Beruf")
'Create and apply a filter for string fields
    Dim strFilter As String
    Dim strInput As String

    strInput = InputBox(Prompt)

    strFilter = strField & " Like ""*" & strInput & "*"""

    frm.Filter = strFilter

    frm.FilterOn = True

End Sub

Note that there are 2 required arguments and one optional argument.
1.frm as Form

this argument passes the form object to your procedure. So anywhere in your procedure you can use frm to point to your form.

2. strField as string

you use this argument to tell your procedure the field you want to filter by.

3. Optional Prompt as string = "Beruf"

this argument is optional. It is used to change the message prompt of the inputbox. If you leave this argument out the default will be "Beruf"

So why would you do it this way?

lets say you wanted to filter your form by 10 different text fields.
Rather than typing the same procedure over and over and substituting the field names in each, you can write it generically like above and then use it with different arguments for each button. You can even use it on different forms without having to re-write anything.

to use this procedure you would create a command button and choose event procedure from the buttons property sheet. you would call it like

Private Sub Command37_Click()

FilterMe [COLOR="Red"]Me[/COLOR], "Publikationname"

End Sub

FilterMe is the name of the procedure
Me (after the FilterMe) is the form object. You could also write it as Forms("YourFormName")
"Publikationname" is the argument for the field you want to filter by.

so now if you wanted to filter by "Kundenname" you only need to create a button with this code under it (this also has the optional argument for the text of the prompt in the inputbox.)

Private Sub Command41_Click()

FilterMe Me, "Kundenname", "Kundenname"

End Sub

You can repeat this for as many buttons as you like provided they are text fields. For any other datatype you would need to either modify the generic code or write a similiar procedure for the datatype used.

Also note that if nothing is entered in the inputbox or it is cancelled all the records are returned.

Attached is a sample with several buttons to filter by.

Thank you for coming to my TED TALK.

 

[vba_php]

moke,

at this point we have become competitors. that is utterly stupid. bambi, I'm sorry about that. That is typical of programmers who want attention, and me and moke are probably both to blame for that. so YOU choose who you want to talk to and get back to one us. until you do that, I won't post anymore here. again, I'm terribly sorry about that. ur probably confused.

 

[NauticalGent]

Adam, although I understand your concern about too many cooks in the kitchen, you position on this matter and words you have used are uncalled for and unprofessional.

Moke thinks there is a better way to achieve the OP's goals and is sharing his/her ideas. Because they are not aligned with yours does not mean it is a competition.

Rather than get upset at Moke's involvement, why not engage and explain why your way could be better, much like Moke has done?

This forum isn't about who is right more often, high post counts, number of Thanks and reputation points. One team, one fight.

BambiKiss, don't let us scare you off. Hear what everyone has to say and then decide what works best for you...

 

[vba_php]

Rather than get upset at Moke's involvement, why not engage and explain why your way could be better, much like Moke has done?

I said I wouldn't post anymore, but you asked for it Gent. The REASON I said what I did is because it's obviously that Bambi does not have a high level of skill in MS Access and she is a sensitive person to boot. So confusing the hell out of her with anything more than necessary will only do her harm. surely you're smart enough to understand that? She's PMed me on here numerous times and at this point I know her well enough to tell you that I know what I'm talking about.

 

[NauticalGent]

Fair enough, but she will not get the full benefits that others like myself have received by being exposed to all this forum has to offer. Coddling someone is a disservice.

And...

I stand by words. If Bambi wants a mentor, then she should continue to PM you for help and not use a forum.

And finally, I truly am not that smart or I would have held my virtual tongue...

 

[vba_php]

If Bambi wants a mentor, then she should continue to PM you for help and not use a forum.\

she doesn't want a mentor, dude. I told her a few times to post questions in the forum and not ask me. I can just relate to her, that's why she sends messages, I would assume. but all of that aside, I've noticed for a while now that we on this forum have a pretty big problem offering question askers too much information by posting in threads on top of each other. while options are indeed good to have, a lot of these people that come here are in bambi's boat, in that they are beginners, business people who don't know what they're doing or programmers who are also sem-beginners. what we do a lot of times can endanger them and confuse them, and what that does is scare them off so they never come back. and it certainly doesn't help Jon's forum visibility if THAT happens.

 

[NauticalGent]

That is the nature of a forum, inputs from many. It is up the the OP to decide how to handle it, no one else.

This forum has done rather well doing it the "wrong" way...

 

[vba_php]

This forum has done rather well doing it the "wrong" way...

well that's great to hear. Only Jon would know for sure. What's unfortunate though at this point, is that Bambi will probably not post in this thread again, so we won't know if she got her issue fixed or not.

 

[NauticalGent]

I have faith in Bambi's steadfastness and resiliency...