Lookup data in another query (1 Viewer)

Hi Folks
I have a form that is based on a query.
I want to change the unique ID that is displayed on this form.
The new ID is sourced from a different query. I.E. Not the same query that the form derives its information.

I have set the data source property of the field (Combo1) to the "other" query.

How do I get the Combo1 to update to the value of the first record?
Currently its defaulting to Null.

I have tried using :
Me.Combo1.Requery - This does NOTHING to the vaue.
Me.Combo1.MoveNext - I keep getting a "Method or data members not found" error.

If the form is based on a query, can you not include the "other" query into it so you can select the other new ID in the normal way?

Col

Queries looking for different data

Hi Colin

Thanks for responding.
At the outset, I would have liked to change the structure of the tables to accomodate the problem that I am nopw faced with but the owner of the data has insisted that they wish to work this way. So to try and cut a long story short.....

Student details are stored in a table that has a finite number of records. (9999)
There are 3 "types" of students - identified by "P", "N" and "C" in the type field.
The client wants ceretain ranges of the Student_ID to fall into the relevant type categories.
EG 1 - 3000 = Type P
3001 - 6000 = Type C and
6001 - 9999 = Type N

Suddenly (surprise, surprise) they are finding that students no longer belong to the group that they initially belonged to. So now its not just a simple matter of changing the Type, but they also want me to re-allocate the student_ID (I know that this does NOT make sense - But that's how they want it)

So - If I want to transfer students from say Type P to Type C, The form is finding all records where there is a FirstName and a LastName (irrespective of the type)
The user then finds the appropriate record using a ComboBox.
Then user selects what other type the record must be transferred to.

Somehow, I need to pick up an unallocated Student_ID that belongs to the new type (Blank FirstName and LastName).
I have placed a ComboBox (Visible property =False) on the form which sources its data from another query where Type=C and FirstName=null, LastName=Null
The problem is that I cannot get the record pointer to move to the first record.

Then using a modified "bubble sort", I will re-assign the Student_ID and change the type accordingly.

Firstly, I'm guessing (or hoping) you have an Autonumber as a unique ID (which doesn't change) as an identifier of the student. Then the "Student_ID" is a number field (which can be changed) of that record as is the status (also changeable).

Personally, I would use a search ListBox to find the student that needs changing, then select that student from the listbox to display all the student details. I would then have a "FindNext" bit of code (or a query) which will find the next (vacant) number to be used for a specific status. Then just key it in and change the status accordingly.

Incidentally, do you re-allocate "old" numbers? i.e. if the numbers 3001 to 3400 are allocated and 3204 becaomes vacant due to change, do you re-allocate 3204 to someone else?

Col

Here is how I would solve this problem. I would do one of the following:

a) Take a big stick to the person who wants to change the ID of a record.
b) Quit my job.

Both would probably involve a financial penalty but needs must.

Thanks Cuttsy - thats about where I am at with this one.

Colin - Let me answer your questions.
1. No - Unfortunately there is no Autonumber field. That's the route I would like to have gone.

2. Yes - I am re-allocating "unused" numbers. - Hence the problem

3. In principle. I am doing pretty much what you suggest.
Using a ComboBox to find the student.
I just dont know how use the findNext on the unrelated query

Once I know how to retrieve the next unused number (for the type that the student is being transferred to) I will then
A. Assign the existing Student_ID to mOldNumber
B. Assign the unused Student_ID to mUnUsed
C. Change the existing Student_ID to 10000 (Something outside the current range)
D. Change the unused Student_ID to mOldNumber
E. Change the existing Student_ID to mUnUsed (10000 no longer exists)

Hi folks
Sorry to do this to you. I raised this question on Friday (At "chaila time" - As they say in Africa) so I was wondering whether anybody had some possible solution for me as I still have the problem.
Thanks

its a bit of work, but you could create an autonumber field for the current list, then change all the linked student data to use the new autonumber field, which sorts out and normalises the table properly. This shouldnt be too hard, although it depends how many queries/reports etc you have

after that you can add data fields to the student table, to enable filtering/selections/mods etc, without changing the true underlying relationship.

you may need some before update events to verify the users are selecting appropriate groups for the students.

i think the design decisions should be independent of what the user thinks he wants. if the dbs is designed correctly you can present data anyway you need

hope this helps