Populating multiple fields in form after making selection in combobox? (1 Viewer)

[Nicodemus]

My apologies if this is answered elsewhere.

Access 2007

Tables: tblNewspapers, tblConventionReg

I have a form for entering/editing new registrations for a newspaper convention. All of the relevant information for each regional newspapers is in tblNewspapers, and all of the registrations are going into the tblConventionReg.

Since there are multiple registrants from each newspaper, I have a combobox on the form that executes a query and retrieves the newspaperID, newspapername, mailingaddress1, mailingaddress1, city, state, zip, email, fax, phone

The information for each individual is entered manually.

The problem I am having is filling each of the newspaper specific fields with data after selecting the newspaper name from the combobox and then adding that data to the record in the tblConventionReg.

For example, if I put:

=[NewspaperSelect].[Column](4) NewspaperSelect is the name of the combobox and the data retrieved from a sql query in column 4 is the city

into the text field for city, the appropriate data shows up just fine no problem. It is also reflected in the datasheet view for the form. However, the data does NOT show up in the table. If I change the control source for the field to "City" (the name of the appropriate column in the table) then it will only show data that I enter in manually.

What I need to know, is how I get data to populate the field on the form after selecting a newspaper from the combobox (again, this executes a query and retrieves all the data I need and lists the newspaper name), and then pass that through to the table.

Ideas? Suggestions?

 

[Nicodemus]

ok, solved the problem.

Each field should be bound normally to the appropriate column in the destination table.

On my combobox, I open the property sheet, go to the event tab, and then select [Event Procedure] for After Update.

I added:

Private Sub NewspaperSelect_AfterUpdate()

Me![NewspaperID] = NewspaperSelect.Column(1)
Me![MailAdd1] = NewspaperSelect.Column(3)
Me![MailAdd2] = NewspaperSelect.Column(4)
Me![MailCity] = NewspaperSelect.Column(5)
Me![MailState] = NewspaperSelect.Column(6)
Me![MailZip] = NewspaperSelect.Column(7)
Me![Phone] = NewspaperSelect.Column(8)
Me![Fax] = NewspaperSelect.Column(9)

End Sub

So, when I select a newspaper from the dropdown (thus executing the sql query to grab newspaper name and other data), the After Update event procedure is activated, which sends data from each column to the corresponding field.

When I move to another record, the data is saved to the destination table. If you don't move to another record or implement a "save" button, the data will not be sent if you just edit the fields and close things out.

Thanks for taking a look at my problem : )

 

[boblarson]

Just an FYI for you - you should look into Normalization as this really violates the rules of normalization and that is an important thing to know when getting the full value out of a RELATIONAL Database Management System (RDBMS).

Read this:
http://support.microsoft.com/kb/283878

 

[Nicodemus]

Thank you Bob!

I needed a quick fix. I inherited the project from a client that had a contractor leave things incomplete and not very well designed.

My solution to my problem is ok, even with duplicated data, as the new table will be used to print out badges and whatnot. Some of the fields (email, phone, etc.) may change from registrant to registrant, even at the same newspaper....so while we want to change that for this particular event, we do not want to change the master table of newspaper data and contact info.

Inelegant (to my chagrin) and rule-breaking, but it gets the job done (its time sensitive). I plan on building the whole thing from scratch, the *right* way when I have time.

Thank you again for the link though...my skills with access are pretty minimal (I haven't used it since Access 95).

 

[tpuhlig]

Hello,
I know this is an old post but I have a follow up question.

I used the same concept of "after update" to populate other fields on my form. Works great, well at least the first time. If a user makes a selection from the pull down menu, the address fields gets updated.

If the user makes the wrong selection, they can choose another address from the pull down menu. The pull down shows the new selection but the address fields do not update with the new request.

I tried putting the same code under "after change" but this did not work.

Anyone have any ideals?

Thanks
Tom