By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,037 Members | 1,839 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,037 IT Pros & Developers. It's quick & easy.

Open form to specific record as double-clicked on separate form

WyvsEyeView
P: 46
Okay, now I need to open a form (frmTopics) and select the same record that has been double-clicked on in a separate form (frmTopicsFound). Here are the details:

frmTopicsFound contains datasheet subform sfrmTopicsFound. sfrmTopicsFound is populated from a query that returns a list of topics based on a keyword search. I want users to be able to double-click a row in sfrmTopicsFound and have frmTopics open to that same record. frmTopics may or may not not be open already.

So basically, I need some code on the OnDlbClick event for sfrmTopicsFound.nbrTopID that will:

1. Open frmTopics if it's not already open
2. Display the record from tblTopics where tblTopics.topID = Forms!sfrmTopicsFound!nbrTopID

I'm sure this has been done so if anyone could point me to some sample code, maybe I can take it from there. Thanks!
Sep 2 '08 #1
Share this Question
Share on Google+
19 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Basically, in the Double-click event procedure you would call the open of the form but with a WhereCondition set to a string which identifies the record selected.
Expand|Select|Wrap|Line Numbers
  1. "[TopID]=" & Me.nbrTopID
Does that give you enough?
Sep 2 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
By the way, I used Me.nbrTopID rather than Forms!sfrmTopicsFound!nbrTopID as, presumably, the code runs within the same form as the control is contained in.
Sep 2 '08 #3

WyvsEyeView
P: 46
When I double click the field, I am getting a parameter box, where I have to manually enter the topic ID that I double-clicked on in sfrmTopicsFound. So apparently the value in sfrmTopicsFound.nbrTopID is not being read into the parameter. After I manually enter the topic ID, the correct topic appears in frmTopics. Here is my line of code:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmTopics", , , "nbrTopID=" & Me.nbrTopID
Something similar happened in another situation and the problem turned out to be the relationship between forms and subforms. Could I possibly need to change

Expand|Select|Wrap|Line Numbers
  1. Me.nbrTopID
to syntax as if I were on the main form referring to the subform or something along those lines?

BTW, nbrTopID is an autogenerated number field.
Sep 2 '08 #4

WyvsEyeView
P: 46
Or could it have anything to do with the fact that my subform is a datasheet and not a single form with only one record displayed at a time?
Sep 2 '08 #5

NeoPa
Expert Mod 15k+
P: 31,494
Is the code in the module of the main form or the subform?
Sep 3 '08 #6

WyvsEyeView
P: 46
The code is in the subform because that is where the double-clicked field is.
Sep 3 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
Please try the following code before your DoCmd.OpenForm line and post what you see :
Expand|Select|Wrap|Line Numbers
  1. Call MsgBox("nbrTopID=" & Me.nbrTopID)
BTW I was expecting the code SHOULD be in the subform, but people do funny things sometimes ;)
Sep 3 '08 #8

WyvsEyeView
P: 46
I got a message box saying "nbrTopID = 7" which happens to be the id number of the first record returned by the particular keyword search I used in this instance.

And I am very likely to be one of those people doing funny things! :)
Sep 4 '08 #9

NeoPa
Expert Mod 15k+
P: 31,494
I think we need to look at the underlying query then.

Let's first try opening the form without any WhereCondition and see what results you see.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmTopics"
Sep 4 '08 #10

WyvsEyeView
P: 46
frmTopics opens as expected, to a new/blank record
Sep 4 '08 #11

NeoPa
Expert Mod 15k+
P: 31,494
Curioser and curioser :S

Must shoot off now (your response was just too quick), but I'll have another look when I get home.
Sep 4 '08 #12

WyvsEyeView
P: 46
I had another thought. frmTopics is set to always open to a new record in the form's Open event. Could that setting be overriding the query calling it to open from the record selected in frmTopicsFound? If so, then how do I conditionalize the Open event to be "new record" in every case except where frmTopics is opened from frmTopicsFound?
Sep 4 '08 #13

NeoPa
Expert Mod 15k+
P: 31,494
I would use the OpenArgs parameter in the OpenForm call.

This must be accessed pretty well first thing in the Form_Open() procedure as it gets reset automatically after code is run :(

Let me know if this needs further explanation or, alternatively, how well it works :)
Sep 4 '08 #14

WyvsEyeView
P: 46
SUCCESS! I kept digging around the internet and finally found some code, that when modified, worked perfectly:

Expand|Select|Wrap|Line Numbers
  1. Private Sub nbrTopID_DblClick(Cancel As Integer)
  2.  
  3.     Dim strFrmName As String
  4.  
  5.     strFrmName = "frmtopics"
  6.  
  7.     DoCmd.OpenForm strFrmName
  8. With Forms(strFrmName)
  9.     .Filter = ""
  10.     .Filter = "TopID = " & nbrTopID
  11.     .FilterOn = True
  12. End With
  13.  
  14. End Sub
I believe one problem was that I did not specify that my control names do not have the same name as their control sources. (Probably for most noobs they are the same.) So on frmTopics, the control source for nbrTopId is topID. I should have been using topID all along. However, even when I changed the code you suggested to include topID:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmTopics", , , "topID = Me.nbrTopID"
it still did not work. But at least I do have a working solution now. I declare victory and retreat! Thanks again for all your patience.
Sep 5 '08 #15

