Re start numbers on linked table

[sByrne]

Hello,

I have two tables each with autonumber primary keys. The main table is 'Incidents' and the second is 'Notifications' with a one (incident) to many (notification) relationship. Is there anyway that I can have a field on the Notification table that will number the notifications for a particular incident?

For example incident #100 has 3 notifications. Using autonumber the notifications will be numbered 1-3, but for the next incident, they will be numbered 4, 5, etc. Can I reset it so the the number for notifications on the second incident starts at 1 again?

If I haven't explained this clearly enough, let me know and I'll try again!

Thank you!!

 

[ecniv]

Yes you could.
Leave the auto number field in as the unique identifier.
Make a new field for the response number.
Use code or a query to get the max response number so far and add 1 (if null default to 0 using the NZ function).

Its an option.

Vince

 

[sByrne]

Hello,

Thank you for responding. Unfortunately I have no idea how to do what you suggest - I've been trying to do the query but haven't figured it out. Would you mind clarifying some more?

Thank you !

 

[Pat Hartman]

As ecniv said, leave the autonumber as the primary key. Then in the BeforeInsert event of the 'Notifications' form add a single line of code to calculate the sequence number.

Me.SeqNum = Nz(DMax("SeqNum", "Notifications", "IncidentID = " & Me.IncidentID), 0) + 1

 

[jrhessey]

I'm trying use this formula, but I'm messing it up somewhere. I have a form, called Data Entry, that uses the autonumber and the field is ID. I have a subform, called time entry, to insert times that machines have run. The form has a run #, time start, time stop, and run time field. Here is the code I'm using...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.SeqNum = Nz(DMax("SeqNum", "[Time Entry]", "Run = " & Me.Run), 0) + 1
End Sub

With that code I am getting a "Compile error, Method or data member not found" with the private sub form highlighted...

I would like to have the run field count up for each set of times that is entered for that particular main id number...

What am I doing wrong??

Thanks!!

 

[Pat Hartman]

Make sure that SeqNum is actually in the table/query [Time Entry]. Can you make the DMax work if you supply a hard coded value for run?

 

[jrhessey]

First off, thanks for helping me out!!

I want the Run column to be the sequential number...

does that mean I need to change the me.senum to me.run??

Should it look like this??

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Run = Nz(DMax("Run", "[Time Entry]", "Run = " & Me.Run), 0) + 1
End Sub

Run is the field the seq. number will be in and Time Entry is the table.

 

[Pat Hartman]

Try the following. The search field must be the field that identifies the "group" so if IncidentID is not the correct name, change it.

Me.Run = Nz(DMax("Run", "Notifications", "IncidentID = " & Me.IncidentID), 0) + 1

 

[jrhessey]

Ok, I think I understood what you said... Here is the code I am using now...

=Me.Run=Nz(DMax("Run","[Time Entry]","Forms![Data Entry]![ID] = " & Me.Forms![Data Entry]!ID),0)+1

Now with this code I'm getting an error, "the object doesn't contain the Automation object 'Me.'. I'm pretty sure the code contains the correct fields. The forms ID is the id off of the time entry form that ID's each "group" of runs. Time Entry is the subform that contains the runs and time. I don't know why it is putting an = sign in front of the Me.Run part.

 

[Pat Hartman]

We are zeroing in on the syntax. Keep trying:
=Me.Run=Nz(DMax("Run","[Time Entry]","[ID] = " & Me.ID),0)+1

"[ID] = " = the name of the field in the table/query named [Time Entry]
Me.ID = the name of the field on the form. If the field is not on the current form, you'll need to use the formal Forms!etc. reference style.

The final problem is that the statement starts with an =. The = is only used when you place an expression in the ControlSource property. Since you need this value to be stored, you need to place the code in the correct event procedure. I would suggest the BeforeUpdate event if the user doesn't need to see the generated value or use the BeforeInsert event if he does.

If Me.NewRecord Then
    Me.Run=Nz(DMax("Run","[Time Entry]","[ID] = " & Me.ID),0)+1
End If

The problem with putting the code in the BeforeInsert event is that that event is executed as soon as the user types a single character in the form. This means that the amount of time that the ID is "generated" but not committed is maximized. This increases the potential for another user to attempt an update within the same time frame and that could cause duplicate numbers to be generated. You really should include code that traps duplicate key errors when you generate your own sequential numbers. Access takes care of this problem for you when you use Autonumbers but when you code your own, you need to handle the problem yourself.

 

[jrhessey]

I am getting the error, "can't find the macro Me" Do I need to create one of these??

Here is the code I have now...

Me.Run=Nz(DMax("Run","[Time Entry]","Forms![Data Entry]![ID] = " & Me.Forms![Data Entry]!ID),0)+1

The ID field in the Time Entry form is set to copy the ID field in the data entry form. I did this in case someone would happen to mistakenly enter data for one of the subforms and nothing else, throwing the autonumbers off and therefore the tables would no longer be linked correctly. So the control source of the ID field in the time entry form is "=Forms![Data Entry]![ID]". That why I'm using that in the formula, is that ok/correct? I tried it on the Before Update also, it doesn't matter where the formula is considering the two you've suggested, either will be fine. I don't have to worry about multiple people entering data, this will be on one computer with one person entering data at a time.

BTW, Thanks for all of your help!!!

 

[Pat Hartman]

That why I'm using that in the formula, is that ok/correct?

- NO. Access makes a query out of the information supplied to the DMax() function. The query will look something like:

Select Max(Run)
From [Time Entry]
Where [ID] = Forms![Data Entry]!ID;

You didn't make any change that I suggested. The third argument for the DMax() MUST evaluate to a where clause without the where. It doesn't make any sense for it to be comparing one form field to another.

Also, Me.Forms![Data Entry]!ID is invalid syntax. Either use Me. or Forms!formname. Me. is used when referencing a control on the current form (or its parent form) from within the current form's class module. The longer Forms! reference is used when referencing a control on a different open form.

 

[jrhessey]

I tried your code and I got the same error. Here is a link to my database, I don't want you to do it for me, I am just hoping this will better help you understand what I'm doing, I suck at explaining things.

parts washer

Take a look at the file, Data Entry is the main form with subforms of Time Entry, Secondary Time Entry, and Piece Count Entry. Each of the subforms can have multiple records pertaining to the one Data Entry record. I am calling each id field in the subforms from the main data entry ID field so even if someone goofs up by entering data in a subform and not the data entry form, which would mess up the autonumbers and throw everything off.

If I read your last paragraph correctly, Me.ID (data entry ID) would work becuase it is in the parent form. I thought you wanted to use the ID from the Time Entry form. Thats why I kepting thinking I needed to use the forms method. Here is the code I now have in the Before Update field...

Me.Run=Nz(DMax("Run","[Time Entry]","[ID] = " & Me.ID),0)+1

Since the ID is from the Data Entry form, do I reference that anywhere??

Thanks again!!

 

[Pat Hartman]

You are trying to write code in the properties dialog form. You need to place code in a module. Remove the code from its present location and then press the builder button to the right of where your code was. Choose Code if you are given an option and past the code in the generated module.

If the ID field in question is in the parent form, you need to use Me.Parent.ID to reference it.

There are lots of issues with your db.
1. Naming standards to start with. Names should never include embedded spaces or special characters.
2. Most of your tables have no primary keys defined.
3. ID is a poor choice for a name since it has no meaning. Use DataEntryID. Just as meaningless but at least it identifies which table the ID is the key for.
4. Change all the ID's to the name you choose for the primary key in the Data Entry table.
5. Add unique ids to each of the other tables. Give them relevant names. All tables should have primary keys.
6. Create relationships and enforce referential integrity.
7. The code associated with the "update" button updates EVERY row in the table. That doesn't make sense. There is no need to run an update query if all you want to update is the current record. Just add a single line of code to the FORM's BeforeUpdate event:
Me.[check to print] = 1

I'm sure there's more but that should get you started. Go to the Tools/Options/General tab and make sure that all the name autocorrect options are turned on. If you start by changing the field names in the table, Access will propagate the changes to queries and forms (but not code or macros). Make sure that you open each object and verify the changes. The process is somewhat tricky. You can find a complete description of how name autocorrect works in the MSDN library.

 

[jrhessey]

Woohoo!!! I've got a number coming up in the run field now, only problem is that it's always 1, it doesn't count up, here is the code I've got...

Me.Run = Nz(DMax("Run", "[Time Entry]", "[Run] = " & Me.Run), 0) + 1

I think I've got a loop in there or something, but I don't know? Going on what you told me before...

[Run] is the field in the table
Me.Run is the field in the form

I figured you find some things wrong in the DB...
I think I got everything you mentioned...

I changed the names, removed all of the spaces
Added unique ID's to the tables and listed those as primary keys and Autonumber
renamed all of the original ID fields to DataEntryID

As for the relationships, should I create more? I already have all of the renamed ID fields linked. I don't of anything else I can link between everything, the ID is the only thing that is common.

As for the update print query, I'm not going to have the update on the data entry form, it will be somewhere else. The people want a report of all the parts done since the last time it was printed. Here are the steps they will take...

1. Enter in 15 or 16 records on the data entry form with multiple records in the subforms.

2. They will print a report of the stuff they ran for that day.

3. They will click a button to update that group of records to show as printed and from there on those records will not be printed.

