Calling "Me" in a SQL expression.

[LPurvis]

>> The SQL route didn't work out as well as I had hoped.

You don't really offer any reason as to why that was.
Poor performance? You couldn't wrap your head around the methods? You feel the methods just aren't up to the task?
I'm not saying I'd disagree, just that some information for future readers can only be a good thing.

Similarly:
>> The big problem I had going with VBA was how/where I was supposed to interact with the forms

What have you tried. Have you looked at the example search forms that are kicking about (or even the one I mentioned)? They must show some clue of acquiring form values when building your SQL string in code.

I'm not sure why you're using tables to store and receive the criteria and resulting SQL. If you're happy with that then that's fine.
But it shouldn't make the process easier.
How are you then using this table filled with parameter values?

Cheers.

 

[eTom]

>> The SQL route didn't work out as well as I had hoped.

You don't really offer any reason as to why that was.
Poor performance? You couldn't wrap your head around the methods? You feel the methods just aren't up to the task?
I'm not saying I'd disagree, just that some information for future readers can only be a good thing.

Mostly can't wrap my head around the methods. I have some past experience with C++, JavaScript and HyperTalk (way back in the day) so I'm far more comfortable with something a little more familiar. In terms of IF statements and that sort of logic, building a VBA one like this seems far simpler:

IF Parameter = This THEN 
Form.RecordSource = "Simple SQL Statement here" 

ELSE IF Parameter = That THEN
Form.RecordSource = "Other SQL Statement"

I can keep adding Else Ifs until I've covered all the search options in the Option Group Box (which has allowed me to use a single Text Box for input, incidentally.)

Similarly:
>> The big problem I had going with VBA was how/where I was supposed to interact with the forms

What have you tried. Have you looked at the example search forms that are kicking about (or even the one I mentioned)? They must show some clue of acquiring form values when building your SQL string in code.

What I've been working on so far had been SQL statements directly, and trying to do the IF logic in there. Aside from the SQL editor being less than ideal to look at and work with, it got REALLY complicated really quickly.

I'm not sure why you're using tables to store and receive the criteria and resulting SQL. If you're happy with that then that's fine.
But it shouldn't make the process easier.
How are you then using this table filled with parameter values?

Cheers.

Oops, that's my mistake. When I said tblSearchParameters... I don't know what I was thinking. SearchParameters and SearchResults are both forms. One to enter the parameters and one to display results. I haven't had my coffee yet this morning, sorry!

 

[LPurvis]

As I say, I don't dissagree with the choice.
Yes, database development is a different discipline to being an out and out programmer - but if you're going to be working with databases a lot then learning SQL and the set based ethos is going to be important.

None the less - in this instance the chances are, by trying to perform all the logic in a single SQL statement, you will be adding inefficiency to your application and creating the single statement you want in code is a better option.

Hope all continues to go well.

 

[eTom]

As I say, I don't dissagree with the choice.
Yes, database development is a different discipline to being an out and out programmer - but if you're going to be working with databases a lot then learning SQL and the set based ethos is going to be important.

None the less - in this instance the chances are, by trying to perform all the logic in a single SQL statement, you will be adding inefficiency to your application and creating the single statement you want in code is a better option.

Hope all continues to go well.

It's actually starting to come together. I had some issues with typos here and there, which I fixed by taking the SQL statement out of the VBA and just putting it straight into the form to see if it worked. I tweaked it til it was correct and then put it back into the IF statement and tweaked that til it worked. Slowly but surely!

  If Forms!SearchParameters.SearchBy.Value = 1 Then
    Me.RecordSource = "SELECT tblIncidents.* FROM tblIncidents WHERE (((tblIncidents.ContactName)=([Forms]![SearchParameters]![SearchInputBox]))); "
    
  ElseIf Forms!SearchParameters.SearchBy.Value = 2 Then
    Me.RecordSource = "SELECT tblIncidents.* FROM tblIncidents WHERE (((tblIncidents.DateSubmitted)=([Forms]![SearchParameters]![SearchInputBox]))); "
  
    End If

"SearchBy" is an OptionGroup with three radio buttons that I've implemented.

Here's what I'm stuck with now. I'm pulling data from tblIncidents. It's linked to tblIncidentDetails via a primary key IncidentID (one to many.) From what I've read, it's a standard order entry scenario where you enter one Incident/Order and with a subform on the main form you can add multiple IncidentDetails/Products.

With my IF statement, I can use it to search by Date and Customer Name, as they are both on the main tblIncidents. How do I search by "LotNumber" if it's a field from the tblIncidentDetails ? I am pretty sure this just comes down to me not fully understanding the SQL syntax.

Can I use AND or another operator to include two tables, for example?

 

[Simon_MT]

My database starting getting precious with "Too Many databases are open", admittedly this was Access 1997 so I converted all my Lookups to VBA Modules no Form Modules as I only wanted one Lookup and needed to reuse the code

