Help with my VBA SQL statement?

[blodzoom]

I'm not even sure that you can structure a select statement this way but I'm trying to subtract the items that have exited from the items that have been damaged to figure my current inventory.

There may or may not be a value in SumExits for each ItemID. I have checked the Control variable and it is giving me the appropriate ItemID but rs!Inventory continues to be null.

strSQL = "SELECT UnitsDamaged - nz(SELECT SumExits FROM qrySumExits WHERE ItemID = " & Control & ",0)AS Inventory FROM [qryUnitsDamaged] WHERE ItemID = " & Control

Set rs = CurrentDb.OpenRecordset(strSQL)
Forms!frmExit!txtInInventory.Value = rs!Inventory

 

[blodzoom]

Can you not run a SQL SELECT statement against an Access query? It seems that that's where I'm going wrong. I thought you could treat them as a table.

 

[Joe4]

What is the structure of your two objects, qrySumExits and qryUnitsDamaged)?
If they both have an ItemID field, why not just create a new query joining these two queries on this ItemID field as opposed to trying to nest it like you have?

 

[blodzoom]

What is the structure of your two objects, qrySumExits and qryUnitsDamaged)?
If they both have an ItemID field, why not just create a new query joining these two queries on this ItemID field as opposed to trying to nest it like you have?

That is a good question and the answer is twofold. First is that I couldn't figure out how, I was basically writing the same SQL statement as above and it wasn't working. Second is that when the SQL is in VBA, I have at least a little bit of knowledge about what to do with it but I have no idea how to populate a txtbox from an Access Query.


I am currently using Dlookup and it works but it's a continuous form and the Dlookup field populates much more slowly than the rest and it just looks bad.

Here are my queries... Couldn't be simpler.

ItemID UnitsDamaged
1 10
2 40
3 1
4 30
5 40
6 8

ItemID SumExits
1 7
2 12

So the first item should read 3, the second should read 28, the rest should just be unitsdamaged - zero.

 

[Joe4]

This query should return what you want:

SELECT qryUnitsDamaged.ItemID, 
[qryUnitsDamaged]![UnitsDamaged]-nz([qrySumExits]![SumExits],[qryUnitsDamaged]![UnitsDamaged]) AS Inventory
FROM qryUnitsDamaged 
LEFT JOIN qrySumExits 
ON qryUnitsDamaged.ItemID = qrySumExits.ItemID;

I can't say I understand it is what you are trying to do with Forms and Controls in VBA though. Why do you need this in VBA instead of a straightforward Select Query?

 

[blodzoom]

Much appreciated, and maybe i don't need it in VBA, but I might need a little help with the next step, it could very well be that my whole design isn't optimal...

So now that I have your query, I would like to get that information into a textbox on a continous form that is linked to my Item table so that I could display details on each item along with the inventory number that you just helped me calculate.

Dlookup works, and I use that on other forms in the Default value but on a continous form, it has to be in the control source and for some reason that goes very slow.

As I was writing this, I thought "Why don't I just add more details to the query and link the form to the query instead of the table?" Is that what you would do? That would work since it's read-only but I'm still curious about getting the query results into a txtbox on a form that is linked to something else.

 

[Joe4]

Would this work?

Make the query a Parameter Query by putting this in the criteria line of the ItemID field:
[Enter the ItemID you wish to return]

Then create your Form using this query as its Control Source. Then, whenever you open the Form, it will prompt you to enter the ItemID you want to see, and it will return a Form with all the values for that ItemID.

Is that what you are looking for?

 

[blodzoom]

Would this work?

Make the query a Parameter Query by putting this in the criteria line of the ItemID field:
[Enter the ItemID you wish to return]

Then create your Form using this query as its Control Source. Then, whenever you open the Form, it will prompt you to enter the ItemID you want to see, and it will return a Form with all the values for that ItemID.

Is that what you are looking for?

Not really, I've created a searchable list in the form of a continous form, so I'm looking to display all 6 items (or possibly hundreds, this is test data) at the same time, then I have a button on each item that links to a "details" form. the way that I have made things is probably clumsy but it all looks pretty slick from the user's perspective except for the Dlookups populating slowly.

 

[Joe4]

Not really, I've created a searchable list in the form of a continous form, so I'm looking to display all 6 items (or possibly hundreds, this is test data) at the same time, then I have a button on each item that links to a "details" form. the way that I have made things is probably clumsy but it all looks pretty slick from the user's perspective except for the Dlookups populating slowly.

I can't say that I understand how you are describing this form works. But maybe the following will help.

I have many forms where the query is built "on-the-fly" as the user makes criteria selections/entries on the form. What I do is have VBA build the SQL code based on the form selections/entries and then assign that SQL to an existing query. I then have a Form which uses this same pre-existing query as its Control Source. So after I made my selections, it updates the SQL code and opens the Form based on that updated SQL code.

That code might look something like this:

    Dim mySQL as String
 
'   Build SQL string (using value entered in combo box on Form for ItemID)
    mySQL= "SELECT qryUnitsDamaged.ItemID, " & _
            "[qryUnitsDamaged]![UnitsDamaged]-nz([qrySumExits]![SumExits],[qryUnitsDamaged]![UnitsDamaged]) AS Inventory " & _
            "FROM qryUnitsDamaged " & _
            "LEFT JOIN qrySumExits " & _
            "ON qryUnitsDamaged.ItemID = " & Me!cboItemID & ";"
 
'   Assign SQL code to a pre-defined query
    CurrentDb.QueryDefs("MyQueryName").SQL = mySQL
 
'   Open Form that uses query referenced above ("MyQueryName") as its Control Source
    DoCmd.OpenForm "MyFormName", acNormal, , , acFormEdit

 

[blodzoom]

It actually worries me a bit that you don't understand what I've done. I think it means that I suck at words or I suck at Access.

Here is a pic of the main form, it is searchable by "type", "Descrip" and "inventory". It is linked to a table called tblItem. The "Qty" field is the piece that I'm working on.