Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

String criteria for a subform 1

Status
Not open for further replies.
Trudye

Trudye

Programmer
Sep 4, 2001
932
0
0
US
Hi Guys I have problem. Actually I have many problems but only one that is attributable to Access Forms.

I have a form (Client) that houses a subform (Deals). I want the form to open based on a string criteria for a client and a particular deal.

IE. (Client_ID = 250, deal_id = 543). I am able to get the right client but cannot get it to open to the correct deal. This client has 7 deals.

I tried setting up a the following in the Form_OPen event.

If gFilterForm Then
Me!DEALID = gDeal_Id
End If

gFilterForm: a switch set in a Module
gDeal_ID: global field, value set in the same Module


Thanks
Trudye
 
Sort by date Sort by votes
How are ya Trudye . . . . .

What you want can be done with [purple]OpenArgs[/purple] of the [purple]DoCmd[/purple]. You wont need any filters and such. Question:

Where are you getting your [blue]Client_ID[/blue] & [blue]deal_id[/blue] criteria values from?

Calvin.gif
See Ya! . . . . . .
 
Upvote 0 Downvote
Filter is just a variable I set as a switch/indicator. Anything with a "g" in front of it is a global variable.

I have not idea how to set up OpenArgs to find a subform location. I have looked in FAQ and MS Help and see nothing that resenbles what I am trying to do.

Client is the Main form and Deals is the subform. I want to open to a particular client and a particular deal. How do I construct OpenArgs to do that?

Thanks much
Trudye
 
Upvote 0 Downvote
How are ya Trudye . . . . .

In the [blue]Load[/blue] event of the [blue]Client[/blue] form, copy/paste the following code:
Code: 
[blue]   Dim sfrm As Form, rst As DAO.Recordset, Idx As Integer
   
   Dim idClient As Long, idDeal As Long
   
   Set frm = Me!Deals.Form
   
   Idx = InStr(1, Me.OpenArgs, ".")
   idClient = Val(Left(Me.OpenArgs, Idx - 1))
   idDeal = Val(Right(Me.OpenArgs, Len(Me.OpenArgs) - Idx))
   
   Set rst = Me.RecordsetClone
   rst.FindFirst "[Client_ID] = " & idClient
   Me.Bookmark = rst.Bookmark
   
   Set rst = sfrm.RecordsetClone
   rst.FindFirst "[deal_ID] = " & idDeal
   sfrm.Bookmark = rst.Bookmark
   
   Set sfrm = Nothing
   Set rst = Nothing[/blue]
I believe your using the [blue]DoCmd[/blue] method to open the form. Change it to the folowing ([purple]substitute Client_ID & deal_id proper, [blue]where ever your getting the values from[/blue][/purple]):
Code: 
[blue]   DoCmd.OpenForm "Client", , , , , , Str([purple][b]Client_ID[/b][/purple]) & "." & Str([purple][b]deal_id[/b][/purple])[/blue]


Calvin.gif
See Ya! . . . . . .
 
Upvote 0 Downvote
For soem reason my code is not executing Form_Load or Form_Open. the only Form event being exec is Form_Current

Is there a reason for that?

Thanks so much AceMan for the feedback, Ill try your suggestion and let ya know.
 
Upvote 0 Downvote
Thank you, thank you , thank you!! It worked!!!!

AceMan You are my new HERO!

Trudye
This project is going to be the death of me.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Bubba002
  • Locked
  • Question
Pass a subform fieldname-ID as a string variable in CF expression
2 3
Replies
50
Views
494
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Use Crosstab query in subform
Replies
9
Views
109
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Subform not updating 1
Replies
12
Views
164
TrekBiker
TrekBiker
BallunarCrew
  • Locked
  • Question
Looking for a color chart 1
Replies
2
Views
62
BallunarCrew
BallunarCrew
TechieJr
  • Locked
  • Question
Filtering for null or empty field
Replies
8
Views
204
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top