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

 

[directorz]

So we will have the main, multi tab form and on 1 tab will be SubformSerialNumber which is the one we made to display contents but is not editable, and SubformSerialNoDataEntry. Is this correct.

Sorry about the response delays, I' trying to do 2 things at once

 

[scriverb]

The idea is to have one subform control on your form and have the button change the SourceObject property of that subform control. It would change what is displayed in the subform control from the continous form to the single record Entry form. They would toggle back and forth between the two forms.

Take a look at the code I provided above and work on getting that to work. I am leaving for the day and will not be able to respond until later tonight.

Good luck.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

I did not have much time for anything yesterday. Now I see where you’re going, correct me if I am wrong. When we were talking about creating subforms, I did that and was under the impression that I was to have these on the main form. By subform I mean the subform and its contents ie: controls / fields. I have 2002 with the wizards. So, when I was at the main form and brought in an already created subform, the wizard asks for the name of the form to bring in. It’s at this point that you are saying to hit cancel, thus leaving a subform object only. I should place a subform object only, as in the blank, square shape, yes?
If this is correct, then this is where I misunderstood and was lost.

Then, we will reference each of the subforms, by name, with code, which is what you provided. Ingenius to me, as I have never done this.

With the time I had, I played a bit. I noted that a command button does not have an AfterUpdate property however a toggle button does.

 

[scriverb]

Now I think you understand the direction I am going. Yes, just put a subform object(blank sq. object box) on the main form. And I must have misspoke myself before because the Command Button we need to use will have an OnClick event procedure where we want to put the code. We do not want to use an Option button.

The command button is the tool of choice here.

Let me know how you are doing as you progress.


[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

Scriverb

I'm finally doing some work. I assembled the items and ran it with the following result:

Compile error, method or data member not found
in this line:

If Me.cboSubFormToggle.Caption = "Add Record" Then

with cboSubFormToggle highlited

Directorz

 

[scriverb]

I would use a command button rather than a toggle buton. Secondly, where have you located the button? It should be in the main form. If the above code is placed in the OnClick of the control it must be able to find the named control. Please check the spelling. Probably just a typo.

[COLOR=006633]Bob Scriver[/color]

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

 

[PHV]

Sorry to jump into this (long) thread, but it's just to advise that combo don't have Caption property.

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

 

[directorz]

In your code, when you call for subformcontrolname, are you talking about the name of the sub form or the name of the command button. Here's what I did. I used a command button. I placed in on one of the tabbed pages of the main form. A wizard pops up and I clicked cancel, leaving the button. I went into the properties of the button and entered the above code for the OnClick event. Thus

Private Sub Command427_Click()
If Me.cboSubFormToggle.Caption = "Add Record" Then
Me.cboSubFormToggle.Caption = "View All"
Me.[SubFormSerialNumber].SourceObject = "formSerialNoDataEntry"
Else
Me.cboSubFormToggle.Caption = "Add Record"
Me.[SubFormSerialNumberToggle].SourceObject = "subformserialnumber"
End If
End Sub

 

[scriverb]

PHV: It is getting long isn't it. But, I don't think I ever recommended a combo with a caption property. I repeatedly referred to a command button. ???

[COLOR=006633]Bob Scriver[/color]

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

 

[PHV]

Compile error, method or data member not found
in this line:

If Me.cboSubFormToggle.Caption = "Add Record" Then

with cboSubFormToggle highlited

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

 

[scriverb]

You have a command button now called Command427. Either change the name of the CommandButton and the Subroutine name to cboSubFormToggle or change the If statement below to refer to Command427. Either will work.

Private Sub Command427_Click()
If Me.cboSubFormToggle.Caption = "Add Record" Then

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

PHV,
My original question is being focused on and addressed, setting up a numbering system for records. I realize that from the start, this was a task beyond my capability which is why I decided to query professionals such as yourself. Unfortunately, I am not well versed in VB or SQL. The process of obtaining an answer involves steps, as I'm sure you know, which is being patiently provided by another professional. I welcome your input at any time and apologize for its length but sometimes that's what it takes.

 

[PHV]

directorz, no apology needed. I just tempted to offense nobody when I jumped into the thread as I thought that cboSubFormToggle was a combo, due it's name, and thus had no .Caption defined.
Sorry if anybody misinterpreted my intrusion (and for my poor english).

 

[directorz]

I always welcome an xtra set of eyes. I'm blind in 1 eye and can't see out of the other...just kidding

Scriverb,
Made a few typo changes as you suggested and wah-lah, it works beautifully. I will tweak it just a tad. It's a rather neat solution

 

[PHV]

wah-lah, it works beautifully
You may consider clicking the Thank scriverb for this valuable post! URL in any of scriverb's post.

 

[directorz]

Scriverb,
Thank you so much for your valuable time. This arrangement is awesome!

Sincerely,
Directorz

 

[scriverb]

Glad that we could get this worked out. Good luck with your project.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

Scriverb,

I will double check on my end, but could you double check this when you get a chance...

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

I seem to still have the numbering problem as before. The related records are numbered 4,5,6 instead of 1,2,3. They should be records 1,2,3 of the vehicle 2.

Thanx

 

[scriverb]

Just as a test try removing the Link Child and Master field properties of the subform control. We may have to remove them for the continous form and then in the OnClick event when we goto the entry process put them back in. So, just remove them first for the continuous and see if the numbering straightens out.

[COLOR=006633]Bob Scriver[/color]

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

 

[directorz]

I removed them from the subform control and have the same symptoms