Don"t understand an Error box? 1

[schines]

I keep receiving this error message every time I close out of a certain form "Do you want to save changes to the design of query 'qryConfigItems(find)'. Please Help!

 

[THWatson]

schines
It's not really an Error message. It's a warning.

What is the query "qryConfigItems(find)"? Is it being modified each time the form is run?

Tom

 

[schines]

well each time that I open the form wether or not I make any corrections, the warning "Do you want to save changes to the design of query 'qryConfigItems(find)'?" appears.

 

[THWatson]

schines
I'm searching for possibilities here. So some questions...

1. Do you have a query named "qryConfigItems(find)"? Is it possible that you made changes to that query but the query wasn't compiled? If so, just run the query once and it should compile.
2. I'm assuming that, since your post is in the Reports section, some report runs from the form to which you refer. What is the relationship between the form, the report, and that query? Does the query have anything to do with the report?

Tom

 

[schines]

May-be I can help by giving you alittle background!

I just started working with my company and I have never delt with Access before but I was through into it and told to fix the problems. I took one class and thats it!!

so Yes I have a Query named "qryConfigItems(find)" - not sure if anyone has made any changes and I have ran the Query-still the same.

Not to sure what the relationship is to the report, but after I open the form and close it is when the message appears.
Thanks for the help!

 

[THWatson]

schines
Something is triggering this. We will have to do some tracing to see what is going on.

Would you do these things and post back...
1. Open the report in Design view, check its Properties, and post the Record Source for the report.
2. I assume the Record Source will be a query. It's maybe the query called "qryConfigItems(find)", but regardless open that Record Source query in SQL view, copy the SQL and paste it into this post.
3. Regarding the form that you run, what is its purpose? Does it feed parameters to a query which then runs the report? In any event, open that form in Design view, and on the View tab select Code. That will take you to the Visual Basic editor. Once in there, click on the Debug tab and see if the Compile is bold or grayed out. If it's bold, click on it to compile the code and then, under the File tab, save your database...if not don't worry about it, just "Close and return to Microsoft Access" under the File tab. (we may have to look at this again later, if nothing else solves your problem)

Tom

 

[schines]

Tom,

Record Source for the report: qryECPDataFind

SELECT tblECPData.*,[Document#]&"-"&[NewRevLvl]AS ECPNumber,
tlbProject.ProjectManager
FROM tblProject INNER JOIN tblECPData ON tblProject.ProjectName=tblECPData.ProjectName
WHERE ((([Document#]&"-"&
[NewRevLvl])=[Forms]![frmEditECPSelect]![cboECPNumber]));

Yes my form feed parameters to a query which then runs the report.
I compile the code and then I recieved an error message:
Compile Error
Syntax Error
I then pressed OK and it highlighted this line of code:

strtblECPData.ECPNumber=Me!ECP Number

I fixed the space between ECP Number and recieved another highlighted line:

IfErr=2046 Then"The Command Delete Record isn't available now - No records to delete

Any suggestions?
schines

 

[THWatson]

schines
This feels a little like looking for a needle in a haystack, doesn't it? And oftentimes it's frustrating trying to fix something you didn't build yourself, as you are finding.

Since the record source for the report is qryECPDataFind, then where does qryConfigItems(find) come into play?

I'm assuming that the highlighted line that begins "IfErr" is part of an Error handling procedure, and if Error 2046 is encountered the error handling is bypassed. But that error applies to more than Delete Records action. The entire message for Error 2046 is as follows...
The command or action '|' isn't available now. You may be in a read-only database or an unconverted database from an earlier version of Microsoft Access.
* The type of object the action applies to isn't currently selected or isn't in the active view. Use only those commands and macro actions that are currently available for this database.

Can you post the entire VBA code for that form?

How large is the database?

Tom

 

[schines]

Tom,

Thank you for all your help so far. It's exsactly like finding a needle in a haystack, you took the words right out of my mouth!!
The Record Source for the: qryConfigItems(find)

SELECT qryConfigItems.[ConfigItem #] AS [CI#], qryConfigItems.ProductName, qryConfigItems.Version, qryConfigItems.Location
FROM qryConfigItems
WHERE (((qryConfigItems.ECPNumber)=[forms]![frmEditECP]![ECP Number]))
ORDER BY qryConfigItems.[ConfigItem #] DESC;

The Database is 5.48 MB (5,750,784 bytes)


VBA code for that form is as follows:

Option Compare Database

Private Sub Command70_Click()
On Error GoTo Err_Command70_Click


DoCmd.PrintOut

Exit_Command70_Click:
Exit Sub

Err_Command70_Click:
MsgBox Err.Description
Resume Exit_Command70_Click

End Sub




Private Sub Command81_Click()
Dim MyRs As Recordset
Dim MySql As String
MySql = "SELECT ECP Number FROM tblECPData WHERE ECP Number =" & InputBox("0N# to Find")
Set MyRs = CurrentDb().OpenRecordset(MySql, dbOpenDynaset)
Do While Not MyRs.EOF
Debug.Print MyRs!ECP; Number
MyRs.MoveNext
Loop
MyRs.Close
End Sub

Private Sub Checked_By_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr$(KeyAscii)))
End Sub

Private Sub Document_Updated_By_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr$(KeyAscii)))
End Sub

