Numbering a Recordset

[ksgirl]

I am very new to all of this, but I need some help! Currently I have two forms. These forms are for entering new data into the database. The first form's primary key is the 'Clearance ID' field which has an autonumber format. After all info is filled into the first form then the user is prompted to fill out the second form. This is where I need help.....The second form is referenced to the 'Clearance ID' given in the first form. This second form, 'Tag Info', allows the user to add tag placement information. Right now the 'tag' field is a number field, where the user has to input the tag #, such as 1, 2, 3, 4...... for each record they add. Ultimately I want this field to be auto-populated, starting with 1 and increasing by 1, each time a field is added, within that specific 'Clearance ID'. I've tried the following:

Private Sub Form_OnOpen()

Dim rstTagID As DAO.Recordset
Dim Tag As Long

Set rstTagID = DMax("[Tag]", "Clearance Tag List&quot

If IsNull(rstTagID) Then
Tag = 1
Else
rstTagID.MoveLast
Tag = rstTagID!Tag + 1
End If

Me.Tag = Nz(DMax("[Tag]", "Clearance Tag List&quot,0) + 1

End Sub

But this doesn't work! Does anyone have a suggestion?

 

[FoxProProgrammer]

Hi,

I'd suggest relating your tables and using a subform. This will simplify your code because Access will add a new record to the parent table (Clearance) if you add a new record to the child table (Tag) that isn't already in the Clearance table. dz

 

[ksgirl]

I really don't want to do this. But if I do attemp this, how would I go about doing this to the subform? Wouldn't it be about the same concept? To populate that field?
Any other ideas?

 

[FoxProProgrammer]

It isn't really the same concept because subforms are used specifically to simplify the task of displaying parent/child relationships. Access handles much of the "housekeeping" behind the scenes so you don't have to code it manually. You might read the Help in Access on Subforms as there's no reason to repeat it here. It is very easy to create a subform. All you do is create the subform just like you would any other form. You could display the Tags on the subform either in a continuous form, datasheet, or in a list box. Next create your Main form. It will display the Clearance data. Now, select the subform tool and click it on your main form. Move it and shape it how you want, and now you're done. When you add records to the child table (Tag), Access automatically adds a record to the parent table (Clearance) if needed. It sounds like you have a one to many relationship between Clearance and Tag. Is this correct? If not, then scratch what I just wrote. dz

 

[ksgirl]

I know how to make a subform, and yes it is a one-to-many relationship, but I need to clarify my question to you. I was wondering how I would populate the field 1, 2, 3, etc...for every record that is added if I did create a subform. I ultimately need this to save back into the 'Tag' table..... Basically this is what I have:

1 Clearance ID
* Tag 1
* Tag 2
* Tag 3 and so on.....

There might be anywhere from 1 to 10 tags added to that specific clearance ID, and all I want is a field that will number this way automatically. Does this make sense? Let me know if I need to clarify more!

 

[FoxProProgrammer]

Hi,

You're right; I did misinterpret your issue. Sorry! )

I still think that you could use a subform to display the two forms in one, but it isn't salient to your issue. Now that I understand better, let's start over. Is this what you have?

Clearance Table
Clearance ID (primary key) Field2 Field3...
1 .... .....
2 .... .....

Tag Table
Clearance ID (foreign key) TagNo Field3...
1 1 ....
1 2 ....
2 1 ....
3 1 ....
3 2 ....
3 3 ....

Ok, assuming that the above is correct, you want to increment TagNo by 1 when the user adds a new record.

What I would do is..

I don't know how you are saving the record. If you use a Save button, you could place the following code in the OnClick event of the button.

Dim lastTag as Integer

lastTag = DMax("[TagNo]", "Clearance Tag List", "[Clearance ID] = " & Chr(34) & txtClearID & Chr(34)&quot

Now, assuming that you have created a recordset named rstTagID, you could save the new Tag like this:

rstTagID!TagNo = lastTag + 1

The last parameter in the Dmax function is like a Where clause in a query. It makes the function return the highest number in the TagNo field in the table named "Clearance Tag List" Where Clearance ID is a specific value. In this case, I assumed that your form contains a text box with the current Clearance ID. If it doesn't, you will need to add it. It could be hidden if you don't want to display it.

Is this more what you were looking for? Sorry for the initial confusion.

dz

 

[FoxProProgrammer]

Oops, I forgot to allow for the case when you are adding the first tag for a Clearance ID. Here's a more complete example that includes code to save the record. How are you saving it now?

Dim db as DAO.Database
Dim rstTagID As DAO.Recordset
Dim lastTag as Integer

Set db = CurrentDb
Set rsTagID = db.OpenRecordset("tablename", dbOpenDynaset)

lastTag = DMax("[TagNo]", "Clearance Tag List", "[Clearance ID] = " & Chr(34) & txtClearID & Chr(34)&quot

rstTagID.Edit
rstTagID!TagNo = Nz(lastTag, 0) + 1
rsTagID!field1 = somevalue
rsTagID!field2 = somevalue
rsTagID.Update
dz

 

[ksgirl]

Hey thanks, I was in the middle of replying to you when you posted again, so here is a modified version of what I was typing! This is what I am looking for, but I don't know if I am going to get this to work quite yet, but I'll try. I just have a few questions before I tackle this. Is there anyway I can do this in the "OnOpen" event of the form? If I use it in the save-OnClick method, it won't show on that form when you open in right? And yes, the second form does contain a text box with the current clearance ID. I guess I'm confused. Any explanations are great, because I am still so very new to Access.
~Nicole
PS. What does Chr(34) stand for?

 

[FoxProProgrammer]

Hey Nicole,

I don't see how you can do this in the On Open event of the form. If you want to add 1 to the TagNo when adding a new record, you will have to do it in the event that runs when you save the record. When you first open the form, the data will be displayed based on how you have the form set up. Is the form bound or unbound? Do you have anything in the Control Source of the Form? What kind of controls are on the form and where do they get their data from? Are they bound or unbound? The answer to these questions will determine how you initialize the form when it is opened.

Chr(34) is the ASCII representation for a double quote (&quot. It is a convenient way to concatenate a string that needs to be enclosed in quotes.

Let's say that txtClearID contains the string "ABC". If you were to write out the DMax function with the 2 in place of txtClearID, it would look like this:

lastTag = DMax("[TagNo]", "Clearance Tag List", "[Clearance ID] = "ABC&quot

The Chr(34) in front and in back of txtClearID enclose its value in quotes.

When I went through this, I realized that I may have made a mistake. If txtClearID is a number, lastTag should be assigned like this:

lastTag = DMax("[TagNo]", "Clearance Tag List", "[Clearance ID] = " & Trim(Str(txtClearID)))

In this case, you have to convert the number to a string to concatenate it with the rest of the string.

When it is evaluated, it will look like this:

lastTag = DMax("[TagNo]", "Clearance Tag List", "[Clearance ID] = 2&quot

Hope I didn't confuse you!


dz

 

[ksgirl]

OK....I think we are getting somewhere. I just have a few more comments/questions.... I currently don't have a "save" button on the form. This is because I was under the assumption that access already did this for you as typed in fields. I have the form set up so the user has to fill in all fields necessary before exiting the form. Do I need a save button? Also, so if I'm interrepting this correctly, the number one won't show up on the first record until it is "saved"....and this is the only way this can be achieved? When the form first opens, it is the first record, and therefore I need that number to appear in the field. I guess I'm making this too complicated! Do you see my frustration? Any suggestions now? Thanks again for all your help!
~Nicole

 

[FoxProProgrammer]

Access can be frustrating!

You are correct that Access saves records automatically, but only if the form is Bound. This makes it easier to save records, but is more prone to errors and not very robust (in my opinion). If you use a bound form, you should ask the user if they want to save changes to the record if they close the form before moving the record pointer, which would save the record. If you use a Save button, changes would only be made to the table when the user presses the Save button, so obviously you have more control. I also think that a Save button gives an application more of a Windows feel, but it does require some coding to do it.

The code we have been working on only assigns the tag number when you add a record. That was your original issue and we haven't yet addressed what happens when you open the form. If the form is bound and the tagNo control is bound, then it will populate for you. Otherwise you are going to have to write some code to get the data. How is your form set up? dz

 

[ksgirl]

I believe the form is bounded, if you mean that its control source is linked to a table.....if not I'm not sure what you are getting at. And yes the "tagID" control is bounded to that field in that table also. Is this what you were wanting to know? I'm still so very new to all of this, so I'm not sure what meaning to place behind the work "bound".... so any explanation is great! So you think adding a saved button would be to my benefit...if this is the case then how do I unbound a form? And is there any special code that needs to be put in the save button (other than the code we've been working on)? So if I add a save button, then the user has to save each record each time they add a new one, or the save every record they've added when they exit the form?? I'm sorry I'm so "Access" illiterate!! Thanks for all your help!
~Nicole

 

[FoxProProgrammer]

Hi Nicole,

Your understanding of a bound form and control is correct. No explanation necessary )

Re the save button...that's really a personal design issue. It depends on how you want it to work and how your users would prefer it work. If you add a save button, they would save each record before going to the next one. It doesn't work well for continuous forms, but does work well for a form that only displays one record at a time. It doesn't appear from your description that you are using a continuous form.

To unbind the form, you would delete the table name in the Record Source property of the form. To unbind the controls, you would delete the field name from the Control Source property of each control. It would also be advisable to rename the text boxes so they don't match the field names exactly. For example, if you had a bound control named TagNo, I'd recommend naming the control txtTagNo to distinguish it from the field in the table.

Now you have an unbound form and unbound controls. To save the record, you would place the following code in the On Click procedure of the Save button.

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordSet("tablename", dbOpenDynaset)

rs.Edit
rs!TagNo = txtTagNo
rs!fieldname = somevalue
etc.
rs.Update

rs.Close
db.Close

This code opens a recordset based on the table, sets the recordset up to be edited, sets the value of the fields, and updates the table. dz