Database Form Query

[mogul0212]

All the boxes were blank.

I think I found a simple work around. Using the Button Command Wizard, under Record Operations, Undo Record seems to clear the form just fine without any errors.

 

[mogul0212]

Only bad thing I find about the Button Command Wizard option is that when the form is closed with information in the combo boxes etc, it keeps that info and the "clear" function won't work next time it's opened.

 

[KeithG]

Are you talking about if you leave all criteria boxes blanking and trying to open the form?

 

[mogul0212]

Are you talking about if you leave all criteria boxes blanking and trying to open the form?

Yes, after clicking on the clear/reset button on the form and it having all empty boxes on teh form, and then submitting the search form. I get an empty search results form that opens.

I've tried deleting the clear code itself in the database, but I still end up with no results in the search even with all criteria blank.

Any idea?

Of you open the database file I posted asking about the error, it may illustarte it better to you.

 

[KeithG]

Can you post the newest version of the db? so I can see the form with the clear button?

 

[KeithG]

You need to add some code to the button to modify the forms SQL statement.

 

[mogul0212]

KeithG

Here you go.

I have the one zip file with the clear button on it that won't give any search results now. And I have another zip attached that has the original file before I added the clear button and the code.

Hope this helps.

 

[KeithG]

Is this what you wanted?

 

[mogul0212]

Is this what you wanted?

Yes it is.

Please explain what to do to fix that, or what I was doing wrong.

Thanks.

 

[KeithG]

basically I added code to the clear button to modify the SQL statement of the form (took out the Where clause)

 

[mogul0212]

ok, now is that code in the "on click" event procedure or somewhere else?

 

[KeithG]

Its in the On_Click event of the clear button.

 

[mogul0212]

I'm trying to learn the coding better.

Could you post what was the line of code before and after so I can see the difference.

 

[KeithG]

You didn't originally have the line of code

CurrentDb.QueryDefs("Query1").Sql= SQL Statement
CurrentDB.QueryDefs("Query1").Close

 

[mogul0212]

so putting that at the end of what I had for my "on click" event would take of that then?

I notice in the example you just posted for me, it has

    CurrentDb.QueryDefs("Query1").SQL = "SELECT [Tooling Information].[NC Tool #], [Tooling Information].[NC Division], [Tooling Information].[Profile # or Name], [Tooling Information].[Profile Type], [Tooling Information].[Tool Type], [Tooling Information].Notes, [Tooling Information].[Tool Material], [Tooling Information].[Shank Diameter], [Tooling Information].[Minor Diameter], [Tooling Information].[Max Cut Depth], [Tooling Information].[Board Thickness], [Tooling Information].[Tool Description], [Tooling Information].[Tool used in Fergus Falls - Veneer], [Tooling Information].[Tool used in Corbin - Thermofoil], [Tooling Information].[Tool used in Vanceburg, KY - Veneer], [Tooling Information].[Tool used in Vanceburg, KY - Wood], [Tooling Information].[Tool used in Arkansas City, KS - Veneer], [Tooling Information].[Tool used in Fergus Falls - Thermofoil], [Tooling Information].[AutoCAD File], [Tooling Information].[PDF File] FROM [Tooling Information]"
    CurrentDb.QueryDefs("Query1").Close

What is the signficance of all of the Field names after that? Or does that get added automatically?

 

[KeithG]

That is what you are changing the SQL statement too. Try this open the search form and enter some criteria and view the form. Then open Query1 in SQL view and look at the SQL statement. Then open the search form again and search for different criteria or hit the clear botton then open the form and then look at the SQL statement behind Query1 it should be different. Each time you click the view button access will rewrite Query 1 to fit your criteria.

 

[mogul0212]

I tried just modifying my "on click" event for the "clear" button by putting following code and I end up with the same issue as before (no search results even with a blank form. What am I doing wrong?

Private Sub ClearForm_Click()

    Me.[cmbNCTool].Value = ""
    Me.[cmbNCDivision].Value = ""
    Me.[cmbToolMaterial].Value = ""
    Me.[cmbProfile].Value = ""
    Me.[cmbToolType].Value = ""
    Me.[cmbProfileType].Value = ""
    Me.[cmbBoardThick].Value = ""
    Me.[cmbMinorD].Value = ""
    Me.[cmbShankD].Value = ""
    Me.[cmbMaxCut].Value = ""
    Me.[txtNotes].Value = ""
    Me.[txtDesc].Value = ""
    Me.[chkUsedArksCtyVeneer].Value = ""
    Me.[chkUsedCorbinFoil].Value = ""
    Me.[chkUsedFFallsVeneer].Value = ""
    Me.[chkUsedFFallsFoil].Value = ""
    Me.[chkUsedVbrgVeneer].Value = ""
    Me.[chkUsedVbrgWood].Value = ""
    
    CurrentDb.QueryDefs("Query1").SQL = "SELECT [Tooling Information].[NC Tool #], [Tooling Information].[NC Division], [Tooling Information].[Profile # or Name], [Tooling Information].[Profile Type], [Tooling Information].[Tool Type], [Tooling Information].Notes, [Tooling Information].[Tool Material], [Tooling Information].[Shank Diameter], [Tooling Information].[Minor Diameter], [Tooling Information].[Max Cut Depth], [Tooling Information].[Board Thickness], [Tooling Information].[Tool Description], [Tooling Information].[Tool used in Fergus Falls - Veneer], [Tooling Information].[Tool used in Corbin - Thermofoil], [Tooling Information].[Tool used in Vanceburg, KY - Veneer], [Tooling Information].[Tool used in Vanceburg, KY - Wood], [Tooling Information].[Tool used in Arkansas City, KS - Veneer], [Tooling Information].[Tool used in Fergus Falls - Thermofoil], [Tooling Information].[AutoCAD File], [Tooling Information].[PDF File] FROM [Tooling Information]"
    CurrentDb.QueryDefs("Query1").Close
    
End Sub

 

[KeithG]

Sorry, I forgot I did one other thing. If you notice in the code for your view button each combo box is checked to see if the box is not null and Len>1. Because when you hit the cancel button you are assigning a zero length string to the comboboxes so they won't be null.

 

[mogul0212]

Sorry, I forgot I did one other thing. If you notice in the code for your view button each combo box is checked to see if the box is not null and Len>1. Because when you hit the cancel button you are assigning a zero length string to the comboboxes so they won't be null.

I'm lost. I'm sorry. I didn't understand this post. I've included my most up to date database with the code you gave me to try fix it. Can you look at this and tell me what's wrong?

 

[KeithG]

Currently for every Combo box your have;

if Isnull(Me.[ComboName)=false then

What you need for each combo box and text box is

if Isnull(Me.[ComboName])=False and len(me.[ComboName])>1 then