How to update a combo box from another form combo box in Ms access (1 Viewer)

nector

Member
Local time
Today, 20:25
Joined
Jan 21, 2020
Messages
346
Hi all;

Is there a way to update a combo box in form 1 from form 2 combo box? I need this for costing purposes. I have created two forms being the first with combo box 1 the main form while the second one comes in as a popup form updating the underlying main.

Everything is working ok as per plan, except that to get the underlying update the main form I have to requery manually , that is where my issue is.
Can VBA help on this?

This means that at the time of closing the popup form then the main form combo box 1 should be updated. Below is the code I use for updating the first form from the second form and that is where I want to also update the combo box 1

Code:
Private Sub CmdUpdatelanded_Click()
On Error GoTo Err_Handler
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryLandedCostUpdates"
Beep
MsgBox "Please note that the final costing is now successful and the form will now close", vbOKOnly, "Costing Now Done Congratulations!"
DoCmd.Close
Exit_CmdUpdatelanded_Click:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_CmdUpdatelanded_Click
End Sub
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:25
Joined
Feb 19, 2013
Messages
16,553
would help if you provided names of forms, controls, the nature of the change and the combo rowsources. Since you have not provided anything, best I can suggest is

forms!formmain.comboname=somevalue

See this link about referencing forms from different locations
 

nector

Member
Local time
Today, 20:25
Joined
Jan 21, 2020
Messages
346
Ok below are the forms in quetion:

Form 1 = Forms!frmGrn!CboSelectGrntoedit

Form 2 = Forms!frmLandedcosting!CboGrnNumber

Now what I want is that the combo box on form 2 , example Forms!frmLandedcosting!CboGrnNumber should update and refresh combo box on form 1 example Forms!frmGrn!CboSelectGrntoedit when closing form 2.

The update query is ok nothing is needed see below:

Code:
UPDATE tblGrnDetails SET tblGrnDetails.Freight = [Forms]![frmLandedcosting]![txtFinFreight]*[tblGrnDetails].[GrandTotal], tblGrnDetails.Insurance = [Forms]![frmLandedcosting]![txtFinInsurance]*[tblGrnDetails].[GrandTotal], tblGrnDetails.HandlingCharges = [Forms]![frmLandedcosting]![txtFinhands]*[tblGrnDetails].[GrandTotal], tblGrnDetails.CustomesFees = [Forms]![frmLandedcosting]![txtCustoms]*[tblGrnDetails].[GrandTotal], tblGrnDetails.OtherCharges = [Forms]![frmLandedcosting]![txtfinOther]*[tblGrnDetails].[GrandTotal]
WHERE (((tblGrnDetails.GRNID)=[Forms]![frmLandedcosting]![CboGrnNumber]));


The same procedure is working ok to grab the exchange from form1 to form 2 see below :

Code:
Private Sub Form_Load()
On Error GoTo Err_Handler
Me.txtExchangeRate = Forms!frmGrn!FCRate
Exit_Form_Load:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_Form_Load
End Sub
 

nector

Member
Local time
Today, 20:25
Joined
Jan 21, 2020
Messages
346
I have tried to amend the VBA code below, well its working but its taking too long to populate data, any suggestion to speed up this , I can for see 200 lines taking 20 minutes while manual combo box update takes only 4 seconds


Code:
Private Sub CmdUpdatelanded_Click()
On Error GoTo Err_Handler
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryLandedCostUpdates"
Forms!frmGrn!CboSelectGrntoedit = Forms!frmLandedcosting!CboGrnNumber
Beep
MsgBox "Please note that the final costing is now successful and the form will now close", vbOKOnly, "Costing Now Done Congratulations!"
DoCmd.Close
Exit_CmdUpdatelanded_Click:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_CmdUpdatelanded_Click
End Sub
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:25
Joined
Feb 19, 2013
Messages
16,553
again, you need to be clear about what you are talking about. Is the sql you provided the sql to QryLandedCostUpdates, or some other query? Which is the query that is taking too long?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:25
Joined
Feb 19, 2002
Messages
42,971
Running an update query from a popup form to update the record being viewed on the form that opened the popup is dangerous at best.
1. ALWAYS save the current record before opening a second form to ensure that there is no pending update.
2. NEVER use the Load event of a form to populate controls with default data. This event runs only once. So unless you are restricting the form to a single use, this will leave subsequent "new" records without values. The On Insert event is much more appropriate. It runs as soon as the user types the first character into a new record so if the form is used to add multiple records, all will have the foreign key correctly populated. Just because Load works in ONE case, doesn't mean it is the correct event for this purpose. Get in the habit of using the correct event for each task. The additional advantage of using the correct event is that your code won't dirty the record and that will avoid much confusion if the user opens the form but then changes his mind and closes without actually entering any data.
3. If you open the form as modal, code in the calling module STOPS at the OpenForm command and resumes once the open form closes. Therefore, when the popup closes and control returns, you can requery the combo to see the new value. Also, if you use the not in list event, I think Access takes care of this for you.
 

nector

Member
Local time
Today, 20:25
Joined
Jan 21, 2020
Messages
346
Part many thanks to you!

However, the data you are referring to is coming through an another append query which is copying from the the purchase order. In simple terms our procurement procedures works as follows:

(1) The purchases Order is created first
(2) Once the materials are received then the GRN form copy the order details to become the GRN ,if full receipt then no manual quantities adjustments are required ,then we simply attach the following cost elements to make a complete landed cost, that is where the second append query I'm referring to comes into play:
-Freight
-Handling Fees
- Customs duty Fees
- Agent Fees
- Any incidental Labour

All is working nicely except that I just wanted to requery or refresh the appended cost element at the time of closing the modal /pop form. Since this cannot work we will continual refreshing the main form from the edit combo which is faster than the suggested procedure . The reason why I wanted to requery the main form after closing the form is to ensure that the appended cost element appear immediately on the subform after closing the modal/pop up form, I thought this is a good thing and proper programing.

Regards

Chris
 

nector

Member
Local time
Today, 20:25
Joined
Jan 21, 2020
Messages
346
Finally these two code appear to work , but I need to examine them closely:

Code:
Private Sub Form_Current()
On Error GoTo Err_Handler
Call CboSelectGrntoedit_Change
Exit_Form_Current:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_Form_Current
End Sub


Public Sub CboSelectGrntoedit_Change()
Dim ctlCombo As Control

    ' Return Control object pointing to a combo box.
    Set ctlCombo = Forms!FrmGrn!CboSelectGrntoedit

    ' Requery source of data for combo box.
    ctlCombo.Requery
End Sub
 

Users who are viewing this thread

Top Bottom