Refresh combo box data

[the_fuzz]

Hi All,

I have searched this forum and have seen a few ideas but are not answering my question. My apologies if this is a duplication but I am stumped.

I have a form (client information main form) with a combo box (combo281) which is linked to the [agent code] field which once selected populates numerous fields automatically. (see below) This works perfectly.

Private Sub Combo281_AfterUpdate()
Me.[Region] = Me.Combo281.Column(4)
Me.[Brokerage Name] = Me.Combo281.Column(3)
Me.[Agent Name] = Me.Combo281.Column(2)
Me.[Broker Consultant] = Me.Combo281.Column(5)
Me.[Agent 13 Digit Code] = Me.Combo281.Column(6)
Me.[Agent Code Termination] = Me.Combo281.Column(7)
Me.[code status] = Me.Combo281.Column(8)
Me.[Retention] = Me.Combo281.Column(9)
Me.Combo281.Requery
End Sub

My problem is that my database contains about 18,000 records of clients and the above control I have only started using now so I need to be able to refresh all of the existing data. At present to update I have to go into each individual record and type in the [agent code] again for the rest of the data to update.

Is there a macro I could use or is my requery incorrect above.

Please help me as it will take forever to retype the [agent code] for all 18,000 records.

Thanks

Richard

 

[pr2-eugin]

Hello Richard,

My understanding of your current design is, there are two tables AgentsTable and ClientTable.. So one of the field in ClientTable is AgentID, which in your form is a ComboBox.. And the information of AgentTable has several other information associated with the agent ID..

Based on that assumption, you may have ONE-ONE relationship..

My question is why do you wish to duplicate the Data in the AgentTable into the ClientTable? Could you not just get the information based on Main/Subform structure? Why copy them across?

 

[the_fuzz]

Hello Richard,

My understanding of your current design is, there are two tables AgentsTable and ClientTable.. So one of the field in ClientTable is AgentID, which in your form is a ComboBox.. And the information of AgentTable has several other information associated with the agent ID..

Based on that assumption, you may have ONE-ONE relationship..

My question is why do you wish to duplicate the Data in the AgentTable into the ClientTable? Could you not just get the information based on Main/Subform structure? Why copy them across?

Thanks for the reply. Yes I work from 2 tables. The client table I administer and the agent table is administered by someone 700 miles away who emails me the excel which I inport weekly which links to my table. The inforamtion in the agent table therefore I do not keep up to date.

I therefore use the agent table to populate certain fields but now I need to update all the records as they only update when I specifically type over the agent code field.

Hope this helps

Rich

 

[pr2-eugin]

In that case, why not create an UPDATE QUERY? I am doing a generic Query, you can modify it according to what you wish to see or change..

UPDATE clientTable INNER JOIN agentTable ON clientTable.agentID = agentTable.ID SET
clientTable.Region = agentTable.Region,
clientTable.BrokerageName = agentTable.BrokerageName,
clientTable.AgentName = agentTable.AgentName;

NOTE: Try this on a BackUp copy of your Database, as Update cannot be reverted.

 

[the_fuzz]

In that case, why not create an UPDATE QUERY? I am doing a generic Query, you can modify it according to what you wish to see or change..

UPDATE clientTable INNER JOIN agentTable ON clientTable.agentID = agentTable.ID SET
clientTable.Region = agentTable.Region,
clientTable.BrokerageName = agentTable.BrokerageName,
clientTable.AgentName = agentTable.AgentName;

NOTE: Try this on a BackUp copy of your Database, as Update cannot be reverted.

Will give this a try. Thanks for the quick feedback

 

[the_fuzz]

Will give this a try. Thanks for the quick feedback

Where will I place this code, in which field or will it be in the form properties in the afterupdate or which event.

 

[pr2-eugin]

It will be just a normal UPDATE Query, this will update all the Records you currently have in the Table... This is something like a one time thing.. To update the pre existing records, any new record will be following the AfterUpdate of the combo box, so you do not have to worry about this in the future.. Makes sense?

 

[the_fuzz]

It will be just a normal UPDATE Query, this will update all the Records you currently have in the Table... This is something like a one time thing.. To update the pre existing records, any new record will be following the AfterUpdate of the combo box, so you do not have to worry about this in the future.. Makes sense?

Worked like a charm. Thanks very much pal

 

[pr2-eugin]

Glad to have helped.. Good Luck !!