By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

drop list to fill in the blanks on a form

P: n/a
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


Feb 27 '06 #1
Share this Question
Share on Google+
8 Replies

P: n/a
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_AfterUpdate()

Me.ContactName = Me.cmbContact.Column(0)
Me.ContactAddress1 = Me.cmbContact.Column(1)
Me.ContactAddress2 = Me.cmbContact.Column(2)
Me.ContactPhone = Me.cmbContact.Column(3)

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


Feb 27 '06 #2

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


Feb 28 '06 #3

P: n/a
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

Mar 1 '06 #4

P: n/a
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.

Mar 1 '06 #5

P: n/a
"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

Mar 6 '06 #6

P: n/a
I've noticed something odd about the form I created with your

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_AfterUpdate()

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

End Sub
Private Sub Save_Btn_Click()
On Error GoTo Err_Save_Btn_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
DoCmd.GoToRecord , , acNewRec

Exit Sub

MsgBox Err.Description
Resume Exit_Save_Btn_Click

End Sub
Private Sub Clear_Btn_Click()
On Error GoTo Err_Clear_Btn_Click
DoCmd.GoToRecord , , acNewRec

Exit Sub

MsgBox Err.Description
Resume Exit_Clear_Btn_Click

End Sub
Private Sub Exit_Btn_Click()
On Error GoTo Err_Exit_Btn_Click

Exit Sub

MsgBox Err.Description
Resume Exit_Exit_Btn_Click

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.


Mar 8 '06 #7

P: n/a
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.Column(0)


Mar 11 '06 #8

P: n/a
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


Mar 20 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.