OpenForm select first field and display

W

WestWingFan

Thank you in advance for any help or advice. I'm trying to do something which
shouldn't be too hard, but has me stumped. I suspect it has something to do
with where the code is placed. In case it matters, I'm using 2007 to design a
2000 file format.

My first form has a command button. When the command button is clicked I
would like a second form (based on query) to open and be filtered (by combo
box)displaying the indicated set of records.

So, form 1 code:
Private Sub btnGroup1_Click()
On Error GoTo Err_btnOpenGroup1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmConvActivRtgGrp1"
stLinkCriteria = "[GroupID] =" & 1
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize

Exit_btnOpenGroup1_Click:
Exit Sub

Err_btnOpenGroup1_Click:
MsgBox Err.Description
Resume Exit_btnOpenGroup1_Click

End Sub

Form 2 code:
Private Sub Form_Load()
DoCmd.Maximize
Me.Filter = "[JCTARCourse] = " & "'" & cboTARselect1 & "'"
Me.FilterOn = True
Me.sbfrmAnalystClarityList.Requery
Me.sbfrmConvActRtg.Requery
'Me.sbfrmConvActRtg.SetFocus
'Me.sbfrmConvActRtg.Form.[ConvActivityRating].SetFocus
End Sub

Any help would be appreciated.
 
D

Dirk Goldgar

WestWingFan said:
Thank you in advance for any help or advice. I'm trying to do something
which
shouldn't be too hard, but has me stumped. I suspect it has something to
do
with where the code is placed. In case it matters, I'm using 2007 to
design a
2000 file format.

My first form has a command button. When the command button is clicked I
would like a second form (based on query) to open and be filtered (by
combo
box)displaying the indicated set of records.

So, form 1 code:
Private Sub btnGroup1_Click()
On Error GoTo Err_btnOpenGroup1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmConvActivRtgGrp1"
stLinkCriteria = "[GroupID] =" & 1
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize

Exit_btnOpenGroup1_Click:
Exit Sub

Err_btnOpenGroup1_Click:
MsgBox Err.Description
Resume Exit_btnOpenGroup1_Click

End Sub

Form 2 code:
Private Sub Form_Load()
DoCmd.Maximize
Me.Filter = "[JCTARCourse] = " & "'" & cboTARselect1 & "'"
Me.FilterOn = True
Me.sbfrmAnalystClarityList.Requery
Me.sbfrmConvActRtg.Requery
'Me.sbfrmConvActRtg.SetFocus
'Me.sbfrmConvActRtg.Form.[ConvActivityRating].SetFocus
End Sub

Any help would be appreciated.
Click to expand...


So, what's the problem? You need to tell us what you're not getting that
you should, or what you're getting that you shouldn't.

What form is cboTARselect1 on? If it's on the calling form (that is, the
one that is opening frmConvActivRtgGrp1), you would need to qualify the
reference to the combo with a reference to the form. However, if that's
where the button is, there's an easier way to do it.
 
W

WestWingFan

Dirk Goldgar said:
WestWingFan said:
Thank you in advance for any help or advice. I'm trying to do something
which
shouldn't be too hard, but has me stumped. I suspect it has something to
do
with where the code is placed. In case it matters, I'm using 2007 to
design a
2000 file format.

My first form has a command button. When the command button is clicked I
would like a second form (based on query) to open and be filtered (by
combo
box)displaying the indicated set of records.

So, form 1 code:
Private Sub btnGroup1_Click()
On Error GoTo Err_btnOpenGroup1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmConvActivRtgGrp1"
stLinkCriteria = "[GroupID] =" & 1
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize

Exit_btnOpenGroup1_Click:
Exit Sub

Err_btnOpenGroup1_Click:
MsgBox Err.Description
Resume Exit_btnOpenGroup1_Click

End Sub

Form 2 code:
Private Sub Form_Load()
DoCmd.Maximize
Me.Filter = "[JCTARCourse] = " & "'" & cboTARselect1 & "'"
Me.FilterOn = True
Me.sbfrmAnalystClarityList.Requery
Me.sbfrmConvActRtg.Requery
'Me.sbfrmConvActRtg.SetFocus
'Me.sbfrmConvActRtg.Form.[ConvActivityRating].SetFocus
End Sub

Any help would be appreciated.
Click to expand...


So, what's the problem? You need to tell us what you're not getting that
you should, or what you're getting that you shouldn't.

