I made this InputForm, which 1

[RustyDWO]

I made this InputForm, which gives me some problems.
To go to a certain record in the InputForm, for modification, I made, via a go-to-button on the InputForm, a pop-up form where two search criteria can be filled in. Either the IDnumber used in the table or the name of the organisation. Both are textboxes to be filled in with each there own go-to-search button.
Remember that no toolbars , tables or queries should appear on the screen to avoid confusion to the person behind the keyboard!
I have gone through the tips and came across some similar questions but all partial of mine., because on these the search input is on the same form and with comboboxes using the RecordsetClone; works great!
With the books next to me I created the following macro (GaNaar) from the pop-up form, with the search button and OnClick property, which works but not to satisfaction:
Echo – off
SelectObject – to go to the InputForm
ShowAllRecords – just in case....
ApplyFilter – a query that picks the info from the pop-up entry
GoToRecord – puts the filtered record on the InputForm
Close – to close the pop-up form
This in fact brings up the desired record but than the input form is frozen because of the filter.
Oke complicated; but HOW do I remove the filter (without the menubar available) again and stay in the record I selected and be able to scroll manually with Next/PreviousRecord.
What I more or less need is the macro above but without the filter section. However if I remove that part nothing happens; at least not noticeable.
I was also thinking about Dlookup, in the condition collumn, but what to place after the “=” since the input is on a different form than the output and what action to use it with. SetValue? GoToRecord?
Am I to solved this problem or am I going down_under?
Examples are appriciated.
An alternative, understandible VBA-sript, like RecordsetClone with result on other form (Parent/Child), are also welcome.

Marc Rust - Netherlands
Answer can also be sent at marcrust@dagbestedingdwo.nl

 

[jerseyboy]

Huh? This is the most complicated question I have seen posted! If I understand you correctly, this is what you are trying to do:


You have two unbound criteria boxes on a form (no toolbars ect) and you want to select choice (if they are combo boxes) from existing record or enter text (if they are text boxes)

Upon entering in the second value you want to display any or all records that match both criteria. You then want to be able to negotiate to the record(s) that match and edit them.

Is this a correct assesment? If so read on:
FYI: I suspect that you don't need such a detailed explanation, but I do it so it will benefit other readers, please go with it.

To make it easier to explain, I will start from scratch. You can pick up wherever you are.
----------------------------------------------------------
1.
Create a form with two unbound controls (text or combo) and name them correctly
--Right click on the control||choose Properties||Other||type the name in the the "Name" box --

2.
Create a query that will display the record(s) if you enter both criteria correctly.
3.
In the criteria for the two fields enter the name of the unbound form controls.
---------------------------------------------------------
eg.
Forms![frmYourFormName]![cboYourFormControlName]
This can be done automatically by right clicking in the criteria slot and following this path:
Build...
Forms||AllForms||frmYourFormName||YourFormControlName

