combo box question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a novice at MS Access and have a question on creating a combo box.

I have a database that manages contact information (name, address, phone, etc)

I would like to create a combo box that will enable me to see if a contact
already exists in the data base.

I would also like the combo box to change the information in my form if I
make a selection of a contact from the combo box.

I used the wizard to create a Query (called contact lookup) if sorts the
contacts by alpha and shows the contact name address and state in the combo
box. This all works fine but I need to have the form data change when I make
the selection from the combo box. I am not sure if this is the best way to
proceed but it was all that I could come up with. Any help would be greatly
appreciated.

Thanks..........
 
There are two "Source" properties for a combo box.

The Row Source is the source of the data that are displayed in the combo
box's dropdown list. As you note, that usually is a query that returns
various records, each record being one row in the dropdown list.

The Control Source is the field to which the combo box is "bound", meaning
that the value that is in the combo box's "Bound Column" column will be
stored in that field. The field must be one of the field's that are in the
form's Record Source (the source of data for the form).

So, to store your selection, the combo box must be bound to a field in the
form's RecordSource. When you click in the box next to Control Source
(design view of the form, with the combo box selected on the form, and
you're in the Properties window), you can dropdown the list to choose the
appropriate field.
 
Ken,

Thanks for the information.

I selected "name" as my contol source. My row source is the query I spoke
of earlier.

When I return to form view and make a selection from the combo box just the
name field changes. The address, phone, etc fields do not change to reflect
the name selected from the combo box.

The form is not being completely updated based on the selection from the
combo box.

Any Suggesstions.........

Tim
 
Ah-ha... you're wanting to do something different than what I'd thought you
were doing. What you want to do is use a combo box to be a "find the record
in my table".

The easiest thing for you to do is to create a new form that is based on
your table. Then, in design view of the form, with the Wizard icon turned
"on", drag a combo box onto the form. Tell the wizard that you want to use
the combo box to find a record on the form (or words to that effect - I
don't use the wizards and am going from 'memory'); the wizard will set you
up.

Essentially, what you'll have is a combo box in the form's formheader
section. That combo box will move the form to the correct record when you
select an item from the dropdown list.
 
If you use the combo box wizard you will see an option something like "select
a record based on the combo box selection". That should do what you need.
Right now I expect you are making a selection, but there is nothing to tie
that selection to a record.
 
going back to the assumed original question.

if you type in a value in the combo box..what code is used to store the
typed value in a new record in the related table?
 
You would use the NotInList event to add the data to another table through a
form. Here's a generic function I use so that I can just pass it the form
name, the field name, the data, and the response constant. The data is
passed in the OpenArgs argument:

Public Sub AddToList(strFormName, strItem, NewData, Response)
'---------------------------------------------------------------------------
------------
' Procedure : AddToList
' Date : 10/31/2004 Revised
' Author : Arvin Meyer
' Purpose : Generic Not In List Code
' Usage : AddToList "YourFormName", "YourComboItem", NewData, Response
'---------------------------------------------------------------------------
------------
On Error GoTo Error_Handler

Dim intNewItem As Integer
Dim strMsgText As String
Dim intMsgArg As Integer
Dim strTitle As String

strMsgText = "This " & strItem & " is not in the list. Do you want to
add a new " & strItem & "?"
intMsgArg = vbYesNo + vbQuestion + vbDefaultButton1
strTitle = "Not In This List"
intNewItem = MsgBox(strMsgText, intMsgArg, strTitle)

If intNewItem = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm strFormName, acNormal, , , acFormAdd, acDialog,
NewData
Response = acDataErrAdded
End If

Exit_Here:
Exit Sub

Error_Handler:
Call ErrorLog("basUtilities", "AddToList")
Resume Exit_Here

End Sub


Public Function ErrorLog(objName As String, routineName As String)

Open "C:\Error.log" For Append As #1

Print #1, Format(Now, "mm/dd/yyyy, hh:nn:ss") & ", " & db.Name & vbCrLf & _
"An error occured in: " & objName & ", Procedure: " & routineName &
vbCrLf & _
"User: " & CurrentUser() & ", Error#: " & Err.Number & ": " &
Err.Description

Close #1
End Function

Here's a sample of the way it's used:

Private Sub cboLocationID_NotInList(NewData As String, Response As Integer)

AddToList "frmLocations", "Location", NewData, Response

End Sub

And here's some code for frmLocations:

If Me.NewRecord = True Then
Me.txtLocationName = Me.OpenArgs
End If
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top