Command button enabled or visible only when creating a new record. How?

[bfollowell]

I have a db that I've developed to help members of my maintenance department create major breakdown reports. We have a numbering system for these major breakdowns that is made up by XX-YY-999, where xx is a two character system code, YY is the two digit year and 999 is the 3 digit number of the corresponding MBR for that system for that year. I've developed a button that will create these MBR numbers based on the occurrence date that is entered in a date field and the system selected from a combo box. The VBA uses these year and selected system to create the next corresponding MBR number for that system/year. I played on my own and looked at a lot of references from these forums to get this working and it all seems to be working well.

Now on to my last roadblock. I want this button to only be enabled when creating a new record. Preferably it wouldn't even be visible until creating a new record. That way, no one could be looking at an older MBR record and inadvertently create a new number and overwrite the old one. I'm having trouble figuring out how to enable this button or make it visible only when creating this record. I know about the Me![cmdMBRNum].Enabled=False thing so the only thing I need to do is set the button to Me![cmdMBRNum].Enabled=True when creating a new record but I don't know how to trigger it or how to have Enabled=False by default until I trigger it.

Any ideas or is there just an easier way to do this altogether or have I just lost everyone? Thanks in advance.

Sincerely,
- Byron Followell

 

[boblarson]

In the Form's On Dirty event place

Me.cmdMBRNum.Visible = True

You don't need the bang operator (since we're talking about a control and not recordset object) and brackets (since the name has no spaces).

 

[bfollowell]

Bob,

Thanks for the reply. I see what you're saying. I know how to make visible true or false or enable true or false but I need a little more. How to I make visible true or enable true ONLY when creating a new record? I wasn't planning on recreating command buttons for creating a new record. I was planning to just use the built-in form buttons at the bottom of the screen. I guess I could disable the built-in button, create my own command button to do the same thing and then add the NewMBRNum button visible option to its code but it seems like there should be an easier way without doing all of that. Am I making sense? Hopefully I'm not coming across like a babbling idiot. Thanks again for the quick response.

Sincerely,
- Byron Followell

 

[boblarson]

Like I said, you put it in the ON Dirty event of the form. The On Dirty event of the form fires when a new record is started or changes occur within an existing record. You would likely have to place the same code, but with .Visible=False in the ON Current event of the form so that it would go back to being invisible when it moves to the next record.

 

[bfollowell]

Oh, OK, I think I've got you now. I'll have to play some more. Always learning, always learning. Thanks so much for your help Bob.

Sincerely,
- Byron

 

[bfollowell]

Bob,

OK, the OnDirty event didn't do anything for me. I messed around with the other events though and found that the BeforeInsert event worked well for me. My button is invisible until a new record is created and someone starts to enter data, preferrably an occurrence date. Then the Create New MBR button appears. It disappears after creating and saving a new record. That part all works great thanks to your help.

Now my problem lies in the code behind the Create New MBR button. I'll attach my code below. The code works if there is an existing MBR for the selected system and year. However, if there isn't one, as in you're entering the first on for that year, the query that my DMax command is based on pulls back nothing. I had developed an If/Then/Else statement I thought would take care a null situation but I keep getting the following error. Compile Error: Type mismatch.

If I remove the null checking and just have the count increase by one and there isn't an existing MBR, I receive the following error. Run-time error '94': Invalid use of Null.

I have everything working just like I want it with the exception of the null situation. Any assistance you or anyone else might be able to provide would be greatly appreciated. I'll attach my code with the null code and without. Thanks in advance to anyone that might be able to help.

Sincerely,
- Byron Followell


-=-=- Code with Null catching code -=-=-


Public Function NewMBRNum() As String

On Error GoTo NewMBRNum_Err

Dim curMAX As Integer
Dim NewMAX As Integer
Dim NumTEXT As String
Dim NewMBR As String

'Find higest MBR for selected year/system using qryMBRNums query and add 1
curMAX = DMax("[NumVal]", "qryMBRNums", "[Sys] = '" & Me.cmbSysCode & "' and [Yr] = " & Right(Year(Me.DateOccur), 2))

If curMAX Is Null Then
NewMAX = 1
Else
NewMAX = curMAX + 1
End If

'Setup leading zeros and build numeric part of MBR Number
If NewMAX < 10 Then
NumTEXT = "00" & NewMAX
ElseIf NewMAX < 100 Then
NumTEXT = "0" & NewMAX
Else
NumTEXT = NewMAX
End If

'Build new MBR Number with System Code and Year
NewMBR = Me.cmbSysCode & "-" & Right(Year(Me.DateOccur), 2) & "-" & NumTEXT

'Assign function the value of the NewMBR
NewMBRNum = NewMBR

'Exit function now after successful incrementing or after error message
Exit_NewMBRNum:
Exit Function

'If an error occurred, display a message, then go to Exit statement
NewMBRNum_Err:
MsgBox "Error " & Err & ": " & Error$

Resume Exit_NewMBRNum

End Function





-=-=- Code without Null catching code -=-=-


Public Function NewMBRNum() As String

On Error GoTo NewMBRNum_Err

Dim curMAX As Integer
Dim NewMAX As Integer
Dim NumTEXT As String
Dim NewMBR As String

'Find higest MBR for selected year/system using qryMBRNums query and add 1
curMAX = DMax("[NumVal]", "qryMBRNums", "[Sys] = '" & Me.cmbSysCode & "' and [Yr] = " & Right(Year(Me.DateOccur), 2))

NewMAX = curMAX + 1

'Setup leading zeros and build numeric part of MBR Number
If NewMAX < 10 Then
NumTEXT = "00" & NewMAX
ElseIf NewMAX < 100 Then
NumTEXT = "0" & NewMAX
Else
NumTEXT = NewMAX
End If

'Build new MBR Number with System Code and Year
NewMBR = Me.cmbSysCode & "-" & Right(Year(Me.DateOccur), 2) & "-" & NumTEXT

'Assign function the value of the NewMBR
NewMBRNum = NewMBR

'Exit function now after successful incrementing or after error message
Exit_NewMBRNum:
Exit Function

'If an error occurred, display a message, then go to Exit statement
NewMBRNum_Err:
MsgBox "Error " & Err & ": " & Error$

Resume Exit_NewMBRNum

End Function

 

[bfollowell]

Everyone please disregard. I changed my whole incrementing/error trapping code into one like the code below and everything works perfectly now. I'm still not 100% sure why this works and some of my other attempts didn't but I really don't care at this point. Everything is working perfectly now. Thanks to everyone, especially you Bob who helped me on this directly or by helping someone else in one of the many similar posts I read and learned from. Thanks guys, you and this forum are the best.

Sincerely,
- Byron Followell


'Find higest MBR for selected year/system using qryMBRNums query and add 1
If IsNull(DMax("[NumVal]", "qryMBRNums", "[Sys] = '" & Me.cmbSysCode & "' and [Yr] = " & Right(Year(Me.DateOccur), 2))) Then
NewMAX=1
Else NewMAX=Val(DMax("[NumVal]", "qryMBRNums", "[Sys] = '" & Me.cmbSysCode & "' and [Yr] = " & Right(Year(Me.DateOccur), 2)))+1
End If