Open form based on sub-form selection on doubleclick

[remeng]

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:
[ul]

[li]Search (form) - when a user enters the search parameters, the sub-form updates to the records that meet the search criteria[/li]

[li]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[/li]
[/ul]

Other info:
[ul]
[li]Test1 (form) - will show the resulting record of the double click record in Log Spec Query Subform[/li]

[li]The records will have about 75 different values. I plan on having tabs to divide the different pieces of information within Test1.[/li]
[/ul]

What is the best way to do this without having an excessive amount of coding?

Thanks

Mike

 

[MajP]

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

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

 

[remeng]

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

Private Sub Project_Name_DblClick(Cancel As Integer)

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

End Sub

Thanks,

Mike

 

[remeng]

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.

 

[MajP]

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.

 

[remeng]

Hi MajP,

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

Thank you for the continued assistance.

Mike

 

[MajP]

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

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

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

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

 

[remeng]

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

 

[MajP]

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?

 

[remeng]

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?

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

 

[MajP]

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

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.

 

[remeng]

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

 

[MajP]

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

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.

 

[remeng]

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?

 

[MajP]

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

 

[remeng]

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....

 

[MajP]

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.

 

[remeng]

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

 

[MajP]

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.

 

[Andrzejek]

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 [tt]DoCmd.OpenForm[/tt] and which value corresponding to this field to pass?

Have fun.

---- Andy

There is a great need for a sarcasm font.