Split Form not working correctly

[llkhoutx]

I think that our problem is with the form control names.



I have no database to fix. Post your database and I will fix it.

Did your program compile error free? I think that that will specifically identify your instant issue.

[Brian62]

It's posted as databases2.zip.

[llkhoutx]

Your form's general issues:
1. Forms controls are not named consistently, some include blank spaces, some don't.
2. Never, ever use spaces or non-numeric special characters in a control name. It excaerbates VBA and queries.
3. Label control names are sometimes the associated control recordsource.
I recommend that a field control name be the same as the recordsource names (subject to 2, above) and that label names be the associated control's name plus have a prefix of "lbl
4. Combo box record source should have a snapshot Recordset Type, not a dynaset. A snapshot is a copy of the data and is faster; a dynaset is updateable and is not required for a combo box row source.

The correct code is

Private Sub Count_Click()
'note the effect of having spaces in control names
'controls are not named consistently
'spaces exacerbates SQL query construction
Me.[Study No] = Me.StudyInfo.Column(2)
Me.[Type of Study] = Me.StudyInfo.Column(3)
Me.[RIPS Initial Approval Date] = Me.StudyInfo.Column(4)
Me.IRBInitialApprovalDate = Me.StudyInfo.Column(5)
Me.SRSInitialApprovalDate = Me.StudyInfo.Column(6)
Me.[RD Initial Approval Date] = Me.StudyInfo.Column(7)
End Sub

[Brian62]

Can you send me the database you fixed for me. I did everything I was supposed to do but the recordset will not update. When I click on the combobox I get a message below that says the "This recordset is not updatable". Did it work for you? Thanks!

[Brian62]

Here is what I show in After Update in the Form Property sheet:

Private Sub Form_AfterUpdate()
Private Sub Count_Click()
'note the effect of having spaces in control names 'controls are not named consistently 'spaces exacerbates SQL query construction
Me.ID = Me.Studyinfo.Column(1)
Me.PI = Me.Studyinfo.Column(2)
Me.StudyNo = Me.Studyinfo.Column(3)
Me.TypeofStudy = Me.Studyinfo.Column(4)
Me.RIPSInitialApprovalDate = Me.Studyinfo.Column(5)
Me.IRBInitialApprovalDate = Me.Studyinfo.Column(6)
Me.SRSInitialApprovalDate = Me.Studyinfo.Column(7)
Me.RDInitialApprovalDate = Me.Studyinfo.Column(8)
End Sub

It doesn't give me any errors when I run it within VB. If I change the Recordset Type back to Dynaset I don't get the error any more but will not filter the combobox selection to show in the table. It shows all records.

[llkhoutx]

The instant event is not Form_AfterUpdate, but the combo box AfterUpdate event, PI_AfterUpdate. 

I just generated the appropriate code.

Your message is exactly right. The form's recordsouce is not updateable.

To fix that, I created a relationship between the two subject tables which allows the recordset to be updateable. Generate that relationship in the Relationship window.

Remove the default value for table tblCommitteeDataEntry field RDOfficeStudyNo, it's 0. Default values causes a new record to populate in part when moving to a new record. This is generally usually not intended.

The tables really shouldn't be joined for the forms recordsource. There's a parent child relationship, therefore a form and subform would provide you a much better solution. I will not undertake that.

[Brian62]

I created a subform as you sujested but I am still running into troubles.

In the Subform After update I entered this code:

Private Sub Form_AfterUpdate()
'note the effect of having spaces in control names 'controls are not named consistently 'spaces exacerbates SQL query construction
Me.ID = Me.Studyinfo.Column(1)
Me.PI = Me.Studyinfo.Column(2)
Me.StudyNo = Me.Studyinfo.Column(3)
Me.TypeofStudy = Me.Studyinfo.Column(4)
Me.RIPSInitialApprovalDate = Me.Studyinfo.Column(5)
Me.IRBInitialApprovalDate = Me.Studyinfo.Column(6)
Me.SRSInitialApprovalDate = Me.Studyinfo.Column(7)
Me.RDInitialApprovalDate = Me.Studyinfo.Column(8)

End Sub


As far as the relationships:
Link Master: Studyinfo;tblCommitteeDataEntry.ID
Link Child: tblCommitteeDataEntry.ID;Studyinfo

In the query I removed the relashionships to show just two tables. I went into relationships and created: tblcommitteedataentry.ID to tblecommitteedata.studyino
tblcommitteedataentry.PI to tblecommitteedata.studyino

I am not getting anything out of this. When the form opens it is only showing the first dropdown list but will not filter on the next selection. Still showing me "This recordset is not updateable". I pulled the subform into the details section of the main form. I am running out of ideas and you have been a big help so far. Thanks!

[llkhoutx]

Post you database as you presently have it and I'll taken another look.

[Brian62]

Here is the db as requested. As you notice when the db opens up it automatically opens to the first record showing the information in the sub-table below. When I use the dropdown to select the next record it doesn't do anything. Just gives me the error I mentioned before. Thanks for taking another look at it!

[llkhoutx]

When you say

When I use the dropdown 

What dropdown are you referring to and what do you expect to happen when it "updated" (selected)?

[Brian62]

When I open the form (frmstudydataentry) the dropdown shows 7 items there. When I select the second record in the dropdown list the table below should requery to show all records related to that selection. All I am getting is that error.

Also when the dropdown record I want is selected is should have only the 8 items in the dropdown box to show on the form and allow me to fill in the remaining text boxes so I can input the new information in them then hit "enter Dater" which is a requery that will refresh the data for the new record.

I hope that I explained it enough for you. Thx!!

[llkhoutx]

Again, what dropdown are you referring to? The form has several.

[Brian62]

Sorry, the one that says Principal Investigator. The very first one. Thx!

[llkhoutx]

I think that fixing your form compounds the bad design of your form.

Your forms looks good, but is not functional. As I understand it, you want to select, populate and update the PI and the Study Info at the same time in the same form. This is not at all good because a PI can have multiple studies.

Create a "single form" form which selects a PI or allows you to add one on the fly. You don't have to add special buttons to add or navigate records, Access will build them form you, i.e. the "navigation buttons" at the bottom of the form.

Create a "single form" subform to that main form of studies which tumbles with each PI record. Again, there are multiple studies for each PI.

Add to the various combo boxes on the fly.

Your tables and queries should have pneumonic names which identify their contents or purpose, e.g. tblPIs or tblPrincipalInvestigators and tblStudies, related to tblPIs. Months from now you won't remember what non pnuemonic names represent.

What you've bitten off is a challenge for a newbee. Stick with it. Let's abandon this thread, it's getting fractious.

[carlmdobbs]

I can spend the time with you to answer all your problems specifically. Your situation is more complex than that. I suspect you need help with data modeling and other things. You can write me at carlmdobbs@gmail.com to ask more questions about this and I'll be happy to servel If you go to my website you can get regular tips sent to you about MS Access weekly and monthy. dobbscomputerinstitute.web.officelive.com God bless.