Open form command

G

Guest

Hi,

I have a 2 forms. 1 called "company" and one called "centre". I have a
command button on the company form to open the centre form and filter for all
centres that are managed by the given company. Sounds simple enough but i'm
not having any luck with it so far.

Where the probem lies i think is that the primary fields of the 2 tables are
linked in a joining table "ManOrgAtCentre". I have the management
organisation appearing in the centre form in a subform. I have then created a
textbox to draw the companyID from the subform and put it into the main form
and that works fine. Where i run into trouble is that using the wizard in the
open form command it wont display the textbox control "ManCompanyID". As such
i tried to fix it up in VBA (where i am a total amateur) but can't get it
working. My code is below.


Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & "'" &
Me![CompanyID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub



If anyone can find the problem in this please let me know.
 
G

Guest

Hi,

If your ManCompanyID field is a numeric field, not text, you can't use the
single quotes, you need to use the following:

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]

Hope this helps.

Damian.
 
G

Guest

Damien,

Thanks for the response. The code you supplied now opens the form without
errors however it opens a 1 filtered new(blank) form and not the centres with
the matching companyID? Not sure why as the id's are definately visible in
both forms. The code is again below.

Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub

Damian S said:
Hi,

If your ManCompanyID field is a numeric field, not text, you can't use the
single quotes, you need to use the following:

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]

Hope this helps.

Damian.

fordrules01 said:
Hi,

I have a 2 forms. 1 called "company" and one called "centre". I have a
command button on the company form to open the centre form and filter for all
centres that are managed by the given company. Sounds simple enough but i'm
not having any luck with it so far.

Where the probem lies i think is that the primary fields of the 2 tables are
linked in a joining table "ManOrgAtCentre". I have the management
organisation appearing in the centre form in a subform. I have then created a
textbox to draw the companyID from the subform and put it into the main form
and that works fine. Where i run into trouble is that using the wizard in the
open form command it wont display the textbox control "ManCompanyID". As such
i tried to fix it up in VBA (where i am a total amateur) but can't get it
working. My code is below.


Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & "'" &
Me![CompanyID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub



If anyone can find the problem in this please let me know.
Click to expand...
Click to expand...
 
G

Guest

Do the ManCompanyID and CompanyID fields refer to the same data? ie:
ManCompanyID 1 is meant to link to CompanyID 1?

D.

fordrules01 said:
Damien,

Thanks for the response. The code you supplied now opens the form without
errors however it opens a 1 filtered new(blank) form and not the centres with
the matching companyID? Not sure why as the id's are definately visible in
both forms. The code is again below.

Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub

Damian S said:
Hi,

If your ManCompanyID field is a numeric field, not text, you can't use the
single quotes, you need to use the following:

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]

Hope this helps.

Damian.

fordrules01 said:
Hi,

I have a 2 forms. 1 called "company" and one called "centre". I have a
command button on the company form to open the centre form and filter for all
centres that are managed by the given company. Sounds simple enough but i'm
not having any luck with it so far.

Where the probem lies i think is that the primary fields of the 2 tables are
linked in a joining table "ManOrgAtCentre". I have the management
organisation appearing in the centre form in a subform. I have then created a
textbox to draw the companyID from the subform and put it into the main form
and that works fine. Where i run into trouble is that using the wizard in the
open form command it wont display the textbox control "ManCompanyID". As such
i tried to fix it up in VBA (where i am a total amateur) but can't get it
working. My code is below.


Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & "'" &
Me![CompanyID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub



If anyone can find the problem in this please let me know.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes thats correct

Damian S said:
Do the ManCompanyID and CompanyID fields refer to the same data? ie:
ManCompanyID 1 is meant to link to CompanyID 1?

D.

fordrules01 said:
Damien,

Thanks for the response. The code you supplied now opens the form without
errors however it opens a 1 filtered new(blank) form and not the centres with
the matching companyID? Not sure why as the id's are definately visible in
both forms. The code is again below.

Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub

Damian S said:
Hi,

If your ManCompanyID field is a numeric field, not text, you can't use the
single quotes, you need to use the following:

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]

Hope this helps.

Damian.

:

Hi,

I have a 2 forms. 1 called "company" and one called "centre". I have a
command button on the company form to open the centre form and filter for all
centres that are managed by the given company. Sounds simple enough but i'm
not having any luck with it so far.

