Args (2) to pass data to unbound combo boxes (1 Viewer)

[YAM]

Hi everyone,

I hope all safe and healthy during this pandemic.

I have following code on form No 1 (Command button)

Option Compare Database
Option Explicit

Private Sub BtnOpen_Click()
If Me.EntryType = "Bank Transfer" Then
DoCmd.OpenForm "frmBankTransferEntry", acNormal, , , acFormAdd, acDialog, "me.BankAccount;me.EntryNo"
End If
End Sub

and i have following code on form 2 (On load event)

If Not Trim(Me.OpenArgs & " ") = "" Then
   Me.cboSelectAccount = Split(Me.OpenArgs, ";")(0)
    If UBound(Split(Me.OpenArgs, ";")) = 1 Then
     Me.cboEntryNo = Split(Me.OpenArgs, ";")(1)
    End If
End If

its working fine if i try to pass the text but i dont know how to pass the fields data and another form which is going to open after command button event have two 2 unbound combo boxes first is cboSelectAccount and second is cboEntryNo both are integer.

I dont know how to modify this to add the field controls instead of text to pass to another form on (on load event)

Any help will be appreciated.

 

[pbaldy]

Try

me.BankAccount & ";" & me.EntryNo

 

[YAM]

Not working fields are not filled when open frmBankTransferEntry its not opening to that record, remember i said the form i am going to open have two unbound combo boxes.

 

[pbaldy]

What exactly did you try? This may help:

Open a second form using OpenArgs

www.baldyweb.com

 

[YAM]

Your code is ok its working, the small problem is when destination form open with selected data in combo boxes its not firing the form fields i have select it manually again the same entry no than other fields are appearing otherwise no, its just showing the value we pass in cboSelectAccount and cboEntryNo when re-select the same value in cboEntryNo than it showing the data on form ?

 

[pbaldy]

I'm just starting a conference call so I'll be gone a bit. Where do you have the code? I'm having trouble understanding the issue. Is it probably between the bound column of the combo vs what's displayed?

 

[Gasman]

Your code is ok its working, the small problem is when destination form open with selected data in combo boxes its not firing the form fields i have select it manually again the same entry no than other fields are appearing otherwise no, its just showing the value we pass in cboSelectAccount and cboEntryNo when re-select the same value in cboEntryNo than it showing the data on form ?

What code do you have for the combos.?
Populating them is not going to magically retrieve the data?, you would need to execute that code or requery the form if those controls are criteria in the query.?

 

[YAM]

All are ok, just when i open second form with values we are passing it should show that entry right ? on second form, but its showing the value in destination fields on second form and not showing the records of that it is not firing, normally we select combobox and records appear on all fields of form but here combo box is filled with the value we but its not showing the related record on form i have to re-select the same value in combo box manually than only its showing.

This is the row source of cboEntryNo which is not firring

SELECT QryBankTransferEntrySelection.EntryNo FROM QryBankTransferEntrySelection GROUP BY QryBankTransferEntrySelection.EntryNo;

i did tried on after update event of combo and on load event of form with .requery and .refresh but not refreshing the records on form.

 

[Gasman]

No!, if you want the record for that account number and entryno, then use the WHERE parameter of the OpenForm command.

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

 

[pbaldy]

Since this works with text:

its working fine if i try to pass the text

Maybe you can show an example of it working with text, and what the combos contain when you try to use them.

 

[YAM]

I have attached the strip version as much possible, some other codes will be there also so ignore if you see something to mentioned importantly you are welcome.

Please open frmEditBankEntry and select Bank Transfer option in check box than click on folder button beside entryNo the form will open with cboSelectBank Account and cboEntryNo all are good except the fields on form is not refreshed once its open i have to select it manually again in the cboEntryNo this is the issue only.

 

[Gasman]

Try my method?

 

[YAM]

If Me.EntryType = "Bank Transfer" Then
DoCmd.OpenForm "frmBankTransferEntry", acNormal, , , acFormEdit, acDialog, Me.BankAccount = " & cboSelectAccount & ; & Me.EntryNo =  " & cboEntryNo

i did try with where condition in above but it is asking the variables for cboSelectAccount and cboEntryNo which is another form fields, i dont know what exactly need to be mention.

why it is working good with text because text fields have nothing to populate on form.

 

[pbaldy]

Can you give an example of what exactly worked when you used fixed text?

 

[YAM]

This is a example of normal text that being passed to destination form and the code is running without any issue.

DoCmd.OpenForm "frmCreateAccount", acNormal, , , acFormAdd, acDialog, "LIABILITY;ACCOUNTS PAYABLES"

