Form/Subform Problem stumping me Help ASAP

[linuxjr]

Alright I got all parts working and down to one last feature that I have to write for the boss man. He says he has done this and I don't think it can be done but if it can I could use some help. Again I have a Form called Insp which holds the inspections that check the work of the projects in the area. Like Fire Inspections, Mechanical, etc. Now I have a subform Insp1 that shows the records for each permit # on the main Insp form. Not hard wrong! Both of these are pointing to the same table. The main form has all the fields displayed from the Insp Table and the subform just shows some fields and they are linked by the Permit Number.

Insp Table (design)
-----------------------
Permit
Type
Date Requested
Date Inspected
Assign to
Insp Trade
Inspector
Result
Comments

Insp (main form)
-------------
Has all these fields on the form designed neatly.

Insp1(subform)
------------------
Has just these fields
Trade Type Inspector Result Date Inspected

Now I will detail some more. Now the Insp form is open and we do the data entry:
Permit: 1000
Date Requested: 08/31/2001
Date Inspected: 08/31/2001
etc.

Subform will have these for permit 1000
E Electrical John Passed 9/1/01
M Mechanical Jane Failed 9/1/01
P Plumbing John Passed 9/2/01
Now The main form will show all the fields filled in and have the electrical first so of course on the main form we could just move to the next record and be to the mechanical one. Now here is the problem. My boss wants to be able in the subform click on say Plumbing while the main is pointing to the electrical one first and when we click on the plumbing move the main form to the third record of that permit number before going to say 1001 may have five inspections. Can this be done? If not please help me think of a nice way to say no to the boss or if it can be done please I could use some help. Thank you and have a nice day.

 

[braindead2]

perhaps I am confused but you say you do this in one table?
"Both of these are pointing to the same table"
Which field is indexed?
A permit can have many inspections,correct?
If I am correct then the problem is you need to break up your table or assign a unique key to each inspection record so you can identify it. Then you can just move to that inspection Number. As it stands now you could probably attempt to accomplish it by using a recordset clone and a bookmark. but without an index I wouldn't even bother.
If I misunderstand I'm sorry
good luck

 

[linuxjr]

You are correct that you heard that the Permit Number could have many inspections. But How to go about breaking it up is the doozy and stated before the man I'm doing this for doesn't want anything changed he believe his way is the right way. I did get it to work like you said if there is a unique key assign to each record and use the recordset/bookmark feature but without an index I can't think of a way. There is nothing unique in each of the fields on the table. So I was just asking if there was a way. Any ideas or is it finally NO!! Oh I should say he has upgraded from Paradox to Access and he got it to do it in Paradox. But I can't seem to find it and if anyone has done it I would like to know the route but as stated before if it can't be done just let me know and think of a proper way to let the man down without him fussing.

 

[linuxjr]

Ok I am almost done 95% and would like someone to point it out. This still retains to my question and again like I said the form / subform is linked by permit so that the main form could be at Permit 1000 and the subform would show its 4/5 records related to Permit 1000. Now I did get everything to work except for still filtering to 4/5. This shows all records and makes the subform pointless but here is what I did so far and see if anyone can help me out with the little tweak I need.

1. Remove both master and child link field in subform. Instead, establish a link by code later (see step 5).

2. Add a unique autonumber field called recno to the Insp table to idenify each record.

3. Add the recno field in the suform with control source specified as recno

4. Now your subform recordsource should be SELECT DISTINCTROW Insp.permitno, Insp.inspector, Insp.recno, Insp.result FROM Insp;

5. Last in the subform form_current event do

Dim saverecsource as string

saverecsource = me.parent.recordsourceMe.Parent.RecordSource = ""

Me.Parent.RecordSource = saverecsource

DoCmd.GoToRecord acDataForm, Me.Parent.name, acGoTo, Me.CurrentRecord

This works great that I can click on a record and the main form goes to it but it defeats the purpose to show just a few records to 1000, 1001, etc etc. I can't seem to see what needs to be changed to get it to still filter and do this work. Thanks for the time and have a nice day.