"On Click" code to go to record in main form 1

[KennyCaudill]

I haven't used Access for about 10 years and I just started a new database with Access 2007. I have a clients form and have added a subform with all names from the same table as the main form. I want to click any name and have the main form go to that record. I know I'm being lazy, but could someone help me with the code to put in the "On Click" subroutine?

DoCmd.GoToRecord acDataForm, "Clients", acGoTo, (don't know how to get the current record number for here)

Thanks,
Kenny

 

[KennyCaudill]

I just tried this and it didn't work:

DoCmd.GoToRecord acDataForm, "Clients", acGoTo, Forms!Clients.[Clients List subform].CurrentRecord

Run time error 438
"Object doesn't support this property or method"

Kenny

 

[TheAceMan1]

How are ya KennyCaudill . . .

[blue] I have a clients form and have added a subform with all names [red]from the same table[/red] as the main form.[/blue]

Are you saying the [blue]clients name[/blue] is not in the mainform? ... This is backwards! ... What you need is a combobox in the [blue]mainform[/blue] that goes to the client record.

We need form/subform/control names as well as some assemblence of your table structure as well as account of primary/foreign keys in your form structure.

As a completely blind attempt at this ... I guess:

[blue] Forms![purple][B][I]MainFormName[/I][/B][/purple].Recordset.FindFirst "[[purple][B][I]PrimaryKeyName[/I][/B][/purple]] = Me![[purple][B][I]PrimaryKeyName[/I][/B][/purple]][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[KennyCaudill]

The main form does have the client's name. The main form shows one record. The subform shows all clients names down the right hand side of the form. I want to click on a name in the subform and have the main form go to the record for that name.

I'm listing all my attempts to try to clear up what I'm asking:

DoCmd.GoToRecord acDataForm, "Clients", acGoTo, Forms!Clients.[Clients List subform].CurrentRecord

DoCmd.GoToRecord acDataForm, "Clients", acGoTo, Forms!Clients.[Clients List subform].AbsolutePosition

'Set Forms!Clients![CLIENTS KEY] = Forms!Clients.[Clients List subform]![CLIENTS KEY]
'Forms!Clients.Refresh

I'm trying to set the Primary Key in the main form to the Key from the record I click in the subform and refresh or just goto the record number of the clicked name.

The table is called "Clients". The form is based on this table only. The subform is based on this table as well and is used to show all names.

I attached a picture of my table structure, but this form is based on one table only.

Hope this helps and thanks,
Kenny

 

[KennyCaudill]

I tried your suggestion. Here's the code:

Forms!Clients.Recordset.FindFirst "[CLIENTS KEY] = Me![CLIENTS KEY]"

It gave an error saying: The Microsoft Access database engine does not recognize 'Me!CLIENTS KEY' as a valid field name or expression.

This is the Primary Key field for this table and it shows up in the field list for the subform and mainform.

Thanks for helping,
Kenny

 

[KennyCaudill]

I have attached a screen shot of my form. Just created it today and only entered 2 names for testing. I want to click on any last name and have the main form go to that name.

Thanks,
Kenny

 

[TheAceMan1]

KennyCaudill . . .
There's a syntax error in my previous post. One of the following should do:

[blue]Forms!Clients.Recordset.FindFirst "[CLIENTS KEY] = " & Me![CLIENTS KEY]
   or
Forms!Clients.Recordset.FindFirst "[CLIENTS KEY] = '" & Me![CLIENTS KEY] & "'"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[KennyCaudill]

PERFECT, IT WORKED! Exactly what I needed. Thanks so much. I made a database 10 or 11 years ago and lost the code to it 5 years ago. Only have and mde file and the back end data files. It was getting too old and I wanted to do things differently. So I am starting all over again.
Thanks again,
Kenny

 

[TheAceMan1]

BTW ...

[blue] I have a clients form and [purple]have added a subform[/purple] with all names from the same table as the main form.[/blue]

The form you speak of with the names is an independant form. [blue]Shoots[/blue] & [blue]Orders[/blue] are your subforms. This was misleading until I viewed the lask link you provided.

In any case ... good to go.

You take care ... [blue]Ya Hear![/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]