Where Clause

[Evon]

Hi, hope someone can help.

I want to be able to point my where clause to a specific record in a field when I double click on the record.

At present when I doubleClick it is defaulting to the Field but not the specefic record.

 

[pr2-eugin]

Could you give another shot at explaining the problem please? I am

 

[Evon]

I have a subform based on a query using data from various tables. I want to be able to doubleclick on a specific record in a field and have that record from that table displayed.
Let me try to illustrate, I will write the Where Clause I have been using in my macro and put in red the code that I would like to be able to write:

[FieldName]??RecordName=[Forms]![FormName]![FieldName]

 

[pr2-eugin]

So am I right in thinking that, each text box in the Subform represents data from the same table organised by means of a Query? Have you looked at DoCmd.OpenForm with Where clause?

 

[Evon]

Yes, I would like to try that, but somehow I am not able to access the DoCmd in my Macro. Been looking around in the various macros in the pull-down menus and can't find it again!!Whew!

 

[pr2-eugin]

The link I provided is for VBA, not a Macro.. If you are using Macro, it will go one step at a time.. OpenForm -> One of the options would be WHERE condition.

 

[Evon]

It is not accepting that code in the where condition line.

 

[pr2-eugin]

Okay can you upload a Stripped down version of your file?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Evon]

OK pr2-eugin,
This is the link.

When I click on the "Requisitions by Supplier" button, it brings up the "Search Result" form which displays all the records of transactions done with the supplier that has the focus on the "Departmental Requisition Form".

I now want to be able to doubleclick on one of the records in the "RequisitionNo" field and have that record displayed in the "Departmental Purchasing Requisition" form.

 

[pr2-eugin]

Evon as much as I would like to help, you must help me to help you.

1. You have no form called "Departmental Requisition Form".
2. Where is the button "Requisitions by Supplier"?
3. Some of the Forms are bound to tables that are linked. In my case they are not,as you either need to attach those table or make them local tables.

 

[Evon]

1&2) Sorry, I just re-uploaded the database to open to the Main form (Departmental Purchasing Requisition). On it you should see the button: "Requisitions by Suppliers".

3)Do not worry about the linked tables, these came about when I "compacted and repaired" the database, and it then became unstable when I tried to remove them. However, this does should not affect you seeing the problem which I would like help for.

I now want to be able to doubleclick on one of the records in the "RequisitionNo" field i.e. "E0001"and bring up that record in the "Departmental Purchasing Requisition" form.

 

[pr2-eugin]

I followed Step-1 and this is what I get.

No where to get to Step-2, even if I say OK the Form presented does not include whatever you are saying.. Without the tables (which are Linked, thus unavailable to me), that the Query is based on. The form cannot be loaded with the data.

PS: I am genuinely trying to help, but at the same time.......

 

[Evon]

Thanks for staying with me Paul.OK this should be good now.

 

[pr2-eugin]

Okay, in the Double Click Event of the SubForm's RequisitionNo, put the following CODE.

Private Sub RequisitionNo_DblClick(Cancel As Integer)
    DoCmd.OpenForm "Departmental Purchasing Requisition Edit", WhereCondition:="RequisitionNo = '" & Me.RequisitionNo & "'"
End Sub

Now that is sorted, I just looked at your Table design.. Needs a Looooootttttt of work. The tables are completely De-Normalized. No relationships exists, Discounts are stored against each record, Multivalued fields etc..

You need a proper DB structure if you need the right data at the right time. The structure you currently have will be a real pain in the not so far future. In your "Departmental Purchasing Requisition" you have reached up to 213 fields , the limit will kick in soon..

Please refer to the Document attached with this message for understanding basics of relationship.. Do some tutorials available online..

 

[Evon]

Not out of the woods yet. I get a:

run-time error "3021": No current record

 

[pr2-eugin]

Here is the modified file.. Look at it..

 

[Evon]

Thanks Sir Paul. When I grow up I truly want to be like you. I finally got it working in mine.

I can hear you thinking...if he finally got it working then he must have found the trick.

 

[pr2-eugin]

Thanks Sir Paul.

You are most welcome.

When I grow up I truly want to be like you. I finally got it working in mine.

I am flattered. But I would ask you humbly once more to reconsider your DB structure. You need to act on it now.. A painstaking task in the start, but it will make your life a lot lot more easier.. Good Luck..

 

[Evon]

Yes Sir, advice taken. To be honest though, I was aware of that all along, it is just that I have been using this database to assist me at my job for over 10 years and have been tweaking it consistently since. Just never seemed to have the time to clean it up as it still is extremely stable.

However, since I have a few more weeks to retirement (yes,its true), I might be able to find the time.
Thanks again for the help and advice and you will definitely be hearing from me in the future, at least as long as you are around these forums.