Query pulling from form

babin_22

New member
Local time
Yesterday, 19:28
Joined
Aug 10, 2007
Messages
6
I am having an issue pulling information from a form into a query. In my criteria column in the query i have:

[Forms]![frm_createcsv]![txtCriteria]

it pulls from a valid string from a textbox txtCriteria.

My vba code is:

Dim query as string
query = "pkg_not_registered_email"

filename = "C:\querytester\" & Format(Date, "YYYY_MMDD") & "_" & cboCountry.Value & "_" & ".csv"

DoCmd.TransferText acExportDelim, "", query, filename
DoCmd.Open query

____________________

This exact code worked previously with another function and a different query so I do not understand why it is not working now! Help! :(
 
Last edited:
I am having an issue pulling information from a form into a query

Do you get an error message or prompt of some kind? If so, what is the message? Does a file get created at all? If so, does it contain any content?

Kinda hard to help when you don't tell us exactly what is going wrong.

As a shot in the dark, maybe try wrapping your [Forms]![frm_createcsv]![txtCriteria] criteria in an Eval() function:

Eval("[Forms]![frm_createcsv]![txtCriteria]")

You should also dim the variable filename as you have with query.
 
Query_pulling_from_form

The query runs fine but it pulls no values - i've checked the info from the textbox that is being pulled in and it shows a value (in this instance it's states)

"CO" OR "AZ" OR "CA"

And prints a CSV but with nothing in the CSV (assumabely because it's resulting in no results in the query)!

Now when i type this criteria in manually it works perfectly fine. I've even tried copying and pasting what gets pulled from the form, i'm just not understanding why it's not working!
 
I think the problem here is that I'm not sure you can input the operators into the text string in a text box and use that as criteria parameter. I think that what you query sees in your criteria is analogous to
"'CO' OR 'AZ' OR 'CA'".

To get around this you either need to build the sql of your query dynamically in vba using something like...

strSQL = "Select...whatever...From...tablename...Where [State]=" & Forms!FormName!txtCriteria & ";"

or else multiple citeria textboxes on your form, each containing only one state name and have your criteria set analogous to the following:

Like ([Forms]![frm_createcsv]![txtCriteria1] & "*") Or [Forms]![frm_createcsv]![txtCriteria2] Or [Forms]![frm_createcsv]![txtCriteria3] Or [Forms]![frm_createcsv]![txtCriteria4] Or [Forms]![frm_createcsv]![txtCriteria5] Or [Forms]![frm_createcsv]![txtCriteria6] Or [Forms]![frm_createcsv]![txtCriteria7] Or [Forms]![frm_createcsv]![txtCriteria8] Or [Forms]![frm_createcsv]![txtCriteria9] Or [Forms]![frm_createcsv]![txtCriteria10] Or [Forms]![frm_createcsv]![txtCriteria11] Or [Forms]![frm_createcsv]![txtCriteria12] Or [Forms]![frm_createcsv]![txtCriteria13] Or [Forms]![frm_createcsv]![txtCriteria14] Or [Forms]![frm_createcsv]![txtCriteria15]
...etc
 
query crazyness...

That thought crossed my mind as well which is why i tried running the criteria with only one value ie "CO" , and yet it still returns nothing.
 
Hmm. Can you post a stripped down version of your db?
 
DB Code for query,

Here is the click_event that calls the query.

Private Sub cmdCSVStates_Click()
On Error GoTo Err_cmdCSVStates_Click

Dim filename As String
Dim query As String

query = "pkg_not_registered_email"
filename = "C:\querytester\" & Format(Date, "YYYY_MMDD") & "_" & cboCountry.Value & "_" & ".csv"

'Will not allow me to pull from variable must assign to object and pull from there

txtCriteria.Visible = True
txtCriteria.SetFocus
txtCriteria.text = criteria

If txtDate = "" Then
MsgBox "You must enter a date to create a csv"
Else

'Query pulls date , country and state directly from form

'Collects PID's for all courses registered.
DoCmd.OpenQuery "pkg_not_Registered_pid"
DoCmd.Save acQuery, "pkg_not_Registered_pid"
DoCmd.Close acQuery, "pkg_not_Registered_pid"

'Open query and create csv

DoCmd.OpenQuery query
DoCmd.TransferText acExportDelim, "", query, filename
MsgBox "CSV created!"

End If
Exit_cmdCSVStates_Click:
Exit Sub

Err_cmdCSVStates_Click:
MsgBox err.Description
Resume Exit_cmdCSVStates_Click

End Sub
 
I'm gonna have to scoot in 10 (going out of town for the weekend).

The code in the click event wasn't what I wanted to see in the db. I was wanting to examine the actual pkg_not_registered_email query itself and check form references etc. Sometimes it's just a lot easier to troubleshoot 'hands-on' than by playing forum-tag.

Maybe someone else will chime in.
 

Users who are viewing this thread

Back
Top Bottom