Combo box - auto select record to table-based rowsource.
in [Getting Started]
|
From: Rolls on 17 Feb 2007 19:18 I have a form with a combo box. The rowsource is a table. A button to add a new record opens another form to input one record. A button that form closes it. How do I get the record just entered into the combo box so I don't have to go through the process of selecting it on the first form after I just entered it?
From: Ken Sheridan on 18 Feb 2007 10:50 In the AfterInsert event procedure of the second form requery the combo box on the first form and set its value to the values just entered in the second form, e.g. Dim ctrl As Control On Error Resume Next Set ctrl = Forms("YourFirstForm")("YourComboBox") If Err.Number = 0 Then ctrl.Requery ctrl = Me.YourField End If The error handling caters for the second form being used when the first is not open. You can however use the NotInList event procedure of the combo box to insert the new row into the table by typing the value directly into the combo box. If you are only entering a value into one column in the table, the one whose value is shown in the combo box, then you can do it without having to open a second form. Here's an example of code which does this when adding a new city name to a combo box: Private Sub cboCities_NotInList(NewData As String, Response As Integer) Dim cmd As ADODB.Command Dim ctrl As Control Dim strSQL As String, strMessage As String Set ctrl = Me.ActiveControl strMessage = "Add new city to list?" strSQL = "INSERT INTO Cities(City) VALUES(""" & _ NewData & """)" Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandType = adCmdText If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then cmd.CommandText = strSQL cmd.Execute Response = acDataErrAdded Else Response = acDataErrContinue ctrl.Undo End If Set cmd = Nothing End Sub And here's the same, but this time opening a second form, frmCities so that other data than the city name can be added in the new row in the Cities table, e.g. county or state. The value typed into the combo box (the new city name) is passed to the frmCities form as its OpenArgs property. Note that the frmCities form is opened in dialogue mode; this causes the code execution in the combo box's NotInList event procedure to pause until frmCities is closed: Private Sub cboCities_NotInList(NewData As String, Response As Integer) Dim ctrl As Control Dim strMessage As String Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then DoCmd.OpenForm "frmCities", _ DataMode:=acFormAdd, _ WindowMode:=acDialog, _ OpenArgs:=NewData ' ensure frmCities closed DoCmd.Close acForm, "frmCities" ' ensure city has been added If Not IsNull(DLookup("CityID", "Cities", "City = """ & _ NewData & """")) Then Response = acDataErrAdded Else strMessage = NewData & " was not added to Cities table." MsgBox strMessage, vbInformation, "Warning" Response = acDataErrContinue ctrl.Undo End If Else Response = acDataErrContinue ctrl.Undo End If End Sub With the second of these the following code goes in the frmCities form's Open event procedure to set the DefaultValue property of its City text box control to the Private Sub Form_Open(Cancel As Integer) If Not IsNull(Me.OpenArgs) Then Me.City.DefaultValue = """" & Me.OpenArgs & """" End If End Sub Note that when setting the DefaultValue property of a control it should be wrapped in literal quotes as above regardless of its data type as this property is always a string expression. BTW rather than use a table as the RowSource property its usually better to use a sorted query based on the table as this enables you to sort the combo box's list, e.g. in the above case the RowSource would be: SELECT CityID, City FROM Cities ORDER BY City; The query does not have to be saved, the RowSource property can be, and usually is, the SQL statement. I should point out that this example is simplified a little for the sake of clarity. As in real life city names can be duplicated you might want to add a city name which already is listed as a new row in Cities with a different CityID. What I'd do in a real life scenario would be to also include the region (and the country if its an international database) in which the city is located as another column in the combo box's list, and in addition to using the above code also allow the user to open the frmCities form directly by double-clicking the combo box to add a new city with the same name as one already in the Cities table, e.g. Paris, France as well as Paris, Texas. Ken Sheridan Stafford, England "Rolls" wrote: > I have a form with a combo box. The rowsource is a table. A button to add > a new record opens another form to input one record. A button that form > closes it. How do I get the record just entered into the combo box so I > don't have to go through the process of selecting it on the first form after > I just entered it? > > >
From: Rolls on 18 Feb 2007 16:09 Thanks - This problem occurs with single and multiple field appends. Would prefer DAO if possible.
From: Rolls on 18 Feb 2007 17:32 The rowsource is, as you say, a sorted query based on a table. The table may have one or more fields. All records have unique combinations of the fields in them. Common examples are names: NameID Prefix FirstName MiddleName LastName Suffix addresses: AddressID Line1 Line2 City State Country PostalCode I've worked with Access for many years, but don't often write data entry forms. Usually the data already exists in electronic form before I receive it. This time it's entered manually. On frmMain I have cboAddress which selects one of the addresses and displays Line1. Multiple textboxes display the remaining fields using the column() property. When a new address needs to be added cmdAddAddress (button) opens frmAddAddress which appends a record to tblAddress. Then a close button returns to frmMain. The code I'm looking for requeries frmMain!cboAddress then sets itself to the record just added, as if the user had looked it up and selected it, saving this last step of looking up what has just been keyed in.. Controls are bound, the method of coding is usually the oldest most primative method, DoCmd macros, then DAO, then ADO recordsets because users don't have skills to maintain VBA code. Am not looking for a "best practice" which would probably be using unbound forms with the latest object model and a common code module. I'm looking for a fast method with as little code as possible. So if I have followed your instructions: Dim ctrl As Control On Error Resume Next Set ctrl = Forms!frmMain!cboAddress If Err.Number = 0 Then ctrl.Requery ctrl = Me.AddressID End If Thanks - George - Houston
From: onedaywhen on 19 Feb 2007 04:56
On Feb 18, 3:50 pm, Ken Sheridan <KenSheri...(a)discussions.microsoft.com> wrote: > I should point out that this example is simplified a little for the sake of > clarity. As in real life city names can be duplicated you might want to add > a city name which already is listed as a new row in Cities with a different > CityID. What I'd do in a real life scenario would be to also include the > region (and the country if its an international database) in which the city > is located Yes and remember, unlike your seemingly fabricated CityID (point about simplicity noted), there are standards for these: ISO 3166-2:GB administrative divisions of the UK (e.g. Staffordshire = 'GB-STS'): http://en.wikipedia.org/wiki/ISO_3166-2:GB UK Internal [Country] Code (e.g. England = 1) http://www.govtalk.gov.uk/gdsc/html/frames/UKinternalCode.htm ISO 3166-1 alpha-3 country codes (e.g. United Kingdom of Great Britain and Northern Ireland = 'GBR'): http://en.wikipedia.org/wiki/ISO_3166-1_alpha-3 Jamie. -- |