RecordSouce from sql (1 Viewer)

kirkm · Oct 17, 2019

How might I use the sql string here as the Record Source for my Form?

Code:

Function GetTitleMatch(sTitle) As DAO.Recordset
    Dim sql As String
    sql = "SELECT " & MyCompany & ".Artist, " & MyCompany & ".Title, " & MyCompany & ".Label, " & MyCompany & ".Year "
    sql = sql & "FROM [" & MyCompany & "] "
    sql = sql & " WHERE " & MyCompany & ".Title Like p0;"
    With CurrentDb.CreateQueryDef("", sql)
        .Parameters("p0") = SplitIt(sTitle)
        Set GetTitleMatch = .OpenRecordset
        .Close
    End With
End Function

Until using parameters I would get the sql, then set the Forms recordsource to the queryName e.g.

Code:

Dim MyQuery As QueryDef
Set MyQuery = db.QueryDefs(queryName)
MyQuery.sql = sql

DoCmd.OpenForm "TheForm", acFormDS, , stLinkCriteria

But now sql$ will contain "p0" and fail.

Galaxiom · Oct 17, 2019

You can set the recordset as the Recordset property of the Form.

Code:

Set Me.Recordset = GetTitleMatch(SomeString)

BTW It Also works with Listboxes and Combos.

Galaxiom · Oct 17, 2019

Also remove the Close from your function or your recordset won't be any use.

kirkm · Oct 17, 2019

Many thanks. I think I follow.. fair bit to change first.

isladogs · Oct 17, 2019

Whilst you are changing it, I would recommend you get rid of all this unnecessary complexity.
You really shouldn't need two functions GetTitleMatch and SplitIt and the use of query defs to construct the sql for your form record source.

kirkm · Oct 17, 2019

Hi Colin, That's worth knowing as I'm intending to improve code written some years ago. But of course I can only improve to the limit of my know-how. Sometimes the bottom line is just does it work or not. SplitIt just replaces " " with "*" so my query can use "Like". GetTitleMatch builds and returns the recordset. The only way I know to use this as source for a Form is to make it a query to base the Form on. I'm testing changes as Galaxiom said (after figuring out why the Close should be removed, as it seems to work for me).
How about an example of how you would do it, without unnecessary complexity ?

isladogs · Oct 17, 2019

To ensure any example code is relevant, can you explain the MyCompany part of your code. Are you using a fixed table for this and, if so, what is its name?
Or is the table name selected by the user from e.g. a combo box?

For info, I very rarely use parameters as indicated in your post though some other developers do so regularly. Nothing wrong with that approach but not the way I do things

kirkm · Oct 17, 2019

The table (fixed and linked to) is either "UK_Singles" or "US_Singles". Toggles by a button Click, and is retrieved from function MyCompany(). This may not be the best way either, I could change it.
The Parameters were the improvement. No quotes to worry about or (I think) casing. Maybe not essential, TBH I do forget all the reasons for them.

isladogs · Oct 17, 2019

Just going out for a few hours so I can't provide code at the moment.
The following is an outline of one approach you could use as its only one of two tables. Its not the neatest method but it may be a starting point

1. Have a combo or option group to select the table required.
2. Save that value as a string variable strTable
3. Open the form with strTable in the OpenArgs argument
4. In the load event for your form, use code similar to this which uses the actual table names:

Code:

Select Case Me.OpenArgs
Case "UK_Singles"
Me.Recordsource = …FROM UK_Singles..
Case Else
Me.Recordsource = …
End Select

Or use the [" & strTable & "] notation in that event for the Me.Recordsource and scrap the select case / open args part

See if that gets you started. Come back if you need help.
Someone else may offer a better solution in the meantime

NOTE
I use a more elegant generalised version of this instead of the select case in one of my production databases where there are about 100 possible tables to choose from. I can dig out the code if needed but probably not till this evening UK time.

June7 · Oct 17, 2019

Why two tables to begin with? Perhaps use 1 table with a field to identify record as UK or US and apply filter to form? If you cannot change source data structure, then I agree with Colin that should set RecordSource property with SQL statement, not use QueryDefs and Recordset. I've never used Parameters.

isladogs · Oct 17, 2019

Agree with June's point about combining the tables. If you do that, it will significantly simplify coding both now and in the future.
However, if you stick with having two tables, let us know if you need help with my earlier suggested code

kirkm · Oct 17, 2019

Hi June and Colin, thanks for the messages. It's nice to get some other ideas on methods etc.

Maybe I should give you a rundown on the whole task.. as there's a whole process setting up the query before a Form is used. And if only one hit from the query, it's applied directly to another table, and the Form is not called. It's function is just to select the right item from multiple hits.

That aside I'm first going to see what the Form does when it's recordset is set as per Colins steps rather than how I was doing it.

As for two or one table.. there are two and either way a name is needed. Adding a new ID field and filtering the output seems more work than picking a name. No ? They're also used by other things. Both have the same structure though.

kirkm · Oct 18, 2019

Colin, in Form load what is
Me.Recordsource = …FROM UK_Singles..
Supposed to do? I presume …FROM UK_Singles.. is an sql select string ?

That's correct. It would be the SQL string you want when data from the UK_Singles table is used as the form record source

June7 · Oct 18, 2019

Colin just didn't spell out the entire SQL statement. The ellipsis (…) means you need to fill in missing parts.

I prefer to apply filter than to select tables. However, I do have one situation where I must select a table and build RecordSource SQL statement.

kirkm · Oct 18, 2019

I did fill them in June... ended up with
Me.Recordsource = valid SQLstring
But all it did was give me errors.

kirkm · Oct 19, 2019

> But all it did was give me errors.

Do you folk not get an error ?

June7 · Oct 19, 2019

You need to show your actual attempted code and exact error message.

kirkm · Oct 19, 2019

There's very little code to show - just as Colin said - in Form Load I have

Code:

Me.Recordset = "Select * from UK_Singles"

The error is Run-time error '91':

Object variable or With block variable not set

I'm puzzled why I'd get this but seemingly not Colin.

June7 · Oct 19, 2019

Colin's example uses RecordSource not Recordset.

kirkm · Oct 19, 2019

Thanks June that was my mistake. Now no error.. but not sure now why I was doing that !
As I already have a recorsdet. Puzzling.. but good to see what that error was.

RecordSouce from sql (1 Viewer)

Registered User.

Super Moderator

Super Moderator

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

AWF VIP

MVP / VIP

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread