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 !

 

[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!!

 

[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.

 

[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.

 

[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!!!

 

[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!!

 

[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!!!!

 

[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

 

[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!!!

 

[jrhessey]

ok got everything going, created some reports, entered in data for testing and ran into a problem with the data on the report. Why does Access fill in data that shouldn't be there?? What it is doing is this...

Our screw machine runs 2 times for a total of 8 1/2 hours, our secondary machine runs once for a total of 5 hours. Now on the report it has the 2 records that need to be on there, but it puts a second 5 hours for the secondary machine that shouldn't be there. So in the totals it has the screw machine running 8 1/2 which is correct, and the secondary machine running 10 hours, which is incorrect, it should be 5 hours.

On the second ticket I entered 2 times for the screw machine ticket and 3 time for the secondary ticket. I end up with 6 entries on the report. It looks something like this...

sec---screw
1.5-----3
1.5-----4
2.25----3
2.25----4
5.5-----3
5.5-----4

The report should have 3 and 4 hours for the screw machine with a total of 7 hours ran and 1.5, 2.25, and 5.5 hours and total of 9.25 hours ran. It should look like this on the report basically...

sec---screw
1.5-----3
2.25----4
5.5

There is also no relationship between the times from the screw machine and secondary machine, so you could have 5.5 and 1.5 switched, etc... For the run times, I've pulled data from seperte queries also. Here is the DB, you can look at it and see how I'm doing it.

DB