drop list to fill in the blanks on a form

[cwhite]

i want to create a form where the user can choose from a list of names
in table A

associated with this name is a telephone and address

on the form there will be a drop list for the name, fields for address
and telephone and a comment section

i would like the form to fill in the address and telephone
automatically once the name is chosen, (the only field the user can
actually enter anything into is the comment section) the information in
the form will be saved to table B

getting it to select the name from table A is easy, but how do I get it
to automatically enter the associated information for the other 2
fields

thanks

[J-P-W]

Firstly, you might not want too! Access being a relational database
means that you only need to reference the full details but show them
in, for example, a child / parent record.

Or if you actually want to do what you ask, have a combo box with the
name as the first column(0) and then have the next coluns for the other
data, address and so on. Then use the after update event for the combo
to populate the other boxes from the combo:

Private Sub cmbContact_Afte rUpdate()

Me.ContactName = Me.cmbContact.C olumn(0)
Me.ContactAddre ss1 = Me.cmbContact.C olumn(1)
Me.ContactAddre ss2 = Me.cmbContact.C olumn(2)
Me.ContactPhone = Me.cmbContact.C olumn(3)

'and so on..........

Jon

[cwhite]

this gives me something to chew on, i'll give it a try tomorrow

thanks

[cwhite]

I gave it a try and I keep getting the following error message:

run time error '2135':
this property is read only and can't be set

not sure what is going on but I think I'm on the right track now

[cwhite]

I figured out what was happening, I was using "Name" for one of boxes,
once i changed it to something else (Client) the errors stopped.

Thanks again.

[Larry Linson]

"cwhite" wrote

I figured out what was happening, I was using
"Name" for one of boxes, once i changed it to
something else (Client) the errors stopped.

Quite predictably and quite intuitively, you cannot have two objects in the
same scope with the same name.

Not quite so predictably but intuitively once you think about it, you should
not be using "Name" as the name of anything, e.g., Field in a Record, or
Control on a Form or Report, or any Access object? Why not? Because Name is
an Access reserved word... many objects have a "Name" property and it isn't
always possible for Access to determine from context whether you intend to
use the object you have named "Name" or Access' property named "Name".

That's one of the reasons you find people following some naming conventions
that may appear strange to you -- Access has no reserved words that begin
with "txt" or "cbo", for example.

Larry Linson
Microsoft Access MVP

[cwhite]

I've noticed something odd about the form I created with your
assistance.

It selects and enters the information into the boxes just fine, however
it seems to automatically save whatever is selected.

The second you exit the form it saves the info, if you select a
different person becuase the first one was wrong, it will save the
first one., If you start to make a change to the selected client it
will save the information.

I've never created a form that pulls info from one table and puts it in
another before so I think I am missing something. Normally I would
make a save, clear and exit buttons and they would function properly,
but it won't work with this form.

here are the codes for the button and selection:

Private Sub cmbClient_After Update()

Me.Client = Me.cmbClient.Co lumn(0)
Me.SIN = Me.cmbClient.Co lumn(1)
Me.Telephone = Me.cmbClient.Co lumn(2)
Me.Location = Me.cmbClient.Co lumn(3)
Me.Counsellor = Me.cmbClient.Co lumn(4)

End Sub
Private Sub Save_Btn_Click( )
On Error GoTo Err_Save_Btn_Cl ick
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.GoToRecor d , , acNewRec

Exit_Save_Btn_C lick:
Exit Sub

Err_Save_Btn_Cl ick:
MsgBox Err.Description
Resume Exit_Save_Btn_C lick

End Sub
Private Sub Clear_Btn_Click ()
On Error GoTo Err_Clear_Btn_C lick
DoCmd.GoToRecor d , , acNewRec

Exit_Clear_Btn_ Click:
Exit Sub

Err_Clear_Btn_C lick:
MsgBox Err.Description
Resume Exit_Clear_Btn_ Click

End Sub
Private Sub Exit_Btn_Click( )
On Error GoTo Err_Exit_Btn_Cl ick
DoCmd.Close

Exit_Exit_Btn_C lick:
Exit Sub

Err_Exit_Btn_Cl ick:
MsgBox Err.Description
Resume Exit_Exit_Btn_C lick

End Sub

the problem has to be with the combo box but I don't know what it is.

Any help/ideas would be greatly appreciated.

Thanks

[J-P-W]

It is saving the data because the fields (Client; Sin and so on) have
now got data (or the data has changed), they will be saved by default
when you leave the record. If you don't want to save the data anywhere
then use an unbound field

Me.Text1.Text = Me.cmbClient.Co lumn(0)

Jon

[cwhite]

thanks for all of your input, I haven't been back to that particular
location yet so I haven't had a chance to give it a try

I'll let you know how it works out, but I don't forsee any problems

Thanks!!!