INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Open form based on sub-form selection on doubleclick

Open form based on sub-form selection on doubleclick

(OP)
Hi All;

This should be really simple, but my research hasn't yielded good results.

What I would like to do is have a form with a sub-form in it. The sub-form is in table view for easy viewing. When you double click on a record in the sub-form, it opens to a new form with all of the record information.

Access 2010
Local drive currently but will be moved to a network drive or SharePoint in the future

What is working:
  • Search (form) - when a user enters the search parameters, the sub-form updates to the records that meet the search criteria
  • Log Spec Query Subform (Sub-form) - Shows the results of the search in table view. This is located as a sub form to the search form
Other info:
  • Test1 (form) - will show the resulting record of the double click record in Log Spec Query Subform
  • The records will have about 75 different values. I plan on having tabs to divide the different pieces of information within Test1.
What is the best way to do this without having an excessive amount of coding?

Thanks

Mike

RE: Open form based on sub-form selection on doubleclick

https://msdn.microsoft.com/en-us/library/office/ff...

docmd.openform "Test1",,,"SomePrimaryKey = " & Me.SomePrimarykey
if the key is text
docmd.openform "Test1",,,"SomePrimaryKey = '" & Me.SomePrimarykey & "'"

RE: Open form based on sub-form selection on doubleclick

(OP)
Hi MajP,

That is what I found before, just not in as much detail. I think I am doing something wrong. What is SomePrimaryKey supposed to be?

The value that is to be double clicked is "Project Name"

The key is a number

CODE

Private Sub Project_Name_DblClick(Cancel As Integer)

docmd.openform "Test1",,,"SomePrimaryKey = " & Me.SomePrimarykey

End Sub 

Thanks,

Mike


RE: Open form based on sub-form selection on doubleclick

(OP)
update:

So I tried using the macro builder.

Open form

Form Name = Test1
View = Form
Where Condition = [Forms]![Log Spec Query subform]![Project Name]
Data Mode = Read Only

This will open the Test1 form properly, but the field Project Name does not show the correct information. How can I set the fields to equal each other?

Both fields are named Project Name.

RE: Open form based on sub-form selection on doubleclick

CODE

Private Sub Project_Name_DblClick(Cancel As Integer)
   docmd.openform "Test1",,,"[Project Name] = '" & Me.[Project Name] & "'"
End Sub 

This says open form Test1 where the Project Name = whatever the name of the Project is in the active record of the form where you click.

RE: Open form based on sub-form selection on doubleclick

(OP)
Hi MajP,

Still isn't activating the VB when I double click. What could cause that?

Thank you for the continued assistance.

Mike

RE: Open form based on sub-form selection on doubleclick

If you do not get an error and the code is not "activating" then the code is likely not launching. You would either get an error or the form would open but not to the correct record.

To test if the code is actually being called you can put a msgbox in the code

CODE -->

Private Sub Project_Name_DblClick(Cancel As Integer)
  msgbox "code being called" 
  docmd.openform "Test1",,,"[Project Name] = '" & Me.[Project Name] & "'"
End Sub 

If that message box does not come up make sure in the double click event property you see the word
[event procedure]

If you see the message box, but not the form change the code to

CODE -->

docmd.openform "Test1",,,"[Project Name] = '" & Me.[Project Name] & "'", ACDIALOG 

If the form opens but to the wrong record reply back.

RE: Open form based on sub-form selection on doubleclick

(OP)
There it goes. Thanks. I re-built a lot of the forms and I might have messed something up.

Now that this is working, if I want to return multiple values to the test1 form, how can I go about changing the code to do that? I have like 75 fields to possibly transfer to the forms (test1, test2, etc.).

Thanks!

Mike

RE: Open form based on sub-form selection on doubleclick

Not sure what you are asking. You are clicking on a Project and it opens to the details for that project. The detail form should contain any fields you want for that project. Do you mean you would like to open to 75 similar records?

RE: Open form based on sub-form selection on doubleclick

(OP)
So the goal is to have 1 record opened with 75 fields (max). If I double click Project Name field in the sub form it will open the record in the Test1 form.

If I want to allow the the user to double click anywhere in the record, will I need to program something like this?

CODE

Private Sub Project_Name_DblClick(Cancel As Integer)

docmd.openform "Test1",,,"[Project Name] = '" & Me.[Project Name] & "'"

docmd.openform "Test1",,,"[Field1] = '" & Me.[Field1] & "'"

