query result table from VB

[xsnd47]

How do I make the result table from a query run from VB code the source for list or combo box?

 

[Norie]

I don't understand what you mean, could you give more information.

Can you not just use the query as the record source for the listbox/combobox?

 

[xsnd47]

"What do I put as the Row Source in the properties of the combo box?"

 

[Norie]

Set Row Source Type property to Table/Query and then use the name of the query for the Row Source property.

If you press the ellipsis (...) that appears when you enter the Row Source field Access will open the query builder and list all tables and queries.

You can then just select the required table/query or create a new query.

 

[xsnd47]

What i want to do is very similar to this post:

http://www.mrexcel.com/board2/viewtopic.php?t=84257
But I'm not sure about how to handle the result table from running the query out of VB, such as in
the code below from that post. If I used similar code, what do I put in the Row Source box in the
properties of my combo box?

Thanks for the help.

Dim strSQL

strSQL = "INSERT INTO tblname (fld1, fld2, fld3) "
strSQL = strSQL & "SELECT afld1, afld2, afld3 FROM tblName2 "
strSQL = strSQL & "WHERE afld1 = '" & Me!TextBox1.Value & "'"
DoCmd.RunSQL strSQL

strSQL = "DELETE * FROM tblName2 "
strSQL = strSQL & "WHERE afld1 = '" & Me!TextBox1.Value & "'"
DoCmd.RunSQL strSQL

 

[Norie]

Perhaps you could explain further what you are trying to do.

What do you actually want in your combobox?

Do you really need to use VBA to get what you want?

 

[xsnd47]

First off, sorry in advance for the rambling post.

I have an inventory database with 3 main tables: 1 for additions to the inventory, 1 for current inventory, and 1 for removal from inventory. All 3 mostly have the same fields: line name, date, age, location, creator, number; the additions table also has date added, and the removals table has date removed. There are also various other lookup tables for several of the fields in the main tables.

When somebody wants to remove an item from inventory, I want them to be able to search the current inventory table to find the record of the specific item they want to remove. The problem is that this search needs to be able to be done with a variable number of criteria. For instance, one time a user may want to search by line name and date. Another time, by line name alone or date alone, or creator and date, etc. Pretty much any combination will be need to be used. I couldn't figure out to do this with a standard query you build in Access.

My half-baked plan (I'm pretty new to Access) is to have a form with combo or text boxes for each of the possible criteria. The user would enter whichever of the criteria they want to use, and then click a button to do the query. The VBA code for the button would check which of the criteria boxes have had info entered, and add any of that info to a query string, then execute the query.

That's where I'm stuck for now. Thanks for the help!

 

[Norie]

First thing I would say is why do you have 3 different tables for the inventory?

Why not have one table with a field that indicates the status of each item?

Or you could also have a date added and date removed fields for every item. These could be used in queries etc to get current inventory and past inventory as required.

Have you tried adding a button to your form with the Controls Wizard turned on?

Access should give you the choice Find Record in the Record Navigation category.

If you follow the steps it should generate code for you that can then be adapted for your needs.