Parameter Form Doesn't Create Correct Filter per VBA

Stevan Bias

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;

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"
CMD on "Select" (frmItem Select3)
"frmEdit Item" Feilds
CMD on "Update Quantity"
CMD on "Select" (frmItem Select1)
Update Query SQL
These are pulled straight out of the database attached. Please let me know if I can better explain the question onhand.

Sgt B

Jan 3 '12 #1

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.

Jan 3 '12 #2

Stevan Bias

Ok, so I figured it out!

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

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

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

Stevan Bias

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

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

Stevan Bias

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

Jan 10 '12 #7

Stevan Bias

NeoPa,

Any suggestions? I am still having trouble with this.

Sgt B

Jan 14 '12 #8

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.

Jan 15 '12 #9

Stevan Bias

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

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

Stevan Bias

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

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