 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / April 2005Marsh...you were right!! HELP please!
In a few posts back of mine about dependent cbo boxes, Marsh wrote: Since a continuous (sub)form displays multiple records, there is no way to keep the RowSource query in a dependent combo box synchronized with the value in the next "higher level" for **all** of the displayed records. The best you can do is make the Current record work properly (same as your previous problem). But the other rows will still have the display problem you had before we added the Requery to the Current event. In single view, you can't see those records, but in continuous view, the problem is right in front of you. The work around is kind of tricky, involving placing a bound text box directly on top of the text portion of the combo box. You'll also need to munge around with the form's RecordSource query to include the display value for the combo box as well as add a small amount of code to manage the focus if the user should click into the text box used to display the correct combo text portion. OK! I added the requery to the OnCurrent event of the form like you said, and it works...as long as I'm on that record. I tried the work around you mentioned, but quite honestly, I'm confused. If I put a text box on top (I assumed bound to the value in the table) how can the user change the cbo if they need to? TIA! Ang > ... as well as add a small amount of code to manage > the focus if the user should click into the text box used to [quoted text clipped - 5 lines] > text box on top (I assumed bound to the value in the table) how can the > user change the cbo if they need to? Going back to Marsh's suggestions, note that he stated (and I will clarify what he meant here) that you would need to add a bit of code to ensure that the focus, if acquired by the textbox, would then be assigned to the combo box (by adding a .SetFocus line to the textbox's "On Enter" or "Got Focus" event procedure). As soon as the focus is moved to the combo box, the combo box will be brought to front, as if it were the only control in that position and allowing full control over the use of the same - moving the focus from the combo box will again allow the textbox to regain its position over the combo box. 'SAMPLE CODE Private Sub MyTextBox_Enter () 'Set focus to combo box Me.MyCombo.Setfocus End Sub  SignatureBruce M. Thompson, Microsoft Access MVP bthmpson@mvps.org (See the Access FAQ at http://www.mvps.org/access) >> NO Email Please. Keep all communications within the newsgroups so that all might benefit.<< >In a few posts back of mine about dependent cbo boxes, Marsh wrote: > [quoted text clipped - 22 lines] >text box on top (I assumed bound to the value in the table) how can the >user change the cbo if they need to? Bruce explained how to shift the focus to the combo box. What the text box is bound to depends on if the combo box's bound column is visible (its ColumnWidth is >0). If it is visible then, yes, the text box would be bound to the same field as the combo box. If the combo box's bound column is not visible (the combo box displays something different than the stored value), then you have to modify the form's RecordSource query to Join to the combo box's table and include the combo box's first visible column field in the query's field list. The text box should be bound to this field. I told you it was a tricky situation ;-) SignatureMarsh MVP [MS Access] Thanks guys!! OK...good news and bad news: Good news: got the text box to work. It changes focus when I enter it. Bad news: the bound column (subclassID, 0 width) is not the value I need. I'm getting a number instead of the description. ie: getting subclassID (15) instead of subclassname (Rret). Can you please tell me what I need to change? Here's the control source for the subform: SELECT OrdersDetails.OrderDetailID, OrdersDetails.ClassID, OrdersDetails.SubclassID, OrdersDetails.SizeID, OrdersDetails.Quantity, OrdersDetails.ExtPrice, OrdersDetails.Discount, ordersdetails.UnitPrice, ordersdetails.quoteid FROM Products INNER JOIN OrdersDetails ON (Products.SizeID=OrdersDetails.SizeID) AND ((Products.ClassID=OrdersDetails.ClassID) And (Products.SubClassID=OrdersDetails.SubclassID)); The subclassname is in the Subclass table and it's connected to the Orderdetails table through the Products table. (Does that make sense??) Also, when i try to open this up to edit it, I get the error: Database can't represent the expression (Products.classid=ordersdetails.classid) AND (products.subclassid=ordersdetails.subclassid) in design view. Can I get rid of the INNER JOIN line?? I always mess those up and don't know when I should use them or when I shouldn't. I don't know why it's in there since I used the wizard to create the form. This is finally coming along!! Thanks for your help!! Ang >Thanks guys!! OK...good news and bad news: > [quoted text clipped - 26 lines] >don't know when I should use them or when I shouldn't. I don't know >why it's in there since I used the wizard to create the form. Well, Angi, it looks like you didn't need the Products table before thhis combo box issue came along, BUT now, you do need it. In fact, you need another one. Add the Subclass table to the query and add the join line from Products to Subclass between the appropriate fields. Then drag the subclassname down to the query's field list. The ON clause you have above isn't correct because the Subclassid field is not in the Products table, it's in the orderdetails table, right? Getting the join lines correct in the top part of the query design window should take care of this issue. Once the query is sorted out properly, the text box on the form would then be bound to the new subclassname field. Are we having fun yet? SignatureMarsh MVP [MS Access] Marsh.. I know this is a little late, but I wasn't having any problems, so I thought I would leave you alone! :) The box works great...as long as I don't have to change anything. This is probably considered a new thread, but rather than explain everything again, I'd thought I'd continue it...hope that's ok. My problem is, when I change the first cbo (Class) it requeries the cboSubClass and cboSize cbos, which of course, become blank (this lets the users know they have to select a new subclass and size) but the text box we created is staying the old value so it looks like there's something already there. It fixes itself after a new subclass and size is selected, but how can I make them blank until something is selected?? I've tried requery, making it = "" (which deletes my subclass name), tried visible=false...everything. Is this possible?? Thanks for all your help! Ang Don't worry about being a little late, I've been out of town for a week. This situation is too tricky for me to work out using newsgroup messages. I think I need to see your forms, queries and tables. If you can make a copy of your mdb file(s?), strip out all irrelevant stuff, compact and zip it to me as an emali attachment, I'll see what I can figure out. SignatureMarsh MVP [MS Access] >Marsh.. >I know this is a little late, but I wasn't having any problems, so I [quoted text clipped - 11 lines] >selected?? I've tried requery, making it = "" (which deletes my >subclass name), tried visible=false...everything. Is this possible?? Marsh Ok...call me stupid, but I tried emailing you and it got returned "User unknown" What address should i be using? Marshbar...@wowway.com or marshbar@wowway.com? i tried the latter. >Marsh >Ok...call me stupid, but I tried emailing you and it got returned "User >unknown" > >What address should i be using? Marshbar...@wowway.com or >marshbar@wowway.com? It should be in the From field of all my posts, but here it is again: marshbarton@wowway.com SignatureMarsh MVP [MS Access] ok....ya can't say i didn't try! :) ----- The following addresses had permanent fatal errors ----- <marshbar...@wowway.com> ----- Transcript of session follows ----- ... while talking to wowway.com.s7a1.psmtp.com.: >>> RCPT To:<marshbar...@wowway.com> <<< 550 5.0.0 <marshbar...@wowway.com>... User unknown 550 <marshbar...@wowway.com>... User unknown AND ----- The following addresses had permanent fatal errors ----- <marshbar@wowway.com> ----- Transcript of session follows ----- ... while talking to wowway.com.s7a1.psmtp.com.: >>> RCPT To:<marshbar@wowway.com> <<< 550 5.0.0 <marshbar@wowway.com>... User unknown 550 <marshbar@wowway.com>... User unknown Where are you getting the ... part of the address??? Remove the "..." and replace it with "ton" SignatureMarsh MVP [MS Access] >ok....ya can't say i didn't try! :) > [quoted text clipped - 6 lines] ><<< 550 5.0.0 <marshbar...@wowway.com>... User unknown >550 <marshbar...@wowway.com>... User unknown the ... is in all of your addresses....it reads marshbar...@wowway.com. Even when you typed it a few posts ago and the posts I just pasted. Must be something with google. Does anyone else see the ...??? Will send again. Thanks! (I knew I wasn't stupid or crazy!!!) OK...better late than never!! First, thank you so much for doing all that!! Secondly, I'm sorry I forgot the size form in there. Didn't make it easy on ya, did I?? I'm getting ready to add the Edge cbo so I'm sure I'll be back! Here we go: 1 - I noticed you changed the table relationship from QuoteDetails to Products from 1 to 3....why? 2 - You added Option Explicit to the code...why? Is that something I should do with all my mods? 3 - About the inner join --> left join...why? I was getting the right records with inner join, so I'm just curious. Can you tell I don't pay much attention to joins??? 4 - I understood all of the code, except...in the following, why did you add the isnull's? Those fields cannot be left blank and still add a record. Am I reading it right? If that's the case, then I need to add some code in case they try to lose focus while blank. Private Sub cboSizeID_AfterUpdate() (event) If Not (IsNull(Me.cboClassID) Or IsNull(Me.cboSubID) Or IsNull(Me.cboSizeID)) Then 5 - You mentioned something about my table relationships in the email. Are they right?? I just tried adding the Edges table/field I told you about and I'm not sure what to connect it to. Shouldn't my tables relationships be like the cbo's?? Products -> Class -> SubClass -> Size -> Edge??? Right now, everything's going through Subclass to get to Products. Not right, right??? 6 - Was it that bad? Responses inline below. >OK...better late than never!! First, thank you so much for doing all >that!! Secondly, I'm sorry I forgot the size form in there. Didn't [quoted text clipped - 5 lines] >1 - I noticed you changed the table relationship from QuoteDetails to >Products from 1 to 3....why? I'm still not sure this was necessary, but it was an attempt to do what the Left Join in the form's query takes care of. >2 - You added Option Explicit to the code...why? Is that something I >should do with all my mods? Option Explicit should be in every module. Its purpose is to tell the compiler to generate an message whenever it finds an undefined variable in your code. You can set an Option to have it inserted automatically in every ** new** module, but you'll have to manually add it to existing modules. >3 - About the inner join --> left join...why? I was getting the right >records with inner join, so I'm just curious. Can you tell I don't pay >much attention to joins??? The type of Join can be critical, pay close attention to them. In this case, your inner join was wreaking havoc on the synchronized combo boxes. Every time you selected a subclass, we wanted to set the size combo value to Null to invalidate any previous size value. BUT, this means that the combination of class/subclass/size would not be found in the size table, and because of the inner join the record that was being edited should be excluded from the form's recordset. This created a catch22 where the edited record shouldn't be available for editing. Changing it to a left join meant that we want to see/edit the record whether there was a matching record in the size table or not. >4 - I understood all of the code, except...in the following, why did >you add the isnull's? Those fields cannot be left blank and still add a [quoted text clipped - 5 lines] >If Not (IsNull(Me.cboClassID) Or IsNull(Me.cboSubID) Or >IsNull(Me.cboSizeID)) Then That is to prevent adding (invalid) records to the size table when any of those fields is Null. >5 - You mentioned something about my table relationships in the email. >Are they right?? I just tried adding the Edges table/field I told you >about and I'm not sure what to connect it to. Shouldn't my tables >relationships be like the cbo's?? Products -> Class -> SubClass -> >Size -> Edge??? Right now, everything's going through Subclass to get >to Products. Not right, right??? Considering that I don't know what an "edge" is, I will guess that that's right. Actually, size could (should?) go through subclass to get to class to get to products, but this depends on the details of what entities you're trying to model. >6 - Was it that bad? No. it wasn't too bad at all. The thing that took so long to track down was that inner join conundrum. SignatureMarsh MVP [MS Access] Marsh, Ok, regarding #4...I didn't notice everything was in ( ). I was reading it as: If class IS NOT null or if subclass IS null or if size IS null, then look it up. If there's not a match, then create a new product. I now realize, it says if ANY of them are not null. That was my fault. The bad thing is I can tab right out of subclass and go to size without an error until I try to create a new size. Or it creates an incomplete line on the quote (class only). That's why I was thinking I needed to add some code there (LostFocus) that wouldn't allow a blank field when losing focus. I can handle that. Does the left join have anything to do with that? I'm going to look into joins at greater depth, because I really don't understand the differences. I understand the the option explanations, I just don't know when the right times to use them are. As far as Edges...it's just a new table that's similiar to Size. Another wonderful dependent cbo. After looking at the relationships, I was wondering how frazzled I was that day to do that!! I've now changed them to logical order. Doesn't work any differently, but looks better and now I know how to connect the Edge table. I think this form is almost done! I know I've said it a lot, but I can't say it enough...Thank you, thank you, thank you!!! Gratefully, Angi >Ok, regarding #4...I didn't notice everything was in ( ). I was >reading it as: If class IS NOT null or if subclass IS null or if size [quoted text clipped - 18 lines] >is almost done! I know I've said it a lot, but I can't say it >enough...Thank you, thank you, thank you!!! Angi, using the LostFocus event to force data entry, is seriously flawed because the user may never use the control. It is almost always better to use the form's BeforeUpdate event or behind a button for something that requires all the data. OTOH, if a user just doesn't know what data to enter and you block them from procedding with what they do know, then a smart user will enter garbage data to get past your checks. And, no, this has nothing to do with the join type. Getting close to done is a good thing, but make sure you really are close before you relax too much ;-) 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.