Return of Field Value from Access 2000

[PALman]

I have a form in VB6 which allows me to view and edit data in my Access 2000 table (Tablename = QAReview.mdb). The table has a key field called JobNo which I want to use to open, from my pulldown menu, Word, Excel and Adobe documents relevant to that Job Number which all carry the JobNo within their filenames.
So firstly, I need to know how to code my pulldown menu to return the ACTIVE JobNo of the record which is in view/focus on the form.
The pulldown menu is on the form and is executed when form is displaying the data for that particular JobNo/record, so Table QAReview.mdb is already connected by the Record Navigator Bar (called... datPrimaryRS Adodc) at bottom for form.
I was able to program this in Access using Dlookup and Me.JobNo but these are not recoginsed in VB6.
I have tried searching for the answer but most refer to the passing of variables and not exactly what I am looking for i.e. the return of the field value of JobNo of the IN-FOCUS RECORD of the form.
Any help is, as always, much appreciated.

 

[Andrzejek]

You may want to take a look at post

http://www.tek-tips.com/viewthread.cfm?qid=1294110&page=2

I would suggest to use ADODB instead of adodc

HTH

---- Andy

 

[PALman]

Thanks Andy,
Can you tell me the difference between ADODC and ADODB and in your link to previous help can you point me to the line which returns the active field/record? This is the link I told you I would have to revisit someday.
My objective is to be able to view documents relevant to the record displayed on the form without doing much other than clicking on the required type of document on my pulldown menu.
Thanks again.

 

[PALman]

I have added here, the code for pulldown menu I have at the moment. This works but only displays the one document "222555 - MPI.xls" which comes from line 9... excelFile = "222555 - MPI.xls". Obviously this line needs replacing...
Private Sub mnuPO_Click()
Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Set xlApp = New Excel.Application
xlApp.Visible = True
'SET PATH
'Path = "C:\000QCP\Templates\"
'SET excelFile FILENAME
excelFile = "222555 - MPI.xls"
'OPEN PATH AND FILE
Set xlWbk = xlApp.Workbooks.Open(FileName:=Path + excelFile)
xlApp.Quit
Set xlWbk = Nothing
End Sub

 

[Golom]

ADOD[red]B[/red] refers in this instance to an ADO recordset while ADOD[red]C[/red] refers to an ADO Data Control.

In most cases you can use a recordset and ADODC Data Controls have a reputation for being somewhat flaky. The only disadvantage of the recordset is that has no displayable representation on the form so you would need to build your own navigation buttons.

If you did something like

[COLOR=black cyan]' General Declarations Section[/color]
Dim Conn   As ADODB.Connection
Dim rsJobs As ADODB.RecordSet

Sub Form_Load()
Set Conn = New ADODB.Connection
Conn.Open [blue]<<< Some ADO Connection String >>>[/blue]
Set rsJobs = New ADODB.Recordset
rsJobs.Open "Select * From tblJobs", Conn
End Sub

Private Sub mnuPO_Click()
Dim JobNo As String
Dim Path As String
JobNo = rsJobs![JobNo]
Path = "C:\000QCP\Templates\"
[COLOR=black cyan]' Code to determine what files to
' display for the specified JobNo[/color]
ExecuteWait Path & FileName
End Sub

Where the ExecuteWait function is in this module.

Option Explicit

Private Type STARTUPINFO
    cb                              As Long
    lpReserved                      As String
    lpDesktop                       As String
    lpTitle                         As String
    dwX                             As Long
    dwY                             As Long
    dwXSize                         As Long
    dwYSize                         As Long
    dwXCountChars                   As Long
    dwYCountChars                   As Long
    dwFillAttribute                 As Long
    dwFlags                         As Long
    wShowWindow                     As Integer
    cbReserved2                     As Integer
    lpReserved2                     As Long
    hStdInput                       As Long
    hStdOutput                      As Long
    hStdError                       As Long
End Type

Private Type PROCESS_INFORMATION
    hProcess                        As Long
    hThread                         As Long
    dwProcessId                     As Long
    dwThreadId                      As Long
End Type

Private Const INFINITE = -1&
Private Const MAX_FILENAME_LEN = 256

Private Declare Function ShellExecute Lib _
        "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, _
         ByVal lpOperation As String, _
         ByVal lpFile As String, _
         ByVal lpParameters As String, _
         ByVal lpdirectory As String, _
         ByVal nShowCmd As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" _
         (ByVal lpApplicationName As Long, ByVal lpCommandLine As String, _
          ByVal lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
          ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
          ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
          lpStartupInfo As STARTUPINFO, _
          lpProcessInformation As PROCESS_INFORMATION) As Long

