Open form on combobox after update event and set combobox selection on opened form (1 Viewer)

zulu100

Member
Local time
Today, 15:34
Joined
Nov 3, 2021
Messages
54
Hi
I have a (frm_Main) with one combo box "cbm_Main". In the combo box after update event I would like to open (frm_List) which also have a combo box "cbm_List"
Both combo boxes has the same control source "qry_Departments" and the bound column is 1. The content are numeric values.
I would like frm_List to open with the same combo box selection as I choose on frm_Main, but I cant seem to get the where condition syntax correct.
Something like:
docmd.OpenForm "frm_List",acNormal,, <cbm_Main=cbm_List>
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:34
Joined
Oct 29, 2018
Messages
21,357
Try using the OpenArgs argument.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 19, 2002
Messages
42,970
You need to fix your syntax.

docmd.OpenForm "frm_List",acNormal,,"SomeFieldName = " & Me.cmb_List

Replace SomeFieldName with the actual field name. This sample also assumes the value is numeric. If it is text, you will need to enclose it in quotes.

docmd.OpenForm "frm_List",acNormal,,"SomeFieldName = '" & Me.cmb_List & "'"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:34
Joined
May 7, 2009
Messages
19,169
add code to the Load Event of frm_List:
Code:
'arnelgp
Private Sub Form_Load()
' check if frm_main is open
If syscmd(acSysCmdGetObjectState,acform,"frm_Main") <> 0 Then
    'check if combobox on frm_main has a valid item on it
    If Forms!frm_Main!cbm_Main.ListIndex > -1 Then
        'assign it to this local combobox
            Me!cbm_List = Forms!frm_Main!cbm_Main
    End If
End If
End Sub
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 19, 2002
Messages
42,970
It is always wrong to dirty a record before the user does. There are at least three better methods to accomplish this. If, this is in fact what the user is looking for.
 

zulu100

Member
Local time
Today, 15:34
Joined
Nov 3, 2021
Messages
54
add code to the Load Event of frm_List:
Code:
'arnelgp
Private Sub Form_Load()
' check if frm_main is open
If syscmd(acSysCmdGetObjectState,acform,"frm_Main") <> 0 Then
    'check if combobox on frm_main has a valid item on it
    If Forms!frm_Main!cbm_Main.ListIndex > -1 Then
        'assign it to this local combobox
            Me!cbm_List = Forms!frm_Main!cbm_Main
    End If
End If
End Sub
Thanks arnelgp
That worked great
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 19, 2002
Messages
42,970
@zulu100
If you add a second record, the FK will not be populated
If you close the form without completing it, you will create an "empty" record unless you have validation code in the form's BeforeUpdate event.

thedbguy's suggestion to use the OpenArgs is far better but he didn't complete the thought so I will.

Pass the foreign key value in the OpenArgs argument of the OpenForm method. Then in the pop up form's BeforeInsert event:

Me!cbm_List = Me.OpenArgs.

This will solve both listed problems. But you still probably need validation code in the form's BeforeUpdate event.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 19, 2002
Messages
42,970
@arnelgp
Please explain in simple words, why you think that it is correct to dirty a record with code before the user ever types a single character. Then please explain how the FK gets populated in the second or subsequent records using your suggested method. We are providing answers to people who are not capable of discerning sound solutions from poor solutions because they don't understand the ramifications of what we suggest.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:34
Joined
May 7, 2009
Messages
19,169
that was the intention (see post#1).
your post #7, will it not dirty the form? you know better.

be happy that the OP achieved what he want.
don't be a bitter gourd.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 19, 2002
Messages
42,970
@arnelgp, I know English is not your native language so I applaud your contributions to the forum and the effort you put into them. You go out of your way to write code and even create databases to help people. However, sometimes, you need to stand back and read between the lines to understand where a question is going. Of course the OP wants to put a value into the popup form, but there are multiple ways of accomplishing that without causing potential problems. Just opening a form and plopping a value in a field does dirty the form. That means that Access is going to want to save it regardless of whether the user ever types a single character. I'm sure the OP doesn't understand the ramifications of that and I'm pretty sure he doesn't know enough to put validation code into the form's BeforeUpdate event to prevent the save from happening if no other data has been added. That means, he's causing junk records to be added to the table. That is problem #1. Problem #2 is if a second record is added using the popup and we have no way of knowing whether or not that will ever happen, the second and subsequent records will not have the combo populated. We have no idea if that will be a problem but we do know how to avoid it by using a different method of populating the combo. Problem #3, which I didn't even address is that if the popup is being used as a "subform", the OP probably also doesn't know that he needs to provide the foreign key that will link the record to the parent record. I thought of the request as being for that purpose but I could have been wrong and he could be back tomorrow wanting to know where the records he added went because they are not showing up in his report.

We give answers knowing nothing about the business rules and close to nothing about what the user is trying to achieve. We can think about what prompted the question and anticipate potential problems that the user not only hasn't considered, they are not even his universe. Think of it as defensive programming. Why would WE want to pass data from formA to formB? WE know the kind of problems that result from unintended consequences so the least we can do is try to provide solutions that are not likely to break or leave gaping holes for the user to fall into rather than just jumping in with blinders on and answering the specific question and only the specific question.
 

zulu100

Member
Local time
Today, 15:34
Joined
Nov 3, 2021
Messages
54
Sorry if I caused an issue here, this was certainly not my intention.
@Pat Hartman you are right. My knowledge about VBA is very limited and I grab a little bit from here and there to put my database together. In this case maybe I should have been more specific.
The combo-box on the opened form (frm_List) is actually a filter showing production orders for different department. In a test period I only had one department using the database and therefore the on open event was set to this one specific department. Now this is being rolled out to other departments I needed a Main form from where to choose which list of productions orders to see. This can also be done from the combo-box in "frm_List" but it is more user friendly to do it on a main form.
The combo boxes has the same source so they will always be identical even when new departments are added. So to my limited knowledge I think both @arnelgp and @Pat Hartman's will work OK, but I could be wrong :)
Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 19, 2002
Messages
42,970
@zulu100 you didn't cause a problem.

Now that we know what you actually want to do, the best option is to use the OpenForm method's where argument. No additional code is necessary anywhere. Just modify the OpenForm method to include the where argument. Remove the proposed code which is currently in the load event. I say this only because if you leave it, you will think that that is the proper way to open a form to a specific record and it is not.

So to my limited knowledge I think both @arnelgp and @Pat Hartman's will work OK, but I could be wrong
As long as you are putting the value into an unbound control, arnelgp's suggested method won't do any damage but the best way to do what you asked is with the OpenForm method as I said.

I gave you the syntax to do that in my original post. Apparently, you never bothered to try it. For some reason that escapes me, arnel thinks we are in a competition so he always posts alternative methods (sometimes they are quite clever but they are always different from the standard Access method) and he won this battle because he sucked you in to using his cockamamie code rather than the strait forward Access method.

When Access provides a method for doing something, you really need a very good reason for rolling your own.
 
Last edited:

Users who are viewing this thread

Top Bottom