What form is cboTARselect1 on? If it's on the calling form (that is, the
one that is opening frmConvActivRtgGrp1), you would need to qualify the
reference to the combo with a reference to the form. However, if that's
where the button is, there's an easier way to do it.
Click to expand...

Whoops! Sorry about that. The problem is, that when the button is clicked on
the first form, the second form opens, but does not display the desired
information. The filter says it is filtered, but nothing shows on the screen.
The cbo box is blank (and not filtered - it should only display the distinct
options available for the filtered from) therefore the rest of the form is
blank. Does that make sense?

cboTARSelect is on form 2.

Thanks!
 
D

Dirk Goldgar

WestWingFan said:
Whoops! Sorry about that. The problem is, that when the button is clicked
on
the first form, the second form opens, but does not display the desired
information. The filter says it is filtered, but nothing shows on the
screen.
The cbo box is blank (and not filtered - it should only display the
distinct
options available for the filtered from) therefore the rest of the form is
blank. Does that make sense?
Click to expand...

A bit more sense, but I still don't follow everything. I don't see any code
to set the value of the combo box, so unless it has a default value, I would
expect its value to be Null at this point, and so naturally using it in a
filter is not going to return any matching records.
 
W

WestWingFan

A bit more sense, but I still don't follow everything. I don't see any code
to set the value of the combo box, so unless it has a default value, I would
expect its value to be Null at this point, and so naturally using it in a
filter is not going to return any matching records.
Click to expand...

I have code in form 2 to set the rowsource of the combo box. Which is
currently commented out because it does not use a variable, which I suspect
it will have to. The first line is to set the row source. The second is to
select the first value on the list. I would like to do this programatically,
not manually as it is done now. Does my lack of coding show? I'm learning as
I go. The commented out lines look like this:

'Me.cboTARselect1.RowSource = " SELECT DISTINCT JCTARCourse FROM
tblJCTARTitle WHERE (((tblJCTARTitle.GroupID)=1)); "
'Me.cboTARselect1 = "CCNA 1: Networking Basics"

Perhaps this is my question. How do I pass the information from form 1 into
the rowsource of the combobox. I only need to change the WHERE and I know I
could set that to a user prompted parameter, but I would LOVE it if I can get
the computer to do that... and it seems that it should be able to...

Thanks for hanging in there with me!

How would I use code to set the value of the combo box in form? I have
 
D

Dirk Goldgar

WestWingFan said:
I have code in form 2 to set the rowsource of the combo box. Which is
currently commented out because it does not use a variable, which I
suspect
it will have to. The first line is to set the row source. The second is to
select the first value on the list. I would like to do this
programatically,
not manually as it is done now. Does my lack of coding show? I'm learning
as
I go. The commented out lines look like this:

'Me.cboTARselect1.RowSource = " SELECT DISTINCT JCTARCourse FROM
tblJCTARTitle WHERE (((tblJCTARTitle.GroupID)=1)); "
'Me.cboTARselect1 = "CCNA 1: Networking Basics"

Perhaps this is my question. How do I pass the information from form 1
into
the rowsource of the combobox. I only need to change the WHERE and I know
I
could set that to a user prompted parameter, but I would LOVE it if I can
get
the computer to do that... and it seems that it should be able to...

Thanks for hanging in there with me!

How would I use code to set the value of the combo box in form? I have
Click to expand...


Although I'm not sure I understand completely what you want to do, I think
maybe things would be a whole lot simpler if you pass the GroupID as an
argument to the form, and let the form set everything up in its Open event.
So your code to open the form (on what you called form1) would be:

Dim stDocName As String

stDocName = "frmConvActivRtgGrp1"
DoCmd.OpenForm stDocName, OpenArgs:=1
DoCmd.Maximize

And then you would have code in the form's Open event to set the form's
RecordSource, set the combo box's RowSource and value, and set the form's
Filter, like this:

'----- start of code -----
Private Sub Form_Open(Cancel As Integer)

Dim strGroupID As String

strGroupID = Me.OpenArgs & vbNullString

If Len(strGroupID) > 0 Then

' Set form's recordsource to filter by this GroupID.
' ** CHANGE THE SQL STATEMENT BELOW APPROPRIATELY **
Me.RecordSource = _
"SELECT ... FROM ... WHERE GroupID = " & strGroupID

