Open form at specific record

[trawets]

Hi
I had asked before about how to make a value of a combo box depend on
another, I received good information on what and how to do it, my
question now is how do open a form from the second combo box at the
chosen record.
What I have now is a form "find contact" which has two combo boxes
where I can choose a company and then a name from that company, from
my
command button I can run the query which find thew chosen entry but I
would like to open the "contacts" form at the specific entry.

my cmd button so far is

Private Sub Run_Click()
DoCmd.OpenQuery "qrycontacts"
DoCmd.OpenForm "Contacts" ( I need to know what syntax I can out here
to open at specific record)
DoCmd.Close acForm, "Find Contact"
DoCmd.Close acQuery, "qrycontacts"
End Sub

\the query may not be neccessary I think

I have searched and have seen a few suggestion but its difficult
finding the best solution and some seem quite complicated

Thanks in anticipation

 

[Klatuu]

You don't need to open or close the query. What you need is the Where
Condition argument of the OpenForm method. See VBA Help for OpenForm method
for details.

 

[trawets]

You don't need to open or close the query.  What you need is the Where
Condition argument of the OpenForm method.  See VBA Help for OpenForm method
for details.
--
Dave Hargis, Microsoft Access MVP










- Show quoted text -

Hi Thanks for the reply

I Have been trying to figure this out today, not having muck joy, Can
find examples which will open form which will give a specific entry

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

but I want that lastname to be the one in my combo box

I have tired various combinations of something like this, am in the
right ball park here

DoCmd.OpenForm "Contacts", , ,"WHERE Contatcs.Last_Name = '
comName.value" &_

could you recommend an example I might reference to help

 

[Klatuu]

The reference to the combo box has to be outside the quotes:

