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
|
|||
|
|||
Using a Combo Box to Find Records
Greetings,
I have been reading Allen Browne's Access website and I just tried the trick to use a combo box to find records on a form. It works great except that the cbo is empty when the form opens. The form and subform have the right records, but I have to check the correct entry in the cbo to have it display the entry data. After that it works until I close and reopen the form. Does anyone know what I can do to correct this? Thanks, Scott |
#2
|
|||
|
|||
Using a Combo Box to Find Records
Hi Scott,
Here is a way to do it. In the form's load or open event put a line of code like this-- Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0) The code sets the combo to show the first row from the list. If the first row of the combo is not the one that matches what the form shows, you can add an extra line-- Call Me.[NameOfCombo]_AfterUpdate Replace NameOfCombo of your combo. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Scott_Brasted" u56211@uwe wrote in message news:9f2eceffbb760@uwe... Greetings, I have been reading Allen Browne's Access website and I just tried the trick to use a combo box to find records on a form. It works great except that the cbo is empty when the form opens. The form and subform have the right records, but I have to check the correct entry in the cbo to have it display the entry data. After that it works until I close and reopen the form. Does anyone know what I can do to correct this? Thanks, Scott |
#3
|
|||
|
|||
Using a Combo Box to Find Records
Oops,
that extra line is wrong. It should read Call [NameOfCombo]_AfterUpdate Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Jeanette Cunningham" wrote in message ... Hi Scott, Here is a way to do it. In the form's load or open event put a line of code like this-- Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0) The code sets the combo to show the first row from the list. If the first row of the combo is not the one that matches what the form shows, you can add an extra line-- Call Me.[NameOfCombo]_AfterUpdate Replace NameOfCombo of your combo. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Scott_Brasted" u56211@uwe wrote in message news:9f2eceffbb760@uwe... Greetings, I have been reading Allen Browne's Access website and I just tried the trick to use a combo box to find records on a form. It works great except that the cbo is empty when the form opens. The form and subform have the right records, but I have to check the correct entry in the cbo to have it display the entry data. After that it works until I close and reopen the form. Does anyone know what I can do to correct this? Thanks, Scott |
#4
|
|||
|
|||
Using a Combo Box to Find Records
Good morning Jeanette,
Thanks for the reply. I woke up this morning thinking that I needed a form_current or on_open event, I just did not know wat to write. I am very new to vb in Access. When I put your code in the open or current event, the cbo puts the field name for the id key (setupID) in and then gives me an error that says "The value you entered isn't valid for this field . . ." When I add the 2nd line of code, it is red in the code window and throw an even more ominous error messsage. Any thoughts? Best, Scott Jeanette Cunningham wrote: Oops, that extra line is wrong. It should read Call [NameOfCombo]_AfterUpdate Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia Hi Scott, Here is a way to do it. [quoted text clipped - 28 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#5
|
|||
|
|||
Using a Combo Box to Find Records
The Current event will run every time you navigate to a record. That may not
be what you need. To set a value initially I would use the Load event. Open may work, too, but I tend to regard the Load event as the first opportunity to work with data. If you are checking the value of a field, Open is too soon. I'm not sure why Jeanette suggested calling the combo box After Update code. Perhaps there is something in the code at Allen Browne's web site of which she has specific knowledge I am lacking, but in any case you need to compile the code if you have not done so (Debug Compile). You speak of adding a second line of code, but I don't see where Jeanette suggested that, so it is not clear to me what you did. It would help if you post the actual code, as well as the text of any error messages. There is no way to know what you would regard as ominous. Scott_Brasted wrote: Good morning Jeanette, Thanks for the reply. I woke up this morning thinking that I needed a form_current or on_open event, I just did not know wat to write. I am very new to vb in Access. When I put your code in the open or current event, the cbo puts the field name for the id key (setupID) in and then gives me an error that says "The value you entered isn't valid for this field . . ." When I add the 2nd line of code, it is red in the code window and throw an even more ominous error messsage. Any thoughts? Best, Scott Oops, that extra line is wrong. [quoted text clipped - 9 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#6
|
|||
|
|||
Using a Combo Box to Find Records
Buce, thanks for the reply.
Here is the code for the cbo: Private Sub CboMoveTo_AfterUpdate() Dim rs As DAO.Recordset If Not IsNull(Me.cboMoveTo) Then 'Save before move. If Me.Dirty Then Me.Dirty = False End If 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[SetupID] = " & Me.cboMoveTo If rs.NoMatch Then MsgBox "Not found: filtered?" Else 'Display the found record in the form. Me.Bookmark = rs.Bookmark End If Set rs = Nothing End If End Sub As I said it works, as is but show a blank box when the form opens. Jeanette's code: Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0) Jeanette's second respone had the following comment: Oops, that extra line is wrong. It should read Call [NameOfCombo]_AfterUpdate None of this does anything to the cbo. When I try to compile, I get errow I cannot understand. The code the compiler stosa at is MS code. It came with the db and is part of the switchboard. Here is the code that contans the place wher the compiler stops: Private Sub cmdExit_GotFocus() Dim intOption As Integer 'If the Exit Button has received the focus, turn off the focus on all the menu options For intOption = 1 To conNumButtons Me("Option" & intOption).Visible = False Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal Next intOption ExitLabel.FontUnderline = True End Sub ExitLable is highlighted by the compiler. Best, Scott BruceM wrote: The Current event will run every time you navigate to a record. That may not be what you need. To set a value initially I would use the Load event. Open may work, too, but I tend to regard the Load event as the first opportunity to work with data. If you are checking the value of a field, Open is too soon. I'm not sure why Jeanette suggested calling the combo box After Update code. Perhaps there is something in the code at Allen Browne's web site of which she has specific knowledge I am lacking, but in any case you need to compile the code if you have not done so (Debug Compile). You speak of adding a second line of code, but I don't see where Jeanette suggested that, so it is not clear to me what you did. It would help if you post the actual code, as well as the text of any error messages. There is no way to know what you would regard as ominous. Good morning Jeanette, [quoted text clipped - 16 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#7
|
|||
|
|||
Using a Combo Box to Find Records
Jeanette did not know the name of your combo box, so she used a generic name
NameOfCombo, and the instruction "Replace NameOfCombo of your combo". I will admit this is a bit cryptic. The idea is that you would substitute the actual name of your combo box: Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0) This should show you the first item on the list. Try placing the code in the form's Load event. If this is not what you want, please specify what you do want. As for the compile error, I understand you to be saying this is the troublesome line of code: ExitLabel.FontUnderline = True This underlines the font in ExitLabel, which is presumably a label, probably next to the Exit button. If there is no such label (as I suspect), delete the code or rename the label (if it is now named something else). It seems the code in the Exit button's Got Focus event is looping through controls named Option1, Option2, etc., and OptionLabel1, OptionLabel2, etc., making Option1 etc. invisible, and changing the label font to normal weight. It does this for the number of times represented by the variable conNumButtons, which presumably is a constant somewhere in the code. You could delete the entire Got Focus code and not do any harm I can see. All it does is some formatting and stuff like that. Or comment it out by placing an apostrophe in front of each line of code. A compile error contains some sort of description. Like any error about which you are seeking help, post the full message you receive. In this case I think I can guess what is going on, but the less guessing responders have to do the sooner you will receive a targeted response. Scott_Brasted wrote: Buce, thanks for the reply. Here is the code for the cbo: Private Sub CboMoveTo_AfterUpdate() Dim rs As DAO.Recordset If Not IsNull(Me.cboMoveTo) Then 'Save before move. If Me.Dirty Then Me.Dirty = False End If 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[SetupID] = " & Me.cboMoveTo If rs.NoMatch Then MsgBox "Not found: filtered?" Else 'Display the found record in the form. Me.Bookmark = rs.Bookmark End If Set rs = Nothing End If End Sub As I said it works, as is but show a blank box when the form opens. Jeanette's code: Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0) Jeanette's second respone had the following comment: Oops, that extra line is wrong. It should read Call [NameOfCombo]_AfterUpdate None of this does anything to the cbo. When I try to compile, I get errow I cannot understand. The code the compiler stosa at is MS code. It came with the db and is part of the switchboard. Here is the code that contans the place wher the compiler stops: Private Sub cmdExit_GotFocus() Dim intOption As Integer 'If the Exit Button has received the focus, turn off the focus on all the menu options For intOption = 1 To conNumButtons Me("Option" & intOption).Visible = False Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal Next intOption ExitLabel.FontUnderline = True End Sub ExitLable is highlighted by the compiler. Best, Scott The Current event will run every time you navigate to a record. That may not be what you need. To set a value initially I would use the Load event. Open [quoted text clipped - 17 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#8
|
|||
|
|||
Using a Combo Box to Find Records
Bruce,
Without getting frustrated or upset, I know what Jeanette wanted me to do with the code. I changed the cbo to the name I have in my form when I got her answer. The code she suggested is not doing anything and I am tryng to figure out why. I have it in the on_load event and I have an empty cbo when I open the form. As I mentioned, the data is correct and I can choose an entry to click on and that will be correct also. Everything seems fine except for the empty cbo. Here is the entire code for that form: Option Compare Database Option Explicit Private Sub CboMoveTo_AfterUpdate() Dim rs As DAO.Recordset If Not IsNull(Me.cboMoveTo) Then 'Save before move. If Me.Dirty Then Me.Dirty = False End If 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[SetupID] = " & Me.cboMoveTo If rs.NoMatch Then MsgBox "Not found: filtered?" Else 'Display the found record in the form. Me.Bookmark = rs.Bookmark End If Set rs = Nothing End If End Sub Private Sub cmdCloseCampaignPledgeSummaryForm_Click() On Error GoTo Err_cmdCloseCampaignPledgeSummaryForm_Click DoCmd.Close Exit_cmdCloseCampaignPledgeSummaryForm_C: Exit Sub Err_cmdCloseCampaignPledgeSummaryForm_Click: MsgBox Err.Description Resume Exit_cmdCloseCampaignPledgeSummaryForm_C End Sub Private Sub cboMoveTo_OnLoad() Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0) End Sub By the way,I just did as you suggested and commented out the code for the label the switchboard wtill works fine and the code compiled in a few seconds. Can you explain what happens when I compile the code? Does the db work differently or better? Thanks, Scott BruceM wrote: Jeanette did not know the name of your combo box, so she used a generic name NameOfCombo, and the instruction "Replace NameOfCombo of your combo". I will admit this is a bit cryptic. The idea is that you would substitute the actual name of your combo box: Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0) This should show you the first item on the list. Try placing the code in the form's Load event. If this is not what you want, please specify what you do want. As for the compile error, I understand you to be saying this is the troublesome line of code: ExitLabel.FontUnderline = True This underlines the font in ExitLabel, which is presumably a label, probably next to the Exit button. If there is no such label (as I suspect), delete the code or rename the label (if it is now named something else). It seems the code in the Exit button's Got Focus event is looping through controls named Option1, Option2, etc., and OptionLabel1, OptionLabel2, etc., making Option1 etc. invisible, and changing the label font to normal weight. It does this for the number of times represented by the variable conNumButtons, which presumably is a constant somewhere in the code. You could delete the entire Got Focus code and not do any harm I can see. All it does is some formatting and stuff like that. Or comment it out by placing an apostrophe in front of each line of code. A compile error contains some sort of description. Like any error about which you are seeking help, post the full message you receive. In this case I think I can guess what is going on, but the less guessing responders have to do the sooner you will receive a targeted response. Buce, thanks for the reply. [quoted text clipped - 58 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#9
|
|||
|
|||
Using a Combo Box to Find Records
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 "Scott_Brasted via AccessMonster.com" u56211@uwe wrote in message news:9f36ed60806c5@uwe... Bruce, Without getting frustrated or upset, I know what Jeanette wanted me to do with the code. I changed the cbo to the name I have in my form when I got her answer. The code she suggested is not doing anything and I am tryng to figure out why. I have it in the on_load event and I have an empty cbo when I open the form. As I mentioned, the data is correct and I can choose an entry to click on and that will be correct also. Everything seems fine except for the empty cbo. Here is the entire code for that form: Option Compare Database Option Explicit Private Sub CboMoveTo_AfterUpdate() Dim rs As DAO.Recordset If Not IsNull(Me.cboMoveTo) Then 'Save before move. If Me.Dirty Then Me.Dirty = False End If 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[SetupID] = " & Me.cboMoveTo If rs.NoMatch Then MsgBox "Not found: filtered?" Else 'Display the found record in the form. Me.Bookmark = rs.Bookmark End If Set rs = Nothing End If End Sub Private Sub cmdCloseCampaignPledgeSummaryForm_Click() On Error GoTo Err_cmdCloseCampaignPledgeSummaryForm_Click DoCmd.Close Exit_cmdCloseCampaignPledgeSummaryForm_C: Exit Sub Err_cmdCloseCampaignPledgeSummaryForm_Click: MsgBox Err.Description Resume Exit_cmdCloseCampaignPledgeSummaryForm_C End Sub Private Sub cboMoveTo_OnLoad() Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0) End Sub By the way,I just did as you suggested and commented out the code for the label the switchboard wtill works fine and the code compiled in a few seconds. Can you explain what happens when I compile the code? Does the db work differently or better? Thanks, Scott BruceM wrote: Jeanette did not know the name of your combo box, so she used a generic name NameOfCombo, and the instruction "Replace NameOfCombo of your combo". I will admit this is a bit cryptic. The idea is that you would substitute the actual name of your combo box: Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0) This should show you the first item on the list. Try placing the code in the form's Load event. If this is not what you want, please specify what you do want. As for the compile error, I understand you to be saying this is the troublesome line of code: ExitLabel.FontUnderline = True This underlines the font in ExitLabel, which is presumably a label, probably next to the Exit button. If there is no such label (as I suspect), delete the code or rename the label (if it is now named something else). It seems the code in the Exit button's Got Focus event is looping through controls named Option1, Option2, etc., and OptionLabel1, OptionLabel2, etc., making Option1 etc. invisible, and changing the label font to normal weight. It does this for the number of times represented by the variable conNumButtons, which presumably is a constant somewhere in the code. You could delete the entire Got Focus code and not do any harm I can see. All it does is some formatting and stuff like that. Or comment it out by placing an apostrophe in front of each line of code. A compile error contains some sort of description. Like any error about which you are seeking help, post the full message you receive. In this case I think I can guess what is going on, but the less guessing responders have to do the sooner you will receive a targeted response. Buce, thanks for the reply. [quoted text clipped - 58 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#10
|
|||
|
|||
Using a Combo Box to Find Records
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). Scott_Brasted wrote: Bruce, Without getting frustrated or upset, I know what Jeanette wanted me to do with the code. I changed the cbo to the name I have in my form when I got her answer. The code she suggested is not doing anything and I am tryng to figure out why. I have it in the on_load event and I have an empty cbo when I open the form. As I mentioned, the data is correct and I can choose an entry to click on and that will be correct also. Everything seems fine except for the empty cbo. Here is the entire code for that form: Option Compare Database Option Explicit Private Sub CboMoveTo_AfterUpdate() Dim rs As DAO.Recordset If Not IsNull(Me.cboMoveTo) Then 'Save before move. If Me.Dirty Then Me.Dirty = False End If 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[SetupID] = " & Me.cboMoveTo If rs.NoMatch Then MsgBox "Not found: filtered?" Else 'Display the found record in the form. Me.Bookmark = rs.Bookmark End If Set rs = Nothing End If End Sub Private Sub cmdCloseCampaignPledgeSummaryForm_Click() On Error GoTo Err_cmdCloseCampaignPledgeSummaryForm_Click DoCmd.Close Exit_cmdCloseCampaignPledgeSummaryForm_C: Exit Sub Err_cmdCloseCampaignPledgeSummaryForm_Click: MsgBox Err.Description Resume Exit_cmdCloseCampaignPledgeSummaryForm_C End Sub Private Sub cboMoveTo_OnLoad() Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0) End Sub By the way,I just did as you suggested and commented out the code for the label the switchboard wtill works fine and the code compiled in a few seconds. Can you explain what happens when I compile the code? Does the db work differently or better? Thanks, Scott Jeanette did not know the name of your combo box, so she used a generic name NameOfCombo, and the instruction "Replace NameOfCombo of your combo". I will [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 | |
|
|