Filtering and using First() inside a query

[Gilligan]

I have a fairly simple query here.

Basically I have a few records with a similar field "UPC" and also a Yes/No.

I'm trying to do an update query that updates the oldest record to "No" where the UPC = [forms]![frmPurchased]![theUPC]



I'm doing the "first()" part inside the criteria on the "ID" field in the QBE of the query and my yes/no field has the update in it.

In (SELECT First(tblPurchased.ID)
FROM tblPurchased
HAVING ((([tblPurchased]![InStock])=Yes)))

I tried adding the UPC field to the QBE and setting the criteria to = the form control but it isn't working.

It works as a select query no problem... just doesn't do them both.

Basically no errors, it just doesn't update anything.

I also tried to just add a WHERE clause to that sql statement but it just pops up a prompt and doesn't pass the parameter to the statement.

Am I clear?

[orange]

No.
Show all of the Update query sql.

[Gilligan]

I guess that makes more sense... sorry I'm kind of new at all of this.

UPDATE tblPurchased SET tblPurchased.InStock = No
WHERE (((tblPurchased.UPC)=[Forms]![frmPulledStock]![theUPC]) AND ((tblPurchased.ID) In (SELECT First(tblPurchased.ID)
FROM tblPurchased
HAVING ((([tblPurchased]![InStock])=Yes)))));

This all seems to make sense to me from an SQL standpoint, but it prompts for the "frmPulledStock!theUPC when executed.

[orange]

Where do you run this code?

If it's on a form event you could try
Me.theUPC

or
Forms!frmPulledStock!theUPC

More info than you need re form references
http://www.mvps.org/access/forms/frm0031.htm

[Gilligan]

Yes, this is the exit event of a text box. It runs some vba code that calls this with docmd.openquery "qryUpdateInventory"

BTW, your second method was what I was already doing.

[orange]

I think, if you are running a stored query, that the value of Forms!frmPulledStock!theUPC is not known when you Open (Run) the query.

I have to go out at the moment but will look into passing a parameter, or creating the query in vba and then updating the stored query before running it.

[Gilligan]

That would be great...

I did try to put it in the VBA code but I couldn't seem to get the syntax down right.

[orange]

I tried to set up something similar to your situation.

I have 2 tables
Doctor
Doctor_id
Doctor_name
Doctor_Specialty

DoctorSpecialty
id
Specialty

I have a form, bound to DoctorSpecialty
frmSpecialty which has a text box Specialty
and a button that runs a stored query
qryUpdateDrSpecialty

The query is

UPDATE Doctor SET Doctor.DoctorName="zaggggg" 
WHERE Doctor.DoctorSpeciality = Forms!frmSpecialty!Specialty;

I select the value in Specialty and click the button and the query runs as expected.

Is my set up similar to yours? Do you have a bound form?

[Gilligan]

Form is not bound, but I tried setting the "record source" to that table it doesn't seem to matter. I assume that is what you mean by "bound".

I do have a select query (just for test purposes) that works perfectly when I click a button and selects the correct records and shows be a table with them. I then just need my update query to do the same but then give me the first() of that.

[orange]

I have removed the bound form (ie it is not based on a table - it was previously bound to DoctorSpecialty) It's now unbound, I use a value typed into Specialty field on form.
I do not get any request to enter a parameter.

I don't believe your message for parameter is because of the IN clause.

Can you send a copy of your database -- without any confidential info?

[Gilligan]

Ok... but it's kind of embarrassing.

I'm not a trained coder and I've just gotten into Access.

Plus this is a VERY crude proof of concept that I would be tweaking after I got it functioning like I like.

I'd be using a bar code scanner to input the UPC.

Warning, it gets into somewhat of a stupid loop when trying to stop it sometimes because it's on the EXIT event of the UPC text box... so every time you try to leave that box it keeps running the faulty code.

[orange]

A few points.

On your query Query1, I get the message Enter a Parameter and that is because
the form Forms!frmPulledStock!theUPC is not open. Since the form isn't open the query can NOT get the value.

Somewhere, and I can't find the reference at the moment, some described the process of an Update query that had
values being picked up from a Form!frm... sort of thing. First it gets the Form value, then it basically recreates the update query
using the value it got from the Form field. So if the Form isn't Open, the process can't proceed, but will ask for a value for the field it can't resolve.

Beware of field names like Date (this is a reserved word in Access)

List of reserved words
http://allenbrowne.com/AppIssueBadWord.html

I would recommend you look at the data models here
http://www.databaseanswers.org/data_models/index.htm
(look at the inventory ones) The relationships have been worked out.

There is an inventory template database from M$oft that many recommend. It has tables, forms etc. A lot of people say it's a good template that you can adjust.

Get a good model that supports what you're trying to do. Get some realistic test data
and use it to test the model.

There is another good reference to Inventory from Allen Browne that is quoted a lot.
http://allenbrowne.com/appinventory.html

No one will tell you that inventory is easy. It is quite complicated in real life.

I'd also suggest you refer to Crystal's access stuff
Tutorials, videos, design, lots of experience stuff
http://www.accessmvp.com/strive4peace/


Another structured approach to learning more Access
http://www.functionx.com/access2003/index.htm

You can always make small stubs ( little functions that basically do nothing but put out a message like
" Inventory Items have been added"). Just some blank code that let's you test a process or group of processes.
You'll have to go back and do the details, but the technique let's you lay out some complex things without having all the code.

