access on double click go to record on a form
I have a form that displays data from an access 2003 table - nothing out of the ordinary.
I then have a sub form on this form which shows records from another table that relate to the record currently been displayed on the main form. Its default view is datasheet, so it appears as a table.
What I want to do is double-click on a row in the sub form, open a form and go to the record I just double-clicked on.
Is this possible?
This is a previous question which has been answered. It uses docmd.openform and the where to go to the specific record.
I am doing something similar, but I would like to go to the record on the parent form of the subform.
in summary:
I want to double-click on the record in the subform, and go to that record on the parent form.
I then have a sub form on this form which shows records from another table that relate to the record currently been displayed on the main form. Its default view is datasheet, so it appears as a table.
What I want to do is double-click on a row in the sub form, open a form and go to the record I just double-clicked on.
Is this possible?
This is a previous question which has been answered. It uses docmd.openform and the where to go to the specific record.
I am doing something similar, but I would like to go to the record on the parent form of the subform.
in summary:
I want to double-click on the record in the subform, and go to that record on the parent form.
Add the following VBA to the double click event of the control that you want to trigger this functionality:
Docmd.OpenForm "YourNewFormName",,,"IDFie
You need to substitute the actual form name.
I'm assuming you have a numeric ID that can be used to identify the current record (and the record to bring up when you open the new form).
Docmd.OpenForm "YourNewFormName",,,"IDFie
You need to substitute the actual form name.
I'm assuming you have a numeric ID that can be used to identify the current record (and the record to bring up when you open the new form).
ASKER
It is not numeric... each record has a unique identifier, but it is a text name such as
ATS-O2C01-01
Will this open a new instance of the same form? or will it keep the parent open?
ATS-O2C01-01
Will this open a new instance of the same form? or will it keep the parent open?
Btw - if you need this functionality for the entire row, you would have to add that code to the double click event of each control.
If that is the case, I would suggest putting the code into a public function and calling that function from each of the controls. That allows you to easily modify/add to the code (in just one place) if needed.
Another thought -- I have similar setups in many of my own databases, where a double-click event from a list of records brings up a form showing the selected record.
Unless you specifically need to allow the users to edit that list of records using a listbox to list the records is a much cleaner solution than using a datasheet or continuous subform.
If that is the case, I would suggest putting the code into a public function and calling that function from each of the controls. That allows you to easily modify/add to the code (in just one place) if needed.
Another thought -- I have similar setups in many of my own databases, where a double-click event from a list of records brings up a form showing the selected record.
Unless you specifically need to allow the users to edit that list of records using a listbox to list the records is a much cleaner solution than using a datasheet or continuous subform.
<Will this open a new instance of the same form? or will it keep the parent open?>
Sorry - I missed that you are actually trying to go to a record in the parent form not a seperate form.
What I posted initially will indeed open a seperate form.
To go to a record in the parent form, you can do something like this:
Me.Parent.Filter = """ '<--- Clear any existing filters
Me.Parent.Filter = "YourFieldName = '" & Me.IDField & "'" '<--- Embedded single quotes are used to delimit text
Sorry - I missed that you are actually trying to go to a record in the parent form not a seperate form.
What I posted initially will indeed open a seperate form.
To go to a record in the parent form, you can do something like this:
Me.Parent.Filter = """ '<--- Clear any existing filters
Me.Parent.Filter = "YourFieldName = '" & Me.IDField & "'" '<--- Embedded single quotes are used to delimit text
ASKER
I do need the user to be able to edit the records there. And I am also displaying a lot of associated information in the datasheet which would be hard to display using a list box.
I am receiving an error right now with the current code.
DoCmd.OpenForm "frmCustomers", , , "IDTag=" & Me.IDTag & Chr(34)
I added the chr(34) because I believe that is the conversion to text.
however this is not working.
I am receiving an error right now with the current code.
DoCmd.OpenForm "frmCustomers", , , "IDTag=" & Me.IDTag & Chr(34)
I added the chr(34) because I believe that is the conversion to text.
however this is not working.
ASKER
I also tried that me.parent.filter, and that is not a valid command. When I type me.parent. It doesn't give me the option for filter.
<however this is not working.>
You've absolutely got the right approach :-)
You just need to full enclose your IDTag in quotes (left and right):
DoCmd.OpenForm "frmCustomers", , , "IDTag=" & Chr(34) & Me.IDTag & Chr(34)
(However, I think my last post is more along the lines of what you are looking for. Sorry for the confusion. I had misread the question.)
You've absolutely got the right approach :-)
You just need to full enclose your IDTag in quotes (left and right):
DoCmd.OpenForm "frmCustomers", , , "IDTag=" & Chr(34) & Me.IDTag & Chr(34)
(However, I think my last post is more along the lines of what you are looking for. Sorry for the confusion. I had misread the question.)
< It doesn't give me the option for filter.>
Is the parent form bound or unbound?
Is the parent form bound or unbound?
ASKER
I'm not sure how to tell that or define that.
I'm still a rookie I guess.
I'm still a rookie I guess.
<I'm still a rookie I guess.>
We all are in most respects.
Anyhow, a bound form is one that is tied to an underlying table or query through it's recordsource property. This allows you to easily select fields for the control sources of its controls.
An unbound form is not bound to a particular table or query (a menu, for example). They can be populated with data from a table or query using code instead.
That, however is a side issue.
I just checked the Me.Parent.Filter thing in one of my databases, and you do indeed lose the intellisense that autocompletes the various properties when you refer to "parents". I believe this is because a parent is not necessarily a form (as is the case when the child object is a subform), but can be any object. For example, the parent of an associated label is the textbox or whatever control it is tied to.
To make a long story short - despite the fact that the intellisense to autocomplete the Filter property is lost, it will still work syntactically (I tested this in A2K3, and it should apply in A2K7).
Give this a whirl:
Me.Parent.Filter = """ '<--- Clear any existing filters
Me.Parent.Filter = "YourFieldName = '" & Me.IDField & "'" '<--- Embedded single quotes are used to delimit text
We all are in most respects.
Anyhow, a bound form is one that is tied to an underlying table or query through it's recordsource property. This allows you to easily select fields for the control sources of its controls.
An unbound form is not bound to a particular table or query (a menu, for example). They can be populated with data from a table or query using code instead.
That, however is a side issue.
I just checked the Me.Parent.Filter thing in one of my databases, and you do indeed lose the intellisense that autocompletes the various properties when you refer to "parents". I believe this is because a parent is not necessarily a form (as is the case when the child object is a subform), but can be any object. For example, the parent of an associated label is the textbox or whatever control it is tied to.
To make a long story short - despite the fact that the intellisense to autocomplete the Filter property is lost, it will still work syntactically (I tested this in A2K3, and it should apply in A2K7).
Give this a whirl:
Me.Parent.Filter = """ '<--- Clear any existing filters
Me.Parent.Filter = "YourFieldName = '" & Me.IDField & "'" '<--- Embedded single quotes are used to delimit text
ASKER
It is a bound form to a form in the record source.
It is getting closer... So now whenever I double click it shows up at the bottom as unfiltered. Then once I click on that it goes to filtered.
How do you make it filter it automatically. Do I need to turn the filter on?
It is getting closer... So now whenever I double click it shows up at the bottom as unfiltered. Then once I click on that it goes to filtered.
How do you make it filter it automatically. Do I need to turn the filter on?
Hmm - I thought that would happen automatically when you set a filter's criteria through code.
<Then once I click on that >
What are you clicking on?
You can explicitly turn the filter on in code:
Me.Parent.FilterOn = TRUE
Give that a try by placing that line immediately after the code in my last post...
<Then once I click on that >
What are you clicking on?
You can explicitly turn the filter on in code:
Me.Parent.FilterOn = TRUE
Give that a try by placing that line immediately after the code in my last post...
ASKER
That worked great the first time... but it only works once.
Basically I would like to be able to navigate this way. I have a database of equipment that are all connected. So basically if I am viewing record A, it is connected to record B and record C, so I want to be able to click on either on those and go to record B or C, and then once I go to record B (or C, I'm using B) then that will show me that B is connected to P and Q, Then I would like to be able to click on P or Q. It worked going from A to B, but then once I tried to click on P it freaked out and just went back to the first record and wouldn't let me do anything else
Basically I would like to be able to navigate this way. I have a database of equipment that are all connected. So basically if I am viewing record A, it is connected to record B and record C, so I want to be able to click on either on those and go to record B or C, and then once I go to record B (or C, I'm using B) then that will show me that B is connected to P and Q, Then I would like to be able to click on P or Q. It worked going from A to B, but then once I tried to click on P it freaked out and just went back to the first record and wouldn't let me do anything else
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much! You deserve 1000 points if I could give them.
PS... I saw on your profile you are a VT Grad. As you might have guessed from my name, I am as well. I just graduated in May, and I am using databases to really help my company grow. I didn't even know what Access was 1 year ago, now I've got a pretty involved database I think.. Probably not like anything you could do, but it is cool to me.
Thanks for the help...
GO HOKIES!!!
PS... I saw on your profile you are a VT Grad. As you might have guessed from my name, I am as well. I just graduated in May, and I am using databases to really help my company grow. I didn't even know what Access was 1 year ago, now I've got a pretty involved database I think.. Probably not like anything you could do, but it is cool to me.
Thanks for the help...
GO HOKIES!!!
Hey - glad to help out!
I had a hunch you were a Hokie (they are simply above the rest of the crowd ;) )
<I didn't even know what Access was 1 year ago>
You are doing great for a rookie (and new EE Member as well). You would be amazed at how many people just want solutions, but are not interested in the actual mechanics of them.
<Probably not like anything you could do>
I'm still one of the 'junior' experts here by comparison (I've only been at this Access stuff for 5 years). Whenever you're comfortable with it, take a stab at answering some questions. It's a lot of fun, a great way to learn and reinforce your knowledge, and you can maintain a free membership like that.
It is always refreshing to see people taking an active interest in this stuff.
Anyhow - feel free to drop me an email at anytime (address in my profile).
I had a hunch you were a Hokie (they are simply above the rest of the crowd ;) )
<I didn't even know what Access was 1 year ago>
You are doing great for a rookie (and new EE Member as well). You would be amazed at how many people just want solutions, but are not interested in the actual mechanics of them.
<Probably not like anything you could do>
I'm still one of the 'junior' experts here by comparison (I've only been at this Access stuff for 5 years). Whenever you're comfortable with it, take a stab at answering some questions. It's a lot of fun, a great way to learn and reinforce your knowledge, and you can maintain a free membership like that.
It is always refreshing to see people taking an active interest in this stuff.
Anyhow - feel free to drop me an email at anytime (address in my profile).
ASKER