Problem updating Unbound Fields

[MSAccessRookie]

I hope the Title explains the situation appropriately.

I have a problem with a data source that requires special handling. I need to import information (including user names) from another Database that is not structured like ours. I have detailed only the user name issue, and assume that other issues will be similar.


tblContacts (Our Data):

• Primary Key
• First Name
• Middle Initial
• Last NameEmail
• LAN_ID
• (other information)

tblProjects (Their Data):

• Project ID (used as Primary Key)
• (other information)
• Project Manager Name (Firstname, Middle Initial, Last Name)
• Project Engineer Name (Firstname, Middle Initial, Last Name)
• More Names (Firstname, Middle Initial, Last Name)

I quickly determined that only some of the Names that are in tblProjects are already in tblContacts. The missing entries will be added over time, and until they are added, I am to display names from tblProjects when the information is not available in tblContacts.


On the Form

• There is a Combo Box that selects Project Information by ProjectID, including all of the Name Fields.
• There are Combo Boxes for each of of the Name Fields that are based on Queries to tblContacts and need to be modified, because they will not allow values that are not in their list.

I have tried to create a Text Box for each Name that is updated via VB from both the ProjectID Combo Box and the appropriate Name Combo Box. While I have been able to get that to work at the Load Event for the Form, or at the On Change Event for either Combo Box, it does not update if the user scrolls through the records using a mouse wheel or similar method.

• Is there an event to trap so that I can do this?
• Is there a better way to do what I want?

Somehow I feel that I should be able to figure this one out on my own.

-- Rookie

 

[michaeljryan78]

I guess the first step is to Normalize the imported data. extract the first, middle and last names and place them in a temp table that you can create (tblImportData)?. Set the primary key on your table as a composite of Primark Key, FirstName and LastName. Do the same for the tblImportData. You can then use a UNION query to combine and both data sets and group by unique values. First, last to get the Uniques. Base you combo in the union query.

 

[MarkK]

Do you need the Form_Current() event that fires each time a new record is loaded (made current) in the form?
Mark

 

[MSAccessRookie]

Sorry to take so long to get back you you. I was setting up a MouseTrap to control the users input Form, and prevent them from saving incomplete data by using the Scroll Wheel on the mouse. The Form_Current event resolved the issue until there was a need to open a Sub-Form, and the MouseTrap took control of the Sub-Form and would not let me save it. I am working on that now.