Also, there are a lot of free video tutorials at http://www.datapigtechnologies.com/AccessMain.htm

Some times you can go to a forum and search for things like Inventory Control to see what has been posted in the past.
The thing about this is you can see how posts lead to other posts and how ideas/approaches evolve.
Here's one for UtterAccess

http://www.google.com/cse?cx=partner...%2Fforums.html

Good luck with your project.

[Gilligan]

Well, Query1 was just that "test" query... the button was placed there for the same purpose. The real event that I want to see happen is when I "EXIT" the UPC text box. So when you put in 88 in the UPC text box that it should uncheck the top record with 88 in it below the 99's in the "tblPurchased" table.

I'll correct the Date name, thanks.

This will eventually be incorporated into another POS database that my brother did for me in another mdb file. It is also just a project for me to get a better understanding of how to work with Access. Vs harassing him or you guys every time I have a question when trying to tweak his database that he did for me. I was hoping this would be "baby steps" to understanding this (access).

[orange]

I looked into your code a little more.

In your Private Sub theUPC_Exit(Cancel As Integer) routine you have created the Update query in vba, and as well you have a stored query qryUpdateInventory.

I can see where you have commented out areas for testing.

Since you already have A = theUPC, you have the value of Forms!frmPulledStock!theUPC, so when you construct strSQL you could use the variable A.

 strSQL = "UPDATE tblPurchased  " _
                & " SET tblPurchased.InStock = No WHERE " _
                & " ((tblPurchased.ID) In (SELECT First(B.ID) FROM tblPurchased As B " _
                & " HAVING (((B.UPC)='" & A & "') AND((([B]![InStock])=Yes)))));"

and then use conDatabase.Execute strSQL

OR you can change qryUpdateInventory to

UPDATE tblPurchased
 SET tblPurchased.InStock = No
WHERE 
 tblPurchased.ID In 
(
SELECT First(B.ID) AS Expr1
FROM tblPurchased as B
WHERE ((([B]![InStock])=Yes)) and
 B.UPC=[Forms]![frmPulledStock]![theUPC]
);

When you have a subquery, you alias the tablename so the second use of the table is clear to SQL. That's why I used tblPurchased as B. You're using the table twice and this keeps the references clear.

I moved the [Forms]![frmPulledStock]![theUPC] to the subquery, since you need
the UPC and the InStock =Yes to select the proper record.

Hope this helps.


Here is the revised code for the procedure if you choose to use it.
I added an Error routine. I commented the DoCmd.SetWarnings False
I would use the strSQL as per the proc. I commented out the DoCmd.OpenQuery

You do not need the button to Run the Query on the Form and can remove it.
The Update runs when you move out/Exit of the UPC field.

Private Sub theUPC_Exit(Cancel As Integer)
    Dim A, B, C, D As Variant
    Dim strAnswer As String
    Dim iCounter As Integer, iQty As Integer
    Dim conDatabase As ADODB.Connection
    Dim strSQL As String
    
   On Error GoTo theUPC_Exit_Errorm

    A = theUPC
    
    If IsNull(A) = True Then
        Exit Sub
        Else
    End If
    
    B = DLookup("UPC", "tblItems", "UPC = [theUPC]")
    
    If IsNull(B) = True Then
        MsgBox "This item isn't in the DataBase!", vbOKOnly
    Else
        C = DCount("ID", "tblPurchased", "InStock = yes AND UPC = [theUPC]")
        D = DLookup("ID", "tblPurchased", "InStock = yes AND UPC = [theUPC]")
        iQty = txtQty
        If iQty > C Then
            MsgBox "We don't have that many!", vbOKOnly
            Exit Sub
        Else
            'DoCmd.SetWarnings False
            DoCmd.Beep
            Set conDatabase = CurrentProject.Connection
            strSQL = "UPDATE tblPurchased  " _
                & " SET tblPurchased.InStock = No WHERE " _
                & " ((tblPurchased.ID) In (SELECT First(B.ID) FROM tblPurchased As B " _
                & " HAVING (((B.UPC)='" & A & "') AND((([B]![InStock])=Yes)))));"
            Debug.Print strSQL
            For icount = 1 To iQty
            ' = DoCmd.OpenQuery "qrySelectOldest"
           ' DoCmd.OpenQuery "qryUpdateInventory"
            conDatabase.Execute strSQL  '<<< do this
            Next
            conDatabase.Close
            Set conDatabase = Nothing
        End If
    End If

   On Error GoTo 0
   Exit Sub

theUPC_Exit_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure theUPC_Exit of VBA Document Form_frmPulledStock"

End Sub

[Gilligan]

Excellent!!

Yes, I was trying various techniques and just commenting out the other ones while I awaited your suggestions on what I should do to make it work.

I agree, I should have been using the variable when I put the SQL statement in the code (that was a recent attempt and my brain was fried at that point.)

The part about using it as B makes sense though, just curious would it work if we just called it explicitly?

I think this will help me for the next time I attempt anything like this. Thanks for fleshing out both ways.

It is working VERY well now (as I'm sure you know)... the button was just a dirty test method. I tend to fall back to things I KNOW work to help eliminate variables when I'm trouble shooting a stubborn bug. If what I KNOW should work doesn't work then at least I know it isn't the new method I introduced that is causing the bug.

This is sweet! I'm so happy it is working!

Thank you a thousand times!