Buttons to open subforms (1 Viewer)

[jmlight]

3 Tables

Main Table: Clients
Child Table: Skills
Child Table: HelpNeeded
All linked with field named ClientID

I have a form (Form1) which allows me to enter personal information for each client - one record per client.

From this screen, I would like to open a 2nd window in which I can update the Skills and HelpNeeded tables.

ON Form 1, if I add the Skills subform directly to the page, I can make all the changes without any difficulty.

However, I would like a separate window to open when I want to update the Skills Table (and the helpneeded table)

So, I made another form (Form 2) which displays the First Name & Last Name from the Client table - and has a subform which displays the Skills for that particular client (from the Table: Skills) .

IF I start on Form 1 - which has all the client personal information, and click the button to open Form 2 - everything looks OK, but the data displayed for the Client goes back to the 1st record in the table regardless of which record in Client I was viewing when I pressed the Open Form button to access Form2

How can I maintain the current record when the 2nd form is opened?

Oh..and by the way.....I"m hoping I can do this without having to write my own code. I am VERY VERY new to Access and am using Wizards and buttons and all kinds of user friendly devices.

If I have to code it myself - I guess I'll need you to point me to a resource to use so that I can figure out how!

 

[pbaldy]

Does this work for you?

http://www.baldyweb.com/wherecondition.htm

 

[jmlight]

Looks like it does exactly what I'm hoping for - but where do I put this code once I put in my fields/tables?

 

[pbaldy]

That would go behind the button to open the second form, based on your description of the scenario.

 

[jmlight]

I know this sounds silly - but how exactly do I put the code behind the button?

 

[pbaldy]

Presumably there's already code there, you just need to modify it. You'd get to it by right-clicking on the button in design view and selecting properties. On the Events tab there's something in the On Click line. Clicking on the ellipsis to the right of that will take you to the code (or embedded macro if using 2007).

 

[jmlight]

Thanks - I'll give it a try. Does it matter where in the code sequence I place your code?

 

[pbaldy]

Generally not. You'd use it at the point in the code where you want to open that second form.

 

[jmlight]

I think I'm getting closer - but I'm getting a data mismatch error. Here's the code as I've modified it (using the code you sent me as a guide)
[FONT=&quot]
In blue is the code that was generated automatically when I made the Button. I am showing MY additions in Red so that you can see my changes..

Private Sub viewjobskills_Click()
On Error GoTo Err_viewjobskills_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stFldName As String


stDocName = "Clients of Centro Hispano & their Skills"
stFldName = "Me.Last Name"

' The next line is a comment - it was in the original button code before I added the client_id field in the command line below

' DoCmd.OpenForm stDocName, , , stLinkCriteria

' Here is the new line that I added:

DoCmd.OpenForm stDocName, , , "Client_id = '" & stFldName & "'"


Exit_viewjobskills_Click:
Exit Sub[/FONT][FONT=&quot] [/FONT]

I'm not sure what fields I should have listed in the DoCmd.OpenForm command line. The field that links the 2 tables is Client_ID.

WHen the 2nd form opens, I want First_Name and Last_Name to appear on the top of the page (From the Parent table - Client). And then the fields Job_skill, Job_type, Job_description (from the Child Table "Client Job Skills") to appear at the bottom of the page (the actual subform)

Is this too big a question for the forum? Do you want me to RTM?

 

[pbaldy]

That technique only works with the same field, so either use Client_ID for both sides or LastName for both (typically I'd expect Client_ID). Given the error, I suspect Client_ID is a numeric data type, so you'd need to use the syntax appropriate to that from my link.

 

[jmlight]

It worked beautifully!

thank you so much!!!

 

[jmlight]

Aargh - I thought it was working - but it wasn't.

The two tables are linked through the Client_ID field.

In the main form, the table Clients is used.
On the subform, the table JobSKills is used

When I open the subform in a separate window, I would like the job skills related to the client ID in the main form to appear.

I'm getting the job skills from the first client in the Client table instead.

I would also like the First Name and Last name from the Client table to appear on the subform that uses the JobSKills table.

When I use the format that you very kindly provided for me, the subform doesn't open automatically. Rather, I get a prompt requesting me to enter the client_id.

I would like this step to be automatic.

Can you provide guidance?

The exact code I have behind the button is:

Private Sub viewskills_Click()
On Error GoTo Err_viewskills_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Clients of Centro Hispano & Their Skills"

DoCmd.OpenForm stDocName, , , "FieldName = clientid" & Me.Client_ID

Exit_viewskills_Click:
Exit Sub

 

[pbaldy]

I would expect something like this:

DoCmd.OpenForm stDocName, , , "Client_ID = " & Me.Client_ID

 

[jmlight]

Yep - that's exactly it....I'll use it on another button (all by myself!) and see if I can get it to work twice!

Thanks so much.

So, where exactly ARE you?

 

[jmlight]

On another button that calls "Assistance Requested Subform", i copied the exact same line of code you gave me.

But now, when I click on the button, I get a box that says:

Enter Parameter Value

Client_ID

And there's a box beneath it in which I presume I should enter the number.

How can I bypass this step and have the client_id from the main form auto-fill this part?

Is it because I'm calling a Subform and not a regular form?

 

[pbaldy]

So, where exactly ARE you?

In my family room! You?

Yes, the reference would have to be adjusted for the subform:

http://www.mvps.org/access/forms/frm0031.htm

 

[jmlight]

that information sheet confused the heck out of me. I have no idea what it all means.

I think I'll just redo the second form and make it "regular" so that it works

 

[jmlight]

Ignore this - I have posted in a separate thread.I finally have the form opening to display the data. Now, I would like to go to the next step.

the secondary form opens and displays only the records that have a match in the main form (linked on client_id)

I would like to "Add" records to the secondary form, and have the client_id from the main form auto-populate the field so that the data integrity stays intact.

How can I add to the secondary form? Sadly, I used to be able to do this, but I have obviously changed some control that no longer allows me to add a record.

Help?