Subform help and macros

[KevinH]

I have created a form with a datasheet subform, I would like to click on a record in the datasheet subform and have it open and displayed in another form I have created.
I have created a maco to open this second form, but it goes to the first record in the table instead of the record I am clicking on? Any help would be greatly appreciated.



Thanks,

Kevin

[MAF4Fam6]

Try updating the Where Condition in your Macro's Open Form Action.

i.e.

[numSSAN]=[Forms]![frmNumbersSumLock]![numSSAN]

In this example, the Primary Key on both forms is the numSSAN field name.
Hope this helps!

-RC

[KevinH]

Your response helped and I'm almost there! I set my macro up to open the table where there record is at, open the form that I want to view it in, added the code in the where concition and then added a close table,

I get a pop up box asking for parameters, if I enter the primary key then the form opens to the record I had clicked on. This is the result I wanted but is there a way to by pass adding this parameter and having the form open directly to the selected record?

In the code you supplied I assume the [frmNumbersSumLock] section is the form I want to navigate to?

Thanks for the reply!

Kevin

[MAF4Fam6]

The reason Access is asking you to enter a parameter is because you asked the Macro to open a table first. This step is not required since your form is already pulling data from it's record source, being a table or query.
The way this works, is that, before you open your last form with the Macro, the record you are clicking on in your datasheet subform must have focus, meaning it must be the active record immediately before you execute the Open Form Macro.
Therefore, your Macro should only have one action: the Open Form action
1. Ensure your datasheet subform contains the field name with the primary key.
2. Ensure the last Form you want to open contains the same field name with the same Primary Key.
3. Then use the Where Condition in the Open Form action using the same concept in the example I provided in my last post. It should be as simple as one Macro action in your Macro.
4. Open up the Datasheet Form's Property Sheet and add your Macro name to its On Current event. This will ensure the focus moves to the record you clicked on, making it the current record.
5. To prevent any problems, you will need to remove ALL tab stops in ALL controls in your Datasheet Form. This will prevent Access from moving focus to a record, by default, prematurely before you click a record.
Thanks.

-RC

[KevinH]

Thanks again for the reply and help, maybe I have something not set up right to make this work. I'll describe how the tables and forms are layed out if that would help.

Forn 1 is pulling customer first and last name from customers table, primary key is customerid. The I have a subform which is a datasheet, I am pulling 4 fields from my projects table, primary key is projectID.

When I look up a customer I do get all projects associated with them to show up in the datadsheet subform.

I want to click on the projectid field in this sub form to bring to this record I just clicked on in a different for which is created for the projects table as well with the same primary key of projectid. This secod form shows all the fields I need to enter complete project info,

I did find somewhat of a workaround if I put a command button in the form and ask it to open a form and look for specfic info, the projects form opens and has filtered all records for the customer I have selected, what I really want is to select the one record and not all records for selected custiomer.

When I added the macro to On current my second form opens immediately for some reason and not to the record or customer I have selected.

In the where condition I am assuming the following, if this incorrect could you please tell me what I may be doing wrong?

[primary key]=[forms]![name of form I want to open]![primary key]

Both primary keys are the same and come from the same table

Thanks again for any help with this!

Kevin

[MAF4Fam6]

Kevin,
The reason your second form is opening immediately upon opening the first form is due to Access making the first record active upon entering the form.
If you wouldn't mind trying a different approach: try removing the Macro from the On Current event, and instead, add the Macro to the On Got Focus event for ALL controls on your datasheet form with the exception of the last field name. Then, ensure to remove ALL Tab Stops (again) from all controls with the exception of that last field name on the datasheet. This way, Access will move to the last field name upon form entry, thus not triggering the Macro. If feasible, you may also be able to hide the last field name.
Also...there is a minor error in your Where Condition Code from your last post.
Change Where Condition Code to:

[second form's field name with Primary Key] = [Forms]![name of the first form...not second form]![first form's field name with Primary Key]

Thanks.

-RC

[MAF4Fam6]

Kevin,
Just following up...
Were you able to get a resolution to your issue using a command button instead to open your new form?
The correction to your Where Condition expression in my last post might have been the answer to your issue all together.
Thanks.

-RC

[carlmdobbs]

docmd.OpenForm "formName",,,"ID = " & lID.

You see, you open the form with a where clause that denotes the identity
of the primary key and set it to only that record.

The idea with this approach is, that after you open the form, it is set to only that record.
There is an alternate way to do it which shows that record but gives you the option of going to another record. Assume the form name is "frmCustomer" and its id is a long integer "CustomerID"
dim frm as form
dim lCustomerID as long
lCustomerID = 1 (or assign it to the customerid that is on the form that
has the button on it like this: lCustomerID = me.customerid)

Dim r as recordset
Docmd.openform ,,"frmcustomer"
set frm = forms!frmcustomer

set r = frmrecordsetclone
r.findfirst "CustomerID = " & lCustomerid
frm.bookmark = r.bookmark.

This sets the record to the correct record but doesn't freeze the recordset.

I'd offer to let you speak to me over the phone but there are restrictions as to what I can offer on this site. I will read and answer your responses though.

I have created a form with a datasheet subform, I would like to click on a record in the datasheet subform and have it open and displayed in another form I have created.
I have created a maco to open this second form, but it goes to the first record in the table instead of the record I am clicking on? Any help would be greatly appreciated.

Thanks,

Kevin

Open the form with something like the following:
docmd.openform formanme