Creating 'Common' VBA code

[Nevsky78]

Hi all,

I have code attached to the BeforeUpdate event for each of 12 columns (which correspond to 12 months of the year).

I know this is possible with a custom function of some sort but would anyone know how I could get this 'built in' and not have to have 12 sub routines?

What they are saying is if there is any value in any of the other month columns, then you cannot enter an amount.

I have the examples for apJan, apFeb and apMar.

Private Sub apJan_BeforeUpdate(Cancel As Integer)    
    If Nz(Me.apAug, 0) + Nz(Me.apSep, 0) + Nz(Me.apOct, 0) + Nz(Me.apNov, 0) + Nz(Me.apDec, 0) + Nz(Me.apJul, 0) + Nz(Me.apFeb, 0) + Nz(Me.apMar, 0) + Nz(Me.apApr, 0) + Nz(Me.apMay, 0) + Nz(Me.apJun, 0) > 0 Then
    MsgBox "This record has an amount in another month"
    Me.apJan.Undo
    Cancel = True
    End If
    
End Sub


Private Sub apFeb_BeforeUpdate(Cancel As Integer)
    
    If Nz(Me.apAug, 0) + Nz(Me.apSep, 0) + Nz(Me.apOct, 0) + Nz(Me.apNov, 0) + Nz(Me.apDec, 0) + Nz(Me.apJan, 0) + Nz(Me.apJul, 0) + Nz(Me.apMar, 0) + Nz(Me.apApr, 0) + Nz(Me.apMay, 0) + Nz(Me.apJun, 0) > 0 Then
    MsgBox "This record has an amount in another month"
    Me.apFeb.Undo
    Cancel = True
    End If
    
End Sub


Private Sub apMar_BeforeUpdate(Cancel As Integer)
    
    If Nz(Me.apAug, 0) + Nz(Me.apSep, 0) + Nz(Me.apOct, 0) + Nz(Me.apNov, 0) + Nz(Me.apDec, 0) + Nz(Me.apJan, 0) + Nz(Me.apFeb, 0) + Nz(Me.apJul, 0) + Nz(Me.apApr, 0) + Nz(Me.apMay, 0) + Nz(Me.apJun, 0) > 0 Then
    MsgBox "This record has an amount in another month"
    Me.apMar.Undo
    Cancel = True
    End If
    
End Sub

Any help would be gratefully received!

Nick

[orange]

If you have a table with 12 columns representing months, I think you may have a table structure/normalization issue.
Please post a jpg of your tables and relationships.

You may want to review the first few topics at this site
http://www.rogersaccesslibrary.com/forum/topic238.html

[Nevsky78]

Hi orange,

Thank you for getting back to me.

I am aware that I have not set up the database in the best normalized structure. My issue has been I do not have advanced enough VBA to create a form with datasheet/continuous subform with 12 months horizontally and however many records vertically. I know that requires the use of a temp table based on a crosstab but no matter how much I have tried I haven't been able to set my database up like that.

The 'data entry' forms need to have 2 filter parameters and a datasheet/continuous subform by months.

I have therefore got round this by having 12 months columns in the 'base' table and then normalizing that table into the correct structure.

I've also corrected my code that I have pasted above. I have attached my DB.

If you have any advice it would be greatly received!

Thanks,
Nick

[Missinglinq]

...I know this is possible with a custom function of some sort but would anyone know how I could get this 'built in' and not have to have 12 sub routines?

If you want carry out some action (such as prohibiting data in 11 Controls if a 12th Control is populated) you're going to have to associate code with all 12 Controls! You can have 'common code,' i.e. a User Defined Function, but you'd still have to Call the Function from each Control!

Given that you only want Data entered for one month per Record, the logical thing, to my mind, would be to replace your 12 Fields with two Fields, one for the month and one for the amount. On the Form I'd use a Combobox for the month. Once an amount was entered for a given month, you could not enter an amount for a second month except by removing the amount from the original month. You could still do your summing for each month by using Sum() against the 'amount' Field with the 'month' Field being used in 'Where' clause.

Linq ;0)>

[Nevsky78]

Hi Linq,

Thanks for getting back to me with that.

The 2nd option would definitely be my ideal solution. However, I don't know how to set up the form (and any crosstabs etc.) so that it appears with Jan to Dec horizontally and the records vertically, dependent on the 2 filters - brand and category.

Would it be some kind of after update event to put the Month in the month field depending on which combobox you have entered data? i.e. cboJan, cboFeb...

Regards,
Nick