Return from MsgBox to Form Stalling

[EufJoe]

First, thanks in advance for any assistance given! I'm working in Microsoft Access 2007 on a data base developed by someone no longer with the company.

The database I'm working on is designed for parts to be entered with a specific SKU/Identification number and is running on seperate computers. The original developer set a starting point for the SKU number that works but its usage has grown beyond its original intentions. I was asked to add code into the form so a range could be set on each computer as to prevent duplicates when the data is compiled to a central computer. I entered the following code which sets up the range and functions but once the range is met you have to click the okay button 16 times to return to the form. Any help would be greatly appreciated.

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "SKU Data") >= 1 Then
Cancel = True
MsgBox "Only 1 record allowed!  Contact Kevin for assistance!"
End If
End Sub
 

[patjones]

I take it that you don't have a network drive on which to setup a single back end file?

Just by looking at your code, I can't really say why you need to click the OK button 16 times to get out of the dialog box. However, I can suggest a couple of things that will bring your code more in line with good practice and might perhaps fix your problem.

I would first use the BeforeUpdate event for the text box that is taking the SKU number in; second, I would write the DCount( ) call with more specifics in the arguments, and actually use the third argument which calls for criteria. So for instance, assuming a text box name of txtSKU, and a field name of fldSKU in the table:

Private Sub txtSKU_BeforeUpdate(Cancel As Integer)
 
If DCount("fldSKU", "SKU Data", "fldSKU = " & Me.txtSKU) > 0 Then
   Cancel = True
   MsgBox "Only one record per SKU allowed!"
End If
 
End Sub

What do you think? If I've misunderstood anything then let me know and we can refine the response.

Pat

[EufJoe]

Thanks Pat. The databases is being run on laptops at different locations and what they are looking for is a range of 1000 on each computer. I apologize as my having the code set to 1 probabply miss led you, (I was keeping a low number so not to have to key in but one record to test).

Thanks,
Joe

[Mihail]

Hi all !
Hope I understand OK your problem.
So, I think you can use the LOAD event of the form to check if there is a record. If YES then set the Allow Additions property to No.
Also you can design a label where you can inform the user that maximum numbers of records is reached.

[patjones]

@Mihail:

I'm not sure how the On Load event for the form is relevant here. If I'm understanding the OP's issue correctly, he wants to make sure that upon starting a new record entry, the user does not commit a duplicate SKU to the table. The form would already be loaded at that point.

Also, the Allow Additions property is relevant only in the context of a bound form. The OP has not indicated to us whether or not he is using a bound or unbound form.

EufJoe...can you let us know whether or not we're on the right track here, and in particular whether you've tried to implement the Before Update code in my previous post? Thanks.

[EufJoe]

@Mihail

Thanks for your assistance! I believe zepphead80 is correct and I've used part of his original suggestion and have it working now.

@zepphead80
I'm not sure why but for some reason when I call the code out by field names as you suggested it ran with no exceptions under "Before Update" but failed to hold the range. I just finished entering my original code under "Before Update" and it is working and holding the range without having to click "Okay" 16 times as I was having to earlier.

Thanks again for all your help and hopefully one day I will be able to return the favor,
Joe

[patjones]

Joe,

I'm glad that it's working better for you now. Can you clarify what you mean by 'hold the range'?

[EufJoe]

Pat,

Range may not have been my best choice of words, "limit" probably would have been a better choice. The "SKU" number field is currently populated with 30,000 entries and they are planning to run this database on multiple computers at random locations. What they are wanting is to set a 1,000 entry range or limit on each computer and then set the "SKU" number accordingly to prevent duplicates, (computer 1 - SKU set to begin at 30,000, computer 2 - SKU set to begin at 31,000 and so on) with each database holding a 1,000 entry limit as not to encroach on another users assigned "SKU" range or limit.

Thanks again,
Joe

[patjones]

OK, then there's a subtle point that we're uncovering here. The code that I gave you has nothing to do with range checking. It simply checks to see if the SKU that the user is inputting is already in the table. It's a duplicate checker, essentially.

For checking on the range, you could append my code like this, for example:

Private Sub txtSKU_BeforeUpdate(Cancel As Integer)
 
If Me.txtSKU < 30000 Or Me.txtSKU > 30999 Then
   Cancel = True
   MsgBox "SKU out of range!"
Else
   If DCount("fldSKU", "SKU Data", "fldSKU = " & Me.txtSKU) > 0 Then
      Cancel = True
      MsgBox "Only one record per SKU allowed!"
   End If
End If
 
End Sub

This requires you to set the range appropriately on each computer. One way to minimize the tedious nature of this task is to define the lower and upper limits as constants at the top of the module:

Public Const SKU_low = 30000
Public Const SKU_upper = 30999

Then you simply use the constant names everywhere in the code in place of the actual values. Just a suggestion!

Pat

[EufJoe]

Thanks Pat! I'll apply this and let you know the outcome.

I apologize if I failed to state this correctly, this is my first time posting to a site like this and was trying to keep it brief as per instructions.

Again, thanks for all your help,
Joe

[EufJoe]

@zepphead80

Pat, your code worked perfect and you taught me something new.

Thanks for all your help,
Joe

[patjones]

Joe it's no problem. I should also point out that you posted your question just fine as far as I'm concerned. It is not uncommon for issues to take ten or more posts to come to full resolution.

Enjoy your weekend...

Pat