 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / March 2006recordsetclone question
I have a form with two combo boxes, Style and BlendID. For each new record, if the user selects an existing Style, I want to display the corresponding BlendID. If a new Style is typed in, I want the user to be able to select an existing BlendID or enter a new one. Complicating matters, when a BlendID is selected, I want to display a pop-up form showing Blend details (BlendID-AfterUpdate event) I'm having a hard time gettng the Style-BlendID link to work without scrambling the link between my BlendID combo and the popup form. I've tried a filtered query to limit the values available to the BlendID combo, but I had some indecipherable problems (no errors, just not working). So, I thought maybe working with RecordsetClone would be the way to go, but I'm stuck on how to get my second combo box to display the recordset retreived for the first. Any suggestions? Thanks in advance - ElizCat Here's what I've got: Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[StyleNumber] = " & str(Nz(Me![BlendID], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark 'do something here to pass [BlendID] to the BlendID combo box Call BlendID_AfterUpdate My guess is that you Dimmed your object references in one of the procedures. This makes them visible only to that procedure. Try moving the Dim statements for your recordset clone to the top of your form module. That way, they are visible to all procedures in the module. > I have a form with two combo boxes, Style and BlendID. For each new record, > if the user selects an existing Style, I want to display the corresponding [quoted text clipped - 25 lines] > Call BlendID_AfterUpdate > Oh dear, I think you might have lost me. I'm really inexperienced as a programmer. When you say to try moving the Dim statements for the recordset clone to the top of the form module, do you mean to put the "Dim rs as Object" statement in the General Declarations section? > My guess is that you Dimmed your object references in one of the procedures. > This makes them visible only to that procedure. Try moving the Dim > statements for your recordset clone to the top of your form module. That > way, they are visible to all procedures in the module. Exactly. > Oh dear, I think you might have lost me. I'm really inexperienced as a > programmer. [quoted text clipped - 9 lines] > > > Okay, I can't seem to add anything to the General Declarations section. If I put it in a public Sub, will that work? thanks for your help. ElizCat > Exactly. > [quoted text clipped - 11 lines] > > > > > BTW, I'm working in Access 2002 (although my dbase seems to have saved as 2000). Putting the Dim statement in a public sub does not seem to have the intended effect. When I select an existing Style from the combo box, the BlendID combo box does not move to the associated BlendID. In addition, while my BlendID_AfterUpdate event pops up the new form, it displays the first item from the BlendID table instead of the BlendID associated with the selected Style. Arrrgh! It doesn't go in a sub. It goes right after the Option statements at the top of the form's module. Here is an example: Option Compare Database Option Explicit Option Base 0 Private xlApp As Object 'Application Object Private xlBook As Object 'Workbook Object Private xlSheet As Object 'Worksheet Object Private xlChartObj As Object 'Chart Object for Charts Private rstActual As Recordset 'Recordset to load Actual Data Private rstPlan As Recordset 'Recordset to load Plan Data Private rstItms As Recordset 'Recordset to load ITM/Program Manager Name > BTW, I'm working in Access 2002 (although my dbase seems to have saved as > 2000). [quoted text clipped - 7 lines] > > Arrrgh! Thank you for your patience and willingness to shepherd newbies like me. I've put "Private rs as Object" in the General Declarations at the top of the form module & saved it. When I go through the form and check the new code, I still get the same behavior. My data sources for the Style combo box and the BlendID combo box are two different tables. Would that cause the problem? I really don't know anything about working with recordsets, and the six (yes, count 'em, six) reference books I have on my desk aren't being much help. If there is an online resource you could point me toward as well, I would be most grateful. Best Regards (and a happy Friday!) ElizCat Post your code. It is hard to tell from here. The different recordsources for the combos should not be causing this. > Thank you for your patience and willingness to shepherd newbies like me. > [quoted text clipped - 12 lines] > Best Regards (and a happy Friday!) > ElizCat Okay, here's the code for the two subs I'm having problems with. thanks again --------------------------------------- Option Compare Database Private rs As Object ---------------------------------------- Private Sub StyleNumber_AfterUpdate() 'if StyleNumber already exists, display BlendID on record ' Dim rs As Object 'moved this to the declarations section for testing Set rs = Me.Recordset.Clone rs.FindFirst "[StyleNumber] = " & str(Nz(Me![BlendID], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark Call BlendID_AfterUpdate End Sub ---------------------------------------------------------- Private Sub BlendID_AfterUpdate() 'If the BlendID is blank, then exit the Sub. If IsNull(Me.BlendID) Then Exit Sub End If 'Dimension variables. Dim FormName As String, SyncCriteria As String Dim F As Form, rs As Object 'Set the formname to "BlendForm," the form that will be 'synchronized. FormName = "BlendForm" 'Check to see if BlendForm is open. If it 'is not open, open it. If Not fIsLoaded("BlendForm") Then DoCmd.OpenForm FormName End If 'Define the form object and Recordset object for 'the Products form. Set F = Forms(FormName) Set rs = F.Recordset.Clone 'Define the criteria used for the synchronization. SyncCriteria = "[BlendID] =" & Me![BlendID] 'Synchronize the corresponding record in BlendForm to 'the current record in the subform. rs.FindFirst SyncCriteria 'If a record exists in Blendform, find the 'matching record. If rs.EOF Then MsgBox "No match exists!", 64, FormName Else F.Bookmark = rs.Bookmark End If End Sub I made a few changes. Take a look, try to run it. I will not guarantee it will work first time around, but it is closer. Notice I put your reference to rs back in the sub. It really doesn't need to be outside. There were a few problems, but I think I have them fixed. Option Compare Database ---------------------------------------- Private Sub StyleNumber_AfterUpdate() Dim rs As Recordset 'if StyleNumber already exists, display BlendID on record Set rs = Me.RecordsetClone With rs 'Since this is a string, it needs quotes .FindFirst "[StyleNumber] = '" & str(Nz(Me![BlendID], 0)) & "'" If Not .NoMatch Then Me.Bookmark = rs.Bookmark End If .Close End With Set rs = Nothing If Not IsNull(Me.BlendID) Then ' Do it here so you save doing a call if not needed Call BlendID_AfterUpdate End If End Sub ---------------------------------------------------------- Private Sub BlendID_AfterUpdate() Dim FormName As String, SyncCriteria As String Dim F As Form, rsF As Database ' Need a different name here, because rs is used 'My Guess is you still need to check here If IsNull(Me.BlendID) Then Exit Sub End If 'Set the formname to "BlendForm," the form that will be 'synchronized. FormName = "BlendForm" 'Check to see if BlendForm is open. If it 'is not open, open it. If Not fIsLoaded(FormName) Then DoCmd.OpenForm FormName End If 'Define the form object and Recordset object for 'the Products form. Set F = Forms(FormName) Set rsF = F.RecordsetClone 'Define the criteria used for the synchronization. 'Again, if it is string, it needs quotes SyncCriteria = "[BlendID] ='" & Me![BlendID] & "'" 'Synchronize the corresponding record in BlendForm to 'the current record in the subform. With rsF .FindFirst SyncCriteria 'If a record exists in Blendform, find the 'matching record. If .NoMatch Then MsgBox "No match exists!", 64, FormName ' What is 64. best to use standard vb constants for readability Else F.Bookmark = .Bookmark End If .Close End With Set rsF = Nothing Set F = Nothing End Sub > Okay, here's the code for the two subs I'm having problems with. > thanks again [quoted text clipped - 58 lines] > > End Sub Omigosh! Thank you so much for taking the time to root through my code! You're a God (unless that offends you, in that case, please accept my sentiment that you're a superhero!) I've plugged it in, and Access is having issues with the structure used in the With...End With part in the SampleNumber_AfterUpdate module. It doesn't like the phrase .FindFirst "[StyleNumber] = '" & str(Nz(Me![BlendID], 0)) & "'" because it thinks it should have an object. I've never used With before, so I don't see anything obvious. I'll check some of my reference books, but if you could recheck your modifications I'd be grateful. Brian has a good point. Is it reasonable that Style should = Blend? In any case, the FindFirst line as is is expecting Me![BlendID to be character data. If it is numeric data, then it should be like this: .FindFirst "[StyleNumber] = " & str(Nz(Me![BlendID], 0)) The With End With structure is just a shorthand method of referencing properties of an object. For example, you could write code like this: Me.txtSomeControl = "Foo" Me.txtSomeOtherControl = "Boo" Me.txtSomeOtherControl.Locked = True Using the With End With, it would be like this: With Me .txtSomeControl = "Foo" .txtSomeOtherControl = "Boo" .txtSomeOtherControl.Locked = True End With So that everything that start with a period between With and End with is considered a proprerty of the With object. In this case, the current form. > Omigosh! Thank you so much for taking the time to root through my code! > You're a God (unless that offends you, in that case, please accept my [quoted text clipped - 9 lines] > I don't see anything obvious. I'll check some of my reference books, but if > you could recheck your modifications I'd be grateful. Klatuu, my version of Access still doesn't like the following statement: rsF.FindFirst "[StyleNumber] = " & str(Nz(Me![BlendID], 0)) I get the error "Method or data member not found" . I've gone through MS help to try to understand the error message, and I can't figure out what the problem is. Any ideas? thanks again - ElizCat Hi ElizCat, 1. The logic in your "rs.FindFirst" is wrong. It will find no matches because Style <> Blend. Style = Style, and Blend = Blend. 2. Do you have a StyleNumber as a FK in your Blends table, or a BlendID as a FK in your Styles table? Any field which defines the relationship between the two tables? Perhaps you could post the relevant table structures. tblStyles(*StyleNumber, Style, etc) tblBlends(*BlendID, Blend, +StyleNumber, etc) 3. Assuming that the above table structures vaguely resemble what's in your db, the rowsource for BlendID (using the query builder) would look like the following: SELECT BlendID, Blend FROM tblBlends WHERE ((([StyleNumber]) = Forms!MyFormName!Style)) In the AfterUpdate event of your Style combobox (after you've selected a style), you'd issue a command to requery your BlendID combobox: Private Sub Style_AfterUpdate() Me.BlendID.Requery End Sub Or if you want to set its rowsource programmatically, you'd use the following in the AfterUpdate event of your Style combobox with no requery necessary: Private Sub Style_AfterUpdate() Me.BlendID.Rowsource = "" & _ "SELECT BlendID, Blend " & _ "FROM tblBlends " & _ "WHERE tblBlends.StyleNumber =" & Me.Style End Sub 5. If you want to programmatically select the first item in the combo, then you can add the following line to the end of the code above: Me.BlendID = Me.BlendID.ItemData(0) HTH, Brian > I have a form with two combo boxes, Style and BlendID. For each new record, > if the user selects an existing Style, I want to display the corresponding [quoted text clipped - 24 lines] > > Call BlendID_AfterUpdate Hi Brian and Klatuu! Thanks to you both for the explanations and code. Brian is correct, Style <> Blend, and so I will have to look at his code to help me set it up to find the right info from the right tables. Klatuu, you had a good point - BlendID is a number, Style is text. I will have to adjust my code to account for that. Blend and Style are related in the following manner: Table Sample (SampleID PK, Style, BlendID FK, other data) Table Blend (BlendID PK, other data) I don't have Style set up as its own table right now, although it would be easy enough to do that if needed. I don't know why my version of Access isn't liking the With End With structure. Obviously I can work around that though. Thank you both very much for helping me plow through this. I'm on vacation right now in Savannah, so it may be a few days before I get motivated to work through all this, but I'll let you know how it turns out. warmest regards, ElizCat Back from vacation, having made some of your suggested modifications, and I'm still having issues... I'm apparently missing some punctuation in the first If statement, and I can't figure out what I've got wrong. Do you guys see anything obvious? As reference, BlendID is a number and is the PK in the Blend Table and a FK in the Samples table. StyleNumber is just a text field (because of our numbering convention) in the Samples table. If Not IsNull(Me.StyleNumber) Then 'if StyleNumber already exists, display BlendID on record Me.BlendID.RowSource = "" & _ "SELECT BlendID" & "FROM Samples" & _ "WHERE Samples.StyleNumber =" & "Me.StyleNumber" End If If Not IsNull(Me.BlendID) Then 'display blend info as usual Call BlendID_AfterUpdate ElseIf IsNull(Me.BlendID) Then 'set the datasource for BlendID back to the Blend Table Me.BlendID.RowSource = Blend.BlendID End If Welcome back, If StyleNumber is a numeric datatype then the following will work: If Not IsNull(Me.StyleNumber) Then 'if StyleNumber already exists, display BlendID on record Me.BlendID.RowSource = "" & _ "SELECT BlendID FROM Samples " & _ "WHERE Samples.StyleNumber =" & Me.StyleNumber End If Otherwise, if StyleNumber is textual then you'll need additional quotes in your WHERE clause: "WHERE Samples.StyleNumber ='" & Me.StyleNumber & "'" Brian > Back from vacation, having made some of your suggested modifications, and I'm > still having issues... [quoted text clipped - 18 lines] > Me.BlendID.RowSource = Blend.BlendID > End If Brian, thank you very much for helping me through the syntax and punctuation pitfalls! My combo box link now works perfectly! Now, if I can just get the BlendID_AfterUpdate event to work properly, my major hurdle will be crossed. thanks again so very much for your patient help! I cannot begin to convey my appreciation for your kind and selfless donation of time and experience to help newbies such as myself. all my best - ElizCat Glad to be of service. Post back if you need help with the BlendID portion. Brian > Brian, > thank you very much for helping me through the syntax and punctuation [quoted text clipped - 7 lines] > all my best - > ElizCat Okay, the happy dance was a little premature. I've got everything working fine, except when a new StyleNumber is entered. With the existing code, my BlendID list is empty (everything filtered out.) Is there a way I can use Me.StyleNumber.OnNotInList inside the StyleNumber_AfterUpdate module to bypass all the code I've just put in and exit the sub? My crude attempts haven't worked, apparantly due to syntax issues, and I'm not finding MS Help very helpful here, since it points me to the NotInList event. thanks again - ElizCat I realized that in all the back-and-forth, the code I was actually using wasn't posted. Here it is (entire sub) 'ignore null values for StyleNumber If Not IsNull(Me.StyleNumber) Then 'if StyleNumber already exists, display BlendID on record Me.BlendID.RowSource = "" & _ "SELECT BlendID FROM Samples " & _ "WHERE Samples.StyleNumber ='" & Me.StyleNumber & "'" Me.BlendID = Me.BlendID.ItemData(0) End If If Not IsNull(Me.BlendID) Then 'display blend info as usual Call BlendID_AfterUpdate ElseIf IsNull(Me.BlendID) Then 'set the datasource for BlendID back to the Blend Table Me.BlendID.RowSource = Blend.BlendID 'this line not working right End If Hi ElizCat, Maybe we can put a little jig back in your step. But first I'll need to know in which event procedure your using the second 'IF' clause. If Not IsNull(Me.BlendID)....... I've got to leave for a few hours, but I'll check back. Or perhaps you'd like me to take a look at a 'stripped-down' version of your db. If so, then only leave a few records in the tables, compact the db, and zip it up. I'm using A2K, so make sure the file format is compatible. Brian R8bastel<at>SMalltel<dot>net remove Caps and numbers, and change at and dot > I realized that in all the back-and-forth, the code I was actually using > wasn't posted. Here it is (entire sub) [quoted text clipped - 14 lines] > Me.BlendID.RowSource = Blend.BlendID 'this line not working right > End If That entire sub lives in the SampleNumber_AfterUpdate module. If you think it would be easier, I'll zip the stripped version I'm using for test programming and email it to you. thanks - ElizCat > Hi ElizCat, > > Maybe we can put a little jig back in your step. But first I'll need to know > in which event procedure your using the second 'IF' clause. If Not > IsNull(Me.BlendID)....... Yes, that would be good. Brian > That entire sub lives in the SampleNumber_AfterUpdate module. If you think > it would be easier, I'll zip the stripped version I'm using for test [quoted text clipped - 8 lines] > > in which event procedure your using the second 'IF' clause. If Not > > IsNull(Me.BlendID)....... done (conversation moved off discussion group) Spastic Happy Dance!!! It works! It works! It *all* works! thank you, thank you, thank you! ElizCat LOL!!! Can picture this quite clearly! > Spastic Happy Dance!!! It works! It works! It *all* works! > > thank you, thank you, thank you! > > ElizCat |
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.