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 think we are throwing too many ideas at once. The one that I think will be the easiest for the user to relate to is NOT the popup form but just modifying the subform controls Source Object property with a different form. i.e. Add Record form It just fits into the subform control and the user enters the new data. Multiple new records can be added. Then when completed just change the property back to the original setting.

First thing is to create a form with the table tblserialno as the control source. Set this form up with the same width as your Continuous Form but set its properties to Allow Adds: Yes, Data Entry: Yes, and Form instead of Continuous designation. Make its height just high enough to fit the subform control size. As a test just change the subform control Source Object to the name of this new form. Get it to fit and work properly allowing the addition of new records with the appropriate fields being updated automatically from the main form record.

When that is done we just need to add a button to the Main form that allows for the flip-flopping of the forms into the subform control.

Let me know when you are at that point. Provide the name of the new form also.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

I will. Houston, we have a problem. I started with 1 vehicle and records related to it are numbered correctly, say 1,2,3.

Just for kicks, I added a second vehicle. These related records are numbered 4,5,6 instead of 1,2,3. They should be records 1,2,3 of the vehicle 2.

Must be numbering based on all records as opposed to the parent record

 

[huskerdon]

directorz,

For a completely different approach to the numbering issue on a continuous form, see thread702-843719,
and then look at the response from ZmrAbdulla on 5-19-04.

This worked great for me!

huskerdon

 

[scriverb]

directorz: You are right as the rownumbering is being perform ed on the whole table and we only want it numbered for the linked records. So, modify the SQL Record Source for your continuous form to the following:

SELECT Count(*) AS Count, A.[ID], A.[Description], A.[Serial Number]
FROM tblserialno AS A, tblserialno AS B
WHERE A.[ID] = FORMS![[i][red]mainformname[/red][/i]]![[i][blue]IDcontrol[/blue][/i]]  and (A.[ID] & A.[Description])<=(B.[ID] & B.[Description])
GROUP BY A.[ID], A.[Description], A.[Serial Number]
ORDER BY Count(*);

This will select the records based upon a match to the ID control on the main form. Only the matching records will be row numbered. The Link Child and Master fields property does not need to be changed.

Post back with your results. I have not had a chance to look at the other posting by huskerdon. But, will do that as always looking for a new way to make it work.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

I will work with these in the a.m. and post.

Thank you
Directorz

 

[directorz]

Scriverb

I ran it with the following result. I get ‘Enter parameter value with FORMS![mainformname]![IDcontrol] highlighted. I have 2 forms I am using. My mainform name is FormVehiclesandEquipment. This is the multi-tab form. It’s based on another table (tableVehicles) and linked to TblSerialNo.ID. (In fact, I will change it to TblSerialNo.IDunit for clarification.) The table reads:

ID – pk
VehorEquipID

The other form is FrmSerialNo which is what I am using as the subform (continous form).

So, I used FORMS![FormVehiclesandEquipment]![ID] and returned the error.

In this line, do you mean the main form I’m noting? I’m assuming that IDcontrol is the ID field of this table. This is what I have:

SELECT Count(*) AS Count, A.[IDunit], A.[Description], A.[SerialNo]
FROM tblserialno AS A, tblserialno AS B
WHERE A.[IDunit] = FORMS![FormVehiclesandEquipment]![ID] and (A.[IDunit] & A.[Description])<=(B.[IDunit] & B.[Description])
GROUP BY A.[IDunit], A.[Description], A.[SerialNo]
ORDER BY Count(*);

I'm working on an Add Record form

Directorz

 

[directorz]

Huskerdon,
I believe that the method that the article addresses requires a single, unique key field. My table(s) design is not set up for this as I have 2 pk's. Good article however where applicable

Directorz

 

[scriverb]

The references in color are to be changed to your mainform name and the ID control name on the form. What I posted was just for reference. You see whichever record is currently the active record on the mainform is the ID value we want to use in the criteria for the subform forms Record Source. This way the query will only select the matching records and row number them starting at 1. This solves the numbering problem that cropped up just a few postings back.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

Yes, I understand. I believe that's what I did. I will double check

 

[directorz]

All appears OK. Here's the error I think I made. I modified your last code. As a standalone form, which is how I was testing it, does not work. Once it is placed into the mainform as a subform, the part of your code FORMS![mainformname]![IDControl] is able to be recognized. The only other issue may be that in the associated table tblvehicles...the ID here is an autonumber which may present a problem. I will run with this some more.

Directorz

 

[directorz]

I take that back. I try to run the query with the modfied code and get:

Enter parameter value FORMS![FormVehiclesandEquipment]![ID]
highlighted. FormVehiclesAndEquipment is the name of the mainform and ID is the pk of the table on which the form is based

 

[scriverb]

What is the name of the control on this form where the ID field is the Control Source. If it is on Tab Control then we must include that in the reference.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

ID is an autonumber, PK field in Tblvehicles and is not used on the FormVehiclesAndEquipment, thus is not tabbed. It is however a link to TblSerialNo.

Maybe I'm confused

 

[scriverb]

Add the field/control to your main form. Make is small and invisible, tabstop NO and remove it's label. Place it up in the corner someplace out of the way. No one will know that it is there. But, now we can reference it from within the query.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

I opened the main form, created an unbound textbox, modified its control source to ID, set tabstop to no, saved it, closed it, then went to the query and got the same message

 

[scriverb]

Remember you cannot run just the query without having the main form open to a record. The query is relying upon the forms control having the ID of an active record.

So, you must open the main form and then the subform will use this reference to only show the related records numbered properly.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

Gotcha. Onward, I say!

Need to take a short break

 

[directorz]

Well, ok, a longer break

Directorz

 

[directorz]

I've created the form and named it formSerialNoDataEntry, rather creative. I set it up as a single form as opposed to continuous and modified its properties as you noted.

I'm not able to change the control source of the subform as we are limited to tables or queries, not forms.

From the main form I can call up formSerialNoDataEntry with a command button w/ no problem. However, the IDunit field is not automatically populated with the selected parent record. If I bring the form in as a subform, it is, but we don't want that.

Where do I make this link

Directorz

 

[scriverb]

I want you to bring it in as a subform. The subform control on the main form can have the property Source Object modified through VBA code. By doing this we will include the Child and Master Links property fields which will automatically update the control IDunit.

Try this VBA code in the AfterUpdate event procedure of a command button. Let's call the button cmdSubFormToggle. Set the Caption property of the command button to "Add Record".

If Me.cboSubFormToggle.caption = "Add Record" then
     Me.cboSubFormToggle.caption = "View All"
     Me.[[i][red]subformcontrolname[/red][/i]].SourceObject = "formSerialNoDataEntry"
Else
     Me.cboSubFormToggle.caption = "Add Record"
     Me.[[i][red]subformcontrolname[/red][/i]].SourceObject = "[i][red]continousFormName[/red][/i]"
End If

Update the code with the appropriate names above. This button will toggle the nested subform back and forth between Data entry and continous depending upon what you want the subform control to do. The caption of the command button should also change.

Post back with hos this works.

[COLOR=006633]Bob Scriver[/color]

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