Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-16-2019, 04:36 PM   #1
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
RecordSouce from sql

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.

kirkm is offline   Reply With Quote
Old 10-16-2019, 04:46 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,645
Thanks: 97
Thanked 1,499 Times in 1,414 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: RecordSouce from sql

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 is offline   Reply With Quote
Old 10-16-2019, 04:51 PM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,645
Thanks: 97
Thanked 1,499 Times in 1,414 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: RecordSouce from sql

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

Galaxiom is offline   Reply With Quote
Old 10-16-2019, 05:55 PM   #4
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: RecordSouce from sql

Many thanks. I think I follow.. fair bit to change first.
kirkm is offline   Reply With Quote
Old 10-16-2019, 10:12 PM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,913
Thanks: 114
Thanked 2,982 Times in 2,712 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: RecordSouce from sql

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-16-2019, 10:42 PM   #6
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: RecordSouce from sql

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 ?
kirkm is offline   Reply With Quote
Old 10-16-2019, 10:57 PM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,913
Thanks: 114
Thanked 2,982 Times in 2,712 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: RecordSouce from sql

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-16-2019, 11:31 PM   #8
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: RecordSouce from sql

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.
kirkm is offline   Reply With Quote
Old 10-17-2019, 12:00 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,913
Thanks: 114
Thanked 2,982 Times in 2,712 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: RecordSouce from sql

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 10-17-2019 at 06:41 AM.
isladogs is offline   Reply With Quote
Old 10-17-2019, 12:28 AM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,401
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: RecordSouce from sql

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-17-2019 at 12:34 AM.
June7 is offline   Reply With Quote
Old 10-17-2019, 06:40 AM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,913
Thanks: 114
Thanked 2,982 Times in 2,712 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: RecordSouce from sql

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-17-2019, 02:52 PM   #12
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: RecordSouce from sql

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 is offline   Reply With Quote
Old 10-17-2019, 04:02 PM   #13
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: RecordSouce from sql

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

Last edited by isladogs; 10-17-2019 at 04:10 PM.
kirkm is offline   Reply With Quote
Old 10-17-2019, 05:53 PM   #14
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,401
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: RecordSouce from sql

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 10-17-2019, 06:18 PM   #15
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: RecordSouce from sql

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

kirkm is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't clear recordsouce after unhide columns DeveloperSteve Modules & VBA 3 09-11-2012 09:52 AM




All times are GMT -8. The time now is 04:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World