Help with Docmd Open form

T

Tony Williams

I have a form called "frmprojects" on which is a combo box "cmbproject" and
a command button "cmdopen" I want to open a form "Time Cards 2" where the
value of "cmbproject" is equal to the value of "Projectname" on the subform
"Clientsubform" which is a subform of "Time card 2"
I have used this code but I get an error message that says "Syntax error"

Private Sub cmdOpen_Click()
Docmd.OpenForm ("Time cards 2",acNormal,,Forms![frmprojects].[cmbproject] =
Forms!["Time card 2"]![Client subform].Form![Projectname])
End Sub

Can anyone help me with the syntax here, I thought I'd followed the proper
convention.
Thanks
Tony
 
R

RoyVidar

Tony Williams said:
I have a form called "frmprojects" on which is a combo box
"cmbproject" and a command button "cmdopen" I want to open a form
"Time Cards 2" where the value of "cmbproject" is equal to the value
of "Projectname" on the subform "Clientsubform" which is a subform
of "Time card 2" I have used this code but I get an error message
that says "Syntax error"

Private Sub cmdOpen_Click()
Docmd.OpenForm ("Time cards
2",acNormal,,Forms![frmprojects].[cmbproject] = Forms!["Time card
2"]![Client subform].Form![Projectname]) End Sub

Can anyone help me with the syntax here, I thought I'd followed the
proper convention.
Thanks
Tony

1 - drop the parenthesis
2 - the usual recommandations, are to avoid spaces and special
characters in the name of objects, though I think it should
be OK without [brackets] in the OpenForm method of the DoCmd
object
3 - the where condition should be a *valid* SQL WHERE clause,
without the keyword WHERE, which would mean including a field
name from the form recordsource

Most of this, you would probably have found out by hitting F1 while
the cursor is within the OpenForm keyword in VBE, where there is at
least one sample

Docmd.OpenForm "Time cards 2",,, "Projectname = '" & _
Me!Clientsubform.Form!cmbproject & "'"

From what you say, you seem to want use a criterion received from a
subform of the form you want to open. I don't think that will work,
but do tweak a little with the where condition of the above
suggestion, where I assume the subform is a subform residing on the
current form (Me) in stead.
 
T

Tony Williams

Thanks Daniel , if I do that I get a dropdown list of form properties not
field names. If I use ! instead of . I get a message that says Expected =
Any use?
Tony
Daniel said:
Tony,

The only thing that I noticed was shouldn't
Forms![frmprojects].[cmbproject]
be Forms![frmprojects].Form.[cmbproject] ?

Daniel



Tony Williams said:
I have a form called "frmprojects" on which is a combo box "cmbproject"
and
a command button "cmdopen" I want to open a form "Time Cards 2" where the
value of "cmbproject" is equal to the value of "Projectname" on the
subform
"Clientsubform" which is a subform of "Time card 2"
I have used this code but I get an error message that says "Syntax error"

Private Sub cmdOpen_Click()
Docmd.OpenForm ("Time cards 2",acNormal,,Forms![frmprojects].[cmbproject]
=
Forms!["Time card 2"]![Client subform].Form![Projectname])
End Sub

Can anyone help me with the syntax here, I thought I'd followed the
proper
convention.
Thanks
Tony
 
T

Tony Williams

Thanks Roy I got the suggested syntax from the Microsoft site!

I'm not sure I follow your suggested code. I want to open a form where the
value of a field on a subform on that form is equal to the value I select of
a combo box on an open foen form. cmbproject is on my open form not on the
subform which is what I think you have in Me!Clientsubform.Form!cmbproject
& "'" or am I misunderstanding the code?

Thanks for your help
Tony

RoyVidar said:
Tony Williams said:
I have a form called "frmprojects" on which is a combo box
"cmbproject" and a command button "cmdopen" I want to open a form
"Time Cards 2" where the value of "cmbproject" is equal to the value
of "Projectname" on the subform "Clientsubform" which is a subform
of "Time card 2" I have used this code but I get an error message
that says "Syntax error"

Private Sub cmdOpen_Click()
Docmd.OpenForm ("Time cards
2",acNormal,,Forms![frmprojects].[cmbproject] = Forms!["Time card
2"]![Client subform].Form![Projectname]) End Sub

Can anyone help me with the syntax here, I thought I'd followed the
proper convention.
Thanks
Tony

1 - drop the parenthesis
2 - the usual recommandations, are to avoid spaces and special
characters in the name of objects, though I think it should
be OK without [brackets] in the OpenForm method of the DoCmd
object
3 - the where condition should be a *valid* SQL WHERE clause,
without the keyword WHERE, which would mean including a field
name from the form recordsource

Most of this, you would probably have found out by hitting F1 while
the cursor is within the OpenForm keyword in VBE, where there is at
least one sample

Docmd.OpenForm "Time cards 2",,, "Projectname = '" & _
Me!Clientsubform.Form!cmbproject & "'"

From what you say, you seem to want use a criterion received from a
subform of the form you want to open. I don't think that will work,
but do tweak a little with the where condition of the above
suggestion, where I assume the subform is a subform residing on the
current form (Me) in stead.
 
R

RoyVidar

Tony Williams said:
Thanks Roy I got the suggested syntax from the Microsoft site!

I'm not sure I follow your suggested code. I want to open a form
where the value of a field on a subform on that form is equal to the
value I select of a combo box on an open foen form. cmbproject is on
my open form not on the subform which is what I think you have in
Me!Clientsubform.Form!cmbproject & "'" or am I misunderstanding the
code?

Thanks for your help
Tony

RoyVidar said:
Tony Williams said:
I have a form called "frmprojects" on which is a combo box
"cmbproject" and a command button "cmdopen" I want to open a form
"Time Cards 2" where the value of "cmbproject" is equal to the
value of "Projectname" on the subform "Clientsubform" which is a
subform of "Time card 2" I have used this code but I get an error
message that says "Syntax error"

Private Sub cmdOpen_Click()
Docmd.OpenForm ("Time cards
2",acNormal,,Forms![frmprojects].[cmbproject] = Forms!["Time card
2"]![Client subform].Form![Projectname]) End Sub

Can anyone help me with the syntax here, I thought I'd followed the
proper convention.
Thanks
Tony

1 - drop the parenthesis
2 - the usual recommandations, are to avoid spaces and special
characters in the name of objects, though I think it should
be OK without [brackets] in the OpenForm method of the DoCmd
object
3 - the where condition should be a *valid* SQL WHERE clause,
without the keyword WHERE, which would mean including a field
name from the form recordsource

Most of this, you would probably have found out by hitting F1 while
the cursor is within the OpenForm keyword in VBE, where there is at
least one sample

Docmd.OpenForm "Time cards 2",,, "Projectname = '" & _
Me!Clientsubform.Form!cmbproject & "'"

From what you say, you seem to want use a criterion received from a
subform of the form you want to open. I don't think that will work,
but do tweak a little with the where condition of the above
suggestion, where I assume the subform is a subform residing on the
current form (Me) in stead.

Then perhaps this minor tweak

Docmd.OpenForm "Time cards 2",,, "Projectname = '" & _
Me!cmbproject & "'"

And again - the WhereCondition needs a *field* which is in the
recordsource of the form you wish to open. Do you really have a
*field* in the recordsource called cmbproject? Or is that really
just a control?
 
R

RoyVidar

RoyVidar said:
Then perhaps this minor tweak

Docmd.OpenForm "Time cards 2",,, "Projectname = '" & _
Me!cmbproject & "'"

And again - the WhereCondition needs a *field* which is in the
recordsource of the form you wish to open. Do you really have a
*field* in the recordsource called cmbproject? Or is that really
just a control?

I've assumed that you have a field called Projectname in the
recordsource of the form you open, and a control called cmbproject
on the current form.
 
T

Tony Williams

Roy "cmbproject" is a combo control on a form made up of the values of a
field called "projectname" in my table and when I select a project I want
then to open my form timecards2 (got rid of spaces) where the value of
cmbproject is the same as the field "projectname" on the subform "Clients
subform" on my main formtimecards2. Does that make sense?
Thanks
Tony
RoyVidar said:
Tony Williams said:
Thanks Roy I got the suggested syntax from the Microsoft site!

I'm not sure I follow your suggested code. I want to open a form
where the value of a field on a subform on that form is equal to the
value I select of a combo box on an open foen form. cmbproject is on
my open form not on the subform which is what I think you have in
Me!Clientsubform.Form!cmbproject & "'" or am I misunderstanding the
code?

Thanks for your help
Tony

RoyVidar said:
<[email protected]>:
I have a form called "frmprojects" on which is a combo box
"cmbproject" and a command button "cmdopen" I want to open a form
"Time Cards 2" where the value of "cmbproject" is equal to the
value of "Projectname" on the subform "Clientsubform" which is a
subform of "Time card 2" I have used this code but I get an error
message that says "Syntax error"

Private Sub cmdOpen_Click()
Docmd.OpenForm ("Time cards
2",acNormal,,Forms![frmprojects].[cmbproject] = Forms!["Time card
2"]![Client subform].Form![Projectname]) End Sub

Can anyone help me with the syntax here, I thought I'd followed the
proper convention.
Thanks
Tony

1 - drop the parenthesis
2 - the usual recommandations, are to avoid spaces and special
characters in the name of objects, though I think it should
be OK without [brackets] in the OpenForm method of the DoCmd
object
3 - the where condition should be a *valid* SQL WHERE clause,
without the keyword WHERE, which would mean including a field
name from the form recordsource

Most of this, you would probably have found out by hitting F1 while
the cursor is within the OpenForm keyword in VBE, where there is at
least one sample

Docmd.OpenForm "Time cards 2",,, "Projectname = '" & _
Me!Clientsubform.Form!cmbproject & "'"

From what you say, you seem to want use a criterion received from a
subform of the form you want to open. I don't think that will work,
but do tweak a little with the where condition of the above
suggestion, where I assume the subform is a subform residing on the
current form (Me) in stead.

Then perhaps this minor tweak

Docmd.OpenForm "Time cards 2",,, "Projectname = '" & _
Me!cmbproject & "'"

And again - the WhereCondition needs a *field* which is in the
recordsource of the form you wish to open. Do you really have a
*field* in the recordsource called cmbproject? Or is that really
just a control?
 
T

Tony Williams

Roy when I use your code I get a prompt box asking me for "Projectname"
that's why I thought I had to define that it was on a subform using
Forms![Timecards2]![Clients subform].Form![Projectname]
??????
Tony
RoyVidar said:
Tony Williams said:
Thanks Roy I got the suggested syntax from the Microsoft site!

I'm not sure I follow your suggested code. I want to open a form
where the value of a field on a subform on that form is equal to the
value I select of a combo box on an open foen form. cmbproject is on
my open form not on the subform which is what I think you have in
Me!Clientsubform.Form!cmbproject & "'" or am I misunderstanding the
code?

Thanks for your help
Tony

RoyVidar said:
<[email protected]>:
I have a form called "frmprojects" on which is a combo box
"cmbproject" and a command button "cmdopen" I want to open a form
"Time Cards 2" where the value of "cmbproject" is equal to the
value of "Projectname" on the subform "Clientsubform" which is a
subform of "Time card 2" I have used this code but I get an error
message that says "Syntax error"

Private Sub cmdOpen_Click()
Docmd.OpenForm ("Time cards
2",acNormal,,Forms![frmprojects].[cmbproject] = Forms!["Time card
2"]![Client subform].Form![Projectname]) End Sub

Can anyone help me with the syntax here, I thought I'd followed the
proper convention.
Thanks
Tony

1 - drop the parenthesis
2 - the usual recommandations, are to avoid spaces and special
characters in the name of objects, though I think it should
be OK without [brackets] in the OpenForm method of the DoCmd
object
3 - the where condition should be a *valid* SQL WHERE clause,
without the keyword WHERE, which would mean including a field
name from the form recordsource

Most of this, you would probably have found out by hitting F1 while
the cursor is within the OpenForm keyword in VBE, where there is at
least one sample

Docmd.OpenForm "Time cards 2",,, "Projectname = '" & _
Me!Clientsubform.Form!cmbproject & "'"

From what you say, you seem to want use a criterion received from a
subform of the form you want to open. I don't think that will work,
but do tweak a little with the where condition of the above
suggestion, where I assume the subform is a subform residing on the
current form (Me) in stead.

Then perhaps this minor tweak

Docmd.OpenForm "Time cards 2",,, "Projectname = '" & _
Me!cmbproject & "'"

And again - the WhereCondition needs a *field* which is in the
recordsource of the form you wish to open. Do you really have a
*field* in the recordsource called cmbproject? Or is that really
just a control?
 
T

Tony Williams

Yes Roy that's correct
Tony
RoyVidar said:
I've assumed that you have a field called Projectname in the
recordsource of the form you open, and a control called cmbproject
on the current form.
 
R

RoyVidar

Tony Williams said:
Yes Roy that's correct
Tony

Then, if the combo is bound to the column containing the text
(the actual project name), I'd think this should work.
 
R

RoyVidar

Tony Williams said:
Roy "cmbproject" is a combo control on a form made up of the values
of a field called "projectname" in my table and when I select a
project I want then to open my form timecards2 (got rid of spaces)
where the value of cmbproject is the same as the field "projectname"
on the subform "Clients subform" on my main formtimecards2. Does
that make sense? Thanks
Tony

No, I'm afraid it doesn't make sense to me.

I thought the WhereCondition arguement of the OpenForm method was clean
and simple.

1 you need the name of a field in the recordsource of the form you open
2 you need a criterion from somewhere, typically a form control
3 throw them together with the correct delimiters according to the data
type of the field in question, and voila...

"SomeTextField = '" & Me!txtSomeControl & "'"

So, consentrate on the field name. Does it really exist in the
recordsource of the form you wish to open?

Then, the criterion. Be sure it is spelled correct, and residing where
you think it resides. For instance the current form. Test by using
Debug.Print Me!txtYourControl and see what value it gives you in the
immediate pane (ctrl+g)...
 
T

Tony Williams

Thanks Roy I think it's back to the drawing board. As it's 19.20 here in the
UK I'll have a go in the morning. Brain's gone to sleep!!
Thanks again
Tony
 
R

RoyVidar

Tony Williams said:
Thanks Roy I think it's back to the drawing board. As it's 19.20 here
in the UK I'll have a go in the morning. Brain's gone to sleep!!
Thanks again
Tony

Post back with the progress, and good luck!
 

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

Top