This is the onload event of my 2 form where i need the detail to be uploaded and refreshed on form.

If Not Trim(Me.OpenArgs & " ") = "" Then
    Me.cboGroup = Split(Me.OpenArgs, ";")(0)
    If UBound(Split(Me.OpenArgs, ";")) = 1 Then
      Me.cboType = Split(Me.OpenArgs, ";")(1)
    End If
End If

its working fine for normal text but for fields value its not populating the fields on form and just opening with value on combo box that i am passing through Args.

There is no issue with code neither your code or my code what i am telling is form fields are not populating the data of which we have passed the values in combo box (Record related fields), if you see my strip version of db. than you will know what is the issue.

I hope its very clear now what is the issue.

 

[arnelgp]

you need to make the bank combo and the entry no combo Bound to the table field.
so you can search for the bank and entry no.

 

[YAM]

thanks Arnelgp but i have this code on cboEntryno which is causing me error while i bound control to table.

Option Compare Database
Option Explicit

Private Sub cboEntryNo_AfterUpdate()

'Recordset Explorer
    Me.Recordset.FindFirst "EntryNo = " & cboEntryNo

I am using this form in two ways one direct user can open and explore the specific record and another way is what we are discussing here from another form to edit.

after selection of cboSelectAccount user will go to cboEntryno to select entry which will be based on account he is selecting in cboSelectAccount which is fine but while i am using it manually direct frmBanktransferEntry then its giving me error of "This action cancelled by Associated object and its going to above code of 'Recordset.findfirst which might be causing the error.

Please advise how to handle it. thanks

 

[pbaldy]

I guess I'm just having a brain cramp on what the problem is. When I click on the folder icon you mentioned, the form that comes up has the bank account and entry number filled in as I would expect from the code in the load event.

 

[Uncle Gizmo]

I suspect the problem is that when you open the form like this:-

DoCmd.OpenForm "frmBankTransferEntry", acNormal, , , acFormEdit, acDialog, BankAccount & ";" & EntryNo

you are setting it as "Modal" too early.

I removed the "Modal" setting from the the form "DoCmd.OpenForm" String,

DoCmd.OpenForm "frmBankTransferEntry", acNormal, , , acFormEdit,  BankAccount & ";" & EntryNo

Opened the Form without it being "Modal" then run the the public functions "fcboEntryNoRequery" and then finally set it back to "Modal"...

Dim strFrmName As String
strFrmName = "frmBankTransferEntry"
    DoCmd.OpenForm strFrmName, acNormal, , , acFormEdit, , BankAccount & ";" & EntryNo
   
    With Forms(strFrmName)
    '.prpClientID = Me.ClientID
    .fcboEntryNoRequery
    .Modal = True
    '.Caption = "I CAN CHANGE THE CAPTION"
    End With

The public function "fcboEntryNoRequery" replaces your combo-box "cboEntryNoRequery" after update event so that it can be called from elsewhere.

Public Function fcboEntryNoRequery() As String

Dim conAppName As String
conAppName = "(Replace this Local Variable with a Global One) "

Dim strSubName As String
Dim strModuleName As String

strSubName = "fcboEntryNoRequery"
strModuleName = "Form - " & Me.Name
'strModuleName = "Module - basModuleName"'

On Error GoTo Error_Handler

'Select records based on Entry No
Me.Recordset.FindFirst "EntryNo = " & cboEntryNo
Me.cboEntryNo.Requery
Me.Refresh

        If Me.Void = True Then
            MsgBox "This Entry and its underline transactions are Voided before !"
        End If

'Enabled Entry fields
Me.EntryDate.Enabled = True
Me.cboCategory.Enabled = True
Me.ExhRate.Enabled = True
Me.cboTransferToAccount.Requery

Exit_ErrorHandler:
    
    Exit Function

Error_Handler:  'Version - 1a
    Dim strErrFrom As String
    Dim strErrInfo As String
        
        strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
        strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
            
            Select Case Err.Number
                Case 0.123 'When Required, Replace Place Holder (0.123) with an Error Number
                    MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
                Case Else
                    MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
            End Select
        Resume Exit_ErrorHandler

End Function      'fcboEntryNoRequery

 

[Gasman]

I guess I'm just having a brain cramp on what the problem is. When I click on the folder icon you mentioned, the form that comes up has the bank account and entry number filled in as I would expect from the code in the load event.

I believe that the o/p expects the form to go to the record for that account and entry no, just because the two combos are populated from openargs.