-----------------------------------------------------------
4.
Create a form based on the query keeping in mind that is going to be placed on the main form with the two original controls as a subform
5.
In the new subform write and ON CLICK event that will open the form that you want to edit the records and filter (based upon a field that you choose: I'll call 'txtMainTrackingNumber') to the records that you want to consider for editing.

here is an example:

[tt]Private Sub txtMainTrackingNumber_Click()
On Error GoTo Err_txtMainTrackingNumber_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFormYouWantToEdit"

stLinkCriteria = "[txtMainTrackingNumber]=" & "'" & Me![txtMainTrackingNumber] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_txtMainTrackingNumber_Click:
Exit Sub

Err_txtMainTrackingNumber_Click:
MsgBox Err.Description & “ “ Err.Number
Resume Exit_txtMainTrackingNumber_Click

End Sub
[/tt]

6.
Format this subform and place it on the main form with your two original controls
7.
I would recommend placing simple label on the form and use it test if the user filled in both fields. After testing the input then refresh the form (which will rerun the query and reload your subform with its results!)

Here is and example:

[tt]Private Sub lblRunInquiry_Click()
On Error GoTo Err_lblRunInquiry_Click

If IsNull(txtCriteria1) or IsNull(txtCriteria) Then

MsgBox “Please enter both criteria before submitting request”, vbCritical,”Missing Information”
Exit Sub

Else

DoCmd.Refresh

End If

Exit_lblRunInquiry_Click:
Exit Sub

Err_lblRunInquiry_Click:
MsgBox Err.Description & “ “ Err.Number
Resume Exit_lblRunInquiry_Click

End Sub
[/tt]

Finally the result...

You will have a form with two criteria boxes and a subform the initial on load will display all of the records. When the user choosed criteria and "submits" the search it will then refresh the subform to display only the records that match the search criteria. The user can then look through the records that are displayed and click on the designated field to edit that record in the actual form that the record is normally displayed in. I have used this in many different ways.
Super Search -- enter ANY search string [date,number,string] and it will display the matches that are found in ANY field
Criteria Search -- this criteria AND that criteria Or this...
Date Search -- Select date A then date B AND if they are In Negotion, Active or Expired
Exclusion Search -- This and that but not this, Contains this but not that and so on

The list goes on..

Whew, this is the longest post that I ever made! Lucky my students are working on a project. I am thinking about posting an example for download. Let me see if time permits. Finally if this is not what you are looking form then tell me more and I will try to make it work.

Good Luck, hope this was it
JerseyBoy
Remember: self-praise is no recommendation

 

[RustyDWO]

JerseyBoy
Thanks for being helpfull.
You are right about the degree of difficulty because not many replies received as everyone can see.
However I will certainly try your input but the initial idea was:
To have an Inputform with no control (for disabled) or menu to maintain a table. People with more knowledge can go directly into the table.
For search possibilities in that that via the Inputform I created a button that pops up (or opens) a second form. In this 2nd form I can fill in the search criteria in a textbox and that hit a start-search button on that 2nd form.
I know a search button on the Inputform is much easier but there is also the problem of "space on the screen" since everything is at least fontsize 14. We already had to delete a number of fields from the table.
Now back to the problem; I can't use the filter option due to hiding the rest of the table on the Inputform.
The RecorSetClone for another Form would be the nicest solution....
I'll let you know after the week-end whether your above solution work or not.
TGIF
Marc

 

[RustyDWO]

Jerseyboy,
I found a similar answer you posted on March 22 (702-236472) which looks a lot like this one and I even downloaded your file from Geocities.com/officeteach .
In both cases however I have the idea that an intermediat form, based on query, is unavoidable.
To tell you the truth: I think that's better as well!
Studying your posts I came to realize that the chance of people spelling an organisation name of more than 20 caracters 100% correct is close to zero. So I agree a choise list as intervention is a good thing.

So back to your solution in this thread.
Btw, only one search item has to filled in. Its an OR search but lets concentrate on OrgName.
Up to point 5 its clear and understandable.
Then its getting confussing! Why is its necessary to have that form+subform in one.
Moreover the name has to be spelled 100% or a cobox/lstbox is the alternative which is not a practicle thing in my case due to more than 800 records with different organisations.
So in the query criteria something should be LIKE " "......... I think. But how?
Then I have the intermediat form with a number of OrgNames (not yet really due to the query criteria)
? Can this be done so far with a macro ?

From that list of Org's, I would like to select the correct name which then automatically brings up the full record on the initial InputForm, which is still open, to continue and be able to scroll the entire table again. This means no filter on the InputForm no more.
Am I to solved this problem or am I going down_under?

Thanks again for the effort.
Marc Rust - Netherlands

 

[jerseyboy]

Marc,

The query in the subform is so that when the user enter in what they are looking for the results show in a place where they can look over which one that they really want to work with. If you were looking for a school paper that you wrote two years ago, you would start by pulling all of the papers you wrote for that class. ...

The question about searching via combo box is a valid on, and I offer various search types (with the same format for just that reason) I have a database that had EVERY rule of design broken that it could have. I had to use the data and work forward, because it had over 5000 records before I was asked to look at it. When the search part came, I offered a combo box and the text box with a "like" search feature.

The like statement is something along this line:

in the criteria section of the query put...

LIKE & "*" & Forms![frmName]![controlname] & "*"

This will search the field for the string no matter where it is located.

I have made a clear example if you want I will try to put it up on that site as well.


JerseyBoy
Remember: self-praise is no recommendation

 

[RustyDWO]

Jerseyboy,

I'am looking forward to it and check the site.

Thanks again

Marc

 

[RustyDWO]

Jerseyboy,

After reading your post yesterday I tried your LIKE function but came up with all kinds of errors.
Afterdownloading your SearchExample I found why. The first & after like is too much!

Very impressive that SearchExample and helpful.
However I keep running into problems.

1) My new Form which is attached to the query only comes up with the first found. I tried to figger out why and compared with yours but could not found the difference. The only thing is that yours is a SubForm and mine not.
Strangly enough does the query, when run from the queries tab, show all the records required in table format.

2) How do I continue from that Form when I have all the records in it?
The reason is that this intermediat Form is still a filtered selection and not the goal as intended.
Do I insert again a button that fills the original InputForm with the selected record by using the script
txtMain TrackingNumber_Click() as posted in the first repply?

Later today I’ll have a meeting with the people who have to work with this because its getting out of the original scope (as usual) at this moment but a good learning process.

By Thursday I’ll have a web address with more details and a download from my version which hopefully clear things better. Its in Dutch but that’s only for the text on the form. I use the english version as program.

Greatings from a sunny Netherlands
Marc RustyDWO

 

[RustyDWO]

Jerseyboy,

Sooner than expected, here's the link.
Hope you're still in the mood.

http://www.desli.nl/DWO/dwo.htm

Marc

 

[jerseyboy]

Marc, you don't need a query!

You can use the find button. If you put a find button and tell it to search for the first instance of any record that contains the field then it will let you enter contains strings.

Like everything else there are some small drawbacks. You should start by using the find wizard offered in the command button function of your form toolbox.

If you highlight the keyword and press F1 then it will show you the available goodies. JerseyBoy
Remember: self-praise is no recommendation

 

[rajeessh]

Why cant u try with
Me.FilterBy = ""
Me.FilterByOn peorpety

Set it to False and requery and set with same bookmark
there before u set the property to false

 

[RustyDWO]

Jerseyboy and rajeessh,

Sorry it took so long to get back on this.
Are you both talking in your last posts about the website I provided? I'm a little lost in the problem now.
If so:

Jerserboy, I'm still trying the understand your search form, from your site which raises some questions in the scripting. It works, but esspecially at the search button most of the commandlines start with ' and are in green. This means for me it are explenation lines and are not executed??????
Can you give me more info about this.
Furhermore I'll try you Find option.
Thanks again.

Rajeessh, My problem started that the filter option was no option so.......
Thanks anyway.

Marc