docmd.openform "Test1",,,"[Field2] = '" & Me.[Field2] & "'"

docmd.openform "Test1",,,"[Field3] = '" & Me.[Field3] & "'"

End Sub 

Can I use a With statement? If so, how would the code look like?

Thanks,

Mike

RE: Open form based on sub-form selection on doubleclick

I doubt it, unless I do not understand you table design. Don't you still want to go to the details of that Project record whatever field you click on?

If that is the case you need this code

CODE -->

Public function GotToDetails()
   docmd.openform "Test1",,,"[Project Name] = '" & Me.[Project Name] & "'"
End function 

Then in the design view you select all the fields at once so that you can update the event procedure for all of them.

In the on doubleclick event property you will put in "= GoToDetails()" (no parenthesis)
Now whatever field you click on will open the details to that project.

RE: Open form based on sub-form selection on doubleclick

(OP)
It didn't work. It gave me a function error...

I also tried to make it a sub, but the first VB can't call the sub...

Any ideas?

Mike

RE: Open form based on sub-form selection on doubleclick

Using this technique it has to be a function. Not sure why. Does your on doubleclick event property look like
= GoToDetails()

The other way which will be a pain is to create event procedures for each control and have the event procedure call the function. They would all look the same

CODE -->

Private Sub SomeControl_DblClick(Cancel As Integer)
  GoToDetails
End Sub 
Private Sub SomeOtherControl_DblClick(Cancel As Integer)
  GoToDetails
End Sub
... 56 times 

However, the first technique will work and can all be done in one swoop.
When something does not work or you get an error please provide the error code information or describe in detail what you are seeing.

RE: Open form based on sub-form selection on doubleclick

(OP)

CODE

Public Function Details()

   DoCmd.OpenForm "Log Spec Details", , , "[Log Spec Number] = '" & Me.[Log Spec Number] & "'"

End Function

Private Sub Log_Spec_Number_DblClick(Cancel As Integer)

Call Details

End Sub 

So that works, however it is not passing the log spec number value to the "Log Spec Details" form (previously test1). The only value it can pass is Project Name...

What might I be forgetting?

RE: Open form based on sub-form selection on doubleclick

Again I do not understand. What is in the subform? I thought the subform has records for Properties and each property has 75 fields describing it. You click on a record regardless of which field you click in. You open the detail form to that record. Can you take a screen shot of your form?

If sounds as if a field has a detail form that goes with it. Makes no sense. I do not understand that. But if that is the case then each would need a unique event so you can describe the report to open

However if a field is numeric you do not include single quotes
"[Log Spec Number] = " & Me.[Log Spec Number]
If the field is text you do
"[Project Name] = '" & Me.[Project Name] & "'"

That is because in sql

where [Project Name] = 'Project ABCD'
where [log Spec Num] = 1234

RE: Open form based on sub-form selection on doubleclick

(OP)

CODE

Private Sub Log_Spec_Number_DblClick(Cancel As Integer)

DoCmd.OpenForm "Log Spec Details", , , "[Log Spec Number] = " & Me.[Log Spec Number]

End Sub 

It is still asking me to provide a Log Spec Number parameter... Log Spec Number is a number....

RE: Open form based on sub-form selection on doubleclick

likely it is a spelling mistake ensure you name is exactly [Log Spec Number] not [Log Spec Num] or some other version. Also ensure the report log spec details has log spec number exactly the same way. Do yourself a huge favor and get rid of any spaces in a name.

RE: Open form based on sub-form selection on doubleclick

(OP)
HI,

I checked the spelling and there is no current error. I can now pass any field within the search form, but I can't pull the other fields that are not in the form, but are in record in the data table.

How can I resolve this one?

Thanks,

Mike

RE: Open form based on sub-form selection on doubleclick

Quote:

I can now pass any field within the search form, but I can't pull the other fields that are not in the form, but are in record in the data table.
I got zero idea what you are saying.

RE: Open form based on sub-form selection on doubleclick

"The records will have about 75 different values" and "other fields that are not in the form, but are in record in the data table."

Are you saying your [Log Spec Details] Form displays just a small portion of those "75 different values" (fields?) from your table, but you want to query on all 75 fields, even if they are not a part of your [Log Spec Details] Form?

If so, and "I can now pass any field within the search form" - how would you know (programmatically) which field to pass to DoCmd.OpenForm and which value corresponding to this field to pass?

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close