Help in fixing code

[nepotist]

Hi, I have attached a sample of my project with this Please find.
Basically I have two things that need to be fixed.
It you look at the attachment Open form ProjTMC1 select project Number 2008148 and click the button it will get you to projTMC which has tmc dairy as a subform.
I have basically tow modules for this form , one is called when the sOUTH BOUND RIGHT TXT BOX Is updated(the last but one in the subform) the other is when you click the update button.
Every thing works fine but the thing is that it dosent update the last intersection (Thta is number 11) in to the tblcms intersections table) I had the error 3021 so had return a code to skip the error and I figured that it is one that is preventing the code to update the that intersection 11.
can some one help me with this.

The other thing is that when u loaded the projtmc1 form and you were about to select 2008148 you would have seen many of them( IT ass these in to the table every time i test it) which shouldnt be the case . how do i stop it from adding duplicates to the table(as that is y primary key)

Please explain wHERE I am doing wrong

Thanks a Ton :D

[CreganTur]

Well the first thing I noticed is that you did not set ConcurrencyID in tblProjectList as a Primary Key, so that's part of your problem.

Can you make that major change and then upload a new version of your test database? I think some of your other problems may be solved after your Primary Key is setup correctly.

[nepotist]

Hey randy,
That was the dumbest mistake I could have done. I have uploaded the new one with the please find.

I have one more question for you. As I have developed the application on this database and I am pretty sure I might have messed up some data. What is the easiest way to replace the whole data(Tables)

[CreganTur]

Well... if you want to just replace the table structure (without the data, since it may be incorrect), then you can just copy your table and paste it back into the Tables section of the database window. it will ask you if you want the structure, or the structure and the data, and some third option.

Just copy the structure only- you'll have to name the table something slightly different to get it to save. Then delete the old, incorrect table, rename your new table to the correct name, and you're done.

Does that answer your question?

I'm looking through your code for the problem you mentioned in the first post. I'm a little sluggish due to not much sleep, so it might take me a while. Hopefully I'll come up with something to help you.

[nepotist]

Thank you randy

[CreganTur]

Okay, I need help understanding what I'm seeing as I step through your code.

For one thing, form projTMC1's button has no code behind it, so it couldn't open projTMC. I opened it by hand and fed it the 2008148 value by hand (since it asked for it via a parameter).

Now, on form projTMC for 2008148 I am seeing 3 intersection ID's: 7, 6, & 11.

I'm stepping through the code of intUpdate() and there's something going on that doesn't make sense to me. During the very first iteration, the GlobalID value = 3, since that is the first value in your tblcmsintersections table. Now, to me it seems like a GlobalID of 3 should be skipped by this line of code: "If rstIntersectionProj.NoMatch = False Then" because 3 is not one of the Intersection_ID values on the form. But it's not skipping past this unused ID- it's accepting it as a match and moving through the rest of the code.

Is this a valid scenario for your code?

[nepotist]

Well that is a good point. The purpose of this code is to update the tblcmsintersections table EL,ET,ER...SL,ST,SR VALUES.

If you look at the tmc dairy 2008148,2008130 have certain numbers for same intersection id, and this module will add all the number of different for a intersection and update it in tblcmsinterections table.
The global id is the primary key to the tblcmsintersections. It seeks every intersection add all the individual project number and update this table.

irrespective of the current project shoes the intersection or not.
The problem with this is if you keep using F8 it does for all the intersections till id=10 and it even calculates the values for id=11 but dosent update the table and goes to erro3021 step.

I had that code as it used to give me a tat error message. I dont know if i completely messed up.
I guess I answered you question

DId I?

[nepotist]

o

[nepotist]

Ok , Here is a clear explanation of that module.

Once the user updates the subform it will be updates in the tmc_Dairy table which has no primary key

The update button / the intupdate() module Function:
1) it first opens the TMC_Dairy table and tblCMSIntersections table and indexes it based on the Intersection_ID
2) It check for every intersection in tblCMSInterection in TMC_Dairy, if there is any match it will add all the records for that intersection and update the values in the tblCMSIntersection table.(as we have a record for intersection_ID 3 in the tblCMSIntersections table that is why we are able to view it)
3) The code works fine till intersection_id 11 and gives me a error 3021, It adds up all the records but dosent update it giving me the 3021 error. I had written onerror 3021 statement in the begnning of the code assuming it would skip it, but actually it dosent.

So the Problem with my code is that 3021 error and how to fix it(I dont understand why I am getting it)

I tried to do the following
Tried to see if the tmc_dairy record is EOF is true and then it woul directly update ttblcms.... table. but no it still give me the same error.

I hope answered you question

[nepotist]

Hey Randy,

Thank you for looking in to my code, I fixed it.
Below is the code that i changed (you can see this part of code at the end of the inupdate. What I did is that I gave a line number to the "line10" and then upon error 3021 or 94 It would move to it and update and go to next record.

Though this still work I would like to know if there is any better way to avoid it and the reason why I am seeing it.


line10:
With rstintersections
.Edit
!el = el
!et = et
!er = er
!wl = wl
!wt = wt
!wr = wr
!nl = nl
!nt = nt
!nr = nr
!sl = sl
!st = st
!sr = sr
.Update
.MoveNext

End With
Loop

MsgBox "Update Complete", vbOKOnly

Exit Sub

Err3021:
If Err.Number = 3021 Or Err.Number = 94 Then
GoTo line10:
MsgBox "Update Complete", vbOKOnly
Exit Sub
Else
MsgBox Err.Description & ", " & Err.Number, vbMsgBoxHelpButton
End If
MsgBox "Update Complete", vbOKOnly


Thank you

[CreganTur]

I'm glad you found a workaround.

I've been looking at your code on and off for the past couple of days and I feel like there's something I'm missing that will solve your problem, but I can't put my finger on it. If I figure it out, I'll let you know.

[nepotist]

Well I will not make this thread solved then till I hear from you

Thanks for looking in to it