Get form VBA variables into query where clause
- Thread starter dcavaiani
- Start date
Just throwing ideas:
We could have created a permanent query and change the SQL of the querydef on the fly... that way (as you all know) you won't need to create/delete the querydef.
The other way is to create a parameter query... no code required.
We could have created a permanent query and change the SQL of the querydef on the fly... that way (as you all know) you won't need to create/delete the querydef.
The other way is to create a parameter query... no code required.
Can you point to examples ?
Good job! You found some resource on this? It's been covered many times on here.
I manage to screw things up! I had the parameter query working, but now get a very strange result which someone may have experienced. I have a auto record # ID for a Vendor. When I execute the parameter query from the form, the VendorID value INCREASES BY 1 !! If the VendorID value to be passed from the form is say 27, when the query executes, it display vendor 28. Now, the Vendor ID field in the Vendor Master Table is 'auto increase', but this is a query from the Materials Table, not the Vendor Table. Yet - it seems to increase by 1 when displayed??
DFC - Grab Parameters from a Form
SELECT Materials.VendorID, Materials.VendorInvoice, Materials.PurchaseDate, Materials.Customer, Materials.PartDesc, Materials.Extended, materials.tax, Materials.ohamount, taxable
FROM Materials
WHERE vendorID=Forms![Rick - Materials]!VendorID And vendorinvoice=Forms![Rick - Materials]!vendorinvoice And purchasedate=Forms![Rick - Materials]!purchasedate And customer=Forms![Rick - Materials]!customer
ORDER BY VendorID, PartDesc, purchasedate;
DoCmd.OpenQuery "DFC - Grab parameters from a Form", acViewNormal, acReadOnly
SELECT Materials.VendorID, Materials.VendorInvoice, Materials.PurchaseDate, Materials.Customer, Materials.PartDesc, Materials.Extended, materials.tax, Materials.ohamount, taxable
FROM Materials
WHERE vendorID=Forms![Rick - Materials]!VendorID And vendorinvoice=Forms![Rick - Materials]!vendorinvoice And purchasedate=Forms![Rick - Materials]!purchasedate And customer=Forms![Rick - Materials]!customer
ORDER BY VendorID, PartDesc, purchasedate;
DoCmd.OpenQuery "DFC - Grab parameters from a Form", acViewNormal, acReadOnly
As I look closer, looks like the VendorID displayed on the form is actually ONE LESS THAN what is actually in the Materials Table for Table ID - so I must have something wrong on the field properties for the displaying of the Vendorid within the form.
As you scroll through records in the table, see what is highlighted IN the VendorID List, then AT ANY POINT hit Review Invoice and sEe that iN some cases, the VendorID is actually ONE GREATER in the Table Database - than what is showing in the form for VendorID.
Can you possibly explain this a bit clearer as I don't know which table or which form you're referring to. What would be useful is this:
1. Open the table "table name" and look through the records
2. Open the form "form name" and hit xyz button
3. You will notice that Vendor ID "123" is now "124" in the table "abc"
You get the gist.
1. Open the table "table name" and look through the records
2. Open the form "form name" and hit xyz button
3. You will notice that Vendor ID "123" is now "124" in the table "abc"
You get the gist.
Only 1 form, right? Only two tables, Suppliers and Materials. When you open the form which is controlled by the query which selects only the Uninvoiced Records from the Materials Table... then you see just the open records from the materials table. But, if you see what is in the Vendor ID List box, ad then hit the review invoice, why would the vendor ID field which shows on the Form be in some cases one less than what is actually stored in the Materials Table ?? I am sorry for this snafu - but I must admit - it really has me stumped!
I tried to simplify this to see why error. I just go into materials table and try keying in a record there but when I have the Referential Integrity on the VendorNo I cannot add a materials record - get error 3201. When the R.I. link is removed, all works fine. Of course the entry is chosen from the query dropdown in the list box so no bad vendor id may be added. Seems like a bug in the Access 2002 version?
Similar threads
