Combo box/form not requerying when value " "

[murphy123]

I'm not a pro with Access VBA. Any help is greatly appreciated.
I have one Unbound Audit main form with 55 controls in it. Under the Change Event I have one combo box named(CSC_Audit_type) that has 6 different values ("", Cash, Deduction, Credit, Journaling and CFR), depending on the value selected, it only brings the controls corresponding to that value, which is working fine. However, when the form is open all 55 controls appear jammed on top of each other. I have code for each true/false selection to appear depending on the combo selection. About 10 out of the 55 controls are repeated in every Audit Type, and they could be displayed in all selections. I also have a button on the form that is running an append query, generating an audit#, and re-opening the form. But what I need is for the form when opened just display the main 10 controls or when it's "", or nothing selected. How can I accomplish that? I've tried so many things, and nothing is working.

Part of my display statements:

Private Sub CSC_Audit_Type_Change()

If Me.CSC_Audit_Type = "Cash" Then
Me.UserID.Visible = True
Me.CSC_Name.Visible = True
Me.FM_LAN_ID.Visible = True
Me.Financial_Manager.Visible = True
Me.Date_of_Review.Visible = True
Me.Period_Covered_Start.Visible = True
Me.Period_Covered_End.Visible = True
Me.Payer_No.Visible = True
Me.Payer_Name.Visible = True
Me.Clearing_Doc_No.Visible = True
Me.Check_No.Visible = True
Me.Check_Date.Visible = True
Me.Check_Amount.Visible = True
Me.Worksheet_Attached.Visible = True
Me.Variances.Visible = True
Me.Vistex_Header_Adj_Correct.Visible = True
Me.Other.Visible = True
Me.Cash_Comments.Visible = True

'' *** CASH FALSE STATEMENTS ****
Me.Credit_Audit_Type.Visible = False
Me.Document_No.Visible = False
Me.Amount.Visible = False
.
.
.
.
else
.
.
end if
.
.
end sub


My code for the Submit audit button:

''' ***********
''Add Audit
Private Sub Submit_Click()
On Error GoTo Err_Submit_Click

Dim cdDatabase As ADODB.Connection
Set cdDatabase = CurrentProject.Connection
Dim rsNewAutoIncrement As ADODB.Recordset
Dim stDocName As String
Dim strsql As String
Dim NewAuditNo As String
Dim stFolder As String

If IsNull(Me.CSC_Audit_Type) Or Me.CSC_Audit_Type = "" Then
Me.CSC_Audit_Type = 1
End If
DoCmd.SetWarnings False
stDocName = "Append_Main_Audit"
DoCmd.OpenQuery stDocName, acNormal, acEdit
strsql = "Select max(AuditID)as maxAudit from tbl_Main_Audit "
Set rsNewAutoIncrement = New ADODB.Recordset
rsNewAutoIncrement.Open strsql, cdDatabase, adOpenForwardOnly, adLockReadOnly, adCmdText
MsgBox ("Audit# " & rsNewAutoIncrement(0).Value & " has been created.")
NewAuditNo = rsNewAutoIncrement(0).Value
rsNewAutoIncrement.Close

Set rsNewAutoIncrement = Nothing

Me.AuditNum = NewAuditNo

DoCmd.SetWarnings True
DoCmd.Close

stDocName = "Audit_Form"
DoCmd.OpenForm stDocName, acNormal, acEdit


Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click

End Sub

-----
What can I possibly add to the code to make the form load properly, only showing the common 10 controls and not display a bunch of fields on top of each other?


Thank you.

 

[murphy123]

I attached a link to a copy of the database.

http://www.mediafire.com/?dkfgt6vv106e8eh

 

[murphy123]

Ok. After reading more postings I tried combo box code to say isNull rather than ="" and I placed the code under the Current event procedure of the Form, and it's working now.

Private Sub Form_Current()

'**NULL TRUE STATEMENTS
If IsNull(Me.CSC_Audit_Type) Then
Me.UserID.Visible = True
Me.CSC_Name.Visible = True
Me.FM_LAN_ID.Visible = True
Me.Financial_Manager.Visible = True
Me.Date_of_Review.Visible = True
Me.Period_Covered_Start.Visible = True
Me.Period_Covered_End.Visible = True
Me.Payer_No.Visible = True
Me.Payer_Name.Visible = True

''**NULL FALSE STATEMENTS **
Me.Clearing_Doc_No.Visible = False
Me.Check_No.Visible = False
Me.Check_Date.Visible = FalseEnd Sub
.
.
.
End sub
Thanks anyway.

 

[PHV]

I'd replace this:
If IsNull(Me.CSC_Audit_Type) Then
with this:
If Trim(Me!CSC_Audit_Type & "") = "" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886