Need help with code to refresh form 1

[JillianM]

I don't have much in the way of coding skills, so hang in there with me... I have a form. I have a combo box in that form with a number of fields. The problem is, when I select something from the drop down only some of the fields update in the form. I either have to refresh manually, or leave and return to the record to see all of my fields.

My current code is:

Private Sub Combo70_AfterUpdate()
Me.FIRSTNAME = Me.Combo70.Column(2)
Me.Profession = Me.Combo70.Column(3)
Me.NPI = Me.Combo70.Column(4)
Me.LIC = Me.Combo70.Column(5)
Me.LICState = Me.Combo70.Column(6)
Me.ADDRESS1 = Me.Combo70.Column(7)
Me.Address2 = Me.Combo70.Column(8)
Me.City = Me.Combo70.Column(9)
Me.State = Me.Combo70.Column(10)
Me.Zip = Me.Combo70.Column(11)
Me.EMAILADD = Me.Combo70.Column(12)
Me.PHONe = Me.Combo70.Column(13)
Me.SURGEON = Me.Combo70.Column(14)
End Sub

Can someone give me some code or a tip to have everything refresh when I select something from the drop down?

Thanks!

 

[MaxieMax]

Have you tried repainting the form....

DoCmd.RepaintObject acForm, "FORM_NAME_HERE"

 

[dhookom]

Are all of the text boxes bound to fields in your form's record source (do they have Control Sources)?

Your code suggests you are storing name, address, and lots of other information in more than one table or more than once in the same table. If this is the case, it is generally considered bad practice.



Duane
Hook'D on Access
MS Access MVP

 

[JillianM]

MaxieMax - I get a runtime error when I try to add that to the code using my form name.

Duane - I have a linked Excel file that this database is the user interface for. I have a long list of doctors, that repeatedly need to be entered each time they come to our facility. I have a table that lists all of the doctors and their information, which is what the combo box contains. I also have other fields on my main form for lab date, etc. So, a user would open the main form, choose a doctor from the list, and I want that doctor's information to auto-fill into all of the other fields. Then the user completes the form and all of the information goes to the main linked spreadsheet.

So the answer to your question is yes, each one of my fields goes to a field in my main table. If this is bad practice, what would be a better route? Thank you so much for your help!

 

[dhookom]

So your combo box's Row Source is based on the linked Excel file? I assume you understand the Column() numbers are zero based.

Typically you have a table that stores the doctor information only once in one record. Other tables would store only the primary key from the doctor table. This is called normalization.





Duane
Hook'D on Access
MS Access MVP

 

[JillianM]

Yes, I understand that the colunms start at 0. The fields are auto-filling appropriately, it is just that I have to refresh the form to make them show up. For instance, the combo box is on "LastName" So I would choose a doctor and maybe LastName, FirstName and Address with show up. But in order to see City, State, Zip, NPI, etc. I have to either leave the form and come back, or, refresh the form. I am just trying to figure out how to automatically refresh the form when a doctor in the drop down is selected.

 

[dhookom]

Don't you have a primary/unique key field in your table of doctors?

Duane
Hook'D on Access
MS Access MVP

 

[JillianM]

Yes, I don't use it for anything but it is there. In case you can't tell, this is the blind leading the blind over here. I don't know that much about Access... just more than the people who want this db.

 

[dhookom]

You might want to explore some materials on table structures and normalization. Every table should have a unique value that is the primary key. This could be one field (such as an autonumber) or a combination of several fields.

Let's assume your doctor table has a DoctorID field that is its primary key. If you have a table of doctor certifications, you would store the DoctorID in this table. The field in the doctor certifications table would be called the "foreign key".

Duane
Hook'D on Access
MS Access MVP

 

[JillianM]

I will definitely read up on normalization and the usage of keys. I got my form to automatically refresh by just using the Refresh.Me code after the code I posted above. But knowing how normalization and keys work is critical, I know, so I will study some and try to make my db better. Thanks for your help and advice!