Here is a complicated on (I Think)

[Krash878]

I have a Form (frmMaster) that has a Subform (frmSlave) and a Query (qrySearch).
The frmSlave is in Datasheet veiw and is based off of qrySearch. The frmMaster has a text box (txtSearch) and an option group with 4 choices. The four choices are Member,Sequence,CardNo,and Amount which are also the names of the feilds in the main table.

My Goal is to have the user select an option in the option box and then type in some data and search for the specified record or records. The records would then be displayed in frmSlave.

I do not know how to make the option group set a criteria to txtSearch or how to make txtSearch have perform the search

Thanks to who ever can help with this. Please let me know if I painted the picture well enough to work with.

Krash878

 

[JoeMiller]

One way is make 4 queries all the same as qrySearch but with criteria only in the related field:

qrySearchMember: Member Criteria = [forms]![frmMaster]![txtSearch]
qrySearchSequence: Sequence Criteria = [forms]![frmMaster]![txtSearch]
qrySearchCardNo: CardNo Criteria = [forms]![frmMaster]![txtSearch]
qrySearchAmount: Amount Criteria = [forms]![frmMaster]![txtSearch]

Now in the AfterUpdate event of your option group (I'll call it fraOptionGroup) use a Select Case to decide which query to use as the RecordSource of your subForm like so:

[tt]
Select Case fraOptionGroup
Case 1
[frmSlave].Form.RecordSource = "qrySearchMember"
Case 2
[frmSlave].Form.RecordSource = "qrySearchSequence"
Case 3
[frmSlave].Form.RecordSource = "qrySearchCardNo"
Case 4
[frmSlave].Form.RecordSource = "qrySearchAmount"
End Select
[/tt]

In my example when "Member" is selected the value of fraOptionGroup is 1, and likewise down the line like in the Select statement. You may need to modify your Case statements to match your actual values in your Option Group.

HTH Joe Miller
joe.miller@flotech.net

 

[Krash878]

ok Joe now that I have the queries running I need to have the info from the query show on my form in a text box(txtResult).
The only thing that needs to show up is the ID number from one of the queries. Just the query selected by the option group.
thanks

 

[JoeMiller]

Use the DLookup function to return your result:

=DLookup("[IDNumber]","qrySearch????","Criteria Here If Needed&quot

The criteria will be needed if your query returns more than one record, if it does not then just do the dlookup without the last section.

HTH Joe Miller
joe.miller@flotech.net

 

[Krash878]

I have tried to enter the code but I get a compile error.
Does this code go in before update or in a different area.
The Access help says

Dim varX As Variant
varX = DLookup("[LastName]", "Employees", "[EmployeeID] = 1&quot

Do I need the Dim varX As Variant.

Just to make sure we are on the right page.

Is there a way to make the option group set a criteria for me.
I have a form that I need the default to be changed upon data enter and it works perfect with out altering the Table.
example:
Me!CardType.DefaultValue = cQuote & Me!CardType.Value & cQuote

I think that the way you are describing this is only going to show me one queries data so I would have to have a different set text boxes for each query to return the data to.

could the code work something like this?

Select Case fraOptionGroup
Case 1
Const cQuote = """"
= "qrySearchMember"Me!ID.CriteriaValue = cQuote & Me!ID.Value & cQuote
and get criteria value from txtsearch

I know this is not correct code but it may give you an idea.

Can I set the criteria in a given feild for ONE query with the option group and the text box.

 

[JoeMiller]

Here's the way I thought your form was going to work. Correct me if I'm wrong.

The user selects what they want to search on in the option group. Then types in what they are searching for in the textbox. Then clicks a command button that sets the appropriate query to the recordsource of your subform, which will then show the records resulting from their criteria in the textbox.

The OnClick event of your button should set the record source by selecting the case as I described above. And that same event can also fire your DLookup. Before adding the DLookup into it though (less chance of confusion), I would get the subform displaying the results of the query. Joe Miller
joe.miller@flotech.net

 

[Krash878]

What I originaly planned was.

The user selects what they want to search on in the option group. Then types in what they are searching for in the textbox and presses Enter. The option group would set the criteria in the query remotely and and find the record. The ID of the record would be displayed in a text box(txtResult) that would be used to select a record on the subform. All of the process is set up except for the option group and return ID part.

When I do the multiple queries I can not locate just the ID with out having multiple text boxes to receive the data. With one query I would be able to do this.

Right now if I search on just ID it will be displayed in txtResult but only for the qryID.

If I search on Member Name it looks at the qryMemberName
and does not return the ID to txtResult.

Is a way to set the Query Feild criteria via the option group?

Or

Can the txtResult box be linked to the option box to look at the query the option group is running? If so how?

 

[JoeMiller]

What is the code to populate the txtResult? It should be easy to modify it to show the right result.. Joe Miller
joe.miller@flotech.net

 

[Krash878]

I do not have the Code yet.
I have the lay out of the Form and the Query
If it would help I could Send the DB to you and then keep posting here.

 

[JoeMiller]

Why don't you do that... send it over to me so I can take a look.

Joe Miller
joe.miller@flotech.net