I understand exactly what you are saying about that, but I didn't have it completely finished, I just threw it there to test it out and make sure it worked. With those steps in mind, did I set it up correctly then?

Almost there!! That is, unless you find other things wrong... Again thanks for your help!!!!

 

[Pat Hartman]

Go back and re-read my previous posts. Your criteria needs to be based on Incident NOT Run. In English the Dmax() needs to say - Give me the maximum value for Run where Incident = SomeValue. Yours says - Give me the maximum value for Run where Run = Null. This ALWAYS returns Null so the NZ() converts the null to 0 and the remaining part of the instruction adds 1 to 0 and the result is 1.

 

[jrhessey]

OK, I have the form counting!!


When it's open by itself, it doesn't count when loaded as a subform. I get a compile error, method or data member not found.

I have the formula correct, or atleast I thought I did, since it's working by itself...

Me.Run = Nz(DMax("Run", "[Time Entry]", "[DataEntryID] = " & Me.DataEntryID), 0) + 1

When I open it as a subform though, it has a compile error, Method or data member not found.

The part that is highlighted is this

Private Sub Form_BeforeUpdate(Cancel As Integer)

So I started thinking and decided to try a code you entered in earlier...

If Me.NewRecord Then
Me.Run = Nz(DMax("Run", "[Time Entry]", "[DataEntryID] = " & Me.DataEntryID), 0) + 1
End If

That doesn't work by itself or as a subform. I get an error when using as a subform, "invalid outside procedure". Since that didn't work either way, I assume that isn't right and the first formula is the correct one to use for this. I've tried maybe a different field name, but that errors also. What the heck am I doing wrong here?? (besides using access) Thanks!

Here is a link to the updated file...
Parts Washer

 

[Pat Hartman]

When you change names, you sometimes have to force forms and reports to refresh their fields collections. To do this, open the form in design view, delete the recordsource, save the form, replace the recordsource. The fields collection is now refreshed and should see your new fields. You still have some problems.

1. You need to enforce RI. Access won't let you do it until you remove any preexisting "bad" data.
2. You still have field names with special characters. They should be changed.
3. You still have other object names with embedded spaces. They should be changed.
4. The run field should have its locked property set to yes and enabled set to no. You don't want the user to be able to enter data in this field.
5. On the subforms, remove the =DataEntryID.
6. Make sure that the master/child links are properly set and Access will automatically populate the foreign key fields on the subforms.
7. Remove the defaults for the foreign key fields and set the field to required.

 

[jrhessey]

1. You need to enforce RI. Access won't let you do it until you remove any preexisting "bad" data.

Got It! That is very nice!!

2. You still have field names with special characters. They should be changed.

I didn't even notice that the first time around... Fixed

3. You still have other object names with embedded spaces. They should be changed.

Field names are changed, should I change the table, query, and form names also?

4. The run field should have its locked property set to yes and enabled set to no. You don't want the user to be able to enter data in this field.

Set, I was looking for a way to do that...

5. On the subforms, remove the =DataEntryID.

Changed

6. Make sure that the master/child links are properly set and Access will automatically populate the foreign key fields on the subforms.

Done, and it appears with the first entry that Access is applying the numbers.

7. Remove the defaults for the foreign key fields and set the field to required.

In the process of going through and making all fields we need required, will add these to the list.



Everything is working!!! I'm going through and entering tickets in, everything appears to be working, runs are counting and the id's are propagating. I have a couple of questions left. The first, it's nothing wrong, but when I do the secondary time entry the id number goes blank when I go to the next record in that subform. The other 2 subform ids do not change. For example, I'll enter a start time and the id becomes 14, I enter the stop time, runtime calculates, I go to the next row and the id goes blank, I enter antoher start time and the id becomes 14. Nothing is wrong I would just like it not to change. I've looked in the other 2 subforms to see what I need to change, but I must have overlooked it. Second question is I want to do a switchboard and have a link for entering data and a link for correcting mistakes. What would be the easiest way to go about doing that?? I know if I change the data entry property from no to yes, it will not show previous entries and stick you at the end of the entries to put new entries in. Is there a way I could use a field like the "record number controls" at the bottom of the DB as an actual search field in the DB?

For all of the help that you given, Thank you!!! Thank you!!! Thank you!!!

 

[Pat Hartman]

If you use the Switchboard Manager (I like it although some members don't), you can choose to open a form in add mode or edit mode. So, just select the same form twice. Label them properly and you shouldn't need to modify the form at all.

The best way to add a search feature to a form is to add an unbound combo at the top of the form. The wizard will walk you through building the combo.

I'm not sure what is causing the problem with one of the subforms (I don't have time to look at your db) but it could be that the sequence number field is not bound (has no ControlSource).