Private Sub Form_AfterInsert()
Me.Refresh
End Sub

Private Sub Graphics_Changed_By_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr$(KeyAscii)))
End Sub

Private Sub Initiated_by_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr$(KeyAscii)))
End Sub

Private Sub Parts_List_Changed_By_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr$(KeyAscii)))
End Sub

Private Sub PM_Approval_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr$(KeyAscii)))
End Sub

Private Sub PreviewECP_Click()
On Error GoTo Err_PreviewECP_Click

Dim stDocName As String

stDocName = "rptECP"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport stDocName, acViewPreview, "qryECPData Filter(find)"

Exit_PreviewECP_Click:
Exit Sub

Err_PreviewECP_Click:
MsgBox Err.Description
Resume Exit_PreviewECP_Click

End Sub
Private Sub PrtECPData_Click()
On Error GoTo Err_PrtECPData_Click

Dim stDocName As String

stDocName = "rptECP"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport stDocName, acViewNormal, "qryECPDataFindPrint"


Exit_PrtECPData_Click:
Exit Sub

Err_PrtECPData_Click:
MsgBox Err.Description
Resume Exit_PrtECPData_Click

End Sub
Private Sub Command75_Click()
On Error GoTo Err_Command75_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command75_Click:
Exit Sub

Err_Command75_Click:
MsgBox Err.Description
Resume Exit_Command75_Click

End Sub
Private Sub Command79_Click()
On Error GoTo Err_Command79_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_Command79_Click:
Exit Sub

Err_Command79_Click:
MsgBox Err.Description
Resume Exit_Command79_Click

End Sub
Private Sub Command80_Click()
On Error GoTo Err_Command80_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command80_Click:
Exit Sub

Err_Command80_Click:
MsgBox Err.Description
Resume Exit_Command80_Click

End Sub
Private Sub Command82_Click()
On Error GoTo Err_Command82_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmECP"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmEditECP"
Exit_Command82_Click:
Exit Sub

Err_Command82_Click:
MsgBox Err.Description
Resume Exit_Command82_Click

End Sub
Private Sub Command83_Click()
On Error GoTo Err_Command83_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command83_Click:
Exit Sub

Err_Command83_Click:
MsgBox Err.Description
Resume Exit_Command83_Click

End Sub
Private Sub Command84_Click()
On Error GoTo Err_Command84_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEditECP"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmEditECP"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command84_Click:
Exit Sub

Err_Command84_Click:
MsgBox Err.Description
Resume Exit_Command84_Click

End Sub
Private Sub Command85_Click()
On Error GoTo Err_Command85_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 0, 2, acMenuVer70

Exit_Command85_Click:
Exit Sub

Err_Command85_Click:
MsgBox Err.Description
Resume Exit_Command85_Click

End Sub
Private Sub Command89_Click()
On Error GoTo Err_Command89_Click

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

Exit_Command89_Click:
Exit Sub

Err_Command89_Click:
MsgBox Err.Description
Resume Exit_Command89_Click

End Sub
Private Sub cmdAddNewConfigItems_Click()
On Error GoTo Err_cmdAddNewConfigItems_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmConfigItemsEntry"

