Solved

VB editing

Posted on 2004-04-27
26
324 Views
Last Modified: 2008-02-01
Hello
I'm an Access novice...
I'm trying to edit an Access program I've been sent in which for a reason I can't understand there are sections shown in the VB editor that the author doesn't have in his original.
When I call up the editor in the DB I get a top section: Option compare database
Then I get a section: Private Sub Command1_Click()
(that's the bit of the program I want to keep)

I also get an Adtext section:
     Private Sub AdText_BeforeUpdate(Cancel As Integer)

End Sub
... a Detail section
     Private Sub Detail_Click()

     End Sub
and a Form section:
     Private Sub Form_Load()

     End Sub
and another weird one -
     Private Sub ÊÓãíÉ9_Click()

     End Sub

None of these were in the file at source. i don't know how they crept in or if they are responsible for the fact that the file doesn't work.
I can see how I can call up these functions in the right hand column, but I can't see how to get rid of them. just effacing them and saving doesn't do anything. When I open the file again, there they are!

I'm actually trying to find the best way of incorporating data coming in from a web forms page either by email or some other method - I've been playing around with asp but it seems to need to be installed on a paying server... cgi-bin etc
I'm open to any suggestions as to the best way of doing this but at the moment I'd be very happy if I could automatically point Access at the email replies (saved into Word or excel files if necessary)
They come in like this
name=bloggs
firstname=keith
instrument=euphonium
address=
etc
etc

Any help gratefully accepted
All the best
Keith
0
Comment
Question by:keithbraithwaite
  • 10
  • 9
  • 4
  • +1
26 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 10926644
Looks to me your database got corrupt, try my MS Access database recovery steps:

1) Create a backup of the corrupt database. (Just in case of)

2) Create a new database and use File/Get external data/Import to get all objects of the damaged database.

3) Try these Microsoft solutions:
Repair A97/A2000:
http://support.microsoft.com/support/kb/articles/Q109/9/53.asp
Jetcomp:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273956
and/or read the article:
ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;306204

4) Bit "heavier":
Access decompile:
http://www.granite.ab.ca/access/decompile.htm

5) Try a recovery tool:
Access recovery:
http://www.officerecovery.com/access/index.htm

6) Ask a company (will cost $$'s ! )
http://www.pksolutions.com/services.htm

Nic;o)
0
 
LVL 34

Expert Comment

by:flavo
ID: 10926646
>> I've been playing around with asp but it seems to need to be installed on a paying server

Try Brinkster.com , they offer free server-side scripting with A2k.
0
 
LVL 34

Expert Comment

by:flavo
ID: 10926651
http://www.brinkster.com if you're lazy..

Nico,

Seen that b4  :-)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10926665
Ive suffered a similar thing, and I had a corrupt DB
would go for Nico's apprach
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10926674
I used this link though, same thing I suppose

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304548

0
 
LVL 54

Expert Comment

by:nico5038
ID: 10926764
Yep flavo, and I guess it won't be the last time as database corruption is an always returning problem with many different error warnings (if any <LOL>)

The SR's are indeed a good addition rockiroads as they should be installed to prevent a number of different database crashes !

Nic;o)
0
 

Author Comment