Private Declare Function FindExecutableA Lib "shell32.dll" _
        (ByVal lpFile As String, ByVal lpdirectory As String, _
         ByVal lpResult As String) As Long

Private Declare Function CloseHandle Lib "kernel32" (hObject As Long) As Boolean

Private Declare Function WaitForSingleObject Lib "kernel32" _
        (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

'----------------------------------------------------------
' Procedure : ShellWait
' Purpose   : Runs a command as the Shell command does but waits for the command
'             to finish before returning.
'             The full path and filename extention are required
' Arguments : cCommandLine  The command to be shelled.
' Returns   : FALSE if the shell failed
'----------------------------------------------------------
'
Public Function ShellWait(cCommandLine As String) As Boolean
    Dim NameOfProc                  As PROCESS_INFORMATION
    Dim NameStart                   As STARTUPINFO
    Dim i                           As Long

    NameStart.cb = Len(NameStart)
    i = CreateProcessA(0&, cCommandLine, 0&, 0&, 1&, _
                       NORMAL_PRIORITY_CLASS, 0&, 0&, NameStart, NameOfProc)

    If i <> 0 Then
        Call WaitForSingleObject(NameOfProc.hProcess, INFINITE)
        Call CloseHandle(NameOfProc.hProcess)
        ShellWait = True
    Else
        ShellWait = False
    End If

End Function

'----------------------------------------------------------
' Procedure : ExecuteWait
' Purpose   : Executes a file with it's associated program and waits for the
'           : process to finish before returning.
' Arguments : FileName  The name of the file (path & extension) to open.
' Returns   : returns true on success.
'----------------------------------------------------------
'
Public Function ExecuteWait(ByVal Filename As String) As Boolean
    Dim ApplicationName             As String

    ApplicationName = FindExecutable(Filename)

    If ApplicationName <> "" Then
        ExecuteWait = ShellWait(ApplicationName & " " & Filename)
    Else
        ExecuteWait = False
    End If
End Function


'---------------------------------------------------------------------------------------
' Procedure : FindExecutable
' Purpose   : Finds the executable associated with a file
' Returns   : "" if no file is found.
'---------------------------------------------------------------------------------------
'
Private Function FindExecutable(Filename As String) As String
    Dim i                           As Integer
    Dim ApplicationName             As String

    ApplicationName = String(MAX_FILENAME_LEN, 32) & Chr$(0)

    i = FindExecutableA(Filename & Chr$(0), vbNullString, ApplicationName)

    If i > 32 Then
        FindExecutable = Left$(ApplicationName, InStr(ApplicationName, Chr$(0)) - 1)
    Else
        FindExecutable = ""
    End If

End Function

 

[Andrzejek]

Let me get it straight:

You have Access database (QAReview.mdb) where you have a table (SomeTblInAccess) where you have Field in this table (JobNo) where you keep the names of a file (*.doc, or *.xls, or ADOBE (whatever extension it has)).

All the files are in "C:\000QCP\Templates\"

And you want to have a way to select the file name from this location on your hard drive (C:\000QCP\Templates\) and have a user open any that they want.

If that's the case, you may just want to get the DirListBox and FileListBox (or just FileListBox) to point to your location on C:\ and display all files from your HD. Bypass Access.

---- Andy

 

[PALman]

Sorry Andy,
I've misled you. The database I have connection with is called "QA5.mdb" and the table within that is called "QAReview", which has around forty fields the main one being the key field called "JobNo". The location of this database and its tables is "C:\000-Quality Control Program".
The path you mentioned above "C:\000QCP\Templates\" I use to try and simplify for this forum. Please ignore this now.
Word, Excel and pdf documents are to be found in folders which are named with their relevant Job Numbers. Each job having its own folder with its relavant docs inside. So for Job Number 222555 all various types of docs would be found here... "C:\000-Quality Control Program\Jobs Folder\222555". These documents can be named with or without the job number in their filename eg. "222555-MPI.xls" or simply "Purchase Order.pdf".
So from the VB6 form with the current record being Job Number 222555 I want to be able to view these documents by simply going to my pulldown menu selecting customer docs on the top line, select Purchase Order from the dropdown list and have the code display that purchase order for that JobNo 222555 without having the user pick from a list of various files in a listbox. These Job Folders become polluted and I believe this type of pulldown menu focusing only on the docs like PO, BOM, Specifications, Drawings etc is the way to go. Indeed this is why I am trying to convert all my prorams from Access2000 to VB6 to allow me to have pulldown menus. Its a pity all my efforts at learning about Dlookup and Me.JobNo in Access is lost in VB6.
I think its clear I need to use the returned JobNo field value to enable me to call up both files and folder paths which are basically just strings. So when I can retrieve the current JobNo from the record on view in the form, I'm home and dry.
Hope this clears things up a bit. I cannot thank you enough for the help you have already given me and hope you will continue to help.
I have just started to use the code you gave me yesterday and beleive we are so close to what I need. However when running the program it stopped at line... JobNo = rsJobs![JobNo]with the "Runtime error 91 - Object variable or with block variable not set".
I am just about to start debugging and see if I can sort it.
Thanks again, your help and of others is very much appreciate.

 

[PALman]

Thanks Golom
I am in the process of working with the code you posted above and with code that Andy had sent previously.
I am very new to VB6 so need a little time to get thigs to work. Please see previous post above where I have tried to explain what I have and where I want to go.
Thanks again.

 

[PALman]

Hi Guys,
I've got my programmers and coding mixed up. The error I mentioned above is in Goloms code posted yesterday. The error being... when running the program it stopped at line... JobNo = rsJobs![JobNo]with the "Runtime error 91 - Object variable or with block variable not set".
I am just about to start debugging and see if I can sort it.
Thanks Golom and Andy for all your help it is very much appreciate.

 

[Andrzejek]

Try this

Set rsJobs = New ADODB.Recordset
[b]rsJobs.CursorType = adOpenForwardOnly
rsJobs.LockType = adLockReadOnly
rsJobs.CursorLocation = adUseClient[/b]
rsJobs.Open "Select * From tblJobs", Conn
End Sub

HTH

---- Andy

 

[PALman]

Andy,
Placed your code in Private Sub mnuPO_Click()
Error on line... rsJobs.Open "Select * From QAReview", Conn
"Connection cannot be used to perform this operation it is either closed or invalid in this context".

 

[Andrzejek]

Do you have connection to your database?

From Golom's post:

Conn.Open [blue]<<< Some ADO Connection String >>>[/blue]

What's your Connection String ?

---- Andy

 

[PALman]

Hi guys,
I thought you had it nailed Andy when I checked the connection string to find I had forgot to copy it over from a temporoary form. I have done that now and I am nearly there but unfortunately what I have at the moment does not return the live/active value of the JobNo field. The records in this table have sample JobNo's like 222333, 222555, 222666, 222888. I have added in message boxes to debug and return the path and excelFile names but the only JobNo returned is 222555 and I cannot find where it is picking this up. Obviously if program was running ok I would expect to see 222333, 222666 returned in these msgbox's.
To add to my problem the program starts up Excel ok but does not load any excelfile and then when I close Excel the program runs through the msgbox's and then stops with error messages.
I have listed here what I have got at the moment with comments at certain lines to show errors etc.

Coding at opening of VB6 Form…

General…
Dim Conn As ADODB.Connection
Dim rsJobs As New ADODB.Recordset

Form…
Private Sub Form_Load()
Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\000-Quality Control Program\QC5.mdb;Persist Security Info=False"
Set rsJobs = New ADODB.Recordset
rsJobs.CursorType = adOpenForwardOnly
rsJobs.LockType = adLockReadOnly
rsJobs.CursorLocation = adUseClient
rsJobs.Open "Select * From QAContractReview", Conn
End Sub

Private Sub mnuPO_Click()

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Set xlApp = New Excel.Application
xlApp.Visible = True
Dim Path As String, excelFile As String
'Dim JobNo As String <<< This line has no effect so comment out

JobNo = rsJobs![JobNo] '<<<< This line does not return active value of JobNo field

Path = "C:\000-Quality Control Program\Jobs Folder\" + JobNo + "\"
MsgBox "Path = " & Path

excelFile = JobNo + " - MPI.xls"
MsgBox "Filename = " & excelFile
MsgBox "Path & Filename = " & Path + excelFile
'The above msgboxes appear to show correct path but JobNo is always = 222555 no matter
'which record is visible on form. eg Next record is 222666 but still 222555 is returned
' and I cannot see where the 222555 is coming from

'ExecuteWait Path & excelFile '<<< This line goes to Module1 and returns error in...
'<<< Public Function ShellWait(cCommandLine As String) As Boolean
'<<< NORMAL_PRIORITY_CLASS Variable not defined

Set xlWbk = xlApp.Workbooks.Open(Filename = Path + excelFile) '<<< When previous line is taken out this
'<<< line returns the error...
'<<< False.xls could not be found

MsgBox "End of Program"
'Close file and Workspace
xlApp.Quit
Set xlWbk = Nothing

End Sub

I haven't listed Golom's Module1 as it is as it was untouched. So for last error please see earlier post from Golom... Module.
As always any further help would be much appreciated, and I do feel we are almost there.
Thanks again

 

[Golom]

First of all ...

It looks like you are opening the [blue]rsJobs[/blue] recordset but you are not repositioning it (at least not in this code.) That means that it is always on the first record which, presumably, has JobNo = 222555.

You state that

JobNo is always = 222555 no matter which record is visible on form

Where exactly is it visible?

- In a data grid?
- In a text box?
- On some other control?

Perhaps you need to pick up the JobNo value from that control rather than from an independent recordset that isn't synched with what the screen is showing.

Second ...

The code I gave you was an attempt to remove the requirement to build Excel Objects, Word Objects or Acrobat Objects in your code. All it does is find the application from the system's file associations that opens a (for example) .xls, .doc or .pdf file and then runs that application on the specified file. You don't need to know or care which type of document you are running as long as the system has an application that can open the file.

 

[PALman]

Hi Golom,
The first record has JobNo as 222333 and the second record is 222555 and the field is displayed in a textbox with DataField property set to JobNo.
Interestingly I went into the table in Access and changed job number 222555 to 222556 and then run the program opened the form found the second record to be 222556 and then when I clicked on pulldown menu and tried to open the excel doc I noticed the msgbox's were now returning 222556. The program ran to end with no errors but did not load anything because there was not anything under 222556 to load. So I then created a new folder 222556 and a excel file 222556 - MPI.xls and rerun program. This time it still returned the 222556 job number but stopped with previous error in Module... Variable not defined.
I have played around changing the job numbers but the only one to be returned is Record No.2 which is JobNo 222555 or what ever I change it to. So you appear to be right it has focus only on one record but not number 1 but number 2.
Thanks again

 

[Golom]

OK. Your text box has JobNo as the DataField but what does it have as the DataSource? If it's not the recordset [blue]rsJobs[/blue] then you are still not synchronized between the form display and that recordset. Perhaps you need something along the lines of

Private Sub mnuPO_Click()

Dim Path As String
Dim ExcelFile As String
Dim JobNo As String 

[red]JobNo = Text1.Text[/red] [COLOR=black cyan]' Whatever yout Text Box is called[/color]

Path = "C:\000-Quality Control Program\Jobs Folder\" & JobNo & "\"

ExcelFile = JobNo & " - MPI.xls"

[COLOR=black cyan]' You will need a reference to
' [i]Microsoft Scripting Runtime[/i] to use the
' FileSystemObject.[/color]
With New FileSystemObject
   If .FileExists(Path & ExcelFile) Then
      ExecuteWait Path & ExcelFile
   Else
      MsgBox "File " & Path & ExcelFile & " does not exist."
   Endif
End With

End Sub

Add this to the module level code I gave you in the General Declarations section

Public Const NORMAL_PRIORITY_CLASS = &H20&

 

[PALman]

Thanks Golom,
I did everything you said last post. The DataSource was set to datPrimaryRS which was set up when I created form with Data Form Wizard. I changed it to rsJobs and but no Job Nmbers show in the JobNo text box although other data shows in other fields. Needless to say the selection of doc from pulldown menu doesn't work as it has no JobNo returned. I shall continue with your code and try to find the solution.
Thanks again.

 

[PALman]

Golom,
I have just returned the DataSource to its original value datPrimaryRS and run your new coding and now I am nearly there.
The correct JobNo is now getting passed however when excel starts up it comes up with five not found files which I believe comes from its interpretation of the line...
Path = "C:\000-Quality Control Program\Jobs Folder\" & JobNo & "\"
The errors are...
C:\000-Quality.XLS not found
Control.XLS not found
Program.XLS not found
Folder\222555\222555.xls not found and
MPI.XLS not found.
I am about to try renaming the folders without spaces and see if that does it.
Thanks

 

[PALman]

Success at last...
I have changed all folder and file names to ommit any spaces and now the program runs perfectly. I am now able to call up any document relevant to the active JobNo from my pulldown menu.
Golom and Andy, I can't thank you enough for all the help on this.
No doubt I'll be back with more questions.
Thanks again