so on the form each combibox has Lookup call on Entry. Here is an example:

Function LookupCountry()
Dim MyControl As Control
        Set MyControl = Screen.ActiveControl
    With CodeContextObject
        MyControl.RowSource = "SELECT CountriesQuery.Country, CountriesQuery.[Country Desc], CountriesQuery.[Country Region Ref] FROM CountriesQuery WHERE CountriesQuery.[Country Region Ref]= '" & .[Region] & "' ORDER BY CountriesQuery.[Country Desc];"
        Call ListDisplay
    End With
End Function

ListDisplay force the List to dropdown if empty.

Simon

 

[eTom]

I started looking through the help files and found INNER JOIN. I was having trouble with the Syntax, so I created a query using the Query Wizard that essentially just added all fields from both tables. I then looked at the SQL view and took this from it:

INNER JOIN tblIncidentDetails ON tblIncidents.[IncidentReportID] = tblIncidentDetails.[IncidentReportID]

It seems to work now, yay!

Thanks again for all the help everyone!

 

[eTom]

Here's another one that has me stymied:

ElseIf Forms!SearchParameters.SearchBy.Value = 4 Then
    Me.RecordSource = "
SELECT tblIncidents.* 
FROM tblIncidents 
INNER JOIN tblIncidentDetails ON tblIncidents.[IncidentReportID] = tblIncidentDetails.[IncidentReportID] 
WHERE (((tblIncidentDetails.ItemNumberID) = ([Forms]![SearchParameters]![SearchInputCombo]))) 
ORDER BY DateSumitted; "

I've added a non-visible combo box that can be used to select a product if you want to search by that. By selecting "product" in the SearchBy group, the combo box becomes visible and the text box becomes invisible. It works, but I wanted to add an ORDER BY, as some of our products return quite a few records, and usually we know the general date we're looking for.

For some reason, on opening, the form asks me to input DateSubmitted. I've tried using tblIncidents.DateSubmitted as well, but it still asks.

I had thought that by SELECTing tblIncidents.* that all the fields would be accessible.

Any thoughts?

Thanks again,

eTom

note: I broke the SQL statement into various lines, but in my actual VB code it's of course all one line after the ELSE IF.

 

[LPurvis]

One piece of development advice I rarely see offered but something I live by...
Copy and Paste.
I very rarely type object names if I can avoid it, and I rarely have typos.

ORDER BY DateSumitted
not
DateSubmitted

Cheers

 

[eTom]

One piece of development advice I rarely see offered but something I live by...
Copy and Paste.
I very rarely type object names if I can avoid it, and I rarely have typos.

ORDER BY DateSumitted
not
DateSubmitted

Cheers

Oof. Now I feel stupid. I had assumed typos and had even re-typed it. I wonder how I spelled it incorrectly the first time!

 

[eTom]

Yet another seemingly simple question, I think.

With the sub form, it lists all the products associated with the given event. I want to add a text box that has the values of a few fields.

ie: It's currently broken down to BrandID, ProductID, etc. I want to list in the text box BrandName & ProductName (both are stored in separate tables that are linked via the IDs)

Seems easy enough to do with VB, but I don't know what event to use. It's a snapshot mode form, so there's no updating to do After Update, etc... but On Load doesn't seem to work either since it populates every record with the data based on the first record.

If it were a report, I'd go with On Format I'd imagine, but I'm not sure where to go here.

I guess it should have been a report rather than a form!

 

[LPurvis]

Where are you wanting this related concatenated text data to appear?
In the subform?

Then just add those related tables into the subform's recordsource.
The data will be available and you can add a calculated textbox expression to concatenate the data per row.
e.g.
= [BrandName] & " : " & [ProductName]

 

[eTom]

Where are you wanting this related concatenated text data to appear?
In the subform?

Then just add those related tables into the subform's recordsource.
The data will be available and you can add a calculated textbox expression to concatenate the data per row.
e.g.
= [BrandName] & " : " & [ProductName]

I didn't know you could do that! That's great!

The problem I have with that is that my SQL statement is set by a VBA Else If statement, so I'm unsure how to add the required tables manually.

Me.RecordSource = "SELECT tblIncidents.* FROM tblIncidents INNER JOIN tblIncidentDetails ON tblIncidents.[IncidentReportID] = tblIncidentDetails.[IncidentReportID] WHERE (((tblIncidentDetails.ItemNumberID) = ([Forms]![SearchParameters]![SearchInputCombo]))) ORDER BY DateSubmitted; "

I tried to nest a second INNER JOIN in there, but couldn't get it to work.

tblIncidents is linked to tblIncidentDetails via IncidentReportID
tblIncidentDetails is linked to tblProducts via ItemNumberID

There is now a ProductDescription field in tblProducts that contains the information I want to use in this text box on the subform.

Can multiple INNER JOINS be nested together?

 

[LPurvis]

