Auto Populate expression not working HELP!

[mdavistke]

I am attempting to auto populate 3 fields and having precisely ZERO success. I have 2 tables and 2 forms. Table and Form One are titled "Face Sheet" and has fields for DoctorID, Doctor Name, Doctor Address, and Doctor Phone. Table 2 is Doctor List, Form 2 is Doctors. They contain DoctorID, Doctor Name, Doctor Address, and Doctor Phone. I want to enter the Doctor ID (the only field recorded by the Face Sheet table) and auto populate the name, address, and phone on the Face Sheet form for printing purposes. I have created a combo box (DoctorID) and the corresponding query that looks up all the doctor values. I am entering the following expression in the control source of the text box I want to fill:

=Forms!FaceSheet!DoctorID.column(1).

Note: I have tried several numbers in the parentheses to no avail
Immediate help is requested, am about to pull out hair!

Matt

 

[pcEars]

First: Is there a good reason to store the same info in 2 different tables? Ordinarily that only creates problems.

Secondly: if you want this for printing purposes, you would probably be better off using a report rather than a form.

 

[mdavistke]

Answers

The Face Sheet table stores only the Doctor ID the Doctor table stores the actual information about the doctor. When an employee is filling out the face sheet form I want them to be able to input the doctor ID number and auto populate the rest of the information in the form.

 

[bpaquette]

I don't know if htis will help, but



Private Sub cboSSAN_AfterUpdate()

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "SSAN = '" & cboSSAN & "'"

Me.Bookmark = rs.Bookmark

End Sub



this is the code someone gave me when i was trying to do the same thing. the cboSSAN drew from tblPersonnel, and when the user selected a social security #, it updates all of the other information on the form. it's a little buggy but its a start(?)

 

[pcEars]

First you said: "Table and Form One are titled "Face Sheet" and has fields for DoctorID, Doctor Name, Doctor Address, and Doctor Phone."

Now you say: "The Face Sheet table stores only the Doctor ID "

The question I have is: If the Face Sheet table stores only the Doctor ID, and Doctor List contains DoctorID, Doctor Name, Doctor Address, and Doctor Phone... then why do you have 2 tables when 1 should suffice? Is it solely to implement an autolookup query?

I'm certainly NOT an expert, but the logic evades me.

 

[pcEars]

On another re-read of your original post, this occurs to me...

your combobox, is bound to a query, which I suppose returns the results you want to populate the other form controls... Then perhaps:

=Forms!FaceSheet!DoctorID.column(0)

will return what you want if DoctorID is the 1st column in your query, and your query otherwise works as an autolookup.

 

[mdavistke]

Strange

Ok, did the expression builder code you asked me to put in, which was the one I was putting in before, but this time I changed the boxes I wanted to fill to combo boxes. This time they filled up according to the column I specified. Very strange. Can I get them to auto populate as text boxes?