I currently have a form "client information" with a subform "catagories." Of course, a client can be a member of several different catagories. One catagory in particular "Chapter Member" can have many different attributes to it. I have created a button that will open second form when pressed so that these attributes can be recorded.

What I would like to do is this: When someone presses the button to activate the second form, I would like for the subform catagories to be searched for the specific entry "Chapter Member." If the entry is found. The second form will be opened. If the entry is not found, it will be added and then the second form will be opened. So my question is, how can I accomplish this?

More Info:

There are three tables involved here. I'll shorten up the descritpions just enough so you get an idea without having to have a whole bunch of meaningless details.

Client Info
___________

Client ID - Key
Name
Address
Office

Catagories
__________
Catagory ID - Key
Catagory Descrtiption

Link
____
Client ID - Key
Catagory ID - Key

The relationships are simple with the obvious fields related to each other.

The main form has the name and address information on it. The subform contains all the Catagory data.
The Office field would be on the second form that I want to open. The only catagory that has offices is the "Chapter Member" Catagory.

Thanks in advance for any help you guys can offer.

Good luck...I've been trying to get some help on a very similar situation but never get much of a response (at least nothing's worked yet http://www.access-programmers.co.uk/ubb/smile.gif Hope someone comes to our rescue!

Where are you storing the attributes you are setting for the category Chapter Member?

I thought it would be simplest to store the attributes for that catagory in the Client Info table.

Ok... so you have fields in Client Info that are only active IF the client has a category "Chapter Member" associated with it? Is this correct?

Currently they are active all the time regardless of whether or not the catagory is associated with them. The extra attributes appear on another form (out of sight, out of mind). I can of course change this to make this opperation as efficient as possible.

P.S. I would like to keep the attributes on a second form as I am running out of real estate on my main data entry form.

Well, I still don't fully understand your layout. I'm having trouble visualizing what your are trying to do. I don't know if this is possible or not, but if you wanted and were able to, you could send me a zipped up copy of your db so I could look at it first hand. If this is not an option, we can continue to try to troubleshoot via this forum. Let me know what you want to do.

js

Try This:

DIM rstSubForm as ADODB.Recordset
Set rstSubForm = New ADODB.Recordset

Set rstSubForm = Me.[SubFormName].Form.RecordSetClone

If rstSubForm.RecordCount = 0 Then
GoSub Add_ChapterMember
Else
rstSubForm.MoveFirst
rstSubForm.Find "[CatagoryID] = {Catagory ID for Chapter Member}"
If rstSubForm.EOF = True then
GoSub Add_ChapterMember
End if
End If

rstSubForm.MoveFirst
rstSubForm.Find "[CatagoryID] = {Catagory ID for Chapter Member}"
Me.[SubFormName].RecordSet.BookMark = rstSubForm.BookMark
Docmd.OpenForm "SecondFormName",,,"[ClientID] = " & Me.[ClientIDFieldName]
Set rstSubForm = Nothing


Exit Sub
Add_ChapterMember:
rstSubForm.AddNew
rstSubForm.Fields("ClientID") = Me.[ClientIDFieldName]
rstSubForm.Fields("CatagoryID") = "{Catagory ID for Chapter Member}"
rstSubForm.Update
Me.[SubFormName].Form.RecordSet.Requery
Set rstSubForm = Me.[SubFormName].Form.RecordSetClone
Return

Thanks for the help guys. Travis, I think I've filled in all the field names propperly but I'm gettinga type mismatch error. Any tips on how I can troubleshoot this?

Here is the modified code.

Private Sub cmdChapter_Member_Click()
On Error GoTo Err_cmdChapter_Member_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Extra Data"
stLinkCriteria = "[ID]=" & Me![ID]

Dim rstSubForm As ADODB.Recordset
Set rstSubForm = New ADODB.Recordset

Set rstSubForm = Me.[Category_Link_Subform].Form.RecordsetClone

If rstSubForm.RecordCount = 0 Then
GoSub Add_ChapterMember
Else
rstSubForm.MoveFirst
rstSubForm.Find "[Category_ID] = CHA"
If rstSubForm.EOF = True Then
GoSub Add_ChapterMember
End If
End If

rstSubForm.MoveFirst
rstSubForm.Find "[Category_ID] = CHA"
Me.[Category_Link_Subform].Form.Recordset.Bookmark = rstSubForm.Bookmark
DoCmd.OpenForm stDocName, , , stLinkCriteria
Set rstSubForm = Nothing


Exit Sub
Add_ChapterMember:
rstSubForm.AddNew
rstSubForm.Fields("Member_ID") = Me.[ID]
rstSubForm.Fields("Category_ID") = "CHA"
rstSubForm.Update
Me.[Category_Link_Subform].Form.Recordset.Requery
Set rstSubForm = Me.[Category_Link_Subform].Form.RecordsetClone
Return


Exit_cmdChapter_Member_Click:
Exit Sub

Err_cmdChapter_Member_Click:
MsgBox Err.Description
Resume Exit_cmdChapter_Member_Click
End Sub