Run query from a Form

[Master Klick]

Disregard that last post. I realized how dumb it was after I sent it.

[Dal Jeanis]

Not dumb. You're just building different mental models of how the thing could work. I discard several unworkable models every time I do anything complex. Hopefully, like you this time, I discard them before I get to writing the code.

[Master Klick]

Ok, I got something working, kind of. How do I go about posting on here.

[June7]

Follow instructions at bottom of my post.

[Master Klick]

It doesn't want to upload.

Anyway, I'm trying something different. If I can get a macro to edit the query for me, that should clear up what's going wrong. Problem is I know next to nothing about macros. I started building on top of what I was using before just to requery:

Private Sub Edition_AfterUpdate()
Me!List.Requery
Me!Color.Requery
Me!CardType.Requery
Me!CardSubtype.Requery
End Sub

Now I'm trying to get the macro to put the selected value of the combo box into the criteria of the query, then requery, I think. So far I got:

Private Sub Edition_AfterUpdate()
Dim myStr As String
myStr = Me!Edition.Value
Querys!ImportSheetQuery!Edition = myStr
Me!List.Requery
Me!Color.Requery
Me!CardType.Requery
Me!CardSubtype.Requery
End Sub

Of course I don't have a very good understanding of what I even had working before and now I'm not sure if I'm even going in the right direction. I think the first to lines store the value to be entered. Can't figure out how to tell it to put that value in the criteria. Not sure if I'll need a separate code to take it back out. And I'm pretty sure I'll still need the requery's in there.

Feel free to tell me how wrong I am. I know I'm lost and probably going in the wrong direction. It's a pretty common state for me to be in. lol

[June7]

Why does the upload fail - what happens? An alternative is upload to a fileshare site such as Box.com and post link to the file.

First off, that isn't a macro, it is VBA code.

Second, can't set the value of field in a table or query by direct reference to the table or query. That would require an sql UPDATE action.

Third, I don't think we will be able to progress without analyzing your db. We are going in circles.

In addition to the tutorials for cascading comboboxes in posts 2 and 6, here are several on use of parameterized queries to filter data:
http://datapigtechnologies.com/flash...earchform.html
http://datapigtechnologies.com/flash...mtoreport.html
http://datapigtechnologies.com/flash...tomfilter.html

[Master Klick]

Those links don't help and I can't get this thing to upload. I don't know what else to do. I wish there was something that just explained what everything did, what different commands and stuff actually meant. Every other site I've ever found just gives narrow applications and extremely poor explanations that sound more like politics than instructions. I engineer and program card access systems. This shouldn't be that difficult for me.

[June7]

You zipped the db? How big is the zip file? Tried fileshare site like Box.com?

[Dal Jeanis]

Master Klick - I agree about the disjointedness of learning Access. It's hard to get a straight and simple answer to the one thing you need at any given time, because everything is interrelated and depends on so many other things.

Also because certain pedants insist on answering the line "it hurts when I do this" with "Don't do that, do this!" and "this" doesn't meet your business requirements.

Here's some resources to help clear up the ground fog, when you get the chance.

MVP Crystal Long has graciously put her Access Basics 2003 and her VBA tutorial on line for free, here - http://www.accessmvp.com/Strive4Peace
Allen Wyatt's Access 2000 tutorial is here http://www.learnaccessnow.com/
Cal State Northridge has a 2007 overview here (very basic) http://www.csun.edu/sites/default/fi...s07-basics.pdf
Function X has a 2010 tutorial here, but I'd suggest reading Crystal's book first as an intro, then you can focus on the more advanced items here rather than going through them all - http://www.functionx.com/access/

BTAB has tools and tips here http://www.btabdevelopment.com/ts/
AccessWeb has more tools and tips here - http://access.mvps.org/access/toc.htm
MVP Allen Browne's tips are here http://www.allenbrowne.com/tips.html

[Master Klick]

Cool. I'll check them out. And thanks for all the help.

[Master Klick]

I think I might have made a little progress. I need help with one function though. In the criteria for a query I put"

IIf([Forms]![Form1]![Edition]="", __________,[Forms]![Form1]![Edition])

I need to fill in the blank with either something that makes the query ignore the criteria or with something the will make the query return everything in the field.

Is this possible?

[June7]

Use LIKE with wildcard as criteria (as demonstrated in DataPig tutorials) then the IIf should not be needed:

LIKE [Forms]![Form1]![Edition] & "*"

Either approach assumes every record has a value in the field.

[Master Klick]

IT WORKS!!!! YEAH BABY!!!

Table with all the data, query with all relevant fields and a form with a list and 4 combo boxes.

Boxes and list all reference the query. Criteria in query is dependent on a combo box with the function, LIKE [Forms]![Form1]![Edition] & "*", compliments of June7. Thank you sir.
Boxes use and event such as:
Private Sub Edition_AfterUpdate()
Me!Type.Requery
Me!Subtype.Requery
Me!Color.Requery
Me!List.Requery
End Sub

All combo boxes and the list update each other without error.

Thank you everyone for your help, patients and input.

[Dal Jeanis]

You can return the records with Nulls easy enough.

WHERE ([FieldName] IS Null OR [FieldName] LIKE [Forms]![Form1]![Edition] & "*")

To answer the question you asked in #26, you could probably use something like this as well

WHERE ( IIf(NZ([Forms]![Form1]![Edition],"")="", True , [FieldName] = [Forms]![Form1]![Edition]) )

The first clause tests for Nulls or empty string on [Edition] on the form, then returns true if found. The second tests [Fieldname] on the database against [Edition] on the form and returns either True or False to the overall WHERE condition.

Or, you could use June7's method to handle Nulls at both ends, like this:

WHERE ([FieldName] & "" LIKE [Forms]![Form1]![Edition] & "*")

That last one seems pretty elegant.