Parameter Form Doesn't Create Correct Filter per VBA

[Brilstern]

I am currently creating a database that will keep track of a supply shops instruments, inventory, and mic. gear. In the home form of this database (frmHome) I have created a tabbed switchboard more or less where the user can add to, edit, and update the inventory. I have created simple commands as follows;

Add new Item              "New Item"
Edit Item                 "Edit Item"
Delete Item               "Delete Item"
Update Item               "Update Quantity"
Add one to Item           "+1"
Subtract one from Item    "-1"

The New Item command and Delete Item Command work fine.
However the Edit Item, Update Item, +1, and -1 don't work properly. I have created a simple form with a combo box that runs on the cmd, after you select the item to be updated it runs a query to update the given changes. The queries have a parameter in them that is taken off of the select form. Where my problem lies is that it wont select a certain item as the user does. It always defaults back to the first Item on the table and updates it instead. Why is this and what do I have wrong.

CMD on "Edit Item"

Private Sub cmdEdit_Click()
    On Error GoTo cmdEdit_Click_Err
 
DoCmd.SetWarnings False
        DoCmd.OpenForm "frmitem select3"
 
cmdEdit_Click_Exit:
    Exit Sub
 
cmdEdit_Click_Err:
    MsgBox "Inserted Invalid Item Name"
 
    DoCmd.CancelEvent
 
DoCmd.SetWarnings True
End Sub

CMD on "Select" (frmItem Select3)

Private Sub cmdselect_Click()
DoCmd.OpenForm "frmEdit Item", , _
                "Item Name Description =" & Me.ItemSelect
 
End Sub

"frmEdit Item" Feilds

Nomenclature           TEXT
Item Name Description  TEXT
NSN                    TEXT PK
Reorder Level          NUMBER
Target Level           NUMBER
Quantity Per Unit      NUMBER
Available Inventory    NUMBER
Category               TEXT/LIST (COMBO BOX ON FORM)
Company                TEXT
Company Site           HYPERLINK
Product ID             TEXT
Standard Cost          CURRENCY
Details                TEXT

CMD on "Update Quantity"

Private Sub cmdupdate_Click()
 
DoCmd.SetWarnings False
    On Error GoTo cmdupdate_Click_Err
 
DoCmd.OpenForm "frmItem Select1"
DoCmd.Requery
cmdupdate_Click_Exit:
    Exit Sub
 
cmdupdate_Click_Err:
    DoCmd.CancelEvent
DoCmd.SetWarnings True
End Sub

CMD on "Select" (frmItem Select1)

Private Sub cmdselect_Click()
DoCmd.OpenQuery "qryupdate Inventory"
DoCmd.Close
DoCmd.Requery
End Sub

Update Query SQL

UPDATE [tblInventory Totals] 
  INNER JOIN [tblExpendable Inventory] 
    ON [tblInventory Totals].NSN = [tblExpendable Inventory].NSN 
       SET [tblInventory Totals].[Available Inventory] = 
    [Available Inventory]+[Amount to add to value]
 WHERE ((([tblExpendable Inventory].[Item Name Description])=
[Forms]![frmItem Select1]![Item Name Description]));

These are pulled straight out of the database attached. Please let me know if I can better explain the question onhand.

Sgt B

[NeoPa]

Top job Stevan :-)

I'm off for the night now, but this post ensures I'm subscribed to this thread and I can look into it in the morning.

[Brilstern]

Ok, so I figured it out!

Simple mistake on my part. Below is the SQL change that fixed it.

    UPDATE [tblInventory Totals] 
      INNER JOIN [tblExpendable Inventory] 
        ON [tblInventory Totals].NSN = [tblExpendable Inventory].NSN 
           SET [tblInventory Totals].[Available Inventory] = 
        [Available Inventory]+[Amount to add to value]
     WHERE ((([tblExpendable Inventory].[Item Name Description])=
    [Forms]![frmItem Select1]![ItemSelect]));

I by mistake put the wrong source, I used the field name [Item Description Name] vs the form object name [ItemSelect].


BUT,
I still am having problems with the Edit Button, I dont know what is wrong here.

Sgt B

[NeoPa]

More apologies I'm afraid Stevan. A busy couple of days. I'll see if I can manage to look at it over the weekend.

