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]

If you are looking to create a rownumber for each row in the subform then use a query like this as the recordsource for the subform form:

SELECT (Select Count(*) FROM [[RED][I]yourtablename[/I][/RED]] as B WHERE B.[ID]<A.[ID]+1) AS RowNum, A.[[red]part_desc[/red]], A.[[red]part_code[/red]]
FROM [[RED][I]yourtablename[/I][/RED]] as A;

I have used the ID field from your table as the field to key on to create the order. I assume this is your AutoNumber field which is just fine because that is the order of entry of the records.

Let me know if you have any questions.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

scriverb,
Actually, this is not an autonumber field, simply a number field.

 

[directorz]

The ID field is used for vehicle ID. Thus, without an unbound text box to cover it up, it would look like

1 Engine vf4567
1 Transmission gh69d45

The 1 represents the vehicle ID to which the descriptions and serial numbers belong. If I were to select vehicle 2 as the parent record on the main form, the subform would look like

2 Engine 1238bg
2 Transmission d5ymj89

Make sense?

directorz

 

[directorz]

Autonumber would not be possible here. Plus, I need these items to be editable...

 

[scriverb]

Everything you say I understand but really has no negative affect on what we are doing here. The ID field is your ForeignKey field linking back to the
Vehicle. In the table where these records are entered do you have a record identification field? Is this an AutoNumber field? What is the PrimaryKey for this table?

You said you wanted them displayed in order by how they were entered. This can be determined by an autonumber field. If you don't have one then just add one. This will have no affect on your table or function. It is always there but just not displayed or used unless we need it.

Now name the autonumber field and then put that name in place of the ID field that I used in the query SQL.

SELECT (Select Count(*) FROM [yourtablename] as B WHERE B.[[i]autonumberfield[/i]]<A.[[i]autonumberfield[/i]]+1) AS RowNum, A.[part_desc], A.[part_code]
FROM [yourtablename] as A;

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

Had to run out. I'm in the process of running some samples. Relative to your questions...Yes, the ID field is the ForeignKey field linking back to the vehicle. Looks like this:


tblvehicles:
ID - pk - number (not autonumber)
Vehicle ID - text

tblserialno:
ID - pk-number (not autonumber)/foreign key to tblvehicles.id
Description - pk-text
Serial number -text

tblserialnodescription: - serves as a lookup for tblserialno
Description - pk - foreign key to tblserialno.description

 

[scriverb]

Now I see that the PK key for the table tblserialno is a double field: ID and Description in combination

Is this the sort order that you want the records displayed in your subform? You said you wanted them displayed in the order entered. Well, this would probably be an alpha sort(description) by the way the key is setup.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

scriverb,
The sort order really does not matter nor do they have to be displayed in the order entered, as long as they are displayed numerically. And, if they're not in alphabetical order, so what.

All I need is to see each record numbered as a visual convenience for the user, nothing more. The numbers really mean nothing as far as the db is concerned. Even the numbering thing...it's simply (what I thought) a nice feature to add to a continuous form. (but I see a bunch of trouble heh heh)What I mean by 'numbered as they are entered'...I would like a continuous form design that grows as records are added as opposed to an open, pre-numbered listbox appearance. That's really all I mean't...

directorz

 

[directorz]

scriverb,
I ran a few samples with no success. Did you, or can you run a sample
directorz

 

[scriverb]

I have tested and been success while using an AutoNumber of unique field in a query with the following syntax:

SELECT (Select Count(*) FROM [yourtablename] as B WHERE B.[autonumberfield]<A.[autonumberfield]+1) AS RowNum, A.[part_desc], A.[part_code]
FROM [yourtablename] as A;

Like I said before the easiest way is for you to just add an autonumber to your table and use this format. But, if you choose not to then since the primary key of your table consists of two fields we can probably modify it to accomodate. This is an attempt to do just that:

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

Give this a try and let's see if how it works.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

I will run a sample

 

[directorz]

Scriverb,
Looks like this may work. I'm having some trouble with the continous form. Here's what I did. I started a new query in design view, pasted the code (the one without autonumber) and ran it - all O.K.

I started a new form based on the query fields, set it up as a continuous form. Here's where I may be erroring. With no data in tblserialno, the form, obviously, is blank. But, I have nowhere to input data. Then, if I go directly to the tblserialno and input here, go back to the form, I see what I have inputted but again, have nowhere to add a new record. Am I not seeing something...it must be obvious

Directorz

 

[scriverb]

Make sure that your subforms Allow Additions property is set to Yes and allows navigation buttons. Also, make sure that the RowNumber control is locked and has not TabStop.

let me know if this helps.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

Scriverb,
I have done that. Allow Additions property is set to Yes and I am allowing navigation buttons. RowNumber control is locked and has no TabStop. Same symptoms. Try it your side, you may get what I get.

 

[scriverb]

Because we are creating this new column which is not a real piece of data then the recordset created is "not updateable". I don't think that we can get around this. I have always used this for displaying a rownumber but never for allowing updating.

There are workarounds that we can explore. Try this one. Add Record button that triggers a popup form to appear with unbound controls for adding a new record. The user would add the data for the new record and click a save button on the popup form and the new record is added to the table through code. The original to open the popup form can be set to suspend execution until the control returns from the popup, at which time a requery of the subform control will now include this record as part of the recordset.

I can help with this if it sounds workable.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

Yes, that sounds workable. This type of arrangement seems like it would flow well and be user friendly. Your assistance is welcome Scriverb, and needed.

Directorz

 

[directorz]

I'm thinking of creating a continuous form based on tblserialno and using that as the data entry point ( the pop-up form ). Here, I believe we could have the option of viewing all records relative to the parent record or just 1, depending on personal preference. Sound good or do you have other ideas?

 

[scriverb]

That sounds good. How about trying this. We already have the rownumbered subform for viewing(non-updateable) and if you create an entereable subform that shows all records we can change the subform controls Source Object back and forth with the click of a button on the main form.

The subform data is all that would change. You really don't even have to create a new subform to show all the records just create a regular form the size of the subform object that is setup for Adding New records only. When the user clicks the Add Record button we change the Source Object property and they enter as many records as necessary. When complete they click the button to return to continuous form mode with everything rownumbered again including the new records added.

Sound like a plan?

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

Yes, that sounds like a plan. I’m not sure that we need to show all records in the popup. We should however show all records related to the selected, parent record. If, for example, we have selected vehicle #1 of 25 that are in a fleet.. The editable subform should show only those records related to vehicle #1 so as not to confuse the user. We would end up with vehicle numbers 1 thru 25 and all records. A user could make a mistake otherwise on data entry, having a tough time distiguishing between them. I envision the ID field of tblserialNo displaying the vehicle ID automatically when the (parent record) is selected. It is at this point I am not clear on how to achieve editability (wow, that’s a word?). A cascade delete takes out the description, serial number and the vehicle as well.