By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Need help with Subforms

P: n/a
Greetings. Before I begin, I have been stuck on this problem for about a
5 days, now. I have tried and just seem to be not getting anywhere. I
know that the explanation is lengthy, but I am a relative newcomer to
Access and need to be methodical until it become more familiar. Thanks
in advance for your help.

I have a form with a subform that has a subform.

form: frmEvents
subform: sfrmTrialInfo (controlname = [subfrmTI])
subform in the subform: sfrmTrialClass (controlname = [subfrmTCI])

The table structure is:
tblEvents
tblTrials (multiple trials for every Event)
tblTrialClass (multiple classes for every Trial)

I get to the frmEvents form from a form with unbound controls to setup
the criteria for the display. That form is frmDataEntry.

In frmDataEntry, I have a Combobox to select an Event for display and
two command buttons; one to open the selected event in "browse" mode to
view the Event-Trials-Classes and another to open a blank frmEvents to
create a new Event-Trials-Classes

In frmEvents there is a command button that is enabled while in "Browse"
mode that will cause frmEvents to goto a New Record and should look like
the "New" button was pressed from frmDataEntry.

Problem: The two "NEW RECORD" situations don't look the same.
1. In the "NEW RECORD" from frmDataEntry, [subfrmTI] has ENABLED=FALSE
so it's label is shaded (that's what I want), but [subfrmTCI] has
ENABLED=FALSE but it's label is not shaded. Why? How can I fix this?
2. In the "NEW RECORD" from frmEvents, [subfrmTCI] has ENABLED=FALSE and
it's controls are not displayed (even though they are displayed in the
other "NEW RECORD' scenario)? WHY? How can I fix this?

I can send a word document with the screen shots. That would make the
explanation much shorter.
Here's some of the subroutines that are behind the forms:

[color:red] frmDataEntry:
[color:blue] Private Sub btnNewEvent_Click()
On Error GoTo Err_btnNewEvent_Click

Call svContext("", strMode, 0, "")
DoCmd.Close
DoCmd.OpenForm "frmEvents", acNormal, , , acFormAdd, , "ADD"

Exit_btnNewEvent_Click:
Exit Sub

Err_btnNewEvent_Click:
Call ShowError("frmDataEntry", "btnNewEvent_Click", Err.Number,
Err.Description)
Resume Exit_btnNewEvent_Click

End Sub

[color:red] frmEvents:
[color:blue] Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim intCount As Integer
Dim db As DAO.Database
Dim rsT As DAO.Recordset
Dim strSQL As String

blnDoUpdate = False
blnDataChanged = False

'==============< BROWSE Mode >====================
If strMode = "BROWSE" Or strMode = "UNKNOWN" Then
' Disable Updates to current form
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
Me!btnSave.Enabled = False
Me!btnUndo.Enabled = False

'Find out if there are any records in tblTrials for this Event
intCount = DCount("[trialID]", _
"tblTrials", _
"[eventID] = " & Me!eventID)

' If we have records in tblTrials to display for this Event then
Disable Updates
If intCount > 0 Then
' Disable Updates to subform
Forms(Me.Name)![subfrmTI].Form.AllowAdditions = False
Forms(Me.Name)![subfrmTI].Form.AllowEdits = False
Forms(Me.Name)![subfrmTI].Form.AllowDeletions = False
Else
' Display some sort of message that says that there are no
Trials for this Event
Me![lblNoTrialRecs].Visible = True
Me![subfrmTI].Visible = False
End If

'Find out if there are any records in tblTrialClass to display
for this Event
'by first finding all the Trial Records for this Event
'Then for each Trial Record see if there are any TrialClass
Records
'If there are ANY TrialClass Records then issue the Disable
commands
If intCount > 0 Then 'If we have tblTrials records
(intCount > 0)
intCount = 0 'Reset count field
Set db = CurrentDb
strSQL = "SELECT * FROM tblTrials " & _
"WHERE [eventID] = " & Me!eventID
Set rsT = db.OpenRecordset(strSQL)

If rsT.EOF Then
MsgBox "Serious Error attempting to OpenRecordset in " &
_
vbCrLf & _
"Module: " & "frmEvents" & vbCrLf & _
"Procedure: " & "Form_Load( )" & vbCrLf & _
"rs.EOF = 0 but intCount = " & intCount,
vbExclamation + vbOKOnly, _
"SERIOUS ERROR"
GoTo Exit_Form_Load
End If

rsT.MoveFirst
Do Until rsT.EOF
intCount = DCount("[trialclassID]", _
"tblTrialClass", _
"[trialID] = " & _
rsT![trialID])
If intCount > 0 Then Exit Do
rsT.MoveNext
Loop

If intCount > 0 Then 'We have some TrialClass
records
' Disable Updates to nested subform

Forms(Me.Name)![subfrmTI]![subfrmTCI].Form.AllowAdditions = False
Forms(Me.Name)![subfrmTI]![subfrmTCI].Form.AllowEdits =
False

