Need help with a report numbering issue

[CalvinToo]

DK,

Thanks so much!
I can't wait to get back to work (well in a manner of speaking anways lol) and try this out on Tuesday!

I'll let you know how I make out with the new code

Thank you again your a god send!

Cheers
Calvin

 

[CalvinToo]

Dk,

I have an error in this line of code

ElseIf NumMRR = Me![MRR#] 'user is on the last record

It has a compile error coming up saying
"Syntax Error"

EDIT:

DK,

Sorry I should have taken a harder look I fixed that line it just need me to add "Then" before the

'user is on last record

text. Now I've fixed that error and I have a new one which is a compile error quoting that the

Label not defined

is popping up. I did a quick look and it appears I need to have this "label" with a "procedure" which I don't...

help!

Thanks
Calvin

Thanks again!
Calvin

 

[dkinley]

I am hoping that the MMR# was a control on the form.

Might try Me.[MMR#]. Unless the control has a name attached to it.

-dK

 

[CalvinToo]

Dk,

I'm pretty sure that MRR# is a control in the DB as I've seen the Me! in code before.

I've replaced the command # 71 now and implemented the new Private Sub cmdGotoNext_Click

Now when I compile I'm getting a new error

"compile error:
Member already exists in an object module from which the object module derives"

Any idea on how to fix this one?

Cheers
Calvin

 

[dkinley]

Can't say I've ran into that one before.

Possibility of posting the db?

-dK

 

[CalvinToo]

Dk,

Sadly I can't post the DB (wish I could but the company I'm working for would be upset)

I'm going to go back to a back up copy and re-examine what I did...

My initial steps;

In MS VB I hit [insert] then [procedure] and then copied and pasted your coding in after deleting command71.

I have also gotten this compile error

"Label not defined" again after entering the coding as a procedure and changing the properties on the form.

I think I wasn't supposed to enter this procedure?

I missed a step somewhere or added one I just need to take another look at I think...

Cheers
Calvin

 

[CalvinToo]

DK,

Ok I think I found the error it was just in the text I keep forgetting that everything has to be perfect for these to work.

I was missing the _ in this line

Err_cmdGotoNextClick:

It needed to be

Err_cmdGotoNext_Click:

So now I can compile and test and it doesn't give any errors...but... there is still one issue.

When I open the MRR form and I hit assign MRR# (the button) it does advance to the last field for arguements sake lets say it is MRR# 003. So if I open the DB on report MRR#001 and hit the assign MRR# it will move in sequence to MRR#003 which is the last report to have information in it however it won't generate or "assign" MRR#-004 which would be the new report or the next one in sequence.

I can't seem to get it go past the last record to create a new one now.

On the bright side though there are no more error messages or anything like that it just moves to the next record and stops at the last one when you hit the assign button so I think we are close.

Thanks again
Calvin

 

[dkinley]

Not a problem ...

Also, I didn't ask - but are you using the MMR field as a primary key? If so, I'd recommend setting up another one that autonumbers, making your own could have devastating consequences later.

Name the button a little more meaningful ,,, we can take it from the top (no disrespect in the following directions, btw - just making sure we are on the same page).

- Draw a new button and cancel the wizard.
- Open up the properties of the button and on the 'Other' tab, call it something ... "cmdMMR".
- Click on the 'Evens' tab
- The top row is OnClick .. this row looks like it's a drop down hooya, look just past the down arrow and you will see a box with little ellipses in it. Click that.
- In the Choose Builder screen, click "Code Builder"
- MS VB screen will open and you will see ..

Private Sub cmdMMR_Click()
End Sub

-Paste the code in between those two statements and let's give that a go.

-dK

 

[CalvinToo]

Dk,

Thanks I'll give that a go. I don't have the MRR# as a primary I have another field as MRRID that is the autonumber primary to avoid any issues... I also forsaw some issues with using MRR# so I created a new index key.

We'll I was messing around I seem to have gotten some things to work out but I've removed the assign MRR# button altogether.

[On click] as an event procedure i've called "CMDgotoNext" I have this coding

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
ElseIf NumMRR = Me![MRR#] Then '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
Exit_cmdGotoNext_Click:
Exit Sub
Err_cmdGotoNext_Click:
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

Then on the properties [On Current]

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

What this does is remove the need for the assign MRR# button and forces the user to use the record button arrows on the bottom of the form - every time you hit the next record it assigns the next MRR# however I'm not sure this will work for all my co-workers and I think I'm going back to trying to get the assign MRR# to go to the last record and then assign a new record and new MRR# (hope that made sense)

Cheers
Calvin

 

[dkinley]

Yes it does ... I normally disable all the extra buttons and try to create the db with a real application look and feel because the odds that people using it have no idea and it creates some security of eliminating records created due to ignorance, et al.

More than one way to accomplish the goal at hand. If I have time, I will try and work up a demo and post it to close this out unless you have any more questions.

-dK

 

[CalvinToo]

DK,

Ok I've reinserted the code and now it works as intended with the exception of going to the next record with out hitting the [record] button arrow on the bottom once your at the last MRR that has information.

I think this is pretty good and I'm not sure how much more trouble it is worth to have someone hit the "assign MRR#" button and move to the next free record since they can just hit the next record button a few inches below the button to accomplish the same thing.

One thing I am interested in that I haven't figured out is that the message boxes don't pop up? Any ideas as to what setting I'm missing?

Cheers
Calvin

 

[dkinley]

Ready to hoist that big DUH! sign and carry it around? ... cause I am tired of carrying it ....

The problem is right here:

If NumMRR > 0

This will always be satisfied so the system will never check the rest of the conditions.

Anyhow .. fixed that, did some other, and attached the demo.

-dK

 

[CalvinToo]

Awesome thanks DK - I'll try it out again tomorrow when I'm back at work!

 

[dkinley]

No problem ... I left a little something for you to figure out depending on how you want it to alert the user at a certain juncture (the STOP! portion ... you will see). Anyhow, put in a couple of other ideas you may use or build on now that you can see a sample of the code.

Let me know if it doesn't go.

L8R
-dK

 

[CalvinToo]

Dk,

I can't get it to work with my database...no idea why I've tried changing everything that I can think of and depending on what change I make I get different errors.

I can't understand for the life of me why it doesn't work in mine by swapping out table names using the code you provided but it doesn't

The most common error I keep getting is the compile error

"Type-declaration character does match declared data type"

Here is a screen shot after I've changed to match my DB so you can see the line I'm fighting with...

(click to enlarge)



In your DB I can also overwrite the MRR# manually which is something I wouldn't want the front end user to be able to do ( I can change your MRR# to 200 and it will save it to the table but it won't allow me to change it a second time with out moving to a new record and going back or exiting and coming back to the form I've noticed)

Anyway I think I'm defeated on this one and ready to move on to the next issue I have with the DB. This one is working a lot better then it was and I thank you very much for getting me going on this one!

I'll start a new thread topic with my next problem which I discovered this AM in another table.. I sure hope you can give me some insight on that one as well.

Thanks again
Cheers
Calvin

 

[dkinley]

Several things ...

One, I should have pointed out a long time ago but caught up in the overall resolution I kept forgetting. You use # as the name of an object - bad practice since # is used for date type fields. I would suggest changing the table field name, etc, to use something like MRRNum or MRRNumber (not recommended: MRRNo because 'No' could be your standard of representing specific checkboxes).

Two, if that doesn't correct it then the error makes sense because I wasn't sure of the data type of the MRR Number. I didn't know if it was a number field (like the demo) concantenated with another string, or a string type field, etc.

The variable (iMMRNumber) is an integer and you can't do comparisons with integers to string. There are commands you can use, for instance CInt(Me!ControlName) to convert a string into an integer type (so you can do the comparison). It is not a permanent conversion but singularly for that purpose.

If you do a search in Access Help under 'CInt' it will list all of these conversions.

Last, there are typos through the code ;].

Hope that helps.

-dK

 

[CalvinToo]

Thanks DK!

Man I should have just started from scratch I think... this DB was built in 98 I believe and then update/ upgraded to Access 2001 and now its in 2003 no telling what was lost along the way with the db been upgraded with the new software that and I'm a very basic coder with a great understanding of what needs to happen vs the builder who was a good coder with no real understanding of what the DB required.

I'm still trying to decide if it is better to start over or just keep trying to correct this one... I have no idea which one will take longer and I'm guessing that fixing this one would be faster as it already has a great deal of work done to it...however somedays I do wonder.

Thanks for the tips and help greatly appreciated. I may call upon your expertise again soon with a Purchase order report that is causing me some grief...

Thanks again
Calvin

 

[dkinley]

No problem. Hope that I have contributed ....

Don't sweat the small stuff - you think logically and IMO is 85% - 95% by knowing what needs to happen and in what order - just not sure how to go about it. Coding is translation.

Good luck!
-dK

If a report is causing grief that usually is an indication of overall design ... ?