Query (SQL) with VBA 2

[danvzla]

Hi.

I got a Form "X" that has a lot of comboboxes and texboxes. This form also has a subform "Y" that display a table with the results of a query that depends on the comboboxes and texboxes of Form "X".

Now I'm using in the criteria of the query the texts of the different comboboxes with the operator OR. I would like to use an AND but because only one combobox has the focus at the moment it only takes the text of that one for making the query, so the result is allways blank.

How can I trigger a VBA procedure with a button in the form "X" to do the query and show the correct data on the subform/form. How can I do the query with VBA?

This is the SQL code:

SELECT LogDatafill_Table_NORCROSS.[Tracking Number], LogDatafill_Table_NORCROSS.Request, LogDatafill_Table_NORCROSS.Date, LogDatafill_Table_NORCROSS.Time, LogDatafill_Table_NORCROSS.APPROVED, LogDatafill_Table_NORCROSS.Tech, LogDatafill_Table_NORCROSS.RNWE, LogDatafill_Table_NORCROSS.Comment, LogDatafill_Table_NORCROSS.Hyperlink
FROM LogDatafill_Table_NORCROSS
WHERE (((LogDatafill_Table_NORCROSS.Market)=[Forms]![LogFileForm]![ComboMarketText].[Text])) OR (((Left([ID],5))=[Forms]![LogFileForm]![LogIDTxt].[Text])) OR (((LogDatafill_Table_NORCROSS.Date)=[Forms]![LogFileForm]![cboStartDate].[Text])) OR (((LogDatafill_Table_NORCROSS.Request)=[Forms]![LogFileForm]![Combo8].[Text]))
ORDER BY Left([ID],5);


Thanks!

Daniel

 

[Quehay]

Daniel: I haven't understood your question, and my guess is that others haven't either. Try posting again.


Now I'm using in the criteria of the query the texts of the different comboboxes with the operator OR. I would like to use an AND but because only one combobox has the focus at the moment it only takes the text of that one for making the query, so the result is allways blank.


Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[JoyInOK]

Quehay's right, it was difficult to follow the wording of your question, but I think I get the jist of it. The SQL you provided is the recordsource of your subform, Y. The query pulls data from the combo boxes and refreshes as the selection in the combobox changes. You want to dynamically set the query's criteria in code so you can use the contents of several comboboxes on the form, having the user click a button to refresh the query when they are finished setting all the criteria, correct?

 

[MakeItSo]

Hi Daniel,

I recommend a Refresh button on your X form in order to avoid trouble with NULL values.

put this in the Click() event of your refresh button on the X form:

[blue]
Dim sSQL as String

sSQL="SELECT [Tracking Number], " & _
"Request, [Date], [Time], APPROVED, Tech, RNWE, Comment, " & _ 
"Hyperlink FROM LogDatafill_Table_NORCROSS " & _
"WHERE (((Market) LIKE '*" & [ComboMarketText] & _
"*')) OR (((Left([ID],5)) LIKE '*" & [LogIDTxt] & _
"*')) OR ((([Date])=#" & [cboStartDate] & _
"#)) OR (((Request)LIKE '*" & [Combo8] & "*')) " & _
"ORDER BY Left([ID],5);"

Me.Y.Recordsource=sSQL
Me.Y.Requery

[/blue]

You might need to catch a possible error with the Date field.

Beware:
You should rename your fields [Date] and [Time] to something else, e.g. [LogDate] and [LogTime], since date and Time are reserved words and can cause trouble 6 side effects.

Greetings,
MakeitSo


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de