By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,151 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Parameter Form Doesn't Create Correct Filter per VBA

Brilstern
100+
P: 207
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;

Expand|Select|Wrap|Line Numbers
  1. Add new Item              "New Item"
  2. Edit Item                 "Edit Item"
  3. Delete Item               "Delete Item"
  4. Update Item               "Update Quantity"
  5. Add one to Item           "+1"
  6. 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"
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEdit_Click()
  2.     On Error GoTo cmdEdit_Click_Err
  3.  
  4. DoCmd.SetWarnings False
  5.         DoCmd.OpenForm "frmitem select3"
  6.  
  7. cmdEdit_Click_Exit:
  8.     Exit Sub
  9.  
  10. cmdEdit_Click_Err:
  11.     MsgBox "Inserted Invalid Item Name"
  12.  
  13.     DoCmd.CancelEvent
  14.  
  15. DoCmd.SetWarnings True
  16. End Sub
CMD on "Select" (frmItem Select3)
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdselect_Click()
  2. DoCmd.OpenForm "frmEdit Item", , _
  3.                 "Item Name Description =" & Me.ItemSelect
  4.  
  5. End Sub
"frmEdit Item" Feilds
Expand|Select|Wrap|Line Numbers
  1. Nomenclature           TEXT
  2. Item Name Description  TEXT
  3. NSN                    TEXT PK
  4. Reorder Level          NUMBER
  5. Target Level           NUMBER
  6. Quantity Per Unit      NUMBER
  7. Available Inventory    NUMBER
  8. Category               TEXT/LIST (COMBO BOX ON FORM)
  9. Company                TEXT
  10. Company Site           HYPERLINK
  11. Product ID             TEXT
  12. Standard Cost          CURRENCY
  13. Details                TEXT
CMD on "Update Quantity"
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdupdate_Click()
  2.  
  3. DoCmd.SetWarnings False
  4.     On Error GoTo cmdupdate_Click_Err
  5.  
  6. DoCmd.OpenForm "frmItem Select1"
  7. DoCmd.Requery
  8. cmdupdate_Click_Exit:
  9.     Exit Sub
  10.  
  11. cmdupdate_Click_Err:
  12.     DoCmd.CancelEvent
  13. DoCmd.SetWarnings True
  14. End Sub
CMD on "Select" (frmItem Select1)
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdselect_Click()
  2. DoCmd.OpenQuery "qryupdate Inventory"
  3. DoCmd.Close
  4. DoCmd.Requery
  5. End Sub
Update Query SQL
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblInventory Totals] 
  2.   INNER JOIN [tblExpendable Inventory] 
  3.     ON [tblInventory Totals].NSN = [tblExpendable Inventory].NSN 
  4.        SET [tblInventory Totals].[Available Inventory] = 
  5.     [Available Inventory]+[Amount to add to value]
  6.  WHERE ((([tblExpendable Inventory].[Item Name Description])=
  7. [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
Attached Files
File Type: zip Band Inventory Database.zip (507.7 KB, 53 views)
Jan 3 '12 #1

✓ answered by 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 :
    Expand|Select|Wrap|Line Numbers
    1. 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.

    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdselect_Click()
    2.     Dim strFilter As String
    3.  
    4.     strFilter = "[Item Name Description] = '" & Me.ItemSelect & "'"
    5.     Call MsgBox(strFilter)
    6.     Call DoCmd.OpenForm(FormName:="frmEdit Item", WhereCondition:=strFilter)
    7.     Call DoCmd.Close(acForm, Me.Name)
    8. End Sub

Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 3 '12 #2

Brilstern
100+
P: 207
Ok, so I figured it out!

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

Expand|Select|Wrap|Line Numbers
  1.     UPDATE [tblInventory Totals] 
  2.       INNER JOIN [tblExpendable Inventory] 
  3.         ON [tblInventory Totals].NSN = [tblExpendable Inventory].NSN 
  4.            SET [tblInventory Totals].[Available Inventory] = 
  5.         [Available Inventory]+[Amount to add to value]
  6.      WHERE ((([tblExpendable Inventory].[Item Name Description])=
  7.     [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
Jan 5 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 5 '12 #4

Brilstern
100+
P: 207
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
Jan 6 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 9 '12 #6

Brilstern
100+
P: 207
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
Attached Files
File Type: zip Band Inventory Database.zip (450.8 KB, 73 views)
Jan 10 '12 #7

Brilstern
100+
P: 207
NeoPa,

Any suggestions? I am still having trouble with this.

Sgt B
Jan 14 '12 #8

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 15 '12 #9

Brilstern
100+
P: 207
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
Jan 16 '12 #10

NeoPa
Expert Mod 15k+
P: 31,494
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 :
    Expand|Select|Wrap|Line Numbers
    1. 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.

    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdselect_Click()
    2.     Dim strFilter As String
    3.  
    4.     strFilter = "[Item Name Description] = '" & Me.ItemSelect & "'"
    5.     Call MsgBox(strFilter)
    6.     Call DoCmd.OpenForm(FormName:="frmEdit Item", WhereCondition:=strFilter)
    7.     Call DoCmd.Close(acForm, Me.Name)
    8. End Sub
Jan 16 '12 #11

Brilstern
100+
P: 207
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.
Jan 19 '12 #12

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 19 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.