Forms(Me.Name)![subfrmTI]![subfrmTCI].Form.AllowDeletions = False
Else
' Display a message that says that there are no Classes
for any Trials
End If

rsT.Close
Set rsT = Nothing
Set db = Nothing

End If ' End of Trial Records

End If ' End of BROWSE Mode

'==============< ADD Mode >====================
If strMode = "ADD" Then
' Enable Updates to current form
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True

Me![btnAddEvent].Enabled = False
Me![btnEdit].Enabled = False
Me![btnDelete].Enabled = False
Me![btnUndo].Enabled = True
Me![btnSave].Enabled = True
Me!subfrmTI.Enabled = False

Me!lblNoTrialRecs.Visible = True
Forms(Me.Name)![subfrmTI].Form![lblNoClassRecs].Visible = True
Forms(Me.Name)![subfrmTI]![subfrmTCI].Form.Visible = True
'Forms(Me.Name)![subfrmTI]![subfrmTCI].Form.Enabled = False

End If

Me!lblWindowStatus.Caption = strMode & " Mode"

Exit_Form_Load:
Exit Sub

Err_Form_Load:
Call ShowError("frmEvents", "Form_Load", Err.Number,
Err.Description)
Resume Exit_Form_Load

End Sub

[color:red] frmEvents:

[color:blue] Private Sub btnAddEvent_Click()
On Error GoTo Err_btnAddEvent_Click

strMode = "ADD"

If blnDataChanged = True Then
Call DataHasChanged
End If

blnDoUpdate = False
blnDataChanged = False

Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True
Forms(Me.Name)![subfrmTI].Form.AllowAdditions = True
Forms(Me.Name)![subfrmTI].Form.AllowEdits = True
Forms(Me.Name)![subfrmTI].Form.AllowDeletions = True
Me![subfrmTI].Visible = True
'Me!subfrmTI.Enabled = False
Me!lblNoTrialRecs.Visible = True

DoCmd.GoToRecord , , acNewRec

Me!lblWindowStatus.Caption = strMode & " Mode"
Me!eventname.SetFocus
Me!btnAddEvent.Enabled = False
Me!btnDelete.Enabled = False
Me!btnEdit.Enabled = False
Me!btnSave.Enabled = True
Me!btnUndo.Enabled = True
Me![subfrmTI].Enabled = False

Exit_btnAddEvent_Click:
Exit Sub

Err_btnAddEvent_Click:
Call ShowError("frmEvents", "btnAddEvent_Click", Err.Number,
Err.Description)
Resume Exit_btnAddEvent_Click

End Sub
[color:red] frmTrialInfo:

[color:blue] Private Sub Form_Load()
'Find out if we have any Trail Records
If Me.RecordsetClone.EOF Then
blnYesRecords = False
Else
blnYesRecords = True
End If
End Sub


[color:red] frmTrialInfo:

[color:blue] Private Sub Form_Current()
On Error GoTo Err_Form_Current

Dim intCount As Integer
Dim db As DAO.Database
Dim rsT As DAO.Recordset
Dim strSQL As String
If blnYesRecords Then 'If there are TRIAL RECORDS then do the
following
If strMode = "BROWSE" Or strMode = "UNKNOWN" Then
'Find out if there are any records in tblTrialClass for this
Trial
intCount = DCount("[trialclassID]", _
"tblTrialClass", _
"[trialID] = " & Me!trialID)
If intCount = 0 Then
' Display message that says there are no Classes for
this Trial
Me![lblNoClassRecs].Visible = True
Me![subfrmTCI].Visible = False
Else
Me![lblNoClassRecs].Visible = False
Me![subfrmTCI].Visible = True
End If
End If ' End of BROWSE/UNKNOWN Mode

Me![cboTrialRep] = Me![repID]
End If ' End of YES TRIAL RECORDS EXIST

Exit_Form_Current:
Exit Sub

Err_Form_Current:
Call ShowError("sfrmTrialInfo", "Form_Current", Err.Number,
Err.Description)
Resume Exit_Form_Current
End Sub


Can you help me get the two "NEW RECORD" scenarios to look the same?
Regards,
SueB

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
rkc
Susan Bricker wrote:
Greetings. Before I begin, I have been stuck on this problem for about a
5 days, now. I have tried and just seem to be not getting anywhere. I
know that the explanation is lengthy, but I am a relative newcomer to
Access and need to be methodical until it become more familiar. Thanks
in advance for your help.


<snip a-lot-o-stuff>

That was way to much to wade through.
Are you willing to post the.mdb file somewhere (not here) it
can be downloaded?
Nov 13 '05 #2

P: n/a
rkc,

I agree ... to much stuff to wade through. Sure, I'll post the mdb
somewhere. Just let me know where. Keep in mind that this database is
"under construction". Thanks.

I'll look for your reply post.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.