Hi, Kat.
I'll answer your questions, but you can also consider an alternative approach.
next all I have are text box so what do I do I don't understand on
MyComboBox.Column(1)
My error. I'd assumed your input for SSN was a combo box, which is a
control that gives you a dropdown list of values to choose from to simplify
data entry and control which values are input. If the LimitToList property
is set to Yes, you can only enter values that are in the list. If another
value is entered, it triggers the OnNotInList event, which you can use to
bring up another form to add the new record.
ok explain what you mean by fields of interest in the RowSource of my query
The Combo Box gets its rows from a query statement in its RowSource
property. For example, to include the SSN, FName, LName, and PhoneNumber
fields from an Emp table in a combo box, sorted by LName then FName, the Row
Source would be:
SELECT Emp.SSN, Emp.LName, Emp.FName, Emp.PhoneNumber FROM Emp ORDER BY
Emp.LName, Emp.FName
But you don't need to know SQL syntax to enter a combo box, the wizard will
handle that for you. Toggle the Wizard on in Form Design view from the
Toolbox toolbar (the wand and stars button).
If a column is included in the combo box' RowSource, it can be displayed in
another textbox by using the CB's Column property.
Like any other control, a combo box is Bound to the field specified in its
ControlSource property. If you placed a field on the form from the Field
List, Access sets the ControlSource property for you. Likewise for the combo
box wizard.
The other method is basing your form on a query instead of your table
directly. Start a new query, and include your table and whatever table the
names and phones are in. If you've defined a relationship between them,
there should be a join line across the linking fields. If not, create one by
clicking on one field and dragging it to the other.
Select all the fields you need from your table (including the SSN), then
select the name and address fields you want. Test and save your query, then
open your form in design view, and change the form's RecordSource property to
the name of your query. Now you can place textboxes for the name and address
fields, and they'll show up after you've entered a valid SSN.
Hope that helps.
Sprinks
KAT said:
ok explain what you mean by fields of interest in the RowSource of my query
how do I bind it to SSN?
next all I have are text box so what do I do I don't understand on
MyComboBox.Column(1)
Sprinks said:
Hi, KAT.
I'm going to assume that you don't want to add the name & phone number
redundantly to the form's underlying table, just display the information if
it exists. To do that, include the fields of interest in the RowSource of
your query, and bind it to the SSN. Then use the Column property of the
combo box to display the column values in other textboxes. Column is
0-based--the first column is 0, the 2nd 1, etc.
so if your Last Name field is the second column, set the textbox'
ControlSource to:
=MyComboBox.Column(1)
To handle new additions, set the combo box' LimitToList property to Yes, and
add a Not In List event procedure to insert a new record into the Personnel
table, then display your form to add the new record:
Private Sub cboJobNumber_NotInList(NewData As String, Response As Integer)
' I've assumed your SSN field name is "SSN"
Dim strsql As String, x As Integer
Dim strFrmName As String
Dim strLinkCriteria As String
strFrmName = "YourNewFormName"
x = MsgBox("Do you want to add this record?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into YourTable ([SSN]) values ('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
strLinkCriteria = "[SSN] = '" & NewData & "' "
DoCmd.OpenForm strFrmName, , , strLinkCriteria
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
KAT said:
I have a one table database on my form I have the social security number as
the first input item, if this number is already in the database I want the
last name, first name, address, phn number to auto fill in the text boxes
then the user continues answering the form if the social security number is
not in the database then the user will fill in all the blanks. My question is
how do I make this happen? need help