by:keithbraithwaite
ID: 10928735
OK - I'll try those recovery tips.
And thanks for brinkster (I'm not THAT lazy :) tho' I'm getting old...
K
0
 

Author Comment

by:keithbraithwaite
ID: 10928904
If I could just come back on two things:
1. Is there in fact some way I can erase these "things" - maybe they're called "declarations" - I have to translate from the french - I live in France and I'm using MS Access in french...
They all came back in when I imported everything as in your first suggestion.

2. What should I not import if I want to put the VB script in myself? (I have a copy of it as it should be). Would I be right in thinking its the Form? Can I create a Form with the same name and then open the VB editor?

K

0
 

Author Comment

by:keithbraithwaite
ID: 10929131
I'll try to translate the error message I get when I click on a button in the form window having given the path to the Word file with the data in it:

A big "I" for Infor I suppose - Microsoft Access
The on_click expression entered as a parameter of the property of the event type has caused an error. There was a problem during the communication between MS Access and the OLE server or the ActiveX control.
* The result of the expression is not the name of a macro, the name of a user defined function or event procedure.
* A mistake has perhaps been made during the evaluation of a function, an event or a macro.

Does that make any sense to you.
K

PS hope you guys won't drop me 'cause I live in France... I have to admit that I would have preferred a UN intervention in Irak and I hate to see those American and British soldiers dying out there for what appears to me like a war to control oil supplies. If those oil barons hadn't stopped people developing the electric car, we'd all be driving around in one by now and independant of those countries...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10929163
You could try this

if its a form, copy the controls (textbox, labels etc) only onto a new form (ctrl-a, copy should do the trick when form open)
copy the code behind it into notepad - delete the redundant code

try to delete that object

next on the new form you got, you've got pasted the controls from the old form
open in VB and go behind that form and paste the code from notepad

you will have to relink the events for each control
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10929244
When the import fails I would certainly try the Access decompile:
http://www.granite.ab.ca/access/decompile.htm

And see or that's correcting the code. It's certain that some Access Internal pointers are screwed up.

Nic;o)
0
 

Author Comment

by:keithbraithwaite
ID: 10929592
I tried the decompile first (The .bat seemed to work OK and wen I went into the VB editor I got "compile" in the debug menu.
Error message: "project or library not found"
for the line: Dim oApp As Word.Application

I'm going to try pulling in those elements of the form too and then try to paste in the script...
K
0
 

Author Comment

by:keithbraithwaite
ID: 10929728
Didn't make it... Don't know how to copy just some elements of the Form. Seems to be all or nothing.
In fact all the form does is to let me type in the path to the data file. It will always be the same one. Can't I put the path of the file drectly into the VB script?
That way I could create a new form and just paste in the script...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 65

Expert Comment

by:rockiroads
ID: 10929908
Error message: "project or library not found"
for the line: Dim oApp As Word.Application


just create a reference to Microsoft Work in Tools/References
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10930066
Better:
just create a reference to Microsoft Word in Tools/References

I'm not the only one with typo's I see <LOL>

Nic;o)
0
 

Author Comment

by:keithbraithwaite
ID: 10930137
That looks like a good clue to what's going wrong. Right there in Tools/References i find that the Microsoft Object library is missing!
Seems a good time to reinstall... I'll see if it talks about the library as I do that.
I'll be back
K

PS all this time I've been talking to you I've been repairing a baritone sax - have to make a living too!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10930279
musical instrument repair vs access
wise choice!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10930286
the repair is simple
just uncheck
click ok
then go back to tools/refs
find it, click ok and thats it!
0
 

Author Comment

by:keithbraithwaite
ID: 10930784
Not so simple apparently...
The library that was missing was MSWord 10
The only one I could find to click on when going back into references was Word 9
... and of course the error is still there.
Could an Access code be as delicate as that - not to be able to use a Word 9 when written in Word 10 - that does leave me very confused.
At least the sax is now playable!!!
K
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10935630
possible, using a newer version on an old version doesnt work well
using old version on new version works ok so as long as backwards compatability built in

maybe the Word9 reference is not registered? you normally would get "Cannot create Activex error though"
maybe its worth trying to register, come out of acces, back in and try a debug/compile again

to register regsvr32 <fullpathname of reference>  - pathname can be found in Toool/sReference
0
 

Author Comment

by:keithbraithwaite
ID: 10936213
I'll try that too. I downloaded another MSWord.olb which I'll try to register.

The error message does mention ActiveX as well as other possibilites.

I've increased the points. It's time I did a distribution. I know I haven't yet made it through but you guys have held my hand long enough.

If one of you has, or can point me to a very simple Access file which pulls in data from a Word / text / excel file, I could try to download it or you could send it to me with the short data file. I'm sure that if I can see one working here, I can build on that.
All the best
Keith
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10936786
I use Excel automation, if that helps

here is an example of what I use


example excel automation

Private m_objExcel As Excel.Application
Private m_objWorkBook As Excel.Workbook
Private m_objWorkSheet As Excel.Worksheet

Private m_strError As String
Private m_strFile As String



