Sorry guys

[sir_dan_mitchell]

Sorry guys,

I am still unsure on how to do this Dlookup, i have never done one b4!

Is it in control source I put the calculation.

Here's the exact problem = I want a calculation that will lookup a hospital code, once I enter the hospital name.

I have a seperate table that contains two fields, hospital name and hospital code.

In my form I have a combo box to select the hospital name, but I dont know how to get the code automatically into the code field! My knowledge of access has faded recently as Ive had to do other things, can u please b as simple as possible :O! Does my hospital code field need to b a combo box too? currently its text only.

Thanks

Dan

 

[ColinEssex]

Hi Dan

Perhaps I'm being a bit thick here - not unusual!

Why do you need to look up the hosp Code? why not choose the hospName direct from a comboBox.

Col

 

[sir_dan_mitchell]

thats wot we'll b doing, but we need to store the code too for numerous reasons... :S

 

[afloyd]

I've read through your series of posts with Colin and am not quite sure what you're trying to do. First - is it necessary to display the hospital code on the form your designing.

Second - storage of the hospital codes is done in the table. Where are you entering the hospital codes? On this form or on another form?

I think if you paste the syntax Colin gave you in the control source property of the unbound text box it should return the hospital code that corresponds to the hospital name.

I hope this makes sense.

- AL

 

[The_Doc_Man]

I think I just had an epiphany. That, or a brain cramp. Not sure which.

OK, you want to build a form where someone looks up a hospital from a list of hospitals. You want to store the code number, not the name, of that hospital. Right?

To do this, you must have a table that is

tblHosp
--HospID (for this to work, this MUST be a primary key)
--HospName
--anything else that goes with hospitals but you didn't want to see in the selection box....

OK, now create a combo box using the wizard. Tell it you want to store HospID but see HospName. In this case, the bound column is the first one (with the ID) but it will have 0 width. The second one will be included in the visible part of the drop-down, but since it is the first column that is bound, the name won't be stored.