 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / March 2005Runtime Error 3021
I have created a form and subform with the wizard. After creating, I created a combobox in my subform with the wizard. This combo box lists all products available. My goal is to use the main form "Quote" to create quotes for variuos customers. The subform is where all products and the statistics for those products will be chosen. I have the subform in datasheet view. The issue is with the combobox. When I click on it, all products are listed, however when I choose one I get runtime error 3021. When I click Debug, the following line is in yellow: Me.Bookmark = rs.Bookmark 1. I have no idea what this line means. 2. I think I may need to edit my record source for some part of the form or subform, but I do not know where or how. 3. This is my first subform with a combo box. 4. I have never had any training in coding, I have gotten this far with books and this forum. Any suggestions/detailed information would be appreciated. Please give me step-by-step. Still pretty new with the lingo. I love reading all of the posts with solutions, they have been most helpful to me. I hope someone out there is able to help a newbie like me! Thanks in advance! Private Sub Combo26_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[UPC] = '" & Me![Combo26] & "'" Me.Bookmark = rs.Bookmark End Sub Change the code to this: Private Sub Combo26_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[UPC] = '" & Me![Combo26] & "'" If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark End Sub The error that you're getting is because the code is not finding a match in the recordset for the value you selected in Combo26. I'm guessing this is because the combo box is based on a lookup field in the table, and that what you think you're selecting is not what the value of the combo box is. What is the Row Source query for the Combo26 control?  SignatureKen Snell <MS ACCESS MVP> >I have created a form and subform with the wizard. After creating, I >created [quoted text clipped - 36 lines] > Me.Bookmark = rs.Bookmark > End Sub Ken, The row source query is: SELECT [Products Query].[UPC] FROM [Products Query]; Thank you! Sara > Change the code to this: > [quoted text clipped - 54 lines] > > Me.Bookmark = rs.Bookmark > > End Sub OK, then I was wrong about this being based on a lookup field. Did you try the new code I'd posted? What happens with it? Do you still get the error? SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 72 lines] >> > Me.Bookmark = rs.Bookmark >> > End Sub Ken, I no longer get the error. However, now when I select a product from my combo box, nothing happens. The rest of my text boxes do not populate with any information. Sara > OK, then I was wrong about this being based on a lookup field. > [quoted text clipped - 77 lines] > >> > Me.Bookmark = rs.Bookmark > >> > End Sub So this suggests that your form's recordset (recordsource query) does not contain a record that has a UPC value equal to the one you are selecting in the combo box. Is UPC a text field or a numeric field? If it's a text field, then your code is correct. If it's a numeric field, then change your code to this: Private Sub Combo26_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[UPC] = " & Me![Combo26] If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark End Sub SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 93 lines] >> >> > Me.Bookmark = rs.Bookmark >> >> > End Sub Ken, No, it is a text field. Could this be a Relationship problem? Thank you, Sara > So this suggests that your form's recordset (recordsource query) does not > contain a record that has a UPC value equal to the one you are selecting in [quoted text clipped - 109 lines] > >> >> > Me.Bookmark = rs.Bookmark > >> >> > End Sub Perhaps.... post the RecordSource of the form that contains the combo box. Let's see what the form's data are. SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 134 lines] >> >> >> > Me.Bookmark = rs.Bookmark >> >> >> > End Sub Ken, Record Source for the subform/Subreport is Quote Subform When I click of the upper left hand corner of the subform and click properties, I get a propert window called Form and that Record Source is "Ken Snell [MVP]" wrote: Products Query. Sara > Perhaps.... post the RecordSource of the form that contains the combo box. > Let's see what the form's data are. [quoted text clipped - 136 lines] > >> >> >> > Me.Bookmark = rs.Bookmark > >> >> >> > End Sub So this combo box is in a subform? And the subform's RecordSource is "Products Query"? OK -- now I need you to post the SQL statement of the Products Query (it's a stored query, so open the actual query in design view, then go to left end of toolbar and click the "view" icon and select SQL view. Copy the entire statement that you'll see there and paste it into your reply message. SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 162 lines] >> >> >> >> > Me.Bookmark = rs.Bookmark >> >> >> >> > End Sub Ken, SELECT Products.Items, Products.Unit, Products.[FOB FCD], Products.[FOB QD], Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)], Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty, Products.UpdateDate, Products.[Quote Number], Products.UPC, Products.UPC FROM Products; Sara > So this combo box is in a subform? And the subform's RecordSource is > "Products Query"? [quoted text clipped - 170 lines] > >> >> >> >> > Me.Bookmark = rs.Bookmark > >> >> >> >> > End Sub You have two Products.UPC fields in the query. One of them needs to be deleted from this query. SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 193 lines] >> >> >> >> >> > Me.Bookmark = rs.Bookmark >> >> >> >> >> > End Sub Ken, I have deleted the Products.UPC fields to no avail. Anything else I can try? Thanks, Sara > You have two Products.UPC fields in the query. One of them needs to be > deleted from this query. [quoted text clipped - 196 lines] > >> >> >> >> >> > Me.Bookmark = rs.Bookmark > >> >> >> >> >> > End Sub OK - let me recap the info that we have so far: (1) Combo26 is a combo box in a subform. (2) The Row Source of Combo26 is SELECT [Products Query].[UPC] FROM [Products Query]; (3) The subform's RecordSource is Products Query (4) The SQL statement for Products Query is SELECT Products.Items, Products.Unit, Products.[FOB FCD], Products.[FOB QD], Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)], Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty, Products.UpdateDate, Products.[Quote Number], Products.UPC, Products.UPC FROM Products; (5) UPC field is a text field. (6) The code that runs on the AfterUpdate event of Combo26 is Private Sub Combo26_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[UPC] = '" & Me![Combo26] & "'" If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark End Sub (7) When a UPC value is selected in the combo box, nothing happens. The rest of the text boxes on the subform do not populate with any information. Now, let's change the code in the AfterUpdate event to see if any record is found at all. For testing, change the code to this: Private Sub Combo26_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[UPC] = '" & Me![Combo26] & "'" If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark MsgBox "Record found for UPC """ & Me![Combo26] & """." Else MsgBox "No record found for UPC """ & Me![Combo26] & """." End If End Sub Try selecting a UPC from the combo box. Which message box do you see? Is the UPC value in the message box the one that you thought you selected? Also, post the following values for the Combo26 properties: Bound Column Control Source Column Count Column Widths SignatureKen Snell <MS ACCESS MVP> Ken, I verified all information and it is correct. I chanfed the event procedure. I chose product ACM-982185. The error message I received was "No record found for ACM-982185" Bound Column 1 Control Source None Column Count 1 Column Widths 1" Thanks again, Sara > OK - let me recap the info that we have so far: > [quoted text clipped - 55 lines] > Column Count > Column Widths Then what ACCESS is telling you is that there is no record in Products table with that UPC...which seems completely stupid because you're using Products table as the source of the selection. So what I'm "guessing" right now is that there is something else that we've not yet discussed. What I'm now thinking is that the subform is "linked" to the main form and that it's showing a filtered set of records based on the record in the main form. What are the LinkMasterFields and LinkChildFields values for the subform control (the control that holds the subform -- this control is on the main form)? If there are values there, then the subform's recordset is not all the records in Products but is a filtered set. But the combo box is showing all records from Products, and it's possible that this particular UPC value is not in any of the records that are in the subform's recordset. Post back with your findings and then we'll go from here. SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 75 lines] >> Column Count >> Column Widths Ken Child and Parent Fields are Quote Number. FYI-I removed the parent/child fields and now my subform shows all records. Are we getting somewhere? > Then what ACCESS is telling you is that there is no record in Products table > with that UPC...which seems completely stupid because you're using Products [quoted text clipped - 90 lines] > >> Column Count > >> Column Widths Most likely. Does the combo box work as desired now? What is the record source of the main form? SignatureKen Snell <MS ACCESS MVP> > Ken > [quoted text clipped - 112 lines] >> >> Column Count >> >> Column Widths Ken, The record Source for the main form is Quote. When I open the form, the subform shows all records from my products query. However, the UPC is blank. When I choose the UPC from the combo box, it places that UPC in every record. Sara > Most likely. Does the combo box work as desired now? > [quoted text clipped - 115 lines] > >> >> Column Count > >> >> Column Widths The combo box should be in the "form header" section of the subform, not in the Detail section. SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 135 lines] >> >> >> Column Count >> >> >> Column Widths If your subform is a continuous forms view, what do you want to use the combo box for? The way you're coding it, it's to be used as a "navigation" tool, moving the subform to the desired record. With a continuous forms view, it will still work that way, but by scrolling the subform to the desired record. SignatureKen Snell <MS ACCESS MVP> > The combo box should be in the "form header" section of the subform, not > in the Detail section. [quoted text clipped - 144 lines] >>> >> >> Column Count >>> >> >> Column Widths Ken, My goal is to create quotes for customers. Each quote would have a unique quote number. I have over 11,000 products to choose from. I would like to enter all the quote information (Customer, Date, Rep, Number, etc.) and from there choose the products I would like to quote. I am going to base the customer's price on the price I received from my manufacturing facility. After choosing all products for this quote, I will export to Excel and send to my customer. I have moved the combo box to the form header. But now in datasheet view I can't see it. Sara > If your subform is a continuous forms view, what do you want to use the > combo box for? The way you're coding it, it's to be used as a "navigation" [quoted text clipped - 150 lines] > >>> >> >> Column Count > >>> >> >> Column Widths OK - let's back up. You want to use the combo box to select a specific part to be put into a quote? Then we're taking the wrong approach entirely here. So what you want is to be able to add a part to a new record that is a detail of a quote? And the combo box should show you all the available parts that you can select from? Are you storing the part's UPC in the quote table? The subform is not based on any query that appears to be related to a specific quote? I'm sorry, I am willing to help you get to a solution, but it appears that I have had the wrong understanding of what your form is designed to do. So, can we back up all the way to the beginning? Tell me about the main form (and its RecordSource). Tell me what the purpose of the subform is. And how you want to use the form and subform. SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 180 lines] >> >>> >> >> Column Count >> >>> >> >> Column Widths |
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.