Combo box - auto select record to table-based rowsource.

  • Thread starter Thread starter Rolls
  • Start date Start date
R

Rolls

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?
 
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
 
Thanks -

This problem occurs with single and multiple field appends.

Would prefer DAO if possible.
 
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
 
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.

--
 
The answer turned out to be:

frmMain.cboAddress appended by:

frmAddAddress

After Update event:

forms!frmMain.cboAddress = AddressID
forms!frmMain!cboAddress.Requery
 
Back
Top