What Was Working Isn't

[mmchaley]

Hello all,

I have 2 controls on a form which have been working and are currently working in a older version of the database which have stopped working in the current one.

The purpose of this sub form is to enter resume information.

This subform is in a tab control. The unbound Combobox is on the main form

The controls are;
1. A unbound combo box which on After_Update will refresh a list box. I also have the refresh of the list box connected to Previous and Next Company command buttons. I made a coding error on the previous where I didn't check to see if it was at 1st record and it threw an error. After that the query I have to read the combo-box throws up a dialog box asking for the value associated with the combo box. If I enter the associated value, the list box does the proper requery.

Parts
cboOrgRole
lstRoleList

Code attached to the cboOrgRole

Private Sub cboOrgRole_AfterUpdate()
Me.frmPersonnel!lstRoleList.Requery
End Sub

SQL from the Query Builder lstRoleList Row Source

SELECT tbl00OrgRole.OrgRoleID, tbl00PersonRole.PersonRole
FROM tbl00PersonRole INNER JOIN tbl00OrgRole ON tbl00PersonRole.OrgRoleID = tbl00OrgRole.OrgRoleID
WHERE (((tbl00OrgRole.OrgRoleID)=[Forms]![frm01OrgEntry]![cboOrgRole]));

2. I have 2 sub forms shown in datasheet view where I can select specific values then click a command button to save the values to the personnel record. This control also broke

Private Sub cmdSaveCertExp_Click()

Dim SelectPerID As String

SelectPerID = Me.PersonnelID

Dim SelectExpSQL As String

SelectExpSQL = "INSERT INTO tbl01PersonExp ( PersonnelID, SpecExpID )"
SelectExpSQL = SelectExpSQL + " SELECT tbl01Personnel.PersonnelID, tbl00PersonExp.SpecExpID"
SelectExpSQL = SelectExpSQL + " FROM tbl00PersonExp, tbl01Personnel"
SelectExpSQL = SelectExpSQL + " WHERE tbl01Personnel.PersonnelID = " & SelectPerID & " AND tbl00PersonExp.SelectExp=Yes"

Dim SelectCertsSQL As String

SelectCertsSQL = "INSERT INTO tbl01PersonCerts ( PersonnelID, CertRegID, CertExpire )"
SelectCertsSQL = SelectCertsSQL + " SELECT tbl01Personnel.PersonnelID, tbl00PersonCerts.CertRegID, tbl00PersonCerts.CertExpire"
SelectCertsSQL = SelectCertsSQL + " FROM tbl00PersonCerts, tbl01Personnel"
SelectCertsSQL = SelectCertsSQL + " WHERE tbl01Personnel.PersonnelID = " & SelectPerID & " AND tbl00PersonCerts.HasCert = Yes"

Dim FilterDupSQL As String

FilterDupSQL = "DELETE From tbl01PersonExp"
FilterDupSQL = FilterDupSQL + " WHERE tbl01PersonExp.PersonnelID = " & SelectPerID

Dim FilterDup2SQL As String

FilterDup2SQL = "DELETE From tbl01PersonCerts"
FilterDup2SQL = FilterDup2SQL + " WHERE tbl01PersonCerts.PersonnelID = " & SelectPerID

Dim ResetPerExpSQL As String

ResetPerExpSQL = "UPDATE tbl00PersonExp SET tbl00PersonExp.SelectExp = No"

Dim ResetCertsSQL As String

ResetCertsSQL = "UPDATE tbl00PersonCerts SET tbl00PersonCerts.HasCert = No, tbl00PersonCerts.CertExpire = NULL"

DoCmd.SetWarnings False
DoCmd.RunSQL FilterDupSQL
DoCmd.RunSQL FilterDup2SQL
DoCmd.RunSQL SelectExpSQL
DoCmd.RunSQL SelectCertsSQL
DoCmd.RunSQL ResetPerExpSQL
DoCmd.RunSQL ResetCertsSQL
DoCmd.SetWarnings True

End Sub

There are 2 things I can't figure out. 1. Why are the controls broken in this database and not a past one. 2. What made them break together?

I have attached my database. The offending form is frm01OrgEntry and the associated frmSub01Personnel.

While I may be brilliant in my own head, I fully realize that I might be speaking gibberish and drooling to those I am trying to communicate with. If I have not drawn a clear picture, let me know and I will attempt to elaborate more.

Cheers and Thanks for being a resource.

Mark

 

[essaytee]

I haven't checked your attached file but in the first instance, it appears there may be missing references. These can be checked via the VBA Editor, I forget under which menu item they appear (Tools ???).

If you can, check your references from the previous version then apply the same one (maybe with an increased version number alongside it) to your new database.

 

[mmchaley]

If you are talking about the references menu item which ends up listing things like Visual basic for Applications, Microsoft Access 14.0 Object Library, OLE Automation, etc. The same 4 items are checked in the working database and the non-working database.

Research led me to a possible activeX control, but none of the activeX references are checked in either database. I am naive as to what impact checking additional references will do for me.

Thanks - other ideas I can check?

Mark

 

[JHB]

Create a new database and import all from the old one into the new database!

 

[mmchaley]

JHB - thanks that worked.

Can you give me some insight as to why that worked?

 

[JHB]

You're welcome.

It happen sometimes that something went corrupt, your problem was more or less the same as in the thread I link to below:
http://www.access-programmers.co.uk/forums/showthread.php?t=273546