UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Compile Error:variable Not Defined When Exporting Query To Excel And Display Pícture, Access 2016    
 
   
sal
post Sep 18 2018, 09:00 PM
Post#1



Posts: 35
Joined: 18-October 16



I have a Relational MS Access Db
In a Form, I have a textbox (txtstudent_id) and a command button.
In the text box, the user types the student code
The cmd button has a VBA code to exports the query ("qry_records_student") to excel sheet.
User clicks the search button.

Then, the code opens the excel file open a worksheet with the student's code, name and records
The following lines of code work well:
CODE
Dim st_id As Double, M As String
Dim StudentPhotos
Dim myDir As String
Dim FileName As String
Dim wb As Object
Dim xl As Object
Dim sExcelWB As String
Dim ws As Object
Dim ch As Object

Set xl = CreateObject("excel.application")
sExcelWB = CurrentProject.Path & "qry_records_student.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_records_student", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_records_student")


For the second part of my code, I need code lines to display the student's photo in the excel worksheet.
The student photos are in StudentPhotos carpetr
These are my code to display the student photo in the table with student's records:
CODE
[/code]
[code]myDir = CurrentProject.Path \ StudentPhotos
st_id = txtstudent_id
M = ".jpg"
ws.Shapes.AddPicture FileName = myDir And st_id & M, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=190, Top:=10, Width:=120, Height:=120


However, any time I run the VBA all code VBA displays this message:Compile Error:Variable not defined
VBA add blue colour to "msoFalse,"
Below I include my full code:
CODE
Private Sub cmbsearch_Click()
Dim st_id As Double, M As String
Dim StudentPhotos
Dim myDir As String
Dim FileName As String
Dim wb As Object
Dim xl As Object
Dim sExcelWB As String
Dim ws As Object
Dim ch As Object

Set xl = CreateObject("excel.application")
sExcelWB = CurrentProject.Path & "qry_records_student.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_records_student", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_records_student")

myDir = CurrentProject.Path \ StudentPhotos
st_id = txtstudent_id
M = ".jpg"
ws.Shapes.AddPicture FileName = myDir And st_id & M, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=190, Top:=10, Width:=120, Height:=120
xl.Visible = True
xl.UserControl = True

End Sub


I don't know how to fix this error. confused.gif
I really appreciate any idea.
Go to the top of the page
 
GroverParkGeorge
post Sep 18 2018, 09:49 PM
Post#2


UA Admin
Posts: 33,760
Joined: 20-June 02
From: Newcastle, WA


First a bit of housekeeping. This is not a macro; it is a VBA procedure. Unlike other MS Office applications, there are two different coding tools in Access: Macros and VBA. I commented on this because the post is in the Macros forum, not the VBA forum. Not a big deal, but something to keep in mind as you grow your Access skills.

So, an excellent way to troubleshoot is to ensure that all of your VBA modules have these two lines at the top.
Option Compare Database
Option Explicit
Option Explicit is there to force all variables to be Dim'ed before they can be used.
It's not uncommon, in my experience, that a slight misspelling can creep in, and without Option Explicit, it'll slip right past us.
Then you can compile your VBA and Access will go right to the problem in the code and highlight the error for you.
Attached File  option_explicit.jpg ( 109.33K )Number of downloads: 1

This post has been edited by GroverParkGeorge: Sep 18 2018, 09:55 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
GroverParkGeorge
post Sep 18 2018, 10:01 PM
Post#3


UA Admin
Posts: 33,760
Joined: 20-June 02
From: Newcastle, WA


And a hint. I would focus on this line:
st_id = txtstudent_id

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
sal
post Sep 18 2018, 10:20 PM
Post#4



Posts: 35
Joined: 18-October 16



GroverParkGeorge.

I really appreciate your quick reply.

As you recommend, I always use:
Option Compare Database
Option Explicit

By mistake I did included those lines of code in my post.

On the other hand, I appreciate your help in my learning. I'll keep in mind macro and VBA procedure.

I will focus on the line of code you recommend. I let you know.

Cheers
Go to the top of the page
 
sal
post Sep 19 2018, 02:47 AM
Post#5



Posts: 35
Joined: 18-October 16



GroverParkGeorge
All my attempts to fix the error failed. iconfused.gif

With this line of code, I failed as well: crazy.gif
st_id = Me.txtstudent_id

I attach a zip file with my Ms Access Db and StudentPhotos Carpet.

In txtstudent_id user types student_id, then, click cmb. VBA opens the workbook, but not the student photo
I hope you can see what I am trying to do.