[Brilstern]

NeoPa,

I understand we all have jobs to do. If I could put my full effort into Access I could have probably figured it out by now. Unfortunately this is only a side project and doesn't get my full attention.

In saying that I have put some research into why my cmdEdit Item button is not working. I have came up with several variations of the "Me." function as well as using the "Forms!" function. But to no avail, no change in the outcome. It is always a blank form on filter or it shows only the first record. I have also tried taking the filter out of the VBA and put into the form filter itself in the properties but no change in outcome as well. I completely understand what I am looking for and how to get it but somewhere I am misleading it and it has me at a loss.

Fortunately all the other three buttons work! Getting closer.

Sgt B

[NeoPa]

Stevan I'm struggling here to understand what you're trying to do, mainly because I have no idea why you are trying to do what you appear to be trying to do. I cannot open your database as it's in ACCDB format and I use 2003. If I'm to look at an attached database you'll need to follow the instructions found at Attach Database (or other work).

Why are you trying to treat Add, Update, Delete etc as separate functions when a standard Access form handles all of them anyway?

You've done a very good job of explaining your question, but it doesn't help me to understand why you are making such a strange request. It leaves me in a bit of a no-man's-land when it comes to trying to find a way to help.

[Brilstern]

NeoPa,

Essentially I am making quick buttons on the home form that make it easy to update the inventory with only a few mouse clicks. I don't really want the user to be able to do mass edits without me being there or them know what they are doing so I have made most of the normal view as view only and then function buttons to make the small changes as our inventory is used. I hope the DB helps you understand what I am looking for. Let me know if there is anything else you need me to explain.

Sgt B

[Brilstern]

NeoPa,

Any suggestions? I am still having trouble with this.

Sgt B

[NeoPa]

Stevan, I've had this page up in my browser all week now. I haven't completely forgotten you, but looking at someone's database takes a bit longer than most responses and I've been busier than usual this week. Today's no good either as I'm 3 parts to the wind, but I will certainly look at your database tomorrow (Monday) and see if I can manage to make sense of what's not working the way you want it to (which means I have to work out what you're trying to do first), but I'll certainly open it up and see what I can see.

[Brilstern]

NeoPa,

Thank you very much and no rush. This is still currently a side project and not going to get my full attention till early February. I was just making sure I wasn't lost in the wind. I really appreciate all the things you have helped me on thus far and look forward to your future assistance! Thx

Sgt B

[NeoPa]

Right. Some progress at last. Let me log comments and questions as I progress - even if later ones may make an earlier one obsolete. I'll see how it goes.

1. A question. Is it necessary to include [frmItem Selectedit]? Would not the currently selected item of [frmHome] indicate more easily which item is required, with an easier interface including more information to work with?
2. If you must use an extra form for the selection then may I suggest closing it when the code has completed for tidiness. The code would be :
   

   Call DoCmd.Close(acForm, Me.Name)

3. I thought there would be more to look at, but it turns out the problem is a common one - associated with using procedure parameters by position and where some are omitted - You passed your WhereCondition parameter as a FilterName by mistake. I suggest using named parameters in such situations. Not only is it easier to avoid errors, but it makes the code much more readable too.
   
   

   Private Sub cmdselect_Click()
       Dim strFilter As String
    
       strFilter = "[Item Name Description] = '" & Me.ItemSelect & "'"
       Call MsgBox(strFilter)
       Call DoCmd.OpenForm(FormName:="frmEdit Item", WhereCondition:=strFilter)
       Call DoCmd.Close(acForm, Me.Name)
   End Sub

[Brilstern]

Worked like a charm~! thx.

To give a little on the background of the way i am doing things. I am developing this for people who are not quite as computer friendly as I am. This pop up forces more control on the user making them physically check their input every time giving less room for human error. In the case that this is accountability of gear human error needs to be kept to a minimum so I am putting a lot of user friendly/control thought into this.

[NeoPa]

Pleased to hear it worked Stevan :-)

The rest of it is just various recomendations to make life as a developer easier generally. There will be occasions where the standard ways of doing things need to be modified for particular purposes. As long as you understand why you need something different, then you're in a good position to decide to do things other ways.