Choosing A record from a combo + some

[Rundog]

Ok..it's easy to select a record & go to it from a combo if it's on the same form...How about this...

I have a Form based on a query that looks for certain data in my main table. (contains The search field and the KeyID only)

On my main input Form I have a button that links to the above form so it pops-up - This pop-up contains a set of combos to select ID's 1-999, 1000-1999 and so on, based on the search field criteria.

I want to be able to select the keyID I require from one of the combo's and for my main for to goto that record. Also; at the moment I have a close button on the combo's form but would ideally like to have this form close when i select the keyID.

Any Ideas....Please make your answer simple for me to understand - I am a novice.

Cheers - Andy

 

[mkkashif]

You want to filter the record

hello

you want to filter the record against the id .mean if against id filter the other related record .
http://www.candace-tripp.com/_pages/access_downloads.asp
visit the site and download the example file cascade combobox

 

[Rundog]

Thanks- The example is cool but gives me no insight into how it's done.

 

[PierreR]

This is really interesting, Andy. I will work on it, but be patient - it's a busy weekend. Maybe someone else will beat me to it.

 

[Rundog]

Thanks - I will be a patient puppy - whilst I work on cascding my comboboxes also.

 

[PierreR]

I got it. Tell me if this is what you were looking for.

Just for interest sake - what is the reasoning behind the customer's request for this? Or did you just think it would be cool?

 

[Rundog]

Woohoo...

On first look I think you have a great capacity to understand a sketchy explained example by me- I had a look behind the frmSearch and actually have some Idea what you did - whick again for me right now is a triumph for you!

The reasoning behind the Idea is simple - My end users are incredibly computer dim so anything that I can do to take decisions out of their hands will help both them & me in the future. I havn't been able to find an example like this anywhere on the internet so well done you - I should copywrite quick if I were you lol _ I'll let you know how I get on

Cheers
Diolch yn Fawr
Andy

 

[Rundog]

Well - I got it working tho based on a query rather than a table for the combo data - it linked through to the main table like a dream. The MainForm finds the selected combo choice perfectly. Thanks for your help...Now, how are you with cascading comboboxes?

My big problem with these is that I am looking for data in queries and not data in a table.

I have a set of qry's to look for a vacant slot...they each look for a null value in the FirstName field of my table then display the KeyID for that record in my combobox. Using your example I click on the KeyID I want (Which is vacant as per criteria) There is one query for each set of keyID's 1-1999, 2000-2999 and so on 11 individual qyr's in total (I am sure that it's possible to do this in one query - but not figured it yet)

So - rather than have 11 comboboxes I want just two - the first will enable me to choose which qry (Range of numbers) to look at...then when I make my choice the second combo will be based on the qry I chose from the first combo. -

If this was merely done with tables I think I would have got it by now - It's how to do it with qry's thats puzzling me!

I hope you can help me further - If not..thank you so much for the first assistance, it was purrrrfic!

 

[PierreR]

I hope to do it this evening Central Time USA, which will give it to you in the morning in Wales.

 

[PierreR]

Rundog, I hope this will help you. I made the ranges of numbers a second table, called tblGroup and set up a relationship (with referential integrity) with tblItem.

There are actually SQL statements, not queries, but not obviously visible. You can find a pair of them in each of the Row Sources of the two comboboxes of the new frmSearch, and one on which the main form is based.

I added some error-handling in with the click event of the second combobox, in case the user opens the search form only.

But my error-handling is not perfect. Remember I am still learning Access.

I'D BE GLAD IF SOMEONE LIKE SJ MCABNEY COULD HAVE A LOOK AT THE ERROR HANDLING. ALSO; HOW DO I PREVENT THE USER FROM GETTING TO THE CODE IN CASE OF AN ERROR?

 

[Rundog]

Hi again...I can see where you're coming from, but still cannot get this to work on my model. The problem is that I am not basing the combo's on tables but queries thus...

I have a set of qry's each one looks at a field in my main table 'FirstName'. If this field is Null (there is no name in that field) the query displays all the MemberNumbers where FirstName is Null. Each qury has it's own criteria so that I can split the search into manageble chunks ie
qry1 <1000
qry2 =>1000 And <2000
qry3 =>2000 And <3000
and so on

What I end up with is a list of MemberNumbers in any one range that are currently Vacant. I can make a combo box for each query and it works fine but when I open the form it runs every query. I don't want this to be the case. I need say a listbox or combobox where I can choose which qry i want to run....on my choice the a second combo or listbox will display the results of the qry that I chose. If i choose to use a listbox for the choices....

1-1000
1001-2000
2001-3000

I need to be able to click on my choice as above (Which are not based on a table) to populate the nex box with the results from the corresponding qry.

I hope that explains my problem better, I can't get the cascading combox examples to work on my model.

 

[PierreR]

Rundog,

My comboboxes are not based on tables. They are based on queries, but attached to the form. (they don't show under the queries collection)

See in the row source of the comboboxes.

Pierre.

 

[Rundog]

Hi there again...

I have switched the opperation around a bit so now I will always start with a search for a record.

Now...

I have the following code attached to my combo in the search form:

Private Sub Combo2_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Dim stDocName As String

stDocName = "frmPrimaryMember"

DoCmd.OpenForm stDocName
Set rs = Forms!frmPrimaryMember.Recordset.Clone
rs.FindFirst "[MemberNumberID] = " & Str(Nz(Me![Combo2], 0))
If Not rs.EOF Then Forms!frmPrimaryMember.Bookmark = rs.Bookmark

End Sub

This works great...it opens my main form to the correct record in my database ready to edit etc...I would now like the search form that I started with to close automaticly after I selected the record...If I add DoCmd.Close to the end of this code - my main form closes and not the search form. Any Suggestions?