Filtering a recordset with a variable

[mjdemaris]

I would like to filter a DAO.Recordset using a value from a list box. Currently I am attempting to store the value using a TempVar, but have unsuccessful, and reading through some posts conclude that I should not do it this way.

The value is coming from one form, via list box, and a click event will open another form with just that record.

How is this accomplished?

Mike

 

[pbaldy]

In general, skipping the declarations and such:

strSql = "SELECT * FROM TableName WHERE FieldName = " & Me.ListboxName
Set rs = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)

 

[pbaldy]

Oh, and it sounds like maybe this is what you're trying to do:

http://www.baldyweb.com/wherecondition.htm

 

[mjdemaris]

What's the best way to add/edit data, open forms with more details on an item, use queries or SQL in VBA...

The more time I spend with Access, it's like I am in the middle of a spider web. There is like 100 ways to do the same thing, all with slightly different syntaxes, scopes, accessibilities, and some with differing results.

I've been using TempVars, Macros for controls, macros that show up in the nav pane, global variables, queries, VBA SQL...

I'm getting a little turned around. Some say stay away from TempVars, or stay away from macros, or stay away from VBA. I've seen some DB's with almost no VBA, but I am not sure I can model an Inventory Control DB on that type.

Sigh...

Mike

 

[pbaldy]

They are all tools. Like any tool, there are appropriate places to use each. As you say, there are often multiple ways of accomplishing a goal, and I don't think you can say which is "best" without knowing the specifics of the situation.

Generally speaking, VBA is more powerful and flexible than macros. However, you can't use VBA in a web app. Most experienced developers use VBA, but some are more comfortable with macros. Personally I don't use macros and haven't used TempVars, probably because I learned before they existed and I'm an old dog.

I use the method in post 3 frequently to open a form to a specific record.