Where the probem lies i think is that the primary fields of the 2 tables are
linked in a joining table "ManOrgAtCentre". I have the management
organisation appearing in the centre form in a subform. I have then created a
textbox to draw the companyID from the subform and put it into the main form
and that works fine. Where i run into trouble is that using the wizard in the
open form command it wont display the textbox control "ManCompanyID". As such
i tried to fix it up in VBA (where i am a total amateur) but can't get it
working. My code is below.


Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & "'" &
Me![CompanyID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub



If anyone can find the problem in this please let me know.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

In that case, have you considered that there may be no matching data for the
Company that is being returned? If you know that there is data there that
should match, check that you don't have any additional filters set by
clicking records->remove filter/sort.

D.

fordrules01 said:
Yes thats correct

Damian S said:
Do the ManCompanyID and CompanyID fields refer to the same data? ie:
ManCompanyID 1 is meant to link to CompanyID 1?

D.

fordrules01 said:
Damien,

Thanks for the response. The code you supplied now opens the form without
errors however it opens a 1 filtered new(blank) form and not the centres with
the matching companyID? Not sure why as the id's are definately visible in
both forms. The code is again below.

Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub

:

Hi,

If your ManCompanyID field is a numeric field, not text, you can't use the
single quotes, you need to use the following:

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]

Hope this helps.

Damian.

:

Hi,

I have a 2 forms. 1 called "company" and one called "centre". I have a
command button on the company form to open the centre form and filter for all
centres that are managed by the given company. Sounds simple enough but i'm
not having any luck with it so far.

Where the probem lies i think is that the primary fields of the 2 tables are
linked in a joining table "ManOrgAtCentre". I have the management
organisation appearing in the centre form in a subform. I have then created a
textbox to draw the companyID from the subform and put it into the main form
and that works fine. Where i run into trouble is that using the wizard in the
open form command it wont display the textbox control "ManCompanyID". As such
i tried to fix it up in VBA (where i am a total amateur) but can't get it
working. My code is below.


Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & "'" &
Me![CompanyID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub



If anyone can find the problem in this please let me know.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The data is definately there as i have got a subform in the company form
showing all the centres that below to that company. All i want is for the
user then to be able to open the centres form filtered so they can look at
the full details for all these centres. I can't find any filters on them.

Damian S said:
In that case, have you considered that there may be no matching data for the
Company that is being returned? If you know that there is data there that
should match, check that you don't have any additional filters set by
clicking records->remove filter/sort.

D.

fordrules01 said:
Yes thats correct

Damian S said:
Do the ManCompanyID and CompanyID fields refer to the same data? ie:
ManCompanyID 1 is meant to link to CompanyID 1?

D.

:

Damien,

Thanks for the response. The code you supplied now opens the form without
errors however it opens a 1 filtered new(blank) form and not the centres with
the matching companyID? Not sure why as the id's are definately visible in
both forms. The code is again below.

Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub

:

Hi,

If your ManCompanyID field is a numeric field, not text, you can't use the
single quotes, you need to use the following:

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & Me![CompanyID]

Hope this helps.

Damian.

:

Hi,

I have a 2 forms. 1 called "company" and one called "centre". I have a
command button on the company form to open the centre form and filter for all
centres that are managed by the given company. Sounds simple enough but i'm
not having any luck with it so far.

Where the probem lies i think is that the primary fields of the 2 tables are
linked in a joining table "ManOrgAtCentre". I have the management
organisation appearing in the centre form in a subform. I have then created a
textbox to draw the companyID from the subform and put it into the main form
and that works fine. Where i run into trouble is that using the wizard in the
open form command it wont display the textbox control "ManCompanyID". As such
i tried to fix it up in VBA (where i am a total amateur) but can't get it
working. My code is below.


Private Sub CmdFilterCentreList_Click()
On Error GoTo Err_CmdFilterCentreList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Centre"

stLinkCriteria = "[Forms].[Centre].[ManCompanyID]=" & "'" &
Me![CompanyID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdFilterCentreList_Click:
Exit Sub

Err_CmdFilterCentreList_Click:
MsgBox Err.Description
Resume Exit_CmdFilterCentreList_Click

End Sub



If anyone can find the problem in this please let me know.
Click to expand...
Click to expand...
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

search command 2
subform option group not working 5
Form Button to Open another Form 7
Cant edit Access 2003 form 3
Multiple Search Command criteria in a form 2
Open another subform from a subform 1
Continuous Form OnClick 3
trying to open two forms on maximized other usually max. but small 7

Top