Public Function openFile(ByVal sFile As String, Optional ByVal bReadOnly As Boolean = True) As Boolean

    on error resume next
   
    'Close any existin file
    If m_strFile <> "" Then closeObject

    m_strFile = ""
   
    Err.clear
   
    Set m_objExcel = New Excel.Application
    m_objExcel.Workbooks.Open FileName:=sFile, Notify:=False, ReadOnly:=bReadOnly
    If Err.Number <> 0 Then
        m_strError = "Could not open the template file (" & vbCrLf & sFile & ")" & vbCrLf & vbCrLf & Err.Description
        MsgBox m_strError, vbCritical, "Report Dumper: Open File"
        openFile = False
        Set m_objExcel = Nothing
    Else
        m_strError = ""
        openFile = True
        m_strFile = sFile
    End If

End Function

Private Sub closeObject()
   
    On Error Resume Next
   
    m_objExcel.DisplayAlerts = False
    m_objExcel.Workbooks.Close
    m_objExcel.Quit
   
    Set m_objExcel = Nothing
   
    m_objWorkBook.DisplayAlerts = False
    m_objWorkBook.Workbooks.Close
    m_objWorkBook.Quit
   
    Set m_objWorkBook = Nothing
   
    m_strFile = ""
   
    Err.clear
End Sub


Public Function changeSheet(ByVal sSheetName As String) As Boolean

    On Error Resume Next
   
    changeSheet = False                 'Assume the worst
   
    If m_strFile = "" Then
        changeSheet = False
        m_strError = "Excel object has not been created."
        Exit Function
    End If
   
    Err.clear
    m_objExcel.Sheets(sSheetName).Select
    If Err.Number <> 0 Then
        If Err.Number = 9 Then
            m_strError = "Could not find '" & sSheetName & "' from the Spreadsheet " & m_strFile & vbCrLf & "Check the spelling, spaces before/after sheet name etc."
        Else
            m_strError = "Failed to open Excel sheet '" & sSheetName & "' from the Spreadsheet " & m_strFile & vbCrLf & Err.Description
        End If
        MsgBox m_strError, vbExclamation, "Report Dumper: Change Sheet"
    Else
        changeSheet = True
    End If
End Function

Public Sub positionRange(ByVal sCellPos As String)
    If m_strFile = "" Then m_objExcel.Range(sCellPos).Select
End Sub


Public Function getCell(ByVal iRow As Integer, ByVal iCol As Integer, Optional ByVal sDefaultVal = "") As String
   
    On Error Resume Next
   
    If m_strFile = "" Then
        getCell = sDefaultVal
        m_strError = "Excel object has not been created."
    Else
        If Nz(m_objExcel.Cells(iRow, iCol), "") = "" Then
            getCell = sDefaultVal
        Else
            If iDataType <> RD_DataTypes.rdInt Then
                getCell = m_objExcel.Cells(iRow, iCol).Value
                If Err.Number <> 0 Then
                    'If it fails, check for formula, blank if formula
                    Err.clear
                    getCell = m_objExcel.Cells(iRow, iCol).Formula
                    If Left$(getCell, 1) = "=" Then getCell = ""
                    Err.clear
                End If
            Else
                getCell = m_objExcel.Cells(iRow, iCol).Value
            End If
        End If
    End If
   
    Err.clear
End Function



ok, just some examples

you loop in code using a row and column counter, fetch values and do your stuff
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
ID: 10936796
not done much on word automation though

here is an example of opening word

Sub OpenWordDoc()

    Dim wdApp As Word.Application
   
    Set wdApp = New Word.Application

    ' Make the application visible, and then open and close a document.
    With wdApp
        .Visible = True     'this will make word visible if u want it to
        .Documents.Open "c:\help.doc"
        MsgBox "Has Document Opened?"
        MsgBox "Now do your stuff"
        .ActiveDocument.Save
        .Quit
    End With

    ' Close the object reference.
    Set wdApp = Nothing

End Sub




Public Sub MailMergeIt()

    Dim objWord As Word.Document
   
    Set objWord = GetObject("C:\help.doc", "Word.Document")
   
    objWord.Application.Visible = True
    objWord.MailMerge.OpenDataSource Name:="C:\Dipak\Work\EMEA\xx.mdb", LinkToSource:=True, Connection:="QUERY Test-Query"
    'objWord.MailMerge.Execute
    'objWord.Application.Documents(2).Close wdDoNotSaveChanges
    DoCmd.Hourglass False
    Set objWord = Nothing

