clicking a record in one form to open the same record in another form

[Shem K]

Hello.

I have a summary form that displays a list of records. I need to know how to, by clicking a field one record, it automatically opens (redirects me to) another form (to view its details) but calling up the same record, without necessarily opening the second form then using the Find feature to call it up on the second form.

Kindly assist.

Thank you.

[NeoPa]

Open the second form using DoCmd.OpenForm() and use the WhereCondition parameter.

[Shem K]

Thanks, NeoPa.

I'm kinda new to VB coding, and have only recently started to including VB (thanks to some valuable input from Bytes.com! :) ) into the Access Database I'm working on. Please take me through the coding for the

DoCmd.OpenForm()

and how to use the WhereCondition parameter to tackle my query.

[NeoPa]

I suppose there are a number of steps.

1. Before anything else decide if Click or DoubleClick is what you want for your trigger mechanism. The code is very similar but just clicking may happen more than you expect.
2. Take each control on the form where you want to enable this feature and look at the Properties panel (Alt-Enter opens them for you).
3. Find the property On Dbl Click and double-click on the name of it. This puts the string value "[Event Procedure]" in the property.
4. Next click on the little button next to that with an ellipsis (...) on it. This takes you to the code.
5. The code window will show you the basis of a module which should look something like :
   

   Option Compare Database
   Option Explicit
    
   Private Sub txtID_DblClick()
    
   End Sub

   Line #2 is especially important.
6. This is the basis of your first subroutine and will vary depending on the actual names of your objects. I've assumed the control is called txtID and that it's the primary index of the table. I will further assume that the name of the form to open is frmDetail.
7. Now, add the following code but change all bits that relate to named objects to suit your project.
   

   Option Compare Database
   Option Explicit
    
   Private Sub txtID_DblClick()
       Dim strWhere As String
    
       strWhere = Replace("([ID]=%I)", "%I", Me.txtID)
       Call DoCmd.OpenForm(FormName:="frmDetail", WhereCondition:=strWhere)
   End Sub

Notice that line #7 specifies which records are allowed. We want only the matching record so we form a filter that handles that. I've assumed the ID is a numeric value but if it is a string then change it to :

    strWhere = Replace("([ID]='%I')", "%I", Me.txtID)

Notice the quotes.

Line #8 opens the form. The FormName and WhereCondition parameters seem fairly self-explanatory.

Let us know how you get on with this.

[Shem K]

I've tried this, but it returned the following error on execution:

----
Procedure declaration does not match description of event or procedure having the same name.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
*There may have been an error evaluating the function, event or macro.
----
(in this case, Event Procedure)

Please help me understand this error.


Also, the database I'm developing is intended to store records in the thousands (1.5k - 2k or more), being client Files for the company. I'm trying to create a system that calls up the original record in a second form, onClick or onDoubleClick of same record on the first form. While the filter system in the code could work for a particular record, is there a way to call out other records individually as well?


Also, is there a way to do the same using macros? And how do I go about it (will help me determine which option is easier)?

[Shem K]

Sorry. I forgot to mention, I'm using Access 2007

[Shem K]

The link below provided an executable method for my query (also contains a download for the programmer's accde. trial version).

http://ms-access-tips.blogspot.com/2...n-form-at.html

I tried using the code in my primary key field (File Ref) like this:

Option Compare Database
 
Private Sub File_Ref_DblClick(Cancel As Integer)
On Error GoTo myError
Dim varWhereClause As String
varWhereClause = "File_Ref" & Me!File_Ref
DoCmd.OpenForm "Paul__Matters_in_Detail", , , varWhereClause
leave:
Exit Sub
myError:
MsgBox Error$
Resume Next
End Sub
 

And the following error message returns:
Microsoft Office Access can't find the field 'File_Ref' referred to in your expression. This is the closest I could get to solving my query so far. But alas!. What could be the issue with my coding?

[Shem K]

Hi all.
I finally did manage to make this possible, through the embedded macro way. I had to wrap my finger around what NeoPa suggested, and the directions on this site:
https://www.youtube.com/watch?v=RILqSTAAQpM

At the Where Condition Action Argument of Action Open Form, I inserted the below text:

="[File Ref]=" & "'" & [File Ref] & "'"

This helped me solve my query.

Thanks, NeoPa, for pointing me in that direction (i.e., the Where Condition).

Regards,

[NeoPa]

Shem:
...
(in this case, Event Procedure)

Please help me understand this error.

This indicates that the name of the procedure doesn't match the name of the object associated with it. You should avoid such a problem if you follow my earlier instructions on how to create it.

Shem:
Also, is there a way to do the same using macros? And how do I go about it (will help me determine which option is easier)?

Yes. There is.

However, most experienced developers wouldn't be cruel enough to send you on that journey. They are limited in their availability and, if you progress any further in Access you will certainly get to a point where they can't do what you need. At that point you have to start again in frustration. They are also much harder to manage and maintain from my understanding.

Shem:
Microsoft Office Access can't find the field 'File_Ref' referred to in your expression. This is the closest I could get to solving my query so far. But alas!. What could be the issue with my coding?

I very strongly advise not trying to cobble together solutions from more than one place at a time. It's like trying to ride two horses. Uncomfortable as long as they are both travelling in exactly the same direction. Otherwise they'll split you apart. Not much fun for the horses either. Personally, if you choose to use me as your horse I'll be happy to help you progress your code.

Shem:

="[File Ref]=" & "'" & [File Ref] & "'"

If ever you see code in that format, where the single-quotes are included as separate strings on their own, be cautious. Ask yourself if the person using such code has ever properly understood what they're doing. Other things, like calling a string variable var..., cause me to be very cautious about the code you have been working with. People will expect such a variable to be of type Variant from the var part of the name.

I'm pleased if you have something working. Every step is good when you're making progress. Nevertheless, be aware that some steps simply teach you what to avoid in future.

PS. I've just reread this and I'm concerned you may feel deflated by my comments. Don't be. You are starting out and have find solutions as and where you can. That is a good sign in itself. My advice is intended to guide rather than chastise. Be confident. It seems to me you've done well so far.

[Shem K]

Thanks, NeoPa. I hope I pick up the pace in VBA coding, then probably be able to accomplish much more from where I've started from.

[Shem K]

Hi guys.
Just an addition: I did a follow-through on the DoCmd.OpenForm on this one and recently (this morning) converted the macro events of a few forms from a copy of my project into VBA. In Access 2007, the code line for the Where Condition looks like this:

DoCmd.OpenForm "Paul: Matters in Detail", acNormal, "", "[File Ref]=" & "'" & [File Ref] & "'", , acNormal

Hope this is useful for anyone building code on it :-).

[NeoPa]

Thanks for the update.

BTW Do you realise there is never any good reason for concatenating two literal strings together except when together they are too long to be seen easily. "[File Ref]=" & "'" is equivalent to "[File Ref]='" and a much better (correction - worse) way to code it.

Better because it's so much easier to understand a single item than having to calculate the result of two items concatenated. TBF the task of calculating the result is simple enough, but it's very rare that such a situation is the only thing the poor maintainer has to consider while going through your code. Far better to do it the simpler and easier way (It even involves less typing).

[Shem K]

This makes a lot of sense. I'm updating my code with this one. Thanks NeoPa.