button invokes procedure to export xml file from query using form value as criteria!

[sgottfried]

Hi all,



Sometimes Access VBA programming makes me feel so stupid! I think that I'm doing what I should but it is not working!

I have a button on a form that does the following On Click



Private Sub DiveXML_Click()


Dim xmlname As String
Dim divename As String


divename = Me.diveID


xmlname = "C:\projectname\" & divename & ".xml"


Application.ExportXML ObjectType:=acExportQuery, _
DataSource:="FirstPassDiveInfo", _
DataTarget:=xmlname


MsgBox "Export operation completed successfully."
End Sub

FirstPassDiveInfo is a query which needs to use a value on that form as criteria for the query ([Forms]![CruiseDives]![diveID])

But when I click the button, it prompts me for the value [Forms]![CruiseDives]![diveID] instead of just getting the value itself.

What am I doing wrong?

Thanks!

[Dal Jeanis]

Yeah, there's a lot of details to know. The answer is this: Jet (the Access database engine) doesn't have any accessibility to the controls on a form, so that's an unresolved reference.

One way to get what you are after is to have the AfterUpdate event of the diveID control on the form create (or set) a [TempVars].[diveID] variable. Jet has access to TempVars. Here's a sample of TempVars usage: https://www.accessforums.net/code-re...ars-36353.html

A second way would be to build the SQL for the query using VBA, and concatenating in the value of whatever is in the diveID control.

[sgottfried]

I very much appreciate your taking the time to answer my question. Yes, there is so much I don't know about Jet and what it can and can't do. How can one figure all of this out!? When I try to look up things online, I sometimes find the answer and I sometimes just feel like there is too much complexity about the language.

I will use your TempVars to solve my problem. The SQL query would be very, very complicated.

Thanks again!
Susan

[Dal Jeanis]

We've all been there. Just do your best, and keep asking questions. You can't understand and remember the answers if you haven't formed the question yet.

[sgottfried]

Thanks for that. Would it be OK for me to set the TempVars value like this instead of on AfterUpdate? The dive records are added programmatically and then the button is on the form where the dive information is displayed. No one is entering the diveID so I'm not sure that AfterUpdate is appropriate. I just need to export the dive information into an xml output file.

Private Sub DiveXML_Click()


Dim xmlname As String
Dim divename As String


divename = Me.diveID
Application.TempVars.Add "dive_identifier", divename


xmlname = "C:\projectname\" & divename & ".xml"


Application.ExportXML ObjectType:=acExportQuery, _
DataSource:="FirstPassDiveInfo", _
DataTarget:=xmlname


MsgBox "Export operation completed successfully."
End Sub

So what is the proper syntax to use in the criteria of my query? This doesn't work....

[TempVars]![dive_identifier].[Value]

How do I reference the value I set?

[Dal Jeanis]

You would use

[TempVars].Item("dive_identifier").Value

Depending on how you use it, you might need to double the double-quotes.

strSQL = strSQL & " WHERE [MyTable].diveid = [TempVars].Item(""dive_identifier"").Value  "

[sgottfried]

I used this in the query: [TempVars].Item("dive_identifier") and it at least let me save it without complaining about syntax. But when I run it, I get Undefined function '[TempVars].Item' in expression. Sigh. I'll keep looking....

[Dal Jeanis]

Try Tempvars!dive_identifier and/or [Tempvars]![dive_identifier]. According to several sites, both should work.

http://stackoverflow.com/questions/1...-with-tempvars
http://social.msdn.microsoft.com/For...e-in-web-forms

[sgottfried]

Then is just prompts me for TempVars!dive_identifier so I am back to where I was...

So, you didn't see any problem with how I set the TempVars in the code, then? I found this:

TempVars Collection
The Add method is how you add new variables to the collection.

TempVars.Add Name, ValueWhere Name is the name of your variable and Value is of course the value you wish to assign it. Some examples:


TempVars.Add "strAppName", "Test Application"TempVars.Add "lngClientID", 123456789TempVars.Add "bolLoginAgain", TrueOnce assigned you can now use the value in your queries and code using the following methods:


TempVars!strAppNameTempVars.Item("strAppName")Temp Vars.Item(0)All three methods above would return "Test Application".

so it appears that what I'm doing and what you are suggesting should work. The query I am using joins four tables and pulls variables from each of them for the xml export. There are no sorts or anything other than that I want diveID to equal what is on the form when I click the button. I appreciate your sharing your knowledge. This should not be this hard, right?

[sgottfried]

Well, I solved the problem by using the WhereCondition in my ExportXML call.

Application.ExportXML ObjectType:=acExportQuery, _
DataSource:="FirstPassDiveVideo", _
DataTarget:=xmlname, _
WhereCondition:="diveID = '" & TempVars!dive_identifier & "'"

By invoking the ExportXML command without the WhereCondition and trying to use the TempVars!dive_identification as the query criteria, it just wouldn't work. I still don't know why, but I wanted you to know how I got this resolved.

By the way, I do have other instances in my database where I call queries from a button and use Forms!formname!fieldname as a query criteria and that has always worked for me. So, i just don't know why this wouldn't.

I appreciate your trying to help me! For those of us in la-la land, it helps to have people like you who are willing to share their expertise.

[Dal Jeanis]

I just tested in a junk database, and a saved query with the following syntax worked fine:

 WHERE T3.pcParent = TempVars!guyname

I would bet that the issue was that you needed the single-quotes in what you stored in dive_identifier. Your earlier code might have worked with this line:

Application.TempVars.Add "dive_identifier", "'" & divename & "'"

[sgottfried]

My problems stem from the query being invoked through the ExportXML method. Every other way I tried, it worked fine. The ExportXML method did not allow the query to recognize the TempVars or the Form variables.