So you only need to include tblProducts in the query?
It sounds as if ProductDescription might be containing duplicate data though? Or have you dispensed with the distinct Brand and Product fields?

Removing redundant brackets too and including some table aliases to keep the statement tidier...

Me.RecordSource = "SELECT tblIncidents.* FROM " & _
    "(tblIncidents I INNER JOIN tblIncidentDetails D ON I.IncidentReportID = D.IncidentReportID) " & _
    "INNER JOIN tblProducts P ON D.ItemNumberID = P.ItemNumberID) " & _
    "WHERE D.ItemNumberID = [Forms]![SearchParameters]![SearchInputCombo] " & _
    "ORDER BY DateSubmitted"

 

[eTom]

So you only need to include tblProducts in the query?
It sounds as if ProductDescription might be containing duplicate data though? Or have you dispensed with the distinct Brand and Product fields?

Removing redundant brackets too and including some table aliases to keep the statement tidier...

Me.RecordSource = "SELECT tblIncidents.* FROM " & _
    "(tblIncidents I INNER JOIN tblIncidentDetails D ON I.IncidentReportID = D.IncidentReportID) " & _
    "INNER JOIN tblProducts P ON D.ItemNumberID = P.ItemNumberID) " & _
    "WHERE D.ItemNumberID = [Forms]![SearchParameters]![SearchInputCombo] " & _
    "ORDER BY DateSubmitted"

I must say I like the idea of aliases and using " & _ to continue the code on the next line. That is definitely going to clean things up!

I get a syntax error in FROM clause, however. I tried removing the aliases, putting it all back into one line, and doing both, but to no avail. Is it a bracket issue, do you think?

 

[LPurvis]

There is a trailing bracket and a missed alias

Me.RecordSource = "SELECT I.* FROM " & _
    "(tblIncidents I INNER JOIN tblIncidentDetails D ON I.IncidentReportID = D.IncidentReportID) " & _
    "INNER JOIN tblProducts P ON D.ItemNumberID = P.ItemNumberID " & _
    "WHERE D.ItemNumberID = [Forms]![SearchParameters]![SearchInputCombo] " & _
    "ORDER BY DateSubmitted"

It's easier when you're familiar with the objects you're working with.
Otherwise it's more like painting by numbers, you're having to check what goes where instead seeing the overall picture.

Cheers.

 

[eTom]

Here is what I've done so far:

Me.RecordSource = "SELECT I.* FROM (tblIncidents I " & _
     "INNER JOIN tblIncidentDetails D ON I.[IncidentReportID] = D.[IncidentReportID]) " & _
     "INNER JOIN tblProducts P ON D.ItemNumberID = P.ItemNumberID " & _
     "WHERE D.ItemNumberID = [Forms]![SearchParameters]![SearchInputCombo] " & _
     "ORDER BY DateSubmitted"

The only way I could get passed the syntax error on FROM clause was to put the first open ( right after the FROM , and close it before the second INNER JOIN.

After that, I get an error saying that Access database engine does not recognize tblIncidents.* as a valid field name or expression. I had to change that to the alias, even though it's not defined until *afterwards*.

Is that normal?

 

[eTom]

There is a trailing bracket and a missed alias

Me.RecordSource = "SELECT I.* FROM " & _
    "(tblIncidents I INNER JOIN tblIncidentDetails D ON I.IncidentReportID = D.IncidentReportID) " & _
    "INNER JOIN tblProducts P ON D.ItemNumberID = P.ItemNumberID " & _
    "WHERE D.ItemNumberID = [Forms]![SearchParameters]![SearchInputCombo] " & _
    "ORDER BY DateSubmitted"

It's easier when you're familiar with the objects you're working with.
Otherwise it's more like painting by numbers, you're having to check what goes where instead seeing the overall picture.

Cheers.

Yes, it does seem like that sometimes, though I'm slowly getting little concepts here and there.

We must have been posting at almost the same time there, my email went off just after I hit Submit Reply!

 

[LPurvis]

Posts have crossed in the ether.

Don't think of the statement clauses as happening after another.
Though the statement opens with the SELECT clause, that is merely the syntax.
The FROM clause determines what the objects are and what they're called (aliased).
Those names have to be used consistently throughout. There is no before or after in a single statement like this.

Cheers.

 

[eTom]

See? It's those kinds of concepts that will take me some time. Any coding I've ever done has ingrained a hierarchy and order of operations that is going to be tough to let go!

 

[LPurvis]

Try not to think of SQL as coding in the programming sense.
It is fundamentally a set based paradigm. You're acting upon sets of data (to all intents and purposes simultaneously, though that's actually the perview of the database engine to operate upon the set as it does).
The syntax of the language reflects that too. You execute a statement which is considered as a statement. Not a series of commands.

I consider being a database developer to be a notably different vocation than being a programmer. (I very much consider myself the former, though we often get labelled as the latter because it is involved).

Cheers.