I have an MS Access 2003 problem I’m hoping someone can help me with.
Currently I have a report that has a button you hit to assign the next number and it works fine for me the problem is that I’m supposed to issue the database to others and it is not idiot proof.
The button you hit is an event procedure which goes into a command which tells it to generate the next number in the table with a button click. It is not a primary key field and it doesn’t auto populate as I couldn’t get it to work with auto populate.
The issue is this;
I can make a report and have it numbered #001 and then manually click on the next report using the record arrows on the bottom of the form. If I click to the next record and then hit “assign report” it will generate report # 002 with no issue. However if I stay on record #001 and hit “assign report” it will make report #001 into #002 over writing the report number and leaving the rest of the report information untouched. Additionally if you close the form and open it you can then hit the “assign report” button again and turn the report #002 into report #003 and this can be repeated as many times as you can close the report and “accidently” assign a new report number! So it is possible to have 2 reports and instead of having them showing as Report-001 and Report-002 it could be Report-003 and Report-007 depending on how many times they "accidently" hit the assign new MRR# button which I really hope I can fix!
(I can manually go back into the table and fix this myself with no issue but the report is going to people that are only going to be using the switchboard and won’t have access to the tables or likely have the know how to do this.)
This is the current "command" when you click the assign new # button
Quote:
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
What I need is to have somehow is to make it not generate the next MRR # if there are records etc... I went back to the parent table and I was hoping I could Assign the MRR# as the primary and disallow duplicates but there is already a MRRID field as the primary and if I change it I will end up with a much larger problem as all the relationships through out the DB will be lost (and its a big DB with a ton of tables and reports).
It does scan the Database and assign the next highest number and that part of the command works fine the issue is that is doesn't force the user to be on the corresponding "record" so what I want is to force the front end user to create the same # of MRR as the record # he is on. So for example if they were on Record number 9 they should hit "assign MRR#" and it should show "MRR-009".
The issue we have now is that it will always assign the next number from the MRR# table regardless of the record your on.
Here is the example.
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 if there were already 5 fields in the MRR Table.
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.
Thanks in advance to any that reply
Currently I have a report that has a button you hit to assign the next number and it works fine for me the problem is that I’m supposed to issue the database to others and it is not idiot proof.
The button you hit is an event procedure which goes into a command which tells it to generate the next number in the table with a button click. It is not a primary key field and it doesn’t auto populate as I couldn’t get it to work with auto populate.
The issue is this;
I can make a report and have it numbered #001 and then manually click on the next report using the record arrows on the bottom of the form. If I click to the next record and then hit “assign report” it will generate report # 002 with no issue. However if I stay on record #001 and hit “assign report” it will make report #001 into #002 over writing the report number and leaving the rest of the report information untouched. Additionally if you close the form and open it you can then hit the “assign report” button again and turn the report #002 into report #003 and this can be repeated as many times as you can close the report and “accidently” assign a new report number! So it is possible to have 2 reports and instead of having them showing as Report-001 and Report-002 it could be Report-003 and Report-007 depending on how many times they "accidently" hit the assign new MRR# button which I really hope I can fix!
(I can manually go back into the table and fix this myself with no issue but the report is going to people that are only going to be using the switchboard and won’t have access to the tables or likely have the know how to do this.)
This is the current "command" when you click the assign new # button
Quote:
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
What I need is to have somehow is to make it not generate the next MRR # if there are records etc... I went back to the parent table and I was hoping I could Assign the MRR# as the primary and disallow duplicates but there is already a MRRID field as the primary and if I change it I will end up with a much larger problem as all the relationships through out the DB will be lost (and its a big DB with a ton of tables and reports).
It does scan the Database and assign the next highest number and that part of the command works fine the issue is that is doesn't force the user to be on the corresponding "record" so what I want is to force the front end user to create the same # of MRR as the record # he is on. So for example if they were on Record number 9 they should hit "assign MRR#" and it should show "MRR-009".
The issue we have now is that it will always assign the next number from the MRR# table regardless of the record your on.
Here is the example.
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 if there were already 5 fields in the MRR Table.
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.
Thanks in advance to any that reply