Query by form - best solution ?

[Kobe2932]

Hello guys,

The situation is the following: I have a data table for the records, and 1 record includes 12-14 different information.

I have a form for creating new records, but sometimes I have to update previously created records. The database is protected, the user just clicks on the Add record or the Search record button, everything else is disabled.

So currently searching is done by a parameter query with the 2 most important information, which displays the findings in the same form format as it was recorded.
However my users let me know that sometimes they do not know this information, but something else and they should be able to search based on that information too - they identified that iso of 2 paramters they would like to search 5-6 parameters.

However, 5-6 small popup window is pretty annoying, 2 is OK, 6 is ridicoulous.

I tried to solve it with Query by form, creating a search form with the neccessary parameters, but the problem is that it gives the results back in a data table view - which I would like to avoid, and use a form.
The other problem is that the records are too complex, and access often gives the following error message after running the search a few times: ERROR: THe query is too complex!

So I tried to solve it with coding, like Allen Browne's example

but the results cant be updated with this method too

could you please advise, what could be the best solution in this case ? or link a few databases I could use as reference ?

I think the best would be something like allen b.'s solution (with VBA coding) but giving back the results in a form view or in an updatable view

 

[John Big Booty]

Perhaps the search function demonstrated here will be of interest to you.

 

[Kobe2932]

Hi

nice solution, the only problem is that the search results cannot be updated in form view too. However in my database, the whole aim of this advanced search feature would be to easily find the records (with a method something similar) and then directly updating them somehow from the search results - like opening up a subform, as it is in your databse.

 

[Isskint]

i may be over simplyfying things but;

• Create a form "frmSearch" identical to you normal form but without a record source for the form (or control sources for the controls).

• Create a query, "qrySearch", based on the relevant table(s). This will be edited via VBA/SQL after each search

• Duplicate your main form, "frmUpdate", but change the control source to qrySearch

• In the OnClick event of the Search button on frmSearch, build the querydef

• Set the querydef of qrySearch

• Dcount() records for match(es)

• If match, open frmUpdate

• If no match, open form for new record

 

[Kobe2932]

i may be over simplyfying things but;

• Create a form "frmSearch" identical to you normal form but without a record source for the form (or control sources for the controls).

• Create a query, "qrySearch", based on the relevant table(s). This will be edited via VBA/SQL after each search

• Duplicate your main form, "frmUpdate", but change the control source to qrySearch

• In the OnClick event of the Search button on frmSearch, build the querydef

• Set the querydef of qrySearch

• Dcount() records for match(es)

• If match, open frmUpdate

• If no match, open form for new record

I'm not sure if I fully understood that, but I tried to go further with some advanced search functions. Basically what I'm trying to do is implementing the same thing John Big Booty did , only with one tiny difference: if I doubleclick on the search result in the subform / or click on the View details in Form view button, I want to have the information to be editable (in John Big Booty's sample database, it is not editable).

Any idea what should I update ?

 

[John Big Booty]

Hi

nice solution, the only problem is that the search results cannot be updated in form view too. However in my database, the whole aim of this advanced search feature would be to easily find the records (with a method something similar) and then directly updating them somehow from the search results - like opening up a subform, as it is in your databse.

Did you not try Double Clicking on the Result you wished to edit (per the Tool Tips) in the List Box?

As doing so opens the selected record in a pop up form for editing purposes

 

[Kobe2932]

Did you not try Double Clicking on the Result you wished to edit (per the Tool Tips) in the List Box?

As doing so opens the selected record in a pop up form for editing purposes

Hi

Of course I tried both Doubleclicking on the record, or simply clicking on the Wiev vine details in Form button.

Both pops up the window as a non-editable one - all I can change is ticking the "tasted" checkbox, and the Done button, nothing else. Nothing happens with the other radio buttons or the dropdowns, can't even move or position the popup form.

I thought this is the default setup, but maybe something wrong on my side Using access 2010, but also hitting on the Enable editing button when opening up the file

 

[John Big Booty]

OK, well it's just a matter of changing the properties of the pop up form to allow edits.

You will need to open that form in design view directly from the Objects window, by the sounds of things.

 

[Kobe2932]

I'll give it a try

 

[Kobe2932]

OK, well it's just a matter of changing the properties of the pop up form to allow edits.

You will need to open that form in design view directly from the Objects window, by the sounds of things.

hmm, that's interesting. I tried to open it up from the objects, the fields and moving the window is still blocked. If I go to design view and allow some editing, than the whole thing "fells apart", field contents are not shown in the popup form, neither are editable (despite of being set to "editable")

 

[John Big Booty]

Can you post a copy of your DB?

 

[Kobe2932]

Can you post a copy of your DB?

Sorry, but no, it contains business-related sensitive information, which is confidential.

Anyway, the above problem I described - can't edit the single records from the search form - is appearing with Dynamic Multi Search sample database. Thought I use it as a reference and create something similar, but could not switch the records in the sample database to editable.

The working copy of my database currently contains a simple query by form search option, purely based on this: http://support.microsoft.com/kb/304428

I do not really need a very complex, VBA based search function, I would be fully happy f I could modify the above QBF method to display the search results in a form, instead of a datasheet