recordsetclone bookmark findfirst with 2 criteria

[trigirl67]

I want to have a user search the first combo box of a form and then the results of that box give you choices in the second combo box (that part I have working ok), then I want the form to autopopulate form a table for the remaining fields on the form.
Here is what I have:


Me.RecordsetClone.FindFirst "[TIN]= " & Str(Nz(Me![cboFindRecord], 0)) & " And [Request Type]" = " & Me![cboRequestType] & """
Me.Bookmark = Me.Recordset.Clone.Bookmark

Before I had 2 combo boxes i did the following and it worked for the first combo, but then I added the second combo box dependent on the first and my code is not working:

Set rs = Me.Recordset.Clone
rs.FindFirst "[TIN] = " & Str(Nz(Me![Combo256], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[Privateer]

trigirl67,
When you use one combo, you move to that record. When you use two combos to whittle down the selections, you are filtering/fishing for the right record. If this is the case, let me suggest that you use two forms. One is a continuous form that will display just enough info for someone to further select the right record. Then use the double click event to open another form with all the detail for the selected record. On the continuous form, use the after_update event of the second combo to filter the form using the values from both combos as criteria. Hope this helps
Privateer

 

[trigirl67]

Thank you for your response, but I have not done a continous firm before, Can you walk me thru the proscess you suggested pls

 

[trigirl67]

I am not a pro yet in VBA...need a little more assistance please

 

[vbaInet]

Before I had 2 combo boxes i did the following and it worked for the first combo, but then I added the second combo box dependent on the first and my code is not working:

Set rs = Me.Recordset.Clone
rs.FindFirst "[TIN] = " & Str(Nz(Me![Combo256], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

You had code that was working before (above) that uses a recordset object, rs, why didn't you follow the exact same format instead of trying to do Me.RecordsetClone.FindFirst ... etc?

 

[Privateer]

trigirl67,
The process is simple, create a new form (or use the existing one) and while in design view, click on the box in the upper left corner with the secondary mouse button and you will get a little menu. Select properties, which is the properties of the form itself. Click on the All tab and about six rows down you will see default view which is probably set to single form. Click on that item and click on the arrow to see a drop down of other options; select continuous form. That's it.

Now, a few comments. This will show every record in the underlying table so you need a few things for this form to work properly. First I think it is best if you put just one row of text boxes in the detail section and pick the data that will assure the person can identify the record they are looking for. And by identify, I mean, in each text boxes' double_click event, put code that will open the detail form for that record. Additionally, I usually lock these records so changes can not be made by accident. Once they double click on a text box and the "Edit" form opens with all the fields, that is where the editing goes on.

Now I glanced over an important part. When you select a record, you are on the row for that record and you can reference any field for that record. The one you need is the primary key number, the autonumber field in the table so you can open the "edit" form on that record. So if we are talking about people, you would have this in the double click event

Dim Criteria as string
Criteria = "(PeopleID=" & Me.PeopleID.Value & ")"
DoCmd.OpenForm "frmPeopleEdit", acNormal, , Criteria

Secondly, you will need some filtering, which you have already started with the combo boxes. So on the continuous form you have the combos filtering thousands of records down to a few records, then you double click on the one you want. That's it. It is the basic strategy I use, a filterable continuous form that allows a person to double click on the record they want.

And, not to confuse you, but you can go to different forms from here. With the people example, you click on the person and you get all the details about that person. But if you click on the department, you open up a different form and you get a list of everyone in that department, or all the details about that department. You get the idea. I hope this helps
Privateer