incremental numbering 2

[directorz]

I've got a form based on a 3 field table:

ID - pk
Description - pk
Serial number

The ID field of the table is linked to vehicles to which serial numbers belong and displays the vehicle number. The form was created as a continuous form and is used as a subform on a multi-tab main form.

The plan is to be able to cycle or scroll thru the subforms records whenever a parent record is selected (vehicle). What I want to see is each record incrementally numbered as they are entered so I end up with

1 Engine vf4567
2 Transmission gh69d45

etc

What I believe I need is an unbound textbox on the continuous form to hide the ID field, then used to display this numbering system. I've tried the Dmax thing and several other methods, and Autonumber is not editable. Does anyone have any suggestions...

Thank you
Directorz

 

[scriverb]

I have to leave now but will work on this when I return.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Try this:

Create a query and save as qryID_Select:

Select A.* from tblserialno as A where A.[ID] = FORMS![mainformname]![IDcontrol];

Now modify your existing query to use this query recordset as input rather than the actual table:

SELECT Count(*) AS Count, A.[ID], A.[Description], A.[Serial Number]
FROM qryID_Select AS A, qryID_Select AS B
WHERE (A.[ID] & A.[Description])<=(B.[ID] & B.[Description])
GROUP BY A.[ID], A.[Description], A.[Serial Number]
ORDER BY Count(*);

This I think should fix the numbering problem. Also, you can leave the Link Child and Master field properties intact as they were with this new query SQL.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[directorz]

Yes, this appears to function correctly

Thank you
Directorz

 

[scriverb]

directorz, Great. I hope that all is working well for you now. Make sure that you reinstall the Child and Master link fields to the subform control. They are needed to update the ID field automatically with your entry form.

Thanks for the Star and good luck with the project. I enjoyed working with you to solve this problem together.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[directorz]

Yes, I have re-established that setting and all appears well

 

[directorz]

Scriverb,
Are you up for a modification question relative to this question?
Directorz

 

[scriverb]

Oh, why not. We can go for a record on this one.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[directorz]

How many replies?

I’m considering this modification. This goes back to my ‘unbound textbox’ idea. Currently, there is a subform control that displays the 2 subforms. The command button toggles between the 2. Here’s what I was thinking but can’t quite make it work. If I take subformtblserialno and place it on the main form along with subformtblserialnodata entry. Follow? So now I have 2 visible subforms on the main form. Now, with subformtblserialnodataentery, I make the fields ‘description’ and ‘serialno’ visible. With subformtblserialno I make only ‘count’ visible. This would serve the purpose that my unbound textbox was going to serve. Size them, butt ‘em up and fit ‘em…it look great. The rows read, from left to right’ “Count”, Description” and “Serial No”. The problem I have is this. I can enter new info into subformtblserialnodataentry w/ no problem. (only they are not numbered yet). I close the form, re-open it and the entries are there, numbered correctly. I’m trying to get it to number when I start or after I’ve entered data in the description field. I’ve explored events rather thoroughly (after update, requery) and so on, but can’t seem to find this piece of the puzzle. The toggle is not bad but I find that after I toggle, I don’t have the list in front of me as a visual reference. I know I can toggle back, but to me it feels better to see the list right there without having to toggle. What are your thoughts?

Directorz

 

[scriverb]

I can see if you put the two subform controls one above the other. The top being the rownumbered one with all of the records and the one on the bottom being you data entry subform. Maybe just a single row for entry. This makes sense. The issue of getting the new row added to the list and numbered is just adding a Me.SubFormControl1.requey to the second subform controls underlying forms AfterUpdate event procedure.

This way the scrollable subform will be updated immediately after your entry subform makes the update and the record is saved.

Is this what you were looking for?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[directorz]

Well, I would use the data entry form that we talked about, but in continuous mode so you can see the records rather than single form. To the left of it would be the numbering form, also continous, with only the 'count' field visible, used to number the records in the continuous form ( a variation of the original idea). The 2 forms would butt each other - side by side - and be seamless.

 

[directorz]

This allows a user to view all, edit, add, delete or modify from the same screen. The second form would be used for the numbering

 

[scriverb]

Sounds like a plan. Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[directorz]

...yes, this is what I'm looking for however Me.SubformSerialNumber1.Requery placed in the AfterUpdate event of the second subform (data entry) produces "Method or Data member not found.

 

[scriverb]

The code below is just pseudo code. You must modify it to supply the name of the subform control on your form.

Me.[i][red]SubformSerialNumber1[/red][/i].Requery

Since I didn't know the name of the two controls I just put in a descriptive name.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[directorz]

This is the name of the subform and what I used. The main form has 2 subforms; 2 subform names are subformserialnumberdataentry and subformserialnumber, the latter being the one I want to update. So, Me.SubformSerialNumber.Requery placed in the AfterUpdate event of subformserialnumberdataentry produces "Method or Data member not found.

 

[scriverb]

When referring to a Main Form control from a subform form you must use the long form syntax. Me! doesn't work. It is looking at Me! as the current form which is the subform form. So, give this a try:

FORMS![[I][RED]yourmainformname[/red][/i]]![SubformSerialNumber].Requery

Let me know how this works for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[directorz]

NOW, I'll leave you alone. I understand the reason for the reference modification. I hope someone else is learning from this besides ME!
Directorz

 

[directorz]

Hate to do this...all is well. I can add, edit, modify etc and the 'view all' form updates immediately with the requery. Deletions however, are not immediately shown. I've refreshed the mainform and tried requery after delete confirm w/ no success. If I close the mainform, then re-open, everything is fine. Is this due to nested queries
Directorz

 

[PHV]

Just a note for referencing the main form from one subform:
Me.Parent!SubformSerialNumber.Requery

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244