 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / November 2006moving a main form and subform to a specific subform record
hello Help i have a main form with a subform that contains data that i want to search and goto a specific record. i created a combo find box through the wizard on a form based on the table that creates my subform and then tried copying and pasting to the form i really want it on, but the combo box does nothing. i have a site table that has a one to many relationship with the products table. SiteTable SITEID SITEADDRESS ProductsTable SITEID PRODUCTID SERIALNUMBER the site ID is on the main form and sub form. the mainform is where i need to place the combo box to find the serial number of the product and have the mainform and subform move to that product record. how do i do this? i tried this code: Private Sub Combo46_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.subfrmtblProductList.Form.Recordset.Clone rs.FindFirst "[ProductID] = " & Str(Nz(Me![Combo46], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub the subfrmtblProductList is the name of the subform, but the idea i had doesnt work. do i need to put references to the subform anywhere else? or is there another approach i need to take when creating this combo box? thanks in advance. i have also tried to create a unbound combo and manual create the find function. my row source is: SELECT tblProductList.ProductID, tblProductList.SerialNumber FROM tblSiteInformation INNER JOIN tblProductList ON tblSiteInformation.SiteID=tblProductList.SiteID; this is connected to a combo box called : combo101 the afterupdate event has this code in it: Private Sub Combo101_AfterUpdate() Me.subfrmtblProductList.SetFocus Me.subfrmtblProductList.Form.ProductID.SetFocus DoCmd.ShowAllRecords DoCmd.FindRecord Combo101, , , , , acAll, FindFirst End Sub but ofcourse this isnt working entirely either. it works only for the first record at each site, but if there is a site that has five machines i cant get it to find any machine after the first one. why? > hello > [quoted text clipped - 37 lines] > > thanks in advance. Hello i have one more thing to add. i have tried this row source as well: SELECT tblProductList.ProductID, tblProductList.SerialNumber, tblProductList.SiteID FROM tblProductList ORDER BY tblProductList.SerialNumber; the code behind the combo box is Private Sub Combo101_AfterUpdate() Me.subfrmtblProductList.SetFocus Me.subfrmtblProductList.Form.ProductID.SetFocus DoCmd.ShowAllRecords DoCmd.FindRecord Combo101, , , , , acAll, FindFirst Me.Refresh End Sub i am not sure abouth the second DoCmd line. it wont let me leave out the find first and any other value there, like true or false, causes the combo box to not work at all. i have thought about trying a gotorecord command but that fails as well. if any one has thoughts on this situation please, please, help. > i have also tried to create a unbound combo and manual create the find > function. [quoted text clipped - 63 lines] > > > > thanks in advance. >i have a main form with a subform that contains data that i want to search >and goto a specific record. i created a combo find box through the wizard on [quoted text clipped - 31 lines] >doesnt work. do i need to put references to the subform anywhere else? or is >there another approach i need to take when creating this combo box? "Doesn't work" is not much of a clue about what's happening. I think you are confusing form and subform bookmarks and mixing DAO and ADO methods. If you're using DAO, try this: Private Sub Combo46_AfterUpdate() ' Find the record that matches the control. Dim rs As DAO.Recordset Set rs = Me.subfrmtblProductList.Form.RecordsetClone rs.FindFirst "[ProductID] = " & Str(Nz(Me![Combo46], 0)) If Not rs.NoMatch Then Me.subfrmtblProductList.Form.Bookmark = rs.Bookmark End If End Sub Not 100% sure, but I think the ADO equivalent might be: Private Sub Combo46_AfterUpdate() ' Find the record that matches the control. Dim rs As ADODB.Recordset Set rs = Me.subfrmtblProductList.Form.Recordset.Clone rs.Find "[ProductID] = " & Str(Nz(Me![Combo46], 0)) If Not rs.EOF Then Me.subfrmtblProductList.Form.Bookmark = rs.Bookmark End If End Sub  SignatureMarsh MVP [MS Access] Hello actually depending on the code i use i can get it to work for the first record, in some of the instances. here is the situation: 2 tables SITE SiteID Address, Address2... etc. PRODUCT SiteID ProductID ProductTypeID these 2 tables are joined with a one site to many products relationship. there is a form based on the SITE table. this is the main form. on a tab control i have a subform that is based on the PRODUCT table. everything works great for entering data and storing data relating to both tables. the problem comes when i create a "find a record" combo box. i want the ability to find a record for the site records, and for the product records. because the main form is the thing that users will see first i want the combo boxes that i create to "find record" on the main form. the problem is that the combo box for the products is not working. i have tried 2 different approaches but both are not working fully. the approach that i used for the combo box to find the products works if i use the DoCmd.Findrecord approach, but only for the "first record" on a site. i have also tried to use the combo box wizards default code with modifications such as the code below. Private Sub Combo103_AfterUpdate() Me.subfrmtblProductList.SetFocus Dim rs As DAO.Recordset Set rs = Recordset.Clone rs.FindFirst Me!subfrmtblProductList.Form.[ProductID] = Str(Nz(Me![Combo103], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark Me.Refresh End Sub i also have been thinking about the relationships and data table structures i have set up in this database might be a problem, but between the site table and the product table the relationships are pretty normal. it is the tables that are related to the product table that are weird. if you like i can post information about those, but i would need to put it into pictures in order to give a good description. any suggestions would be much appreciated. > >i have a main form with a subform that contains data that i want to search > >and goto a specific record. i created a combo find box through the wizard on [quoted text clipped - 60 lines] > End If > End Sub >actually depending on the code i use i can get it to work for the first >record, in some of the instances. [quoted text clipped - 40 lines] > Me.Refresh >End Sub It sounds like the wizard is beyond its usefulness and is now getting in the way. I suggest that you stop using it on this form and make the changes directly on the form and in its code module. I am assuming that Combo103 is the product selection combo box and the BoundColumn property corresponds to the ProductID field in the combo box's RowSource table/query. Also assuming that the ProductID field is a numeric type (or AutoNumber), the code would be more like this than what you posted: Private Sub Combo103_AfterUpdate() Dim rs As DAO.Recordset Set rs = Me!subfrmtblProductList.Form.RecordsetClone rs.FindFirst "ProductID = " & Nz(Me!Combo103, 0) If Not rs.NoMatch Then Me!subfrmtblProductList.Form.Bookmark = rs.Bookmark End If End Sub Note that there is no dot in RecordsetClone. SignatureMarsh MVP [MS Access] Hello Marshall thanks for the code. it still isnt working. i would like to post a picture that might help show the way the forms might be getting in the way, and one of the relationships that shows the way the tables are structured. but in the past someone has complained that i put them up on imageshack and was helping advertise by doing so. i wonder if it would bother you if i put pictures on image shack? the form i am trying to so this on is not a simple main form with a subform. there are many subforms on tab controls and tab controls in subforms. i must say that this is the most complicated database i have ever attempted and there are sometimes where i become most uncertain of what i am doing. i have recieved an incredible amount of help from people on this site and really enjoy learning by doing. having said all that i want to really thank you for your help. i put the code on the combo box, there was only one line i added to set the focus to the proper tab and subform and i renamed the combo box. here is the entire code: Private Sub cbFindSerial_AfterUpdate() Me.subfrmtblProductList.SetFocus Dim rs As DAO.Recordset Set rs = Me!subfrmtblProductList.Form.RecordsetClone rs.FindFirst "ProductID = " & Nz(Me!cbFindSerial, 0) If Not rs.NoMatch Then Me!subfrmtblProductList.Form.Bookmark = rs.Bookmark End If End Sub the weird thing is that it doesnt complain about anything. it just doesnt move all the forms to the proper record. one thing that i thought about last night is that on the tblProductList table i have as the primary key 2 fields, ProductID and ProductTypeID. the ProductID is an autonumber and the ProductTypeID is a text field that can have one of five values; Compressor, Dispenser, Storage, Fill Post, Decanting Post. the SQL for the row source looked like this: SELECT tblProductList.ProductID, tblProductList.SerialNumber, FROM tblProductList; now it looks like this: SELECT tblProductList.ProductID, tblProductList.SerialNumber, tblProductList.ProductTypeID FROM tblProductList; the only other thing is the way that i have created the forms and subforms and the parent and child fields. this would be better described with pictures, but i have no way of posting them at the moment. i am going to work on it, but i wont know for a while where i am putting them. thanks again. > >actually depending on the code i use i can get it to work for the first > >record, in some of the instances. [quoted text clipped - 64 lines] > > Note that there is no dot in RecordsetClone. >thanks for the code. it still isnt working. i would like to post a picture >that might help show the way the forms might be getting in the way, and one [quoted text clipped - 52 lines] >pictures, but i have no way of posting them at the moment. i am going to work >on it, but i wont know for a while where i am putting them. First, tab controls have no effect on any of this stuff, so don't let that part of things confuse your thinking. Next issue, you say the primary key in the tblProductList table is a compound key. This is extremely unusual since an autonumber field that is not unique is pretty near useless but, on the other hand, a unique autonumber field is sufficient as a primary key and using a second field to the primary key just confuses everything. I am confused about the difference between the ProductID and SerialNumber. Your new name for the combo box indicates that you are searching for a serial number, while the code is searching for a ProductID. Are these two numbers equivalent? I mean, why have a ProductID if the SerialNumber is unique? If the SerialNumber is only unique within a product type, it would make sense for the SerialNumber and ProductTypeID fields to make up a compound primary key. I don't see how setting the focus is supposed to do anything related to your question. If you are trying to use that to select a specific tab, then I'm not sure that will work. Normally(?) you select a tab by setting the Value of the table control to the page number you want to select. Did you double check that the combo box's BoundColumn property corresponds to the ProductID field? Finally, you said "doesnt move all the forms to the proper record". But I have no idea what "all the forms" you are talking about. The code we've been working on is only supposed to locate a record in the form being displayed in the subfrmtblProductList subform control. If that's not what you want, please explain. SignatureMarsh MVP [MS Access] Hello i found a way to get you pictures of the relationships and the form itself without advertising. here is a link to my personal site which has the necessary information: http://www3.telus.net/actonn/ its is a quick and dirty webpage, so please forgive the ugliness. :) > >thanks for the code. it still isnt working. i would like to post a picture > >that might help show the way the forms might be getting in the way, and one [quoted text clipped - 88 lines] > the subfrmtblProductList subform control. If that's not > what you want, please explain. That is some relationships diagram! I don't see how that can have an effect on this problem except that Access will fill in the subform control's Link Master/Child properties based on the relationship. You should verify that these properties are set the way you want (both set to SiteID??) From what I can tell by looking at the picture, the site form and equipment subform appear to be ok. Double check the Link Master/Child properties of the compressor subform control too. You said something before about it works for the first serial number, but only shows one record. I don't understand what that means. Since serial numbers are unique, shouldn't there be only one???? SignatureMarsh MVP [MS Access] >i found a way to get you pictures of the relationships and the form itself >without advertising. here is a link to my personal site which has the [quoted text clipped - 96 lines] >> the subfrmtblProductList subform control. If that's not >> what you want, please explain. Hello one thing to note. the serial number is "unique" but it is a text field and a field that i dont really like the idea of using for a Primary Key. i could use it. but i just feel that it would be better if the Primary Key data was clean numbers, and something that access took care of for me. i really dont like user input as my PKs. the setting of focus moves the focus to a tab containing the subform. this is helpful for one reason for sure, it means that when you search for the serial number you will end up seeing it as soon as the combo box works and moves you to that record. it was unexpected when i first did that code, that it totaly by passed the tab control and just automatically selected the right tab and then moved to the subform. i was thinking i would have to set focus on the tab control, then the tab and then the subform, but the code there automattically does all three. if i choose ProductID, SerialNumber and ProductTypeID as the source, then the bound column should be 1? one thing i really hate about access is some places, like the tab stop, the numbers start at 0, others it seems to be 1. i am assuming that 1 is the number i need, the only visible columns in the current configuration is the SerialNumber, and the ProductTypeID. thats what i want, but the thing is that there is a mainform that needs to "move" to the correct Site and then the subform needs to "move" to the correct Product. i am just wondering if that should happen with this code, regardless of the fact that there is a "filtering" of the records by Site created by the forms relationship. i think once you see the picture of the form you might have a better understanding. > >thanks for the code. it still isnt working. i would like to post a picture > >that might help show the way the forms might be getting in the way, and one [quoted text clipped - 88 lines] > the subfrmtblProductList subform control. If that's not > what you want, please explain. Hello again another thought was to create a little test db and see if that would allow the combo box to work on the main form to move the subform. here is the little test database link: http://www3.telus.net/actonn/db2.rar the combo boxes on the customer form dont do what i want them to. even with the code you posted, even when i place them on the subform that they should find the records on. why? > >thanks for the code. it still isnt working. i would like to post a picture > >that might help show the way the forms might be getting in the way, and one [quoted text clipped - 88 lines] > the subfrmtblProductList subform control. If that's not > what you want, please explain. >another thought was to create a little test db and see if that would allow >the combo box to work on the main form to move the subform. [quoted text clipped - 8 lines] > >why? Why? You want to know why the combo boxes "don't do what i want"? Probably because what you want is something other than what I think you want ;-) A small sample database is a good idea. Unfortunately, I get a page not found error at the above address. SignatureMarsh MVP [MS Access] Hello yeah i seem to have trouble figuring the links to files on my personal website. try it now and it should work. i have made a set of forms with combo boxes with a description of what they should do. the most important combo boxes are on the main form. i have a couple of questions, why is it that some code i have seen has "Me!Site.Form.Recordsetclone" and other code has "Me.subfrmtblProductList.form.setfocus" what is the difference between ! and . ? is the version of Access? by the way just so you know, i am using 2003. another question. do you think, in your personal opinion, that what i am attempting can be done? i am just curious. would there be better products to create this with? should we move up to sql server? we already use that for our manufacturing software, but i dont know how to use sql server, yet. if there is any other way i can help you help me, let me know. > >another thought was to create a little test db and see if that would allow > >the combo box to work on the main form to move the subform. [quoted text clipped - 15 lines] > A small sample database is a good idea. Unfortunately, I > get a page not found error at the above address. >yeah i seem to have trouble figuring the links to files on my personal >website. [quoted text clipped - 16 lines] > >if there is any other way i can help you help me, let me know. Well, I can get the file now, but what am I supposed to do with an "rar" file type? Can't you just Zip the mdb file? The . or ! after a form object (Me, Forms!formname subformcontrol.Form, etc) is your call. Throughout Access, ! is used to delineate a collection and an element of the collection. Me!controlname is just a convenient short version of Me.Controls!controlname. OTOH, dot is used to delineate an object and its properties/methods. Access auotmatically makes all of the controls on a form properties of the form, so either . or ! can be used to refer to form controls. I don't know enough about what you're trying to do to say it can't be done. Most likely it can, the question is more a matter if how difficult it will be to create and maintain over a significant period of time. "Moving up" to SQL Server will only change the database engine (where the data tables are stored). You still need a development system that provides the tools you need to create your application. Access is without a doubt the easiest to learn and use. SQL Server does not come with a development system sou you would have to use something else suvh as .Net. SignatureMarsh MVP [MS Access] Hello thanks for all your help and responses. sorry about the rar / zip thing. i have been using rar for ages. i used to zip, but i found winrar to be faster and better compression. http://www3.telus.net/actonn/db2.zip there is a zip for you. i would still like to know if this can be done, but at this point i have reevaluated the way i was putting together the forms and subforms. i am currently work from a different angle and think i will be able to do what i need to make the products more easily accessible. thanks for any insights or thoughts you can provide. > >yeah i seem to have trouble figuring the links to files on my personal > >website. [quoted text clipped - 43 lines] > development system sou you would have to use something else > suvh as .Net. Hello again darn, i forgot to mention. i have no idea what ADO or DAO are, or what i am doing in terms of that. the forms are built by using a wizard on the table, then manipulating the fields to be where i wanted. there are a lot of forms and subforms and data is "managed" in tabs. i am trying to make the one form the central area for managing all the data that we are trying to gather, but i have also wondered if this might be a problem with the way things are working. again if you need any more information or have any suggestions, let me know. thanks for any and all help. > >i have a main form with a subform that contains data that i want to search > >and goto a specific record. i created a combo find box through the wizard on [quoted text clipped - 60 lines] > End If > End Sub >darn, i forgot to mention. i have no idea what ADO or DAO are, or what i am >doing in terms of that. [quoted text clipped - 4 lines] >managing all the data that we are trying to gather, but i have also wondered >if this might be a problem with the way things are working. If this a simple database using the default Jet database engine (not SQL Server, Oracle, etc), then you should be using the DAO library. Verify that by using the VBA window's Tools - References menu. SignatureMarsh MVP [MS Access] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.