Need help with a report numbering issue

[CalvinToo]

Hopefully someone can assist me with this issue, I'm drawing a blank on how to fix this...

I need to somehow force the front end user to go to the last record before they can assign a new number for a report command event.

Currently the coding is this

Private Sub Command71_Click()

Dim MAXMRR As Integer
Dim NumMRR As Integer


NumMRR = DCount("[MRR#]", "MRR")
If NumMRR > 0 Then
MAXMRR = DMax("[MRR#]", "MRR")
Else
MAXMRR = 0
End If
'MAXMRR = DMax("[MRR#]", "MRR")
Me![MRR#] = MAXMRR + 1
End Sub


Unfortunately this allows people to overwrite existing assigned report #'s by clicking on the button if they don't go the next record by manually clicking "next record" on the bottom of the form.

So just for examples sake this is what can occur (and did occur bringing this issue to my attention)

If it is used correctly and you created the first 5 reports with no issue we would have

Report #1 = MRR-001 (then you would hit [next record] manually for each report)
Report #2 = MRR-002
Report #3 = MRR-003
Report #4 = MRR-004
Report #5 = MRR-005

The problem occurs when a new user logs in and starts the page / form opens at Report #1 / MRR-001 and then they hit the "Assign MRR#" button it would then overwrite MRR#-001 as MRR#-006 with out changing any other field data.
This error is reproducable on any form in any number of combinations but you can only assign a new # once when you first open the form so to further eloborate on the above say they open report / form #001 and hit assign next MRR# it would change it to MRR#-006 but if you hit again it would not to go to MRR#-007, However if you moved to MRR#-002 and hit assign new MRR# it would go to MRR#-007 and also if you closed out the form and opened it again you would then see MRR#-006 where MRR#-001 used to be and you could again click on Assign MRR# and change -006 to -007.

Hope that makes sense.

Anyone help me out here?

Thanks
Calvin

 

[dkinley]

It sounds like your code is to determine the report/record number, not sending the user to a specific place.

How about when they hit that button they go the last record ...

DoCmd.GoToRecord , , acLast

Or, because they are creating a new record ...

DoCmd.GoToRecord , , acNew

-dK

 

[CalvinToo]

dk,

Thanks I think that should work (I hope) where in the command do I enter this code and does it matter?

Thanks
Calvin

 

[dkinley]

You are establishing record number, so after you have it established then send the user to the new record.

NumMRR = DCount("[MRR#]", "MRR")
 
If NumMRR > 0 Then
     MAXMRR = DMax("[MRR#]", "MRR")
Else
     MAXMRR = 0
End If
     Me![MRR#] = MAXMRR + 1
     DoCmd.GoToRecord , , acNew

Should do the trick.

Disclaimer: I am making a few assumptions here, so it might not work. Let me know. =]

-dK

 

[CalvinToo]

dk,

I've tried both as shown below

Private Sub Command71_Click()
Dim MAXMRR As Integer
Dim NumMRR As Integer


DoCmd.GoToRecord , , acNew

NumMRR = DCount("[MRR#]", "MRR")
If NumMRR > 0 Then
MAXMRR = DMax("[MRR#]", "MRR")
Else
MAXMRR = 0
End If
'MAXMRR = DMax("[MRR#]", "MRR")
Me![MRR#] = MAXMRR + 1

End Sub

It unfortunately doesn't fix the issue you can still generate another MRR# number with out going to a new record which is what I'm trying to stop...

Any other ideas?

Thanks
Calvin

 

[dkinley]

It unfortunately doesn't fix the issue you can still generate another MRR# number with out going to a new record which is what I'm trying to stop...

Oh, my perception of problem is in error ...

If Me.NewRecord Then
     ' Do what you want if it is a new record
Else
     ' Do what you want if it isn't a new record.
End If

That help?
-dK

 

[CalvinToo]

dk,

That is what I want to do but I could use some help with the coding I don't know programming to save my life...sorry.

If it is a new MRR# then I want it to go to a new Record # so that for examples sake say I have 10 reports and 10 records I want the user to have to start on record # 11 and when he hits the "MRR#" button it generates the MRR#-011

Hope that makes sense.

So if new record generate new MRR# (which would be the last field or new # in the MRR Table as shown in the coding above)

Else ' IF it isn't a new record when you hit the MRR "button" I don't want it todo anything so it forces the front end user to go to the next record before they can create a new MRR#)

Thanks again for your help.

Cheers
Calvin

 

[dkinley]

i think I am beginning to understand ...

Dim MAXMRR As Integer
Dim NumMRR As Integer
 
NumMRR = DCount("[MRR#]", "MRR")
If NumMRR > 0 Then 'this MRR already exists so go to the next record
    MAXMRR = DMax("[MRR#]", "MRR")
    DoCmd.GoToRecord , , acNext
 
Else 'this MRR does not exist so a new record needs to be created
    MAXMRR = 0
    DoCmd.GoToRecord , , acNew
End If

Let's try this first and then work on the assignment of the new MMR

-dK

 

[CalvinToo]

Awesome that works so far this pushes you to the next record now I just need it to assign the next MRR record!

Thanks
Calvin

 

[dkinley]

No problem ...

Now, on the OnCurrent() event of the Form, use this ...

If Me.NewRecord Then
    Me![MRR#] = DMax("[MRR#]", "MRR") + 1
End If

And see if that will do it.

-dK

 

[CalvinToo]

dk,

You lost me
"OnCurrent() event of the Form"

So this isn't in the Visual Basic Code area but instead I need to input this coding in the event builder in the Form?

Sorry I'm a bit new and learning fast here.

Thanks again
Calvin

 

[dkinley]

No problem .. you just need to get to the form properties. When you look at the form in design mode. In the top left hand corner (apologies, there may be another way to get here - just the way I do it) where the two rulers meet, there is a box, click there a black dot will appear (let's you know you are on the form).

Left-click that to bring up the properties. Click the event tab and then do the coding trick on the OnCurrent like you did for the click on the command button.

-dK

 

[CalvinToo]

Under the properties on the "command button" [assign MRR#] I have several "ON" options but I don't see and "OnCurrent"?

There is;
On Enter
On Exit
On Got Focus
On Lost Focus
On Click (which is where i have the event procedure we have outlined above in VB)
On Dbl Click
On mouse down
On mouse Move
On Mouse up
On key down
On key up
On key press

but I don't see anything that says OnCurrent?

Cheers
Calvin

 

[CalvinToo]

aha! thanks I got the field I was writing my last post and didn't see your new one - I'll try that and see what happens!

Cheers
Calvin

 

[CalvinToo]

Ok we are getting somewhere I think but it still doesn't quite work as intended.

When I start fresh and have 3 MRR reports fully input and starting on MRR#-001 and record #1 of 3 and I hit the [Assign MRR#] Button it gives me this error

Run-Time Error '2105':
You can't go to the specified record.

It also changes the MRR#-001 to MRR#-004

If I move to MRR#-003 which is record 3 of 3
and hit [Assign MRR#] it changed the MRR#-003 to MRR#-002 and oves me from record 3 of 3 to record 2 of 3, so it is going backwards?
Clicking it again moved me back to Record #1 of 3 and shows MRR#-001 to MRR#004?
Clicking it a third time changed record 1 of 3 from reading MRR#-004 to MRR#-007 and giving the same error message as shown above (run time '2105')


Help!

 

[CalvinToo]

Ok I straightened out those issues by compiling the DB (I remembered that I need to do that sometimes to fix stuff . However I have a new issue )

When I hit the Assign MRR button it will keep moving me to a new record till it ends up at the new record and MRR report (Yay!)

Edit: I have it worked out I forgot to save and when I exited VB it didn't register my On current event - I'm getting to used to Access and quitting a form and having it auto save!


Thanks so much DK. I owe you a couple beer if you end up around Edmonton AB!

Cheers
Calvin

 

[dkinley]

The run-time error is because it is trying to advance outside the records (either the one previous to the first one or the one after the last one). I am not too concerned about that at the moment as I am about the miscounting ...

-dK

 

[CalvinToo]

DK,

I edited my post above but I think you missed it. I beleive it is all working as I want right now more or less the only thing I would like to change now if possible is when you have multiple records and you don't start at the end is when you hit "assign MRR#" it would take you to the next available open record.

Using the current 3 records I have if I open the form and I'm on record 1 and MRR#-001 and I hit "Assign MRR#" it goes through all the records one at a time until it hits the new one - not a big deal I suppose but it would be nice if I could put a line of code in and have it go the next available record.

Either way I owe you a drink!

Thanks
Calvin

 

[dkinley]

Good ... I am thinking that there might be an easier way to do all of this .. but for now we are going to trap the error and force the database to do something when it encounters it.


First though, go into the code of the button and delete it all (even the first line (Private Sub Command71_Click()) and the last line (End Sub).

Click the properties of the button and let's rename it (the Name box on the Other tab) something a little better (that way it will stand out as you add code to other bits of your form later) ... like cmdGotoNext.

Now, using the code builder of the OnClick() event for the button make it look like ...

Private Sub cmdGotoNext_Click()
On Error GoTo Err_cmdGotoNext_Click
    Dim NumMRR As Integer
 
    NumMRR = DCount("[MRR#]", "MRR")
    If NumMRR > 0 Then 'MRR already exists so go to the last record
        DoCmd.GoToRecord , , acLast
    Else 'this MRR does not exist so a new record needs to be created
        DoCmd.GoToRecord , , acNew
    End If
Exit_cmdGotoNext_Click:
    Exit Sub
Err_cmdGotoNextClick:
    Select Case Err.Number
        Case 2105 'reached the end of the record so creating a new one
            MsgBox "You have reached the end of records. Creating a new one."
            DoCmd.GoToRecord , , acNew
        Case Else 'in case a different error ever pops up
            MsgBox Err.Number & " " & Err.Description
    End Select
    Resume Exit_cmdGotoNext_Click
End Sub

Changed it so it will go to last record.
-dK

 

[dkinley]

Hey Cal ... I am an idiot .. got to thinking about it a little more

    Dim NumMRR As Integer
 
    NumMRR = DCount("[MRR#]", "MRR")
    If NumMRR > 0 Then 'MRR already exists so go to the last record
        DoCmd.GoToRecord , , acLast
    ElseIf NumMRR = Me![MRR#] 'user is on the last record
        DoCmd.GoToRecord , , acNew
    Else 'system doesn't know what is going on
        MsgBox "System has experienced an error, please contact your system administrator.", vbCritical , "System Error"
    End If

The ElseIf statement let's the system know if its the last record because the count of MMR's is equal the current MRR number. In this instance, you will not need to trap the error (but save that one for your bag of tricks).

-dK