stLinkCriteria = "[ECP Number]=" & "'" & Me![ECP Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddNewConfigItems_Click:
Exit Sub

Err_cmdAddNewConfigItems_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewConfigItems_Click

End Sub
Private Sub CmdConfgItemsEntry_Click()

Dim db As DAO.Database
Dim stDocName As String
Dim newstrECPNumber, newmainECPNumber As String
Dim strECPNumber As String
stDocName = "frmConfigItemsEntry"

'Store the calling form's (Engineering Change Proposal Form) ECP Number
strtblECPData.ECPNumber = Me!ECPNumber

'Open frmConfigItemsEntry, goto matching ECPNmber field and
'set the focus to it. NOTE: the strECPNumber at the end of
'the following line is the OpenArgs property.It is the
'ECPNumber I wish to locate in frmConfigItemsEntry, and is by the
'DoCmd.FindRecord

DoCmd.OpenForm stDocName, , , , acFormEdit, , strECPNumber
Forms!frmConfigItemsEntry!frmEditECP.SetFocus

'Assign frmConfigItemsEntry ECPNumber to a temp variable
strmainECPNumber = Forms!frmConfigItensEntry!mainECPNumber

'Find the first record in tblConfigMgmt that matches
'the ECPNumber
DoCmd.FindRecord strECPNumber, , True, , True, True

'If the ECPNumber's do not match (not found in tblConfigMgmt, then
'this must be a new record so add a new record and
'populate the listed fields of frmConfigItemsEntry

If strmainECPNumber <> strECPNumber Then
DoCmd.GoToRecord , , acNewRec
Forms!frmConfigItemsEntry!mainECPNumber = strECPNumber
End If

End Sub
Private Sub Command92_Click()
On Error GoTo Err_Command92_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command92_Click:
Exit Sub

Err_Command92_Click:
MsgBox Err.Description
Resume Exit_Command92_Click

End Sub
Private Sub Command93_Click()
On Error GoTo Err_Command93_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"

stLinkCriteria = "[ECPNumber]=" & "'" & Me![ECP Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command93_Click:
Exit Sub

Err_Command93_Click:
MsgBox Err.Description
Resume Exit_Command93_Click

End Sub
Private Sub cmdAddConfigItemsEdit_Click()
On Error GoTo Err_cmdAddConfigItemsEdit_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmConfigItemsEntryEdit"

stLinkCriteria = "[ECP Number]=" & "'" & Me![ECP Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddConfigItemsEdit_Click:
Exit Sub

Err_cmdAddConfigItemsEdit_Click:
MsgBox Err.Description
Resume Exit_cmdAddConfigItemsEdit_Click

End Sub

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click
Beep
If MsgBox("Are you sure you want to delete the current record and all of it's Configuration Items?", vbQuestion + vbYesNo, "Delete Current Record?") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If

Exit_cmdDeleteRecord_Click:
Exit Sub

Err_cmdDeleteRecord_Click:
If Err = 2046 Then "The Command Delete Record isn't available now - No records to delete
Resume Exit_cmdDeleteRecord_Click

End Sub
Private Sub Command105_Click()
On Error GoTo Err_Command105_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command105_Click:
Exit Sub

Err_Command105_Click:
MsgBox Err.Description
Resume Exit_Command105_Click

End Sub

Private Sub Software_Changed_By_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr$(KeyAscii)))
End Sub


I hope that this will tell you something because it tells me nothing!

THanks
Steph

 

[THWatson]

Steph
I looked through this code. I spotted a few things. I'm not sure they will correct the problem, but let's try.

Before that, however, it's quite a bit easier to go through code if command buttons are given meaningful names. Names such as Command70 and Command79 and Command81 aren't blindingly clear as to what they do.

However...

1. At the very beginning of the form's code, under "Option Compare Database," there should be a second line Option Explicit. This forces the programmer to always declare the variables.

2. Find the code for PreviewECP. It runs "rptECP." The filter that shows there is "qryECPData Filter(find)." If this is the correct query, then you either need to remove the space between Data and Filter, or if there is indeed a space in the query's name then enclose the query name in square brackets, "[qryECPData Filter(find)]".
Above you say that the record source for the report is "qryECPDataFind." Without brackets. So I'm not clear which is the correct spelling of the query name.

3. Find the code for PrtECPDate. Note that the filter is "qryECPDataFindPrint." Are there really separate queries for Preview and Print? Not that there can't be. It's not clear why there would be.

4. Find the code for Command84. This code seems to open a form named "frmEditECP," then closes it immediately, and then opens it again on the next line. Seems odd.

5. Find the code for cmdDeleteRecord. It's almost at the end and may be the one that's causing the trouble. It's not clear to me that the line "If Err = 2046 Then "The Command Delete Record isn't available now" is correct.
You can do one of two things to test...
(a) place a ' (single apostrophe) at the beginning of that line to comment it out
(b) Change that piece of code to this

If Err.Number = 2046 Then
MsgBox "The Command Delete Record isn't available now - No records to delete"
End If

See if this gets us anywhere.

Oh a final point...I can't see anywhere in this code where the query called "qryConfigItems(find)," which is what you started this post with, is used.

Tom

 

[schines]

Tom,

Thank you for all of your help so far!

I made all of your recommended changes,
I located the code for PrtECPData, there is two seprate queries for Preview and Print, but they seem to be the same.
Then I tryed to run the debug/Compile Inventory Control 1. It runs now without any errors. Thanks
The only problem is that I am still getting the warning message?
The other thing is I found no Code relating to the Querie about qryConfigItems(find)", but that is the warnig that keeps apearing.
I did find out that the database has been updated from 97 to 2000. Do you think that that might have something to do with the Warnig message?
I just don't know what else to do or look for!

Steph

 

[THWatson]

Steph
The database being updated from 97 to 2000 wouldn't normally cause a problem. But anything's possible, I suppose.

Since neither of us can find any reference to the "qryConfigItems(find)" it's hard to figure out where to look next.

What's the name of the form that had all the code you posted?

One last thing to look at. Go into the VBA editor. Then look under the Tools tab look at References. See what items are checked there. You probably should have these checked...
Visual Basic for Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
and maybe...Microsoft ActiveX Data Objects 2.1 Library

If this doesn't solve anything, are you interested in zipping the database and sending it to me? Unless, of course, sensitive information is contained in it. I'm not sure where you're located. I'm near Toronto, Ontario.

Tom

 

[schines]

Tom,

The databases contain sesitive infomation so I won't be able to send you the database, Because, of course, that would be the easist thing to do. the other problem is that I'm located in Maryland.

I looked at the "qryConfigItems(find)" since both of us couldn't find out the relationship between the two. I ran an object dependency on the query and found that it depends on qryConfigItems and frmEditECP. Also that frmConfigItemEntryEdit,frmConfigItemEntryEdit2, frmConfigItemEntryEditTest depend on "qryConfigItems(find)" . The three are all forms that give me the warning message. Do you think that since we couldn't find any relationship between the forms and the query, I could remove the query and hopfully fix the warning message?
Just a thought!
May-be there is a way that I could send you all of the forms and remove all of the sencitive info? Is tere any way that that could be done?
But I do want to thank you for your help and support.

Steph

 

[schines]

Tom,

I went into the form and at the bottom of the form displays the "qryConfigItems(find)". I then went and viewed the properties. when I closed out of the properties and tryed to go from the design view to the form view an error message appeared 'Query must have at least one destination field. then the query disapears in the design view but is still present in the form view?
Any suggestion
Steph

 

[THWatson]

Steph
Sorry...I have been out for a few hours, and just got back. I'm replying to your last two posts here.

First of all, I wouldn't delete the query. Also it looks to me as if the form, by the same name, is built on that query. And it looks as if 3 forms, or 3 queries are somehow involved.

I understand about the sensitive data, and that's why I asked the question. But I'm at a loss to know what to suggest without seeing the database.

One possible thing would be to make a copy of the database, remove all the data from the tables in that copy in order to remove all sensitive data, and then send the copied database to me.

If you want to go that route, we don't post our e-mail addresses here in order not to attract unwanted interventions. However, I'll give it to you in a way you can interpret..
twatson
at
sentex
(the symbol for a period)
(the last 3 letters of fishnet)

Tom

 

[schines]

Tom

That sounds good but I have to go through a few people at work first to see if I am allowed to send the Database over the internet. It might take a few days is that alright?
Just let me know.

Steph

 

[THWatson]

Steph
Sure. I understand. Just trying to help. It may be useful to know that I am retired, not the least interested in the data, and accustomed to working with sensitive information.

Some things if you are going to send it...
1. Make a copy of the database and work on that, so that you don't damage your existing database.
2. You would be purging all the data from the tables.
3. It would be helpful if you were to put in one dummy record in each of the tables, so that I don't have to create records afresh.

On the other hand, there may be somebody close at hand that could pop in and have a look.

Anyway, good luck.

Tom