Using Access 2007. Auto Populate Fields In Form

Question

Using Access 2007.

I have a table called CaseLog populated with patient information which includes many fields.

Of those are SSNumber, LastName, FirstName, Dentist, Technician, UpperAppliance, LowerAppliance.

 I have generate a form in design view from this same table and have hit a wall, I need one form to be able to:

1. Add a new record by auto populating the fore mentioned fields in bold  just by entering the SSNumber.
2. And give me the choice to enter all new data for a totally new patient.

 I have tried combo boxes with code as demonstrated on you tube videos but it is not working.

 I have purchased the Text "Access 2010 Missing Manual", so I have that available.

 I am just beginning to learn about Access and find it fun but challenging once I startred getting deeper into it..

Thank you.

Answer 1

First, if you have a new question, please don't piggyback on someone else's thread. 

Second, If you read Marshall's response to the original question you should realize that your structure is incorrect. You should NOT have Zip, Office, Address, State in your main table. Only EmployeeID. 

Did you read my blog on Displaying data as mentioned in my response here? It goes into several ways to do this properly.

Finally, you say "it only populate the zip field", Do you mean the ZIP control on your form or the ZIP field in your table. You don't have fields on a form, you have controls that may or may not be bound to fields in a table. 

The code you are running shouldn't be in the change event but in the After Update event. To know why this doesn't work, we need to know the RowSource of your combo and the controlsource of your controls.  Please start a new question with the additional info needed and we will try to help.

Answer 2

Please help me, I follow instruction as I found on youtube but it only populate 1 field only.

I have a table call Employee: EmployeeID (set as primary), Zip, Office, Address, State.

another related (one to many) main table: EmployeeID (not as primary set), Zip, Office, Address, State, Training Date.

I did the form with combo command for EmployeeID and create a event procedure after change:

Private Sub COBLOCID_Change()

Me.Zip.Value = Me.COBLOCID.Column(1)

Me.Office.Value = Me.COBLOCID.Column(2)

End Sub

But it only populate the zip field and not other "office, address & state".

Please help me to solve this issue,

Thanks.

Answer 3

Please check out my blog on Displaying Data from Related tables. 

As Marshall stated you should have a separate Patient table and separate case table at the very least.

The only info you need about the patient in the case table is the PatientID as a foreign key.  Once you select the patient via their ID, you can display the patient data on the form, without putting it in the table.

Answer 4

NOTE:  The caseLog table SHOULD NOT include the patient info you want to see.  Unlike spreadsheets,  Data values like that should exist exactly once in the Patient table and nowhere else.

 

In a database, everthing starts with a query, first you should create a query based on your CaseLog table sorted in the order you want to see the records on the case log form.  Set the case log form's Record Source to this query.

 

Create another query based on the Patient table with the fields you specified earlier, probably sorted by the LastName and FirstName fields.

 

On the case log form, use a combo box bound to the CaseLog table's foreign key field (SSN?).   Use the Patients query above as the combo box's Row Source.  Then, for each patient field in the query, other than the foreign key, add a text box with an expression like:

   =comboboxname.Column(1)

where 1 is the second column in the query.  The other text boxes would use 2, 3, etc.  This way the case log form will display the patient info without messing with the CaseLog table.

 

 

Thank you for your insight on how to structure this correctly. I will attempt and hopefully be able to do this correctly. I think I may have to get myself up to speed by taking a class at a local community college before it is all over.

Thank you again for your time and wisdom.

Answer 5

NOTE:  The caseLog table SHOULD NOT include the patient info you want to see.  Unlike spreadsheets,  Data values like that should exist exactly once in the Patient table and nowhere else.

In a database, everthing starts with a query, first you should create a query based on your CaseLog table sorted in the order you want to see the records on the case log form.  Set the case log form's Record Source to this query.

Create another query based on the Patient table with the fields you specified earlier, probably sorted by the LastName and FirstName fields.

On the case log form, use a combo box bound to the CaseLog table's foreign key field (SSN?).   Use the Patients query above as the combo box's Row Source.  Then, for each patient field in the query, other than the foreign key, add a text box with an expression like:

   =comboboxname.Column(1)

where 1 is the second column in the query.  The other text boxes would use 2, 3, etc.  This way the case log form will display the patient info without messing with the CaseLog table.