DoCmd.OpenForm "Contacts", , , "WHERE [Last_Name] = """ & Me.comName & """"

Notice I added Me. to comName. It is best to qualify your control names.
Also, you don't need to use the Value property. It is the default property.
I also changed the single qoutes to double qoutes. That is because if you
have someone with a last name of O'Brien, for example, the single quote in
the name would cause the statement to error out.

 

[trawets]

The reference to the combo box has to be outside the quotes:

DoCmd.OpenForm "Contacts", , , "WHERE [Last_Name] =  """ & Me.comName & """"

Notice I added Me. to comName.  It is best to qualify your control names..  
Also, you don't need to use the Value property.  It is the default property.  
I also changed the single qoutes to double qoutes.  That is because if you
have someone with a last name of O'Brien, for example, the single quote in
the name would cause the statement to error out.
--
Dave Hargis, Microsoft Access MVP

Hi Thanks for the reply

I Have been trying to figure this out today, not having muck joy, Can
find examples which will open form which will give a specific entry

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

but I want that lastname to be the one in my combo box

I have tired various combinations of something like this, am in the
right ball park here

DoCmd.OpenForm "Contacts", , ,"WHERE Contatcs.Last_Name =  '
comName.value" &_

could you recommend an example I might reference to help- Hide quoted text -

- Show quoted text -

Thanks for the reply and for explaining a few things, which I'm still
trying to understand
so my expression now reads as you posted (apart from my mistake in
naming the combo)

DoCmd.OpenForm "Contacts", , , "WHERE [Last_Name] = """ & Me.cboName
& """"

It's a lot closer to working, the error I now receive
Syntax error (missing operator) in query expression "WHERE [Last_Name]
="Smith".

which is the name I selected and does change when I change selection.

so not far away but what operator is missing ??? your expression looks
good to me.

Stewart

 

[Klatuu]

My bad, the word Where doesn't belong here.
DoCmd.OpenForm "Contacts", , , "[Last_Name] = """ & Me.comName & """"

My apologies.

--
Dave Hargis, Microsoft Access MVP

The reference to the combo box has to be outside the quotes:

DoCmd.OpenForm "Contacts", , , "WHERE [Last_Name] = """ & Me.comName & """"

Notice I added Me. to comName. It is best to qualify your control names..
Also, you don't need to use the Value property. It is the default property.
I also changed the single qoutes to double qoutes. That is because if you
have someone with a last name of O'Brien, for example, the single quote in
the name would cause the statement to error out.
--
Dave Hargis, Microsoft Access MVP

You don't need to open or close the query. What you need is the Where
Condition argument of the OpenForm method. See VBA Help for OpenForm method
for details.

:
Hi
I had asked before about how to make a value of a combo box depend on
another, I received good information on what and how to do it, my
question now is how do open a form from the second combo box at the
chosen record.
What I have now is a form "find contact" which has two combo boxes
where I can choose a company and then a name from that company, from
my
command button I can run the query which find thew chosen entry but I
would like to open the "contacts" form at the specific entry.

my cmd button so far is

Private Sub Run_Click()
DoCmd.OpenQuery "qrycontacts"
DoCmd.OpenForm "Contacts" ( I need to know what syntax I can out here
to open at specific record)
DoCmd.Close acForm, "Find Contact"
DoCmd.Close acQuery, "qrycontacts"
End Sub

\the query may not be necessary I think

I have searched and have seen a few suggestion but its difficult
finding the best solution and some seem quite complicated

Thanks in anticipation- Hide quoted text -

- Show quoted text -

Hi Thanks for the reply

I Have been trying to figure this out today, not having muck joy, Can
find examples which will open form which will give a specific entry

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

but I want that lastname to be the one in my combo box

I have tired various combinations of something like this, am in the
right ball park here

DoCmd.OpenForm "Contacts", , ,"WHERE Contatcs.Last_Name = '
comName.value" &_

could you recommend an example I might reference to help- Hide quoted text -

- Show quoted text -

Thanks for the reply and for explaining a few things, which I'm still
trying to understand
so my expression now reads as you posted (apart from my mistake in
naming the combo)

DoCmd.OpenForm "Contacts", , , "WHERE [Last_Name] = """ & Me.cboName
& """"

It's a lot closer to working, the error I now receive
Syntax error (missing operator) in query expression "WHERE [Last_Name]
="Smith".

which is the name I selected and does change when I change selection.

so not far away but what operator is missing ??? your expression looks
good to me.

Stewart

 

[trawets]

My bad, the word Where doesn't belong here.
DoCmd.OpenForm "Contacts", , , "[Last_Name] =  """ & Me.comName & """"

My apologies.

--
Dave Hargis, Microsoft Access MVP

The reference to the combo box has to be outside the quotes:
DoCmd.OpenForm "Contacts", , , "WHERE [Last_Name] =  """ & Me.comName & """"
Notice I added Me. to comName.  It is best to qualify your control names..  
Also, you don't need to use the Value property.  It is the default property.  
I also changed the single qoutes to double qoutes.  That is because if you
have someone with a last name of O'Brien, for example, the single quote in
the name would cause the statement to error out.
--
Dave Hargis, Microsoft Access MVP
:
You don't need to open or close the query.  What you need is theWhere
Condition argument of the OpenForm method.  See VBA Help for OpenForm method
for details.
--
Dave Hargis, Microsoft Access MVP
:
Hi
I had asked before about how to make a value of a combo box depend on
another, I received good information on what and how to do it, my
question now is how do open a form from the second combo box at the
chosen record.
What I have now is a form "find contact" which has two combo boxes
where I can choose a company and then a name from that company, from
my
command button I can run the query which find thew chosen entry but I
would like to open the "contacts" form at the specific entry.
my cmd button so far is
Private Sub Run_Click()
DoCmd.OpenQuery "qrycontacts"
DoCmd.OpenForm "Contacts"  ( I need to know what syntax I can out here
to open at specific record)
DoCmd.Close acForm, "Find Contact"
DoCmd.Close acQuery, "qrycontacts"
End Sub
\the query may not be necessary I think
I have searched and have seen a few suggestion but its difficult
finding the best solution and some seem quite complicated
Thanks in anticipation- Hide quoted text -
- Show quoted text -
Hi Thanks for the reply
I Have been trying to figure this out today, not having muck joy, Can
find examples which will open form which will give a specific entry
DoCmd.OpenForm "Employees", , ,"LastName = 'King'"
but I want that lastname to be the one in my combo box
I have tired various combinations of something like this, am in the
right ball park here
DoCmd.OpenForm "Contacts", , ,"WHERE Contatcs.Last_Name =  '
comName.value" &_
could you recommend an example I might reference to help- Hide quoted text -
- Show quoted text -

Thanks for the reply and for explaining a few things, which I'm still
trying to understand
so my expression now reads as you posted (apart from my mistake in
naming the combo)

DoCmd.OpenForm "Contacts", , , "WHERE [Last_Name] =  """ & Me.cboName
& """"

It's a lot closer to working,  the error I now receive
Syntax error (missing operator) in query expression "WHERE [Last_Name]
="Smith".

which is the name I selected and does change when I change selection.

so not far away but what operator is missing ??? your expression looks
good to me.

Stewart- Hide quoted text -

- Show quoted text -

Thank You, Thank You, Thank You
I really appreciate all you help,
I'll probally need to come back for more later, you guys sure are the
tops
Stewart