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 |
#11
|
|||
|
|||
Using a Combo Box to Find Records
Jeanette,
Here is what is in imm. win: First item on combo list is: Setup ID But if I perservere and get past the error message, the correct data is in the cbo. SetupID is the autonumber primary key for the table for the main form. The table is the list of fund raising campaigns and the field in the combo box is the campaign name. I get the error: The value you entered isn't valid for this field. Does it matter that there is a subform? Thanks, Scott Jeanette Cunningham wrote: Scott, I am just getting back to this thread - it is 6am here in Australia. This code-- Private Sub cboMoveTo_OnLoad() Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0) End Sub is meant to make the combo show the first item that you would see if you clicked on the combo. By the way, the line -- needs to be put in the Form_Load event. The idea is that when the form load event runs, it puts that first item in the combo. The load event always runs when a form is opened. Let's check out why this doesn't work for you. In the code you have on the Form_Load event, put this line Debug.Print "First item on combo list is: " & Me.cboMoveTo.ItemData(0) Now open the form. Press Alt + G to open the Immediate window. Look what access has put for the first item in the combo. Let us know what access put in the immediate window. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia Bruce, [quoted text clipped - 107 lines] Thanks, Scott -- Message posted via http://www.accessmonster.com |
#12
|
|||
|
|||
Using a Combo Box to Find Records
Hi Scott,
I assume that the combo now correctly shows the first item on its list after the form loads? The error message comes when the next line of code runs Call cboMoveTo_AfterUpdate Is this what happens? If the answer is yes, just comment out the line of code Call cboMoveTo_AfterUpdate and see how that goes. That line of code was put in to simulate the user clicking the combo to finding the records that match the SetupID for the first item on the list. You may not need to do this. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Scott_Brasted via AccessMonster.com" u56211@uwe wrote in message news:9f399cdd77161@uwe... Jeanette, Here is what is in imm. win: First item on combo list is: Setup ID But if I perservere and get past the error message, the correct data is in the cbo. SetupID is the autonumber primary key for the table for the main form. The table is the list of fund raising campaigns and the field in the combo box is the campaign name. I get the error: The value you entered isn't valid for this field. Does it matter that there is a subform? Thanks, Scott Jeanette Cunningham wrote: Scott, I am just getting back to this thread - it is 6am here in Australia. This code-- Private Sub cboMoveTo_OnLoad() Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0) End Sub is meant to make the combo show the first item that you would see if you clicked on the combo. By the way, the line -- needs to be put in the Form_Load event. The idea is that when the form load event runs, it puts that first item in the combo. The load event always runs when a form is opened. Let's check out why this doesn't work for you. In the code you have on the Form_Load event, put this line Debug.Print "First item on combo list is: " & Me.cboMoveTo.ItemData(0) Now open the form. Press Alt + G to open the Immediate window. Look what access has put for the first item in the combo. Let us know what access put in the immediate window. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia Bruce, [quoted text clipped - 107 lines] Thanks, Scott -- Message posted via http://www.accessmonster.com |
#13
|
|||
|
|||
Using a Combo Box to Find Records
Bruce
My appoligies for being too sensitive. It has been a particularly bad couple of weeks. I appreciate the thoughts on compiling. It makes good sense. It amazes me that MS will write db's with code that does not compile. Also thankyou for the idea on the close button. As I understand what you said, It pays to be more specific in my code. What does the ac in acForm represent? Thank you for all your help and patience. Best, Scott BruceM wrote: I didn't know you understood Jeanette's suggestion based on what you wrote. You wouldn't be the first person attempting to use a placeholder field or control name in actual code. The ItemData code needs to go into the *form's* Load event. A combo box doesn't have a Load or OnLoad event (at least not in Access 2003 and earlier). Did that code compile? When closing a form I like to be specific: DoCmd.Close acForm, "frmCampaignSummary" If you are closing the form on which the command button lives you can do: DoCmd.Close acForm, Me.Name I can't say exactly what compiling code does, except that it will identify syntax errors, references to non-existent fields and controls (perhaps due to spelling errors), functions with the wrong number of arguments (for instance, Left("Test text") would generate a compile error because you need to specify the number of characters), functions it doesn't recognize (again, spelling errors perhaps). A database with uncompiled code could harbor an error. In some cases this would be code that runs only when certain conditions are met, so it could be a while before the error happens. I believe compiling improves performance, but most of the time probably not enough to make much of a difference. As an aside, Option Explict is a good thing to have at the top. It means you have to declare all variables. If you do not, Access tends to assume anything it doesn't recognize is a variant, which can lead to very frustrating troubleshooting just because you typed something incorrectly. The difference between lngID and IngID may not be readily apparent by eye (one uses a lower case "l" and the other an upper case "I", in case you are viewing this with sans serif font). Bruce, [quoted text clipped - 58 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#14
|
|||
|
|||
Using a Combo Box to Find Records
Jeanette,
I took out the line: Call cboMoveTo_AfterUpdate a while ago because it turned red in the code window and caused an error. The cbo still shows blank and I get the error mentioned above with the code I posted above with the error message posted above. Best, Scott Jeanette Cunningham wrote: Hi Scott, I assume that the combo now correctly shows the first item on its list after the form loads? The error message comes when the next line of code runs Call cboMoveTo_AfterUpdate Is this what happens? If the answer is yes, just comment out the line of code Call cboMoveTo_AfterUpdate and see how that goes. That line of code was put in to simulate the user clicking the combo to finding the records that match the SetupID for the first item on the list. You may not need to do this. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia Jeanette, [quoted text clipped - 48 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#15
|
|||
|
|||
Using a Combo Box to Find Records
That combo searches using SetupID.
From reading through all your previous posts, I assume that the primary key of the form is something other than SetupID. For the code line with ItemData(0) to work, the combo must search on the primary key field of the form. However, if the form has only value for SetupID when it opens, you could try code like this-- Me.cboMoveTo = Me.SetupID Put that one line of code in the Form_Load event. If the form has more than one value for SetupID when it opens, the code above won't work. If it doesn't work, leave the combo blank when the form opens. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Scott_Brasted via AccessMonster.com" u56211@uwe wrote in message news:9f3ac455d955b@uwe... Jeanette, I took out the line: Call cboMoveTo_AfterUpdate a while ago because it turned red in the code window and caused an error. The cbo still shows blank and I get the error mentioned above with the code I posted above with the error message posted above. Best, Scott Jeanette Cunningham wrote: Hi Scott, I assume that the combo now correctly shows the first item on its list after the form loads? The error message comes when the next line of code runs Call cboMoveTo_AfterUpdate Is this what happens? If the answer is yes, just comment out the line of code Call cboMoveTo_AfterUpdate and see how that goes. That line of code was put in to simulate the user clicking the combo to finding the records that match the SetupID for the first item on the list. You may not need to do this. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia Jeanette, [quoted text clipped - 48 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#16
|
|||
|
|||
Using a Combo Box to Find Records
Hi, setupID IS the primary key.
Jeanette Cunningham wrote: That combo searches using SetupID. From reading through all your previous posts, I assume that the primary key of the form is something other than SetupID. For the code line with ItemData(0) to work, the combo must search on the primary key field of the form. However, if the form has only value for SetupID when it opens, you could try code like this-- Me.cboMoveTo = Me.SetupID Put that one line of code in the Form_Load event. If the form has more than one value for SetupID when it opens, the code above won't work. If it doesn't work, leave the combo blank when the form opens. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia Jeanette, [quoted text clipped - 32 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#17
|
|||
|
|||
Using a Combo Box to Find Records
I wrote in my previous posting that the code needs to be in the *form's* Load
event. You have posted code for the combo box OnLoad event. Maybe there is such a thing in Access 2007 (there is not in Access 2003), but in any case the code needs to be in the form's Load event. You have not mentioned whether you moved the code there, but the most recent posting of the code indicates it is still in the combo box OnLoad event, whatever that is. Does the code compile? Scott_Brasted wrote: Hi, setupID IS the primary key. That combo searches using SetupID. From reading through all your previous posts, I assume that the primary key [quoted text clipped - 19 lines] Thanks, Scott -- Message posted via http://www.accessmonster.com |
#18
|
|||
|
|||
Using a Combo Box to Find Records
By the way, Jeanette also (and originally) specified the form's Load event.
I have just been trying to stress that point. Scott_Brasted wrote: Hi, setupID IS the primary key. That combo searches using SetupID. From reading through all your previous posts, I assume that the primary key [quoted text clipped - 19 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#19
|
|||
|
|||
Using a Combo Box to Find Records
OK, I woke up this mornng withyet another idea. This
time it was a good one. I took Jeanette's code and replaced the Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0) with Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(1) ans voila, magic and all that. Simple is great. Mant thanks for for the persistance. Best, scott Jeanette Cunningham wrote: That combo searches using SetupID. From reading through all your previous posts, I assume that the primary key of the form is something other than SetupID. For the code line with ItemData(0) to work, the combo must search on the primary key field of the form. However, if the form has only value for SetupID when it opens, you could try code like this-- Me.cboMoveTo = Me.SetupID Put that one line of code in the Form_Load event. If the form has more than one value for SetupID when it opens, the code above won't work. If it doesn't work, leave the combo blank when the form opens. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia Jeanette, [quoted text clipped - 32 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#20
|
|||
|
|||
Using a Combo Box to Find Records
Are you saying the code came directly from a Microsoft template, with no
alteration to either the code or the forms? If so, I am a little surprised too, but a subtle and seemingly insignificant change can lead to code that no longer works. I suspect this is what occurred, but it doesn't really matter. Any code should be compiled. One reason has to do with references. In the VBA editor, click Tools References. Various libraries are checked. However, different Access applications may use different libraries, or ones beyond the basic libraries that are almost always in use. If code references something in one of these libraries, but the library is not loaded, you will have problems with the code. This brings me to intrinsic constants such as acForm and vbYesNo. The prefix ac means it is a constant defined by the Access library; the vb prefix means the Visual Basic library. They are used in place of numbers. For instance, in Access 2003 (or my version at least), acForm is the number 2. The following should be equivalent: DoCmd.Close acForm, Me.Name DoCmd.Close 2, Me.Name The number 2 is the same as acForm only after DoCmd.Close. In the following code, acToolbarNo is the number 2 also: DoCmd.ShowToolbar "ToolBarName", acToolbarNo The number represented by the constant varies depending on what comes before it, sort of as a street number depends on the street name to define a specific location. It is possible that some numbers will change in the future, or are different in past versions, so the intrinsic constant should be used. Access will translate it to the correct number behind the scenes. Also, it is easier to understand a constant than a number when reading the code. That's about as much as I can say, and some of that is deduced from some experiments I did. Help has more information about constants in general, and intrinsic constants in particular. Scott_Brasted wrote: Bruce My appoligies for being too sensitive. It has been a particularly bad couple of weeks. I appreciate the thoughts on compiling. It makes good sense. It amazes me that MS will write db's with code that does not compile. Also thankyou for the idea on the close button. As I understand what you said, It pays to be more specific in my code. What does the ac in acForm represent? Thank you for all your help and patience. Best, Scott I didn't know you understood Jeanette's suggestion based on what you wrote. You wouldn't be the first person attempting to use a placeholder field or [quoted text clipped - 34 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
Thread Tools | |
Display Modes | |
|
|