Multiple subforms controled by Combo-list on Main form

[nande123]

Hi,

The main form in my ExemptionDB contains three subforms which are controled by a drop-down list called ExemptionType.

Each subform relate to individual tables ExemptionAccount, ExemptionFirewall and tblExemptionRemoteAccess. And each of the three tables have a one-to-one relationship (Type 1) with the exemption table and the ExemptionID.

Only one subform are visible at a time and it is controled by a drop-down list on the mainform (see below.

Now my problem is I want a subform to be deleted if you decide to change value in the drop-down list i.e. there should only be information in one subform per ExemptionID.

How would I best achive this?

----------------------------------------------------------
Sub ShowSubform()

'Save unsaved changes to currently open subform
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Display appropriate subform based on ExemptionTypeID chosen
If Me.ExemptionType.Value = "Account" Then

Me.subfrmExemptionAccount.Visible = True
Me.subfrmExemptionFirewall.Visible = False
Me.subfrmExemptionRemoteAccess.Visible = False

ElseIf Me.ExemptionType.Value = "Firewall" Then

Me.subfrmExemptionFirewall.Visible = True
Me.subfrmExemptionAccount.Visible = False
Me.subfrmExemptionRemoteAccess.Visible = False

ElseIf Me.ExemptionType.Value = "Remote Access" Then

Me.subfrmExemptionRemoteAccess.Visible = True
Me.subfrmExemptionFirewall.Visible = False
Me.subfrmExemptionAccount.Visible = False

Else
Me.subfrmExemptionAccount.Visible = False
Me.subfrmExemptionFirewall.Visible = False
Me.subfrmExemptionRemoteAccess.Visible = False


End If

--------------------------------------------------


Thanks in advance!!!

Regards,

 

[MajP]

This looks like it would work, but could be written much simpler
[/code]
Sub ShowSubform()
dim eType as string

'Save unsaved changes to currently open subform
me.dirty = false
eType = nz(me.exemptionType.value,"")

'Display appropriate subform based on ExemptionTypeID chosen
Me.subfrmExemptionAccount.Visible = (etype = "Account")
Me.subfrmExemptionFirewall.Visible = (etype = "Firewall")
Me.subfrmExemptionRemoteAccess.Visible = (eType = "Remote Access")
end sub
[/code]

 

[nande123]

Thanks,

Would this code only replace fuctions equal to my current code or will it also take care of the problem with deleting text in subform if the value is changed?

Regards,

 

[MajP]

The delete part was not clear. You said
" I want a subform to be deleted"
so I just assumed you meant to hide the subform.
This really suggests to me that your data is not normalized, and you are making this too hard. So before providing a solution, can you explain the tables a little? My first guess that all these records should be in the same table with some Type field "firewall", "account", "Remote Access".

If it makes sense to keep them in in seperate tables, then we can propose a delete query. However, if normalizing is the correct thing to do, it will make things a lot easier.

 

[nande123]

This is a whole new workd to me so thanks for taking your time with me!

This is a database for manage security exemptions and there are different type of exemptions which require different type of information, there is also some information that is requireed for all types of exemptions.

Common information is stored in the Exemption table and information that is specific to a certain type of exemption is stored in each of the three tables.

To give you an example.

Requestor, Approver, Request Date, Approve Date is required for all types of exemptions and then you have more specific information depending on what type of exemption it is. If it is related to a "Firewall" exemption then you have to type in firewall name, firewall rule id, and this information is stored in the FirewallExemption table.

Should all these four tables me merged into one?

 

[MajP]

No probably not. The key was that you said the different type of exemptions have different properties. So your design probably makes sense.

 

[MajP]

So an exemption can be of only one type "Account", "Firewall","Remote Access", But it can change states? In other words you cannot have an "Account", and "Firewall" record relate back to the Exemption Table? This seems a little strange, but if these are your buisness rules then it is correct.

I assume the combo is bound to a field in the ExemptionTable, that tells what type of exemption. What is that field?

Here is a general idea.

Private Sub cmboExemptionType_AfterUpdate()
  Dim oldType As String
  Dim newType As String
  Dim id As Long
  Dim strWhere As String
  Dim strsql As String
  Me.Dirty = False
  id = Me.ExemptionID
  newType = cmboExemptionType.Value
  oldType = cmboExemptionType.OldValue
  
  strWhere = "ExemptionID = " & id
  
  Select Case oldType
    Case "Account"
      strsql = "Delete * from tblAccountExemption where " & strWhere
    Case "Firewall"
      strsql = "Delete * from tblFirewallExemption where " & strWhere
    Case "Remote Access"
      strsql = "Delete * from tblRemoteAccessExemption where " & strWhere
    Case Else
      Exit Sub
 End Select
    CurrentDb.Execute strsql
  'However do you want to create a record in the new table?
  'You can do an insert query to create a new record based on the newtype
End Sub

 

[nande123]

Yes exemption can only be of one kind and it is decided based on a combobox called ExemptionTypeID and it is a separate table ExemptionType. The type shouldn't change but I want to prevent someone from creating Account, Firewall and Remote Access exemptions linked to the same Exemption ID as it could mess up reporting etc.


I tried this code

--------------------------------
Private Sub ExemptionTypeID_AfterUpdate()

Dim oldType As String
Dim newType As String
Dim id As Long
Dim strWhere As String
Dim strsql As String
Me.Dirty = False
id = Me.ExemptionID
newType = cmboExemptionType.Value
oldType = cmboExemptionType.OldValue

strWhere = "ExemptionID = " & id

Select Case oldType
Case "Account"
strsql = "Delete * from AccountExemption where " & strWhere
Case "Firewall"
strsql = "Delete * from FirewallExemption where " & strWhere
Case "Remote Access"
strsql = "Delete * from tblRemoteAccessExemption where " & strWhere
Case Else
Exit Sub
End Select
CurrentDb.Execute strsql

'Call subroutine to display appropriate subform based on template type
ShowSubform

End Sub

--------------------------------

Bur get this error:

Run-time error '424'
Object required

 

[MajP]

As I stated this was a general idea. I do not know a lot of information such as field and control names. Not sure of your level of coding, but this was to point you in a general direction. However, I am likely not using the same names as you so check the names for controls, tables, and fields.

 

[nande123]

Made a few changes to the code and now I get a different error.

Compile error:
Method or data member not found



New code
------------------------------
Private Sub cmbExemptionType_AfterUpdate()

Dim oldType As String
Dim newType As String
Dim id As Long
Dim strWhere As String
Dim strsql As String
Me.Dirty = False
id = Me.ExemptionID
newType = ExemptionType.Value
oldType = ExemptionType.OldValue

strWhere = "ExemptionID = " & id

'Call subroutine to display appropriate subform based on template type
ShowSubform

Select Case oldType
Case "Account"
strsql = "Delete * from ExemptionAccount where " & strWhere
Case "Firewall"
strsql = "Delete * from ExemptionFirewall where " & strWhere
Case "Remote Access"
strsql = "Delete * from tblExemptionRemoteAccess where " & strWhere
Case Else
Exit Sub
End Select
CurrentDb.Execute strsql



End Sub
---------------------------------

 

[MajP]

Can you show the highlighted line?

 

[nande123]

.OldValue is highlighted in the "oldType = ExemptionType.OldValue" line.

 

[nande123]

There is a one to many relationship between Exemption (many) and ExemptionType (one) and both tables are linked by a ExemptionTypeID.

The actual value is stored in the ExemptionType field (column two in ExemtionType table).

Could this problem be related to that the control source of the combobox is linked to ExemptionTypeID column 1 (column count 2, column width 0cm, 2,5cm)?

Have attached a copy of my DB.

 

[MajP]

I did not get a chance to look at it, but I think I would change the original logic. Instead of checking to see if there was an old value, just delete from both remaining tables. If there is no record then the delete does nothing.

Private Sub cmbExemptionType_AfterUpdate()

  Dim newType As String
  Dim id As Long
  Dim strWhere As String
  Dim strsql As String
  Me.Dirty = False
  id = Me.ExemptionID
  newType = ExemptionType.Value
  strWhere = "ExemptionID = " & id
  'For test purposes to see if you are getting the correct
  'values. What do these say?
  msgbox "ID = " & id
  msgbox "NewType = " & newType
  msgbox "StrWhere = " & strWhere 
    'Call subroutine to display appropriate subform based on template type
    ShowSubform
  
  Select Case NewType
    Case "Account"
      strsql = "Delete * from tblExemptionFirewall where " & strWhere
      CurrentDb.Execute strsql
      strsql = "Delete * from tblExemptionRemoteAccess where " & strWhere
      CurrentDb.Execute strsql
    Case "Firewall"
      strsql = "Delete * from tblExemptionAccount where " & strWhere
      CurrentDb.Execute strsql
      strsql = "Delete * from tblExemptionRemoteAccess where " & strWhere
      CurrentDb.Execute strsql
    Case "Remote Access"
      strsql = "Delete * from tblExemptionAccount where " & strWhere
      CurrentDb.Execute strsql
      strsql = "Delete * from tblExemptionFirewall where " & strWhere
      CurrentDb.Execute strsql
    Case Else
      Exit Sub
 End Select
end sub

 

[nande123]

I changed it and now I get a different error message.

"Run-time error '3061'
Too few parameters. Expected 1."

And it stops at "CurrentDb.Execute strsql" under Account Case.

Msg Value was:
ID = 4
NewType = Account
strwhere = Exemption ID = 4

 

[PHV]

Replace this:
strWhere = "Exemption ID = " & id
with this:
strWhere = "[Exemption ID] = " & id

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

 

[MajP]

Do yourself a big favor, and never put any spaces in table, field, form, report, etc names. It just leads to problems. Also do not use common access terms (reserved words) for your names.
Examples:
Do not name something
field
table
name
sum
count
date
now

So if you are a farmer do not track your plowed fields with a field named fields or if you build furniture do not make a table called tables to track information on tables. I have seen both done. The most common are fields called Name and Date.

 

[nande123]

Hi,

The field is/was called ExemptionID (without space). Have also chanegd name on table ExemptionAccount and ExemptionFirewall to tblExemption...... to make it more concistent.

This is how the code looks now:

Private Sub cmbExemptionType_AfterUpdate()

Dim newType As String
Dim id As Long
Dim strWhere As String
Dim strsql As String
Me.Dirty = False
id = Me.ExemptionID
newType = ExemptionType.Value
strWhere = "[ExemptionID] = " & id
'For test purposes to see if you are getting the correct
'values. What do these say?
MsgBox "ID = " & id
MsgBox "NewType = " & newType
MsgBox "StrWhere = " & strWhere
'Call subroutine to display appropriate subform based on template type
ShowSubform

Select Case newType
Case "Account"
strsql = "Delete * from tblExemptionFirewall where " & strWhere
CurrentDb.Execute strsql
strsql = "Delete * from tblExemptionRemoteAccess where " & strWhere
CurrentDb.Execute strsql
Case "Firewall"
strsql = "Delete * from tblExemptionAccount where " & strWhere
CurrentDb.Execute strsql
strsql = "Delete * from tblExemptionRemoteAccess where " & strWhere
CurrentDb.Execute strsql
Case "Remote Access"
strsql = "Delete * from tblExemptionAccount where " & strWhere
CurrentDb.Execute strsql
strsql = "Delete * from tblExemptionFirewall where " & strWhere
CurrentDb.Execute strsql
Case Else
Exit Sub
End Select
End Sub


However I still get the same error and it breaks at this line "CurrentDb.Execute strsql".

 

[nande123]

Just though of this,

PK in tblExemption is called ExemptionID and FK in the other tables are called [tblExemptionAccount].[ExemptionAccountID], [tblExemptionFirewall].[ExemptionFirewallID] and [tblRemoteAccess].[ExemptionRemoteAccessID]. For your code to work does all fields have to be named the same as the PK in tblExemption (ExemptionID)?

 

[nande123]

YES that solved it!!

I changed the PK in all four tables to the same name ExemptionID and now it delete rows if I change to a new value.

BIG THANKS TO YOU ALL!!!