If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
combo box failure
i have a table with a yes/no field call "Edited". i have a button that runs
code to set the "Edited" field of a selected record to "yes". the code also set the rowsource (using SQL Update and Set statement) of a combo box to display only records with the "Edited" field = "No". the button and the combo box are on the same form. the record is selected from the combo box. when i step through the code in debug mode, the combo box nicely displays correctly only records with the "Edited" field = "No". which could mean nothing wrong with the code? but when i click the button in open form mode and open the combo box, the combo box displays both the record with the "Edited" field = Yes and records with "Edited" field = No. for the record where the "Edited" field has been set to "Yes", the "edited" field displayed in the combo box shows a "No". the "Edited" field change to a "Yes" only after i open the combo box twice. the record with the "Edited" field = "Yes" disappears from the combo box only after i have set another record's "Edited" field to "yes". this does not happen all the time though. what have i done wrong? what is the right way to do it so that the combo box correctly display only records where "Edited" field ="No" at all times? thanks for your help. |
#2
|
|||
|
|||
combo box failure
All you really need to do is use a query for the rowsource property of your
combo box that has a Where Clause of WHERE [Edited] = False Then in the button code where you set the field value of the current record, you need to requery your form. The issue you will have with that is that a Requery will cause the form to go back to the first record in the form's recordset. That can be handled by saving the primary key field value of the current record before the query then repositioning the form's current record to the saved primary key value. This means you need to have the recordset's primary key field bound to a text box on your form. It can be hidden if you don't want the user to see it. Here is an example (it is "air code" with made up names) Dim lngPrimeKey As Long 'Do your SQL Update query here lngPrimeKey = Me.txtPrimeKey Me.Requery With Me.RecordsetClone .FindFirst "[PrimeKeyField] = " & lngPrimeKey If Not .NoMatch Then Me. Bookmark = .Bookmark End If End With -- Dave Hargis, Microsoft Access MVP "Fred Loh" wrote: i have a table with a yes/no field call "Edited". i have a button that runs code to set the "Edited" field of a selected record to "yes". the code also set the rowsource (using SQL Update and Set statement) of a combo box to display only records with the "Edited" field = "No". the button and the combo box are on the same form. the record is selected from the combo box. when i step through the code in debug mode, the combo box nicely displays correctly only records with the "Edited" field = "No". which could mean nothing wrong with the code? but when i click the button in open form mode and open the combo box, the combo box displays both the record with the "Edited" field = Yes and records with "Edited" field = No. for the record where the "Edited" field has been set to "Yes", the "edited" field displayed in the combo box shows a "No". the "Edited" field change to a "Yes" only after i open the combo box twice. the record with the "Edited" field = "Yes" disappears from the combo box only after i have set another record's "Edited" field to "yes". this does not happen all the time though. what have i done wrong? what is the right way to do it so that the combo box correctly display only records where "Edited" field ="No" at all times? thanks for your help. |
#3
|
|||
|
|||
Using RecordsetClone
On Apr 8, 12:11*pm, Klatuu wrote:
In the example below, couldn't the line lngPrimeKey = Me.txtPrimeKey be replaced with lngPrimeKey = Me.RecordsetClone![PrimeKeyField] thus bypassing the need for a bound field? -- Clif All you really need to do is use a query for the rowsource property of your combo box that has a Where Clause of WHERE [Edited] = False Then in the button code where you set the field value of the current record, you need to requery your form. *The issue you will have with that is that a Requery will cause the form to go back to the first record in the form's recordset. *That can be handled by saving the primary key field value of the current record before the query then repositioning the form's current record to the saved primary key value. *This means you need to have the recordset's primary key field bound to a text box on your form. *It can be hidden if you don't want the user to see it. *Here is an example (it is "air code" with made up names) Dim lngPrimeKey As Long * * 'Do your SQL Update query here * * lngPrimeKey = Me.txtPrimeKey * * Me.Requery * * With Me.RecordsetClone * * * * .FindFirst "[PrimeKeyField] = " & lngPrimeKey * * * * If Not .NoMatch Then * * * * * * Me. Bookmark = .Bookmark * * * * End If * * End With -- Dave Hargis, Microsoft Access MVP |
#4
|
|||
|
|||
Using RecordsetClone
Now why in 10 years of doing Access I haven't thought of that. Yes, it would
work just fine. -- Dave Hargis, Microsoft Access MVP "Clif McIrvin" wrote: On Apr 8, 12:11 pm, Klatuu wrote: In the example below, couldn't the line lngPrimeKey = Me.txtPrimeKey be replaced with lngPrimeKey = Me.RecordsetClone![PrimeKeyField] thus bypassing the need for a bound field? -- Clif All you really need to do is use a query for the rowsource property of your combo box that has a Where Clause of WHERE [Edited] = False Then in the button code where you set the field value of the current record, you need to requery your form. The issue you will have with that is that a Requery will cause the form to go back to the first record in the form's recordset. That can be handled by saving the primary key field value of the current record before the query then repositioning the form's current record to the saved primary key value. This means you need to have the recordset's primary key field bound to a text box on your form. It can be hidden if you don't want the user to see it. Here is an example (it is "air code" with made up names) Dim lngPrimeKey As Long 'Do your SQL Update query here lngPrimeKey = Me.txtPrimeKey Me.Requery With Me.RecordsetClone .FindFirst "[PrimeKeyField] = " & lngPrimeKey If Not .NoMatch Then Me. Bookmark = .Bookmark End If End With -- Dave Hargis, Microsoft Access MVP |
#5
|
|||
|
|||
Using RecordsetClone
On Apr 8, 1:56*pm, Klatuu wrote:
Now why in 10 years of doing Access I haven't thought of that. *Yes, it would work just fine. -- Dave Hargis, Microsoft Access MVP Gee; and it's been at least 10 years since I have done any serious programming g I appreciate your time in sharing your knowledge with others! -- Clif |
#6
|
|||
|
|||
Using RecordsetClone
So, who said I was serious g
-- Dave Hargis, Microsoft Access MVP "Clif McIrvin" wrote: On Apr 8, 1:56 pm, Klatuu wrote: Now why in 10 years of doing Access I haven't thought of that. Yes, it would work just fine. -- Dave Hargis, Microsoft Access MVP Gee; and it's been at least 10 years since I have done any serious programming g I appreciate your time in sharing your knowledge with others! -- Clif |
#7
|
|||
|
|||
Using RecordsetClone
fun aside, I was intrigued by your use of
With Me.RecordsetClone I've used RecordsetClone, but always with a Dim / Set = combination as per the MS example code. Are there general 'rules of thumb' to help one determine when 'with' is appropriate, and when to use 'Dim / Set' ? Or is it more a matter of personal preference and coding style? -- Clif On Apr 8, 3:20*pm, Klatuu wrote: So, who said I was serious g -- |
#8
|
|||
|
|||
Using RecordsetClone
Microsoft coding examples are suspect at best.
I believe they try to keep it as simple as possible and actually things that show some different things in the same example. I see no point in enstansiating a recordset when you already have one. I use With constructs extensively. According to Access 2002 Developers Handbook by Litwin, Getz, and Gunderoy, it is a performance enhancer. Once you have established the reference with With, Access doesn't have to figure it our for each line. And, as you see, it is fewer lines of code. Being the lazy type, fewer lines of code attracts me. -- Dave Hargis, Microsoft Access MVP "Clif McIrvin" wrote: fun aside, I was intrigued by your use of With Me.RecordsetClone I've used RecordsetClone, but always with a Dim / Set = combination as per the MS example code. Are there general 'rules of thumb' to help one determine when 'with' is appropriate, and when to use 'Dim / Set' ? Or is it more a matter of personal preference and coding style? -- Clif On Apr 8, 3:20 pm, Klatuu wrote: So, who said I was serious g -- |
#9
|
|||
|
|||
Using RecordsetClone
Sorry, Clif, I don't think my previous reply actually answered your question.
If an object doesn't exist, then a Dim/Set is required. If it already exits and you are going to be addressing properties or methods of the object contiguously, then the With/End With is appropriate. As I stated previously, it is a performance enhances and to my eye it is easier to read and visually brackets the code related to the object. But, even if it is necessary to use a Dim/Set, once enstanciated, I still use the With/End With to address it if I am coding move than one line that references it. As to using it with a form recordset or recordsetclone, they are objects that exist. I see no point in enstanciating an object that references and existing object. I wont say there are exceptions, but I can't think of any at the moment. -- Dave Hargis, Microsoft Access MVP "Clif McIrvin" wrote: fun aside, I was intrigued by your use of With Me.RecordsetClone I've used RecordsetClone, but always with a Dim / Set = combination as per the MS example code. Are there general 'rules of thumb' to help one determine when 'with' is appropriate, and when to use 'Dim / Set' ? Or is it more a matter of personal preference and coding style? -- Clif On Apr 8, 3:20 pm, Klatuu wrote: So, who said I was serious g -- |
#10
|
|||
|
|||
Using RecordsetClone
Thank you. Not instantiating a reference to an existing object makes
sense to me! And I agree that the code is both simpler and more readable -- like you, less code has a definite appeal for me! -- Clif On Apr 8, 4:01*pm, Klatuu wrote: Sorry, Clif, I don't think my previous reply actually answered your question. If an object doesn't exist, then a Dim/Set is required. If it already exits and you are going to be addressing properties or methods of the object contiguously, then the With/End With is appropriate. *As I stated previously, it is a performance enhances and to my eye it is easier to read and visually brackets the code related to the object. *But, even if it is necessary to use a Dim/Set, once enstanciated, I still use the With/End With to address it if I am coding move than one line that references it. As to using it with a form recordset or recordsetclone, they are objects that exist. *I see no point in enstanciating an object that references and existing object. *I wont say there are exceptions, but I can't think of any at the moment. -- Dave Hargis, Microsoft Access MVP "Clif McIrvin" wrote: fun aside, I was intrigued by your use of With Me.RecordsetClone I've used RecordsetClone, but always with a Dim / Set = combination as per the MS example code. Are there general 'rules of thumb' to help one determine when 'with' is appropriate, and when to use 'Dim / Set' ? *Or is it more a matter of personal preference and coding style? -- Clif On Apr 8, 3:20 pm, Klatuu wrote: So, who said I was serious g --- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|