Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access datasheet and hide rows

Posted on 2012-12-28
8
Medium Priority
?
767 Views
Last Modified: 2012-12-28
I have a form that's in datasheet view
It has a subform also in datasheet view

The subform and parent form both have a column txtMyId

What I want to do is if Me.txtMyId <> Me.Parent.txtMyId then

Hide entire data row if possible


I tried testing with the following code and an extra unbound column to update
But it comes back with everything "Matches" which I KNOW isn't correct

Do I need some kind of "For Each?"

I can do that in .Net easily

    If Me.txtMyId = Me.Parent.txtMyId Then
        Me.txt2 = "Matches"
    Else
        Me.txt2 = "No Match"
    End If
0
Comment
Question by:lrbrister
  • 6
  • 2
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727073
<What I want to do is if Me.txtMyId <> Me.Parent.txtMyId then
Hide entire data row if possible>

Why not just filter for this?:

Make the recordsource for the form:
SELECT....
FROM Yourtable
WHERE MyId <> Forms!YourForm!MyID

...or something similar

JeffCoachman
0
 

Author Comment

by:lrbrister
ID: 38727100
boag2000
This subform datasource is a view from SQL Server and set in the properties box.

I want to do this in VBA if at all possible...

The actual <> will look like this...

If Nz(Me.txtMyId,Me.Parent.txtMyId ) <> Me.Parent.txtMyId
...
This will allow nulls and whatever = the Parent

How do I set this in VBA when the datasource is a straight properties view?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727216
With the addition of the NZ(), it is still not clear what you need here, ...

If the Recordsource is set, and cannot be changed, ...you can set the Filter Property of the subform:
MyID<>Forms![frmParent]![MyID]
(and probably set the FilterOnLoad Property to: Yes)

I mean, you can filter dynamically with VBA, but I don't a reason to go through all the extra work, if you can just filter.

Can you post a simple sample of this database?, it is confusing that you seem to want to filter on your "Linking" field.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727231
Here is the sample DB I worked up and it sets the filter Prop of the subform.
(Open the database and examine the raw data first)

It seems to do what you asked:
    "if Me.txtMyId <> Me.Parent.txtMyId then Hide entire data row if possible"
Database5.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727370
I may have crossed to logic, ...but I am sure you can figure out what needs to be done to fix the syntax...
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 38727431
Yeah, I think the filter you may have wanted is actually:
MyID=Forms![frmParent]![MyID]
0
 

Author Closing Comment

by:lrbrister
ID: 38727467
Example set me straight.

Watch for a new question
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727486
;-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question