Thanks again
Attached File(s)
Attached File  student.zip ( 127.08K )Number of downloads: 3
 
Go to the top of the page
 
JonSmith
post Sep 19 2018, 03:01 AM
Post#6



Posts: 3,910
Joined: 19-October 10



Ok, I'll jump in as I think George is in the US so will be asleep.

In VBA there are various constants and Enum stuff.

For example if you were using the built in enum of acObjectType the options are acForm, acReport, acQuery etc etc. Each of these actually correspond to a number however, acForm=2, acReport=3.
The idea is that when you write something like
CODE
DoCmd.DeleteObject(acForm, "Name")
its easier to know what kind of object being used that
CODE
DoCmd.DeleteObject(2,"Name")
.
(Please note, these are just the first enums and methods I found in the object browser. Don't delete your forms in code tongue.gif
Each version of VBA has some enums that are unique to it, for example you wont see the Access Enum of acObjectType in VBA for Excel.


So, with that background, back to your issue.
You are using automation for Excel and using late binding. This means the VBA for Excel isn't loaded and so you cannot read any of the enums present only in Excel. The values you try and use are members of the MsoTriState Enum of which there are 5 possible members.
You must either change from late binding to early binding so that these enums are available in your VBA or convert the enum members you are using to their literal values as I demonstrated above.

Hope that helps!

Go to the top of the page
 
cheekybuddha
post Sep 19 2018, 03:17 AM
Post#7


UtterAccess VIP
Posts: 10,432
Joined: 6-December 03
From: Telegraph Hill


CODE
myDir = CurrentProject.Path \ StudentPhotos

The above line is incorrect and is likely causing the error.

Try using:
CODE
myDir = CurrentProject.Path & "\StudentPhotos\"


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Sep 19 2018, 03:18 AM
Post#8


UtterAccess VIP
Posts: 10,432
Joined: 6-December 03
From: Telegraph Hill


As to George's point about VBA procedure vs. macro, it is easy to confuse this; since, in Excel, VBA procedures are called 'Macros' by MS. shrug.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Sep 19 2018, 08:48 AM
Post#9


UA Admin
Posts: 33,760
Joined: 20-June 02
From: Newcastle, WA


Thanks for watching over me overnight. coffee1.gif
I suspect Jon's identified the most likely problems, and David identified another for you.
Yes, it's true. Access is the red-headed step child when it comes to Office, and the inclusion of BOTH macros and VBA is part of that.
BTW: this seems like a good place to offer one of my favorite analogies, Canoeing on a River. You can read the full story here.

I'm thinking here of things like deleting forms in VBA. That's a hard one to paddle, I'm afraid.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
sal
post Sep 19 2018, 09:25 AM
Post#10



Posts: 35
Joined: 18-October 16



cheekybuddha
Thanks for your help.

I included your path code
I also delete Dim Student Photo, this line was incomplete

Moreover, in Tools-References I checked :
Visual Basic for Application
Microsoft Access 15.0 Object Library
OLE Automation
Microsoft Office 15.0 Access database engine Object Library
Microsoft ActiveX Data Objects (Multi-dimensional) 6.0 Library
Microsoft Excel 16.0 ObjectLibrary
Microsoft Office 16.0 Object Library

Now, my second part of the code to display photo is:
CODE
myDir = CurrentProject.Path & "\ StudentPhotos\"
st_id = txtstudent_id
M = ".jpg"
ws.Shapes.AddPicture FileName = myDir And st_id & M, linktofile:=msofalse, savewithdocument:=msotrue, Lef:=19, Top:=10, Wudth:=120, Height:=120


When I run the code, now VBA display this message:Run-time error '13' Type Mismatch and add yellow colour on this line confused.gif
CODE
myDir = CurrentProject.Path & "\ StudentPhotos\"
st_id = txtstudent_id
M = ".jpg"
ws.Shapes.AddPicture FileName = myDir And st_id & M, linktofile:=msofalse, savewithdocument:=msotrue, Lef:=19, Top:=10, Wudth:=120, Height:=120


I have to study the meaning of such error. reading.gif

Cheers
Go to the top of the page
 
sal
post Sep 19 2018, 10:25 AM
Post#11



Posts: 35
Joined: 18-October 16



JoanSmith

At first sight, your recommendation is completely new for me. I have to read in depth.
For example the concepts: Enum stuf, MsoTriState Enum.

How do I know if I am using late binding o I am using early binding?

On the other hand, in order to understand your recommendation, I have been googling but, there's not a single example on how to display an image on Excel Worksheet from VBA MS Acces.

Thanks again
Go to the top of the page
 
JonSmith
post Sep 19 2018, 10:45 AM
Post#12



Posts: 3,910
Joined: 19-October 10



Great progress Sal!

Lets start with the references.
The Excel reference is spot on! Exactly what you needed to do to fix the msoFalse issue!
I think you've added more than you needed though.
'Microsoft ActiveX Data Objects (Multi-dimensional) 6.0 Library' is all about DAO objects. You might be using them but not in the snippet you gave.
'Microsoft Office 16.0 Object Library' I'm not 100% clear on the intended purpose of this library. I know it contains a filedialog picker that is pretty generic across office. Either way I don't think its used in your code.

You should be able to remove both of these references and still compile your code.


Now, lets look at some debuging techniques. Methods to debug are somewhat personal preference so I'll share what my approach would be.
I try it down to smaller parts and test each piece until I find what fails and keep breaking down into smaller pieces until the issue is clear.

So here, I would use the immediate window and test the value of all the variables being used. This can be done by typing ?myDir for example.

That being said however you have a few more structural issues.

.AddPicture is a method with parameters. The parameters are FileName, LinkToFile, SaveWithDocument etc, you have to enter each parameter in order or specify by parameter name followed by :=


LinkToFile:=msoFalse is valid for example.
You have FileName = myDir and thats not ok.
It must be FileName:=myDir


After this you also cannot concatenate a string using AND. You need to use (myDir & st_id & M).

Check your spelling of the parameters too. Wudth is likely supposed to be Width. Lef is likely supposed to be Left.

Edit
How do I know if I am using late binding o I am using early binding?

Early binding requires a reference set in VBA. After that you can use Excel objects so you can for example declare Dim ws as Excel.Worksheet. Early binding you must declare Dim ws as Object and the object type is assigned at runtime.
Some other differences are that with early binding the intellisense in VBA will work too.
A pitfall is that references aren't backwards compatible. If you reference Excel 2016 then anyone using Excel 2013 will get a missing reference error but early binding wont have that issue.
I can get into it more but don't want to overload you too much.


This post has been edited by JonSmith: Sep 19 2018, 10:48 AM
Go to the top of the page
 
sal
post Sep 19 2018, 11:08 AM
Post#13



Posts: 35
Joined: 18-October 16



GroverParkGeorge
Well, I went to be at 2: 00 AM.
I am just beginning to study your post and your link. I hope to get the key to fix my code.
I let you know.
Cheers
Go to the top of the page
 
JonSmith
post Sep 19 2018, 11:26 AM
Post#14



Posts: 3,910
Joined: 19-October 10



Oh, if I can also point out. I am JonSmith, not JoanSmith tongue.gif
Go to the top of the page
 
sal
post Sep 19 2018, 12:23 PM
Post#15



Posts: 35
Joined: 18-October 16



JonSmith.
I really apologize for my mistake.
I hope to find the way to edit my message.
Go to the top of the page
 
sal
post Sep 19 2018, 06:39 PM
Post#16



Posts: 35
Joined: 18-October 16



JonSmith. First, at all, I’d like to thank you for your time explaining to me the order logic of each parameter. Also, with your educational exposition of early binding and late binding.

From early this morning, I have been googling for the solution, but I have not found it and I can't figure it out.

Trying to run the code, I tried this lines
CODE
[code]wb.Shapes.AddPicture FileName:=myDir & st_id & M, linktofile:=False, Savewithdocument:=True, Left:=19, Top:=10, Width:=120, Height:=120
[/code]
VBA highlights in yellow the above line of code and displays Error 438 "Object Doesn't Support This Property or Method".

Then with this line:
CODE
[code]wb.Shapes.AddPicture FileName:=myDir & st_id & M, linktofile:=msofalse, Savewithdocument:=msotrue, Left:=19, Top:=10, Width:=120, Height:=120
[/code]
VBA highlights in yellow the above line of code and displays Error 438 "Object Doesn't Support This Property or Method".

I would like to know what is wrong with this two lines of code.
Can you walk me through how to do that? I really don't know how to do that.

I really appreciate your help.
Cheers
Go to the top of the page
 
JonSmith
post Sep 20 2018, 02:50 AM
Post#17



Posts: 3,910
Joined: 19-October 10



You keep changing little bits of this code! The original you posted didn't have this issue!

Ok, lets start with the meaning of the error. As I mentioned earlier .AddPicture is a method (Subs and Functions are also methods).
With all the built in VBA methods they are members of another object. In this case the AddPicture method is a member of the [Shapes] collection. If you tried to use AddPicture to say the [Charts] collection you would get the same error as there is no method to add a picture in that collection.

Now Shapes.AddPicture is correct, so we move a level up.
[wb.Shapes]

wb is a Workbook Object. If you look in the object browser (In VBA for to View>Object Browser) you can find the entry for Workbook and see all the members of that object, you'll see that shapes is not one of them.
This makes alot of sense. If I were to tell you to add a picture to a workbook you wouldn't have enough information to know where in the workbook.

If you search for the Shapes collection you'll see its a member of a Chart Object and a Worksheet Object. Hopefully you can see where it goes. Charts.AddPicture wouldn't work but if you did Charts.Item(1).Shapes.AddPicture then it should work as now you are specifying a Chart Object within the Charts collection and a Chart Object has the Shapes Collection.

With your code we want to use a Worksheet object. If you look at your original code you'll see that you were actually using Ws (A worksheet object variable) rather than Wb.

Hope that gives you all you need to know about what that error is and how it occurs.
Attached File(s)
Attached File  Capture.JPG ( 21.87K )Number of downloads: 3
 
Go to the top of the page
 
sal
post Sep 21 2018, 11:39 AM
Post#18



Posts: 35
Joined: 18-October 16



Jon.
I declared Dim ws As Worksheet
Now I tested.
With this line of code I am using the Intellisense
CODE
ws.Shapes.AddPicture FileName:=myDir & st_id & M, linktofile:=msoFalse, Savewithdocument:=msoTrue, Left:=190, Top:=10, Width:=120, Height:=120


VBA displays:Runtime Error 1004 FileName could not be found

Next line of code:
CODE
ws.Charts.Item(1).Shapes.AddPicture FileName:=myDir & st_id & M, linktofile:=msoFalse, Savewithdocument:=msoTrue, Left:=190, Top:=10, Width:=120, Height:=120

VBA dislays: Compilation Error
Method or Data Member not found

Also, I tested ?myDir
Immediate Window C:\Users\Me\Desktop\Students\ StudentPhotos\ StudentPhotos\
My Path is correct

Also, I In the Immediate Window ?ws
VBA displays Runtime ERROR '438'
Object doesn't support this property

ws in the Immediate Window
?ws
VBA displays Runtime ERROR '438'
Object doesn't support this property

I think my ERROR is in ws but, I have been unable to fix.

I feel sad for not correctly write my code despite the help you have given to me.

I already read at least 25 web pages concerning Error 1004 and Error 438.
To date I have reviewed about 25 web pages on how to repair those errors, but, I did not find the solution. I also looked for how to display an image in a worksheet from MS Access and did not find similar examples.

In conclusion, I read a lot, but the solution for my code is still invisible. I don't know what to do.

I hope you can still give me a little help.

Cheers

Go to the top of the page
 
sal
post Sep 25 2018, 09:15 PM
Post#19



Posts: 35
Joined: 18-October 16



GroverParkGeorge, JonSmith, cheekybuddha. Thanks

Finally, I have a code line to show an image on a worksheet, as I expected.
I include the code here in case it can be useful for someone else.

CODE
Option Compare Database
Option Explicit
Private Sub Form_Load()
    With Form_frm_search.Controls("St_image")
            .Visible = False
            .Picture = ""
    End With
cmbpic.SetFocus
End Sub

Private Sub cmbpic_Enter()
Combo9 = Null
    With Form_frm_search.Controls("St_image")
            .Visible = False
            .Picture = ""
    End With
End Sub

Private Sub cmbpic_Change()
    With Form_frm_search.Controls("St_image")
            .Visible = False
            .Picture = ""
End With
cmbsearch
cmbdelete_st_id.SetFocus
End Sub

Private Sub cmbsearch()
    With Form_frm_search.Controls("St_image")
            .Visible = False
           .Picture = ""
    End With
If VarType(cmbpic) = vbNull Then Exit Sub
On Error Resume Next
    With Form_frm_search.Controls("St_image")
            .Visible = Add_StPic(cmbpic.Value) <> ""
            .Picture = Add_StPic(cmbpic.Value)
    End With

Err.Clear
On Error GoTo 0
End Sub


I appreciate the help of the experts in this forum.

Cheers
Go to the top of the page
 
GroverParkGeorge
post Sep 25 2018, 09:17 PM
Post#20


UA Admin
Posts: 33,760
Joined: 20-June 02
From: Newcastle, WA


Congratulations on solving the problem and thanks for sharing your results.
Continued success with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th October 2018 - 03:30 PM