' Set combo box rowsource to filter by this GroupID
Me.cboTARselect1.RowSource = _
"SELECT DISTINCT JCTARCourse FROM tblJCTARTitle " & _
"WHERE GroupID = " & strGroupIID

End If

' Set the combo box to the first value in its list:
With Me.cboTARselect1
If .ListCount > 0 Then
.Value = .ItemData(0)
End If
End With

' Apply a filter based on the combo box.
Me.Filter = "[JCTARCourse] = '" & cboTARselect1 & "'"
Me.FilterOn = True

' Not sure if you have to do these requeries.
Me.sbfrmAnalystClarityList.Requery
Me.sbfrmConvActRtg.Requery

'Me.sbfrmConvActRtg.SetFocus
'Me.sbfrmConvActRtg.Form.[ConvActivityRating].SetFocus

End Sub
'----- end of code -----

So, that may or may not do what you have in mind, because I don't know for
sure that I understand, and I may have just made a mistake anyway. But make
the necessary changes, try it out, and let me know.
 
W

WestWingFan

That worked perfectly!!!! Thank you so much for all of your help.

You rock!

Dirk Goldgar said:
WestWingFan said:
I have code in form 2 to set the rowsource of the combo box. Which is
currently commented out because it does not use a variable, which I
suspect
it will have to. The first line is to set the row source. The second is to
select the first value on the list. I would like to do this
programatically,
not manually as it is done now. Does my lack of coding show? I'm learning
as
I go. The commented out lines look like this:

'Me.cboTARselect1.RowSource = " SELECT DISTINCT JCTARCourse FROM
tblJCTARTitle WHERE (((tblJCTARTitle.GroupID)=1)); "
'Me.cboTARselect1 = "CCNA 1: Networking Basics"

Perhaps this is my question. How do I pass the information from form 1
into
the rowsource of the combobox. I only need to change the WHERE and I know
I
could set that to a user prompted parameter, but I would LOVE it if I can
get
the computer to do that... and it seems that it should be able to...

Thanks for hanging in there with me!

How would I use code to set the value of the combo box in form? I have
Click to expand...


Although I'm not sure I understand completely what you want to do, I think
maybe things would be a whole lot simpler if you pass the GroupID as an
argument to the form, and let the form set everything up in its Open event.
So your code to open the form (on what you called form1) would be:

Dim stDocName As String

stDocName = "frmConvActivRtgGrp1"
DoCmd.OpenForm stDocName, OpenArgs:=1
DoCmd.Maximize

And then you would have code in the form's Open event to set the form's
RecordSource, set the combo box's RowSource and value, and set the form's
Filter, like this:

'----- start of code -----
Private Sub Form_Open(Cancel As Integer)

Dim strGroupID As String

strGroupID = Me.OpenArgs & vbNullString

If Len(strGroupID) > 0 Then

' Set form's recordsource to filter by this GroupID.
' ** CHANGE THE SQL STATEMENT BELOW APPROPRIATELY **
Me.RecordSource = _
"SELECT ... FROM ... WHERE GroupID = " & strGroupID

' Set combo box rowsource to filter by this GroupID
Me.cboTARselect1.RowSource = _
"SELECT DISTINCT JCTARCourse FROM tblJCTARTitle " & _
"WHERE GroupID = " & strGroupIID

End If

' Set the combo box to the first value in its list:
With Me.cboTARselect1
If .ListCount > 0 Then
.Value = .ItemData(0)
End If
End With

' Apply a filter based on the combo box.
Me.Filter = "[JCTARCourse] = '" & cboTARselect1 & "'"
Me.FilterOn = True

' Not sure if you have to do these requeries.
Me.sbfrmAnalystClarityList.Requery
Me.sbfrmConvActRtg.Requery

'Me.sbfrmConvActRtg.SetFocus
'Me.sbfrmConvActRtg.Form.[ConvActivityRating].SetFocus

End Sub
'----- end of code -----

So, that may or may not do what you have in mind, because I don't know for
sure that I understand, and I may have just made a mistake anyway. But make
the necessary changes, try it out, and let me know.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I am not "getting" it.....DoCmd. OpenForm 4
Wherecondition in openform 2
Pass data to new form 2
OpenForm Question 4
Using a wildcard to open a form 2
How to Pass Option Group info to OpenArgs in OpenForm method? 1
Missing operator Error 4
Close and Return to Form 2

Top