Tracing Links Problem and Conversion Errors

[JamesDC]

Hey all,

I have two problems with trying to upgrade an access file, not written by myself.

This first is that I'm trying to change the items on a dropbox list, and they are linked with the following code:

SELECT DISTINCTROW [Processing Data].[ID], [Processing Data].[Number], [Processing Data].[Products] FROM [Processing Data];

My problem is that I cannot find any table called Processing Data. Are there other places that I could look to try to find this? Or is there a way to open the link from inside the properties box for the dropbox?

My second question is concerning these conversion errors that appeared in the Table menu when the database was converted from Access 200 to Access 2002. They seem to each contain a small error comment, and I'm not sure if I should delete them.

Thanks in advance for any help you can provide,

James

[NeoPa]

This code will create a running sum in your Report (except it won't as the .Text bit at the end is an error).
It would be interesting to know what the Test object is that it refers to, but if you remove the .Text bit it should work without error.
Missed a bit earlier - I don't know where the word Report comes in at the start of that line. It should probably be removed too (I assume you've copied and pasted this in - it looks a little bit like a retype gone wrong to be honest).

[JamesDC]

I still get the same error when I remove the .Text part of the code.

Also, I found a hidden table called MySysCompactErr or, I believe this related to my other errors. Once more this thing confuses me. It is a table fof 4 headings: Error Code, ErrorDescriptio n, ErrorRecid, ErrorTable. There is one record in this table: -1003, Invalid Argument, ą, Processing Report (in the same heading order).

ErrorRecid seems to only accept binary values according to design view, but its value is that weird ą value.

[MMcCarthy]

Option Compare Database
Option Explicit
 
Private Sub Report_Open(Cancel As Integer)
Dim iOEE As Integer
Dim iOldOEE As Integer
 
If [test].[Line #] = 1 Then
    iOEE = [OEE] + iOEE
End If
 
Report [test].txtOEE.Text = iOEE
End Sub
 

I've never seen a piece of code like this. The Report would indicate trying to return something from a function but this is a procedure. It's possible that this is a function available in some library that is not available to the current database but as I've never seen it I can't make a guess at what library contains that function.

Mary

[MMcCarthy]

Snap!
:D

You got there seconds before me. ;)

This is one of those funny functions and I've no idea what library it's from. I would suggest giving Nico and ADezii a shout as they may be familar with it.

Mary

[NeoPa]

I still get the same error when I remove the .Text part of the code.

Also, I found a hidden table called MySysCompactErr or, I believe this related to my other errors. Once more this thing confuses me. It is a table fof 4 headings: Error Code, ErrorDescriptio n, ErrorRecid, ErrorTable. There is one record in this table: -1003, Invalid Argument, ą, Processing Report (in the same heading order).

ErrorRecid seems to only accept binary values according to design view, but its value is that weird ą value.

Sorry, I noticed (and updated my post) too late that the word Report needs to be removed from the line too.
I assume that the TextBox actually exists on the report.

[NeoPa]

This is one of those funny functions and I've no idea what library it's from.
Mary

I would simply remove it.
It seems clear that the intention is to create a running sum and, although there is a better way nowadays, leaving the amended code in place doesn't seem like a big problem to me. It should get the OP past that particular problem at least.

[JamesDC]

I got rid of the Report problem with your advice NeoPa, and now I'm getting an error in another area of the code.

Private Sub Command176_Click()
    ID.SetFocus
    ID.Text = ID.Text + 1
    Date.SetFocus
    Date.Text = ""
    ItemNum.SetFocus
    ItemNum.Text = ""
    LineNum.SetFocus
    LineNum.Text = ""
    ActualManHours.SetFocus
    ActualManHours.Text = ""
    ScheduledHoursOnLine.SetFocus
    ScheduledHoursOnLine.Text = ""
    Timebox.SetFocus
    Timebox.Text = ""
    Comments.SetFocus
    Comments.Text = ""
    ItemDescription.SetFocus
    ItemDescription.Text = ""
    LineDescription.SetFocus
    LineDescription.Text = ""
    ActualTotalCases.SetFocus
    ActualTotalCases.Text = ""
    ActualHoursOnLine.SetFocus
    ActualHoursOnLine.Text = ""
    DowntimeReason.SetFocus
    DowntimeReason.Text = ""
 
End Sub
 

It's telling me that ItemNum is an non defined variable, I think this may have to do something with one of the tables, I'm looking into that now.

Also, as a general question, I know when I learned Java and C++ we were really pushed to comment on every bit of code we write. Is it the creator of this file, or is it programming with VB in access, that leads to no commenting in the file? I would think that comments relating where certain items relate to certain tables could be very helpful... but in all of the Access databases used by my company I can see no commenting in any of the code.

[MMcCarthy]

Also, as a general question, I know when I learned Java and C++ we were really pushed to comment on every bit of code we write. Is it the creator of this file, or is it programming with VB in access, that leads to no commenting in the file? I would think that comments relating where certain items relate to certain tables could be very helpful... but in all of the Access databases used by my company I can see no commenting in any of the code.

Access VBA code should be commented like any other code. I know what you mean though. I've troubleshooted on some huge databases with no comments and it's a nightmare. All you need to do to add a comment is to put an apostrophe at the beginning of a line of text.

Mary

[NeoPa]

I got rid of the Report problem with your advice NeoPa, and now I'm getting an error in another area of the code.

Pleased that helped.

It's telling me that ItemNum is an non defined variable, I think this may have to do something with one of the tables, I'm looking into that now.

Please let us know which line it failed on and the exact error message. I can't work out from what you say if this is simply that the code uses the .Text property again, or that the control [ItemNum] is not defined on the form.

Also, as a general question, I know when I learned Java and C++ we were really pushed to comment on every bit of code we write. Is it the creator of this file, or is it programming with VB in access, that leads to no commenting in the file? I would think that comments relating where certain items relate to certain tables could be very helpful... but in all of the Access databases used by my company I can see no commenting in any of the code.

I would always recommend commenting code. Having said that, it is probably more necessary to do so when working in a C related language than in a version of Basic, which typically is nearer to a natural spoken language than C is.
C was once notably described as a Read Only language.

[JamesDC]

Sorry about that,

the line that the error came up on is :
ItemNum.SetFocu s

Where ItemNum is highlighted. The exact error is Compile Error: Variable Not Defined

I can't find any items on any of the forms that use Command176 when clicked. So I'm still looking...