Enter Parameter Value Box

[papadega3000]

I have a from with several pages that house queries. What I wanted to accompolish was a simple search to narrow down the data in the queries.

I added another page to the form and setup combo boxes that have a record source to the appropriate fields I want to search by. I also have this line of code in Query Builder:

[FieldA]=[Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName] Is Null= True

This will set the query to to read from the Combo box and if Nothing selected report everything. This works fine. The problem I am running into is When I want to open up the form I get prompted to enter values into those fields in separate windows... I want to be able to just open the form and be looking at the search page not having to hit the 'OK' button 7 times to view the form.

My idea was to try and trick the form into thiinking it has data. I made a sub routine in the Form_Load() similar to this:

Private Sub Form_Load()
[Forms]![FormName]![ComboBoxName] = 1
[Forms]![FormName]![ComboBoxName] = 1
[Forms]![FormName]![ComboBoxName] = 1
End Sub

My thoughts were that it would take on these values and then pull up the search page and then I could hit the search button and it would requery all the tables.

If there is a way I could do this either with code or inside the form properties I would appreciate the assistence. Meanwhile I will keep trying.

Thanks in advance.

 

[Uncle Gizmo]

.
I'm not 100% sure I'm with you on this, but I think what you mean is you've tried that and it didn't work. If that's the case try this:

[Forms]![FormName]![ComboBoxName].defaultvalue = 1

 

[papadega3000]

That seems simple enough... I will give this a shot and see what happens.

Thanks for getting back to me.

 

[papadega3000]

I tried this as well doing the same thing in the property window just to see if it either way would work... It did not work. What seems to be the issue is the query is happening before the form is being loaded so it is prompting for those values to be entered. So I need to somehow manipulate the query statements I make to happen after the form loads.

 

[jkl0]

Take the line out of the query:

[FieldA]=[Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName] Is Null= True

and try it in the Form Load event (or Form Open). You may have to play with the syntax or use an If statement.

That line is trying to get information before the form is loaded and there is nowhere to get the info from yet.

 

[papadega3000]

Hello,

Just adding those lines to got rid of the error messagaes. But another error popped up. Not sure what this means.

Errror communicating with OLE Server or ActiveX Control

The expression may not result in the name of a macro, the name of a user_defined function,or Event Procedure
There may have been an error evaluating the function


Also I received one enter parameter box on on a field in the table. This is a date field. All of the fields are not null and it shouldn't be asking for that. Could this be a labeling problem?
I do base a formula on this field I will double check that everything there is correct.

Any suggestions would be helpful.

 

[papadega3000]

Okay I fixed the last enter parmeter value box error. It was a spelling error

However, I still get the error with OLE Server....(the one from the the previous post)

What I have at the moment is a form with 5 pages setup.
The first page is a search page. the other four are pages with subforms. The subforms are queries from the tables. So now that I got the form to load without the prompt boxes( but this is still getting the OLE server communcating error) I want to be able to setup these last four pages to be requeried to show the data selected from the combo boxes on the search page.

At this point I have on the Form_load()

[FieldA]=[Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName] Is True
[FieldB]=[Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName] Is True
[FieldC]=[Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName] Is True

Then using a cmdButton I have this code:

[Forms]![Parentformname].[Form]![subformname1].Requery
[Forms]![Parentformname].[Form]![subformname2].Requery
[Forms]![Parentformname].[Form]![subformname3].Requery
[Forms]![Parentformname].[Form]![subformname4].Requery

I am not totally sure I have this written correctly but I could use some guidence on how I can make the work the way I want to.
Do I have to assign the combo box values in this method as well?

 

[jkl0]

I'm at the limit of what I can suggest without knowing more about the setup of the form and what info you are trying to return.

You can try setting break points in your code to find out what is being returned to your fields (FieldA, FieldB & FieldC).

Or maybe someone else may have some suggestions?

 

[papadega3000]

I will try and give you a better picture of what I am doing.

The form setup is as follows

I have 5 pages setup:

Page0- Search Page

This page has three combo boxes that queries fields from three different tables.(Field A, Field B, Field C)
It also has a search button to execute these query selections.

Page1- Subform with query1(includes FieldA)
Page2- Subform with query2(includes FieldB)
Page3- Subform with query3(includes FieldC)
Page4- Subform with query4( no parameter just being refreshed)

What I am trying to accomplish is to select the field you want to see and then hit search and it will requery the data and only show the selections

I hope that helps you understand the form.

 

[papadega3000]

Now I am noticing this error pop up on all of my command buttons.

Errror communicating with OLE Server or ActiveX Control

The expression may not result in the name of a macro, the name of a user_defined function,or Event Procedure
There may have been an error evaluating the function

I followed the link to the details and help and it suggested checking the expressions and looking for missing references. But there are no mispelled expressions and no missing references.

How do I repair this error. I glanced at other forms posts and they suggested running a command from the run prompt to register a dll. but if I am not missing any references what could be the issue.

 

[papadega3000]

BTW... this is happening on all of my copies of this database. Even the backups so this has to be an issue with Access and not my code at the moment.

Any suggestions will be helpful.

 

[jkl0]

This error may be because you have a linked picture or document that cannot be found.
Or may be caused by Norton Antivirus.
http://support.microsoft.com/kb/q295824/

 

[papadega3000]

I read through that post but nothing of use there for I am not linking a picture or document to anything. All the code is based off the data in the database.
And I don't have Norton AV installed on this machine.

Thanks for your input.

Any other suggestions are welcome

 

[papadega3000]

I ended up copying the database and deleting the form that was having problems and re-building it. I no-longer recieve the Access not communicating with OLE and ActiveX controls....etc. error messsage.

Now I am running into some errors with the VBA code to run on this form.

To explain this form once again:

I have 5 pages setup:

Page0- Search Page

This page has three combo boxes that queries fields from three different tables.(Field A, Field B, Field C)
It also has a search button to execute these query selections.

Page1- Subform with query1(includes FieldA)
Page2- Subform with query2(includes FieldB)
Page3- Subform with query3(includes FieldC)
Page4- Subform with query4( no parameter just being refreshed)

In order for me to get passed receiving the enter parameter boxes I used the suggestion from jk10. To insert the code in the Form_On_Load() Event which I did and before all this trouble with the OLE error was working fine.

Now on the recreated form I pasted the code in the event and it spits back an error saying Compiler Error: Argument Not Optional

The code is as follows:

The highlighted part in BOLD is where the error is when I open the form .
Private Sub Form_Load()

[FieldA] = [Forms]![FormName]![ComboFieldA] Or [Forms]![FormName]![ComboFieldA] Is Null = True
[FieldB] = [Forms]![FormName]![ComboFieldB] Or [Forms]![FormName]![ComboFieldB] Is Null = True
[FieldC] = [Forms]![FormName]![ComboFieldC] Or [Forms]![FormName]![ComboFieldC] Is Null = True

End Sub

How can I fix that? Any ideas?

 

[papadega3000]

I resolved the issue by using the inserting the code statements into the Criteria field using an IIf() statement.

Thanks for everyone's help.