Access form Newbie needs help!

[IanNav]

Hi,

I'm sure this is a very simple question to answer. I am a programmer, but am trying to show an untechnical user how to do something in Access. I would normally write the whole thing in VBA, but don't have the time to do it nor want to baffle them with the science.

Can someone tell me the easyiest way to do the following (without using alot of VBA).

I have a form with 2 controls on it.

1) cbName (Combo box)
2) txtId (text box)

and a table which has 4 fields.

(1) PersonID (Autonumber)
(2) First name
(3) Surname
(4) Depaertment


The result i would like to achieve is.

Click on the cbname combobox and select a person (value list formatted as ["first" & "surname"] with dept = "admin" sorted in dsnd order by surname) then once one has been selected the ID corresponding to that name will change in the text box.

eg.

id fname sname dept
0 joe bloggs it
1 jane dough admin
2 john smith admin
3 dave jones sales


select "joe bloggs" in combo box, textbox = "0"
select "dave jones" in combo box, textbox = "3"

etc etc..

very simple i know! i just can't do it easily with the access tools...

Many thanks

Ian

 

[lupins46]

If the textbox is unbound then all you need to do is to set it to the value of the combo box
Controlsource:
=forms!formname!cbocomboname

This assumes that the first column of the rowsource of the combo is the id (if you have created the combo with the wizard this is probably true - although it will be hidden)

 

[IanNav]

I tried that, and i only get the ID of the item on the list, not the ID in column 0.

cbbox id in table fname sname dept
0 4 joe boggs it
1 6 jane dough admin
2 24 john smith admin
3 15 dave jones sales

i've got 3 columns in my combo box, "ID in table", Firstname and surname.

column widths = 0cm;2.5cm;2.5cm (hidden column as you said).

am i making sense?

 

[IanNav]

I have mocked up an eg as an mdb file. can i send it to you? whats your email? are you on MSN? cos it would be easier chat. Thanks

 

[HarleyQuinn]

I think this might be what you are after:

Private Sub Combo11_Click()
YourTextBox.Value = cbbox.ListIndex
End Sub

That gives you the items value in the list.
Is this what you want???

Harleyquinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[IanNav]

no, its not what i mean. thanks for replying though.

basically and simply i want to select a value from the combo box, then the id of the record i selected appears in the text box, not "2" as i am getting.


Many Thanks.


so in the example above i select "John Smith" and i get "24" in my text box

 

[IanNav]

apoligies for the last post! the copy/paste cursor went a bit crazy

---------------------------------------------

no, its not what i mean. thanks for replying though.

basically and simply i want to select a value from the combo box, then the id of the record i selected appears in the text box

Many Thanks.


so in the example above i select "John Smith" and i get "24" in my text box not "2" as i am getting.

 

[lupins46]

I'm not really sure what the problem is here but try this:

=forms!formname!cbocomboname.column(0)

 

[PHV]

Me!txtId = Me!cbName.Column(1) & " " & Me!cbName.Column(2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[IanNav]

i've sorted it now thanks guys.

i used the column function in the control source.

Sorry to waste anyones time.

this was useful.