End Sub
0
 
LVL 34

Accepted Solution

by:
flavo earned 100 total points
ID: 10936859
Here's an example is used to use ADO to connect to an excel workbook to get selected data...

Never used word automation.  Hate using word as it is...

Sub importCLExcel()

Dim rst As ADODB.Recordset
Dim cn  As ADODB.Connection
Dim fld As ADODB.Field
Dim strName As String
Dim intSales As Double
'On Error GoTo error_handle


'open the connection to excel
Set cn = New ADODB.Connection
'Set provider
cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
' Connection string
cn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & OpenTextFile("c:\") & ";"
' Open the connection
cn.Open
'open recordset
Set rst = New ADODB.Recordset
'select all records without null dealerid or sales
rst.Open "SELECT ICBS_Number, MONTH_SALES FROM [Sheet1$] WHERE ICBS_Number IS NOT NULL and MONTH_SALES IS NOT NULL", cn, adOpenDynamic, adLockOptimistic
rst.MoveFirst
'turn off warnings
DoCmd.SetWarnings (False)
 Do While Not rst.EOF
   
   strName = Nz(DLookup("[CompanyName]", "tblComapnyInfo", "DealerID_CL = " & rst(0) & ""), "X")
   If strName = "X" Then
   'do nothing
   rst.MoveNext
   Else
   DoCmd.RunSQL "UPDATE tblMonthlySales SET tblMonthlySales.Sales = tblMonthlySales.Sales + " & rst.Fields(1) & " WHERE tblMonthlySales.CompanyName = """ & strName & """ AND tblMonthlySales.SalesMonth = #01/02/2004#;"
   rst.MoveNext
   End If
 
 Loop
'turn the warnings back on
DoCmd.SetWarnings (True)
'clean up - close connection to Excel
rst.Close
cn.Close


Exit Sub

error_handle:

 If Err.Number = -2147217842 Then
   'no file - user hit cancel
   MsgBox "Import file not selected.", vbCritical, "Canceled"
 ElseIf Err.Number = -2147217904 Then
   'file the wrong format
   MsgBox "The file you selected was not in the correct format. Plase ensure the correct file was selected with the headings ICBS_Number and MONTH_SALES", vbCritical, "Warning, wrong file"
 Else
  'other errors
   MsgBox Err.Description & " " & Err.Number
   Debug.Print Err.Number
 End If
 'make sure the wanings are turned on if it errors out
DoCmd.SetWarnings (True)
Exit Sub

End Sub


In excel the sheet name ( with a $ after it for some reason) is the "table" and the first entires (row 1) are classed as the field names..

Good Luck!

Dave  
0
 

Author Comment

by:keithbraithwaite
ID: 10943771
Just got in... I'll look at all that tomorrow (nearly midnight here - heavy day).
I agree that Excel would be a better source - it's just that my data comes in through email and it seemed easier to copy it into a Word file.
The other problem is that it is vertical when it comes in
name=bloggs
firstname=keith
instrument=baritone sax

I'm sure that's no problem for you guys but in my ignorance I don't see how to read it into horizontal fields...

The real problem remains. I can see more or less how those programs you've sent me are working but I'm such an Access novice that I don't know how to open the VB editor to type them in.
I've seen that if there's already a Form in the Access db I just have to open the VB editor, double click on the name of the form and the editing window comes up.
When I try to create a form I get a window with a gray checkerboard on it - I don't know what to do with it.
That's why I wondered if anyone could send me a working Access db with this sort of program in it and for instance an Excel data file to go with it.
My email address is kb@univ-perp.fr
Good night and thanks again.
keith
0
 

Author Comment

by:keithbraithwaite
ID: 10946999
Flavo - I'm going to try the Excel import.
Hope these questions aren't ridiculous...
1. I presume I have to set up an excel file with some data in it.
2. I presume that I have to put it's name where there is *.xls in the program?
3. Can I use headers of my own if I change them wherever they appear in the program?
4. Why is there "open textfile" in the program? What does it do?
5. Once I've copied all that in and modified it, how do I launch it?
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now