NeoPa
Expert Mod 15k+
P: 31,494
I'm pleased that you've found a solution that works for you. HOWEVER...
Although the very process of finding a solution (especially one that works) is very good in and of itself, it's not advisable to stick with an almost best when the best is, I'm sure, such a small step away.

Posted code can often show crucial information that we were previously only guessing at (although refer back to post #2 to see how close my first guess was).

If it's not too much trouble, please try the following code as a replacement for your version and let me know what you get (I know you want to retreat so please bear with me on this).
Expand|Select|Wrap|Line Numbers
  1. Private Sub nbrTopID_DblClick(Cancel As Integer)
  2.     Dim strFrmName As String, strWhere As String
  3.  
  4.     strFrmName = "frmTopics"
  5.     strWhere = "[TopID]=" & Me.nbrTopID
  6.  
  7.     Call DoCmd.OpenForm(FormName:=strFrmName, WhereCondition:=strWhere)
  8.  
  9. End Sub
Sep 5 '08 #16

WyvsEyeView
P: 46
This code sort of works. I say "sort of" because when it opens frmTopics, frmTopics is behind frmTopicsFound and cannot receive focus until I close frmTopicsFound--as if frmTopicsFound is modal. That is not the result I want. I would prefer frmTopics to have the focus on opening.

Also, using "my" code, I can open frmTopicsFound on its own, kludge the query via the parameters box, double-click the topic ID and frmTopics opens and displays the record. This new code opens frmTopics to a blank record in that scenario. Now, normally users will only open frmTopicsFound via a command btn on frmTopics, and in that scenario the result is as in the paragraph above, but why should this code not work in both scenarios?

Finally, could you explain why the filter-based code is less preferable than what you supplied? I would like to understand the difference. Thanks!
Sep 5 '08 #17

NeoPa
Expert Mod 15k+
P: 31,494
This code sort of works. I say "sort of" because when it opens frmTopics, frmTopics is behind frmTopicsFound and cannot receive focus until I close frmTopicsFound--as if frmTopicsFound is modal. That is not the result I want. I would prefer frmTopics to have the focus on opening.
...
That's not what I would have expected - but there you go - I don't know everything that's set up in your database.
...
Also, using "my" code, I can open frmTopicsFound on its own, kludge the query via the parameters box, double-click the topic ID and frmTopics opens and displays the record. This new code opens frmTopics to a blank record in that scenario. Now, normally users will only open frmTopicsFound via a command btn on frmTopics, and in that scenario the result is as in the paragraph above, but why should this code not work in both scenarios?
...
There are ways to ensure most of this can be done for either approach. Some of it I frankly don't understand, but I expect it would all make sense if I had all the information. I don't suggest we go to this level unless you're particularly keen (I doubt this from your earlier comments).
...
Finally, could you explain why the filter-based code is less preferable than what you supplied? I would like to understand the difference. Thanks!
It's not a very big deal.

Mainly it's the logic of the thing. The WhereCondition parameter is provided for the very purpose of opening a form with a filter set. Your code is essentially opening the form with no filter, then going back and saying, in effect, "hold on guys - I've changed my mind - I want one after all". This may, or may not, have its effect after the form has already been populated. If so, then this is inefficient coding.

As a general rule it is better practice to use the tools provided. After all that is what most people will be familiar with so your code will interoperate better.

I hope all that makes sense. I am happy to take this further if you like, but I can see that, with the design you have of your frmTopics form, this will not be quite as straightforward as I'd anticipated, and I will easily understand if you don't feel the returns will be worth the investment. After all, it seems to work ok now.
Sep 5 '08 #18

WyvsEyeView
P: 46
Well, I really do want to do things the most efficient and correct way, for several reasons. One, just because it's the right thing to do :) Two, I have visions of converting this to a SQL Server db at some point (way) down the road and the tighter my code is the more successful that conversion will be. I'm a technical writer and I'm developing a content management database that's based somewhat on one that I developed for my own team a while back. That one has a lot of kludges because it grew beyond its original scope and also because I learned a great deal as I developed it...I started from zero knowledge of Access or VBA and it shows. I plan to make this database available to the general tech pubs community at an upcoming conference because I believe there is a need for a simple tool for groups who can't shell out big bucks for a commercially available system. So, because I don't want everyone talking behind my back about what a hack I am, I want to do it correctly! I will keep working with the code you sent...I believe I can solve my form focus problems separately. And sorry for partially misleading you with some of my examples and explanations...I didn't want to overwhelm with detail but perhaps I erred on the side of caution there. Thank you so much again!
Sep 6 '08 #19

NeoPa
Expert Mod 15k+
P: 31,494
Dealing with someone who even considers those sort of issues is a rare pleasure, so don't worry in the least if your attempts to get it right ever backfired slightly. The thought counts for a lot :)

I suspect that some of the properties that are set in the design of the form [frmTopics] are what is causing some of the anomalies. Have a play with these to find what's doing what.

After that you can look at some code in the Form_Open() event procedure (of [frmTopics]). If you use the OpenArgs parameter in the .OpenForm call, you can determine which of the form's properties you would like to set differently (from your designed defaults). Remember though, that you should set some variable to the value of Me.OpenArgs as the first line of the code in Form_Open() as otherwise it disappears.

I hope this helps some more, but I'll be around if you have further problems :)
Sep 6 '08 #20

Post your reply

Sign in to post your reply or Sign up for a free account.