Form with a tab and subform

[PennyB]

I have a search page that looks up projects from a query. The query has two
tables the Project table has the job number and the Permit table has the
permit number.

What I would like to do is once a user narrows the search to the project
they want is to click on a command button that takes them to the main form
which has the subform on a tab to the project and permit number they want.

I can get it to come back to the Job Number with out a problem with the
following:

stDocName = "frmProjectsMain"
stLinkCriteria = "[JobNumber]=" & "'" & Me![txtJobNumber] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

But when I try to add the subform's permit task number it stops working.

Any ideas?

Thanks.

 

[Douglas J. Steele]

How are you attempting to add the subform's permit task number? What does
"stops working" mean in this context? Are you getting an error? If so,
what's the exact error message? If you're not getting an error, what are you
getting and what did you want to get instead?

 

[PennyB]

This is how I was trying to add it:

stDocName = "frmProjectsMain"
stLinkCriteria = "[JobNumber]=" & "" & Me![txtJobNumber] & ""
stLinkCriteria =
"Forms![frmProjectsMain]![frmzPermitMainSub]Form![PermitTaskNumber] = " & ""
& Me![txtPermitTaskNumber] & ""

DoCmd.OpenForm stDocName, , , stLinkCriteria

When I do the button without the third line it opens to the correct job
number. When I add the third line I get a syntax error that an operator is
missing.

Thanks.

How are you attempting to add the subform's permit task number? What does
"stops working" mean in this context? Are you getting an error? If so,
what's the exact error message? If you're not getting an error, what are you
getting and what did you want to get instead?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a search page that looks up projects from a query. The query has
two
tables the Project table has the job number and the Permit table has the
permit number.

What I would like to do is once a user narrows the search to the project
they want is to click on a command button that takes them to the main form
which has the subform on a tab to the project and permit number they want.

I can get it to come back to the Job Number with out a problem with the
following:

stDocName = "frmProjectsMain"
stLinkCriteria = "[JobNumber]=" & "'" & Me![txtJobNumber] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

But when I try to add the subform's permit task number it stops working.

Any ideas?

Thanks.

 

[Marshall Barton]

stDocName = "frmProjectsMain"
stLinkCriteria = "[JobNumber]=" & "" & Me![txtJobNumber] & ""
stLinkCriteria =
"Forms![frmProjectsMain]![frmzPermitMainSub]Form![PermitTaskNumber] = " & ""
& Me![txtPermitTaskNumber] & ""

DoCmd.OpenForm stDocName, , , stLinkCriteria

When I do the button without the third line it opens to the correct job
number. When I add the third line I get a syntax error that an operator is
missing.

Try this:

stLinkCriteria = "JobNumber=" & Me!txtJobNumber & _
" AND PermitTaskNumber = " & Me!txtPermitTaskNumber

Your [ ] were ok, but since they were not needed, I removed
them to make it easier to read.

 

[PennyB]

We got it to work with this code. It opens to the correct project and
correct permit, but I am not sure how to get it to go to the field when it
opens as the field is on a tab called PermitMainTab and is in the second tab,
but the page number is 1.

Any suggestions?

Thanks for your help.

Private Sub JobNumber_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

Dim frmPermitMainSub As Form
Dim rstPermitMainSub As DAO.Recordset


stDocName = "frmProjectsMain"
stLinkCriteria = "[JobNumber]=" & "" & Me![JobNumber] & ""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Set frmPermitMainSub = Forms.frmProjectsMain.frmzPermitMainSub.Form
Set rstPermitMainSub = frmPermitMainSub.RecordsetClone

stLinkCriteria = "[PermitTaskNumber]=" & "" & Me![PermitTaskNumber] & ""

rstPermitMainSub.FindFirst stLinkCriteria

If Not rstPermitMainSub.NoMatch Then
frmPermitMainSub.Bookmark = rstPermitMainSub.Bookmark
End If


Exit_JobNumber_DblClic:
Exit Sub




Err_JobNumber_DblClick:
MsgBox Err.Description
Resume Exit_JobNumber_DblClic


End Sub

stDocName = "frmProjectsMain"
stLinkCriteria = "[JobNumber]=" & "" & Me![txtJobNumber] & ""
stLinkCriteria =
"Forms![frmProjectsMain]![frmzPermitMainSub]Form![PermitTaskNumber] = " & ""
& Me![txtPermitTaskNumber] & ""

DoCmd.OpenForm stDocName, , , stLinkCriteria

When I do the button without the third line it opens to the correct job
number. When I add the third line I get a syntax error that an operator is
missing.

Try this:

stLinkCriteria = "JobNumber=" & Me!txtJobNumber & _
" AND PermitTaskNumber = " & Me!txtPermitTaskNumber

Your [ ] were ok, but since they were not needed, I removed
them to make it easier to read.

 

[Marshall Barton]

We got it to work with this code. It opens to the correct project and
correct permit, but I am not sure how to get it to go to the field when it
opens as the field is on a tab called PermitMainTab and is in the second tab,
but the page number is 1.

Private Sub JobNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim frmPermitMainSub As Form
Dim rstPermitMainSub As DAO.Recordset

stDocName = "frmProjectsMain"
stLinkCriteria = "[JobNumber]=" & "" & Me![JobNumber] & ""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Set frmPermitMainSub = Forms.frmProjectsMain.frmzPermitMainSub.Form
Set rstPermitMainSub = frmPermitMainSub.RecordsetClone

stLinkCriteria = "[PermitTaskNumber]=" & "" & Me![PermitTaskNumber] & ""

rstPermitMainSub.FindFirst stLinkCriteria

If Not rstPermitMainSub.NoMatch Then
frmPermitMainSub.Bookmark = rstPermitMainSub.Bookmark
End If


Exit_JobNumber_DblClic:
Exit Sub

Err_JobNumber_DblClick:
MsgBox Err.Description
Resume Exit_JobNumber_DblClic

End Sub

Which **control** (fields are columns in a table/query while
controls are text boxes, buttons, etc on a form/report) do
you want to "go to"? Which form is the control on? What do
you mean by "go to", give it the focus?

Here's some thoughts that may help. You can display a page
in a tab control by setting the tab control's value to the
page number (page numbers start at 0):
frmPermitMainSub.tabcontrol = 1
You can set the focus to a specific control by using the
SetFocus method:
frmPermitMainSub.thecontrol.SetFocus

 

[PennyB]

Forms!frmProjectsMain!MainFormTab = 1

This worked great!!!!

Thanks for your help

We got it to work with this code. It opens to the correct project and
correct permit, but I am not sure how to get it to go to the field when it
opens as the field is on a tab called PermitMainTab and is in the second tab,
but the page number is 1.

Private Sub JobNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim frmPermitMainSub As Form
Dim rstPermitMainSub As DAO.Recordset

stDocName = "frmProjectsMain"
stLinkCriteria = "[JobNumber]=" & "" & Me![JobNumber] & ""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Set frmPermitMainSub = Forms.frmProjectsMain.frmzPermitMainSub.Form
Set rstPermitMainSub = frmPermitMainSub.RecordsetClone

stLinkCriteria = "[PermitTaskNumber]=" & "" & Me![PermitTaskNumber] & ""

rstPermitMainSub.FindFirst stLinkCriteria

If Not rstPermitMainSub.NoMatch Then
frmPermitMainSub.Bookmark = rstPermitMainSub.Bookmark
End If


Exit_JobNumber_DblClic:
Exit Sub

Err_JobNumber_DblClick:
MsgBox Err.Description
Resume Exit_JobNumber_DblClic

End Sub

Which **control** (fields are columns in a table/query while
controls are text boxes, buttons, etc on a form/report) do
you want to "go to"? Which form is the control on? What do
you mean by "go to", give it the focus?

Here's some thoughts that may help. You can display a page
in a tab control by setting the tab control's value to the
page number (page numbers start at 0):
frmPermitMainSub.tabcontrol = 1
You can set the focus to a specific control by using the
SetFocus method:
frmPermitMainSub.thecontrol.SetFocus