Opening form with Value in Combo Box from another form

[sara]

I have a form where the user selects an item from a list box, and then
works on that item. The user chooses an AD, then opens a form to
assign departments to the ad. The top of the Depts form has a combo
box, to select an ad from the drop down list.

I would like the Depts form to open with the Ad selected on the Main
form displaying in the combo box, AND any information already added
presented to the user. (I am thinking this latter part would be a
requery of the combo box or something).

My problem is that I can't get the frmDeptsForAd to open with a value
in the combo box.

Perhaps my form design/approach is wrong. I have a combo box at the
top so the user can update any ad without going back to the main form.

Thanks!
sara

Code:
Private Sub cmdDeptsForAd_C lick()
On Error GoTo Err_cmdDeptsFor Ad_Click

Dim strDocName As String
Dim strLinkCriteria As String
Dim lngAdKey As Long

If IsNull(Me.lstAd s.Column(0)) Then
MsgBox "Please choose an ad"
GoTo Exit_cmdDeptsFo rAd_Click
Else
lngAdKey = Me.lstAds.Colum n(0)
End If

strDocName =
"frmSelDeptsFor Ad","Forms!frmS elDeptsForAd!cb oAdKey.Value =" & lngAdKey
'Have tried many forms of this line of code - nothing worked so far

DoCmd.OpenForm strDocName

Exit_cmdDeptsFo rAd_Click:
Exit Sub

Err_cmdDeptsFor Ad_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdDeptsFo rAd_Click

End Sub
"Forms!frmSelSt oresForAd!cboAd Key.Value =" & lngAdKey

[salad]

sara wrote:

I have a form where the user selects an item from a list box, and then
works on that item. The user chooses an AD, then opens a form to
assign departments to the ad. The top of the Depts form has a combo
box, to select an ad from the drop down list.

I would like the Depts form to open with the Ad selected on the Main
form displaying in the combo box, AND any information already added
presented to the user. (I am thinking this latter part would be a
requery of the combo box or something).

My problem is that I can't get the frmDeptsForAd to open with a value
in the combo box.

Perhaps my form design/approach is wrong. I have a combo box at the
top so the user can update any ad without going back to the main form.

Thanks!
sara

Code:
Private Sub cmdDeptsForAd_C lick()
On Error GoTo Err_cmdDeptsFor Ad_Click

Dim strDocName As String
Dim strLinkCriteria As String
Dim lngAdKey As Long

If IsNull(Me.lstAd s.Column(0)) Then
MsgBox "Please choose an ad"
GoTo Exit_cmdDeptsFo rAd_Click
Else
lngAdKey = Me.lstAds.Colum n(0)
End If

strDocName =
"frmSelDeptsFor Ad","Forms!frmS elDeptsForAd!cb oAdKey.Value =" & lngAdKey
'Have tried many forms of this line of code - nothing worked so far

DoCmd.OpenForm strDocName

Exit_cmdDeptsFo rAd_Click:
Exit Sub

Err_cmdDeptsFor Ad_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdDeptsFo rAd_Click

End Sub
"Forms!frmSelSt oresForAd!cboAd Key.Value =" & lngAdKey

The format is
Docmd.OpenForm "FormName",,,"D ept = " & Me.Combo

Here's another method
Docmd.OpenForm "FormName",,,,, ,Me.Combo

What's the difference? The first opens the form and filters to what you
have passed.

The second opens the form with an argument. In your events you can then
use the value by looking at what is contained in OpenArgs in the second
form. Ex:
If not isnull(Me.OpenA rgs) then
Me.Filter = "Dept = " & Me.OpenArgs
Me.FilterOn = True
Me.Combo = Me.OpenArgs
Endif

[sara]

Thanks, Salad - but I don't quite have it yet. I'm still quite new at
all this coding (Sorry - didn't mention this at first).

Here's the code I tried:

From the Main form:

If IsNull(Me.lstAd s.Column(0)) Then
MsgBox "Please choose an ad"
GoTo Exit_cmdDeptsFo rAd_Click
Else
lngAdKey = Me.lstAds.Colum n(0)
End If

strDocName = "frmSelDeptsFor Ad"
DoCmd.OpenForm strDocName, , , , , ,
"Forms!frmSelDe ptsForAd!cboAdK ey"

On the SelectDepts form On Open event:
Private Sub Form_Open(Cance l As Integer)

' If the user came from the main form and selected an ad, put that ad
in the combo box

If Not IsNull(Me.OpenA rgs) Then
Me.Filter = "Dept = " & Me.OpenArgs
Me.FilterOn = True
Me.cboAdKey = Me.OpenArgs
End If
End Sub
And the first way (I commented out the On Open event code for this
try):
DoCmd.OpenForm strDocName, , , "AdKey = " &
"Forms!frmSelDe ptsForAd!cboAdK ey"

I managed to get the quotes, and format the parameter properly, at
least I got it to compile. But when the SelDepts form opens, the combo
box is still blank. Can you see what I'm missing?

thanks - I relaxed quite a bit when I saw your response - Help is here!
sara

[Rich P]

This is pretty simple. Use the OpenArgs Property of the form. In the
main form add something like this to a command button:

Private Sub Command4_Click( )
DoCmd.Openform "Form4", acNormal, , , , , comboAD
End Sub

Then in the Dept form - in the Load or Open event you can do this:

Private Sub Form_Load()
ComboDept = Me.OpenArgs
End Sub

You can spruce this up a bit by checking for null values in openargs
first:

If Not IsNull(Me.OpenA rgs) Then
ComboDept = Me.OpenArgs
End IF

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[salad]

sara wrote:

Thanks, Salad - but I don't quite have it yet. I'm still quite new at
all this coding (Sorry - didn't mention this at first).

Here's the code I tried:

From the Main form:

If IsNull(Me.lstAd s.Column(0)) Then
MsgBox "Please choose an ad"
GoTo Exit_cmdDeptsFo rAd_Click
Else
lngAdKey = Me.lstAds.Colum n(0)
End If

strDocName = "frmSelDeptsFor Ad"
DoCmd.OpenForm strDocName, , , , , ,
"Forms!frmSelDe ptsForAd!cboAdK ey"

On the SelectDepts form On Open event:
Private Sub Form_Open(Cance l As Integer)

' If the user came from the main form and selected an ad, put that ad
in the combo box

If Not IsNull(Me.OpenA rgs) Then
Me.Filter = "Dept = " & Me.OpenArgs
Me.FilterOn = True
Me.cboAdKey = Me.OpenArgs
End If
End Sub
And the first way (I commented out the On Open event code for this
try):
DoCmd.OpenForm strDocName, , , "AdKey = " &
"Forms!frmSelDe ptsForAd!cboAdK ey"

I managed to get the quotes, and format the parameter properly, at
least I got it to compile. But when the SelDepts form opens, the combo
box is still blank. Can you see what I'm missing?

thanks - I relaxed quite a bit when I saw your response - Help is here!
sara

In your second forms OnOpen event...to see what you passed...you could
enter a line like
msgbox "Openargs = " & Me.OpenArgs
and it will display what you sent to it...or nothing but a blank msgbox
if null.

I expect it would show "Forms!frmSelDe ptsForAd!cboAdK ey". That's not
what you want.

Your code to open the form should be something like
DoCmd.OpenForm strDocName, , , , , ,Me.cboAdKey

This passes the value, not the string "Forms!frmSelDe ptsForAd!cboAdK ey".

The following.
DoCmd.OpenForm strDocName, , , "AdKey = " & _
"Forms!frmSelDe ptsForAd!cboAdK ey"
Is also incorrect, unless you have an adkey value equal to the string
"Forms!frmSelDe ptsForAd!cboAdK ey".

Here is a hint. Strings are surrounded in ". Date in #. Number in
nothing. Ex:
str = "Smith"
dat = #1/1/2006#
num = 2
"LastName = """ & strt & """
or
"Lastname = '" & str & "'"
"StartDate = #" & dat & "#"
"Qty = " & num

Basically, the "where" clause you pass in the Docmd.Openform is like a
SQL whereclause without the word "Where".

Hope this gets you a bit further.

[sara]

Salad - (and Rich P, too!)
Sorry for the delay - I was out for a week.

I have (again) tried to make this work and am still not there. I think
one problem may be about OpenArgs. Do I declare that somehow? I
checked other posts and it appears Args was declared as a string, but
then how does Me.OpenArgs work? Me.OpenArgs is always null for me - no
matter which code I have tried.

Summary of what I've learned through reading (I have finally gotten my
Access Help fixed): - Maybe I have misunderstood something here?
OpenArgs is only available with DoCmd.OpenForm, which I'm using.
OpenArgs is the last parameter of the OpenForm method (which explains
the ",,,," in your code for me.
You use either OpenArgs or Where condition (as you showed above)

So, I am baffled as to why I am not seeing the OpenArgs in my msgbox
(and why the ad it not showing up in the drop down box)
Code for calling form:
AdKey = Me.txtAdKey

strDocName = "frmSelDeptsFor Ad"

strLinkCriteria = "[AdKey] = " & AdKey

DoCmd.OpenForm strDocName, , , , , , AdKey
Code for Second form: (frmSelDeptsFor Ad)
Private Sub Form_Load()
On Error GoTo Err_Form_Load

Set mdb = CurrentDb
Set mqdf = New DAO.QueryDef

Me.cboAdKey = Me.OpenArgs
MsgBox "OpenArgs = " & Me.OpenArgs

Thanks for bearing with me on this one. Your comments make it look so
doable, but I'm just not getting it!
sara

[Rich P]

Hi sara,

You might try something real basic/simple.

In the mainform under some button, just open a form

DoCmd.OpenForm "Form2", acNormal, , , , , "testing"

Note: I am using the constant acNormal. You might try that also.

Then in the form you open - in the Form_Load event

MsgBox Me.OpenArgs

I just did this without any issues. I got the message box with
"testing" as text on the open event of Form2.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[sara]

Thanks. I got it! I don't know why I couldn't do it before, but I am
now all set. Couldn't have done it without your (and Salad's) generous
help.

Moving on to my next issue.

Again, thank you!
Sara