Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-03-2019, 04:40 PM   #1
silversun
Newly Registered User
 
Join Date: Dec 2012
Posts: 53
Thanks: 25
Thanked 0 Times in 0 Posts
silversun is on a distinguished road
A query to call in VBA

Hi all,
I have no experience with this and sometimes I need more clarification in technical parts.
I have a select query with multiple tables as you can see in the attached image. I also have its SQL code here:
Quote:
Private Function someSQL()
sq = "SELECT tbl_customers.fName, tbl_customers.lName, tbl_carsOwners.car_year, tbl_makes.makes, tbl_models.models, " & _
"tbl_colors.color, tbl_carsOwners.lisencePlate, tbl_carsOwners.oilRange, tbl_carsOwners.owner_ID " & _
" FROM tbl_models " & _
" INNER JOIN (tbl_makes " & _
" INNER JOIN (tbl_customers " & _
" INNER JOIN (tbl_colors " & _
" INNER JOIN tbl_carsOwners " & _
" ON tbl_colors.color_ID = tbl_carsOwners.color_id) " & _
" ON tbl_customers.customer_ID = tbl_carsOwners.customer_id) " & _
" ON tbl_makes.makes_ID = tbl_carsOwners.make_id) " & _
" ON tbl_models.models_ID = tbl_carsOwners.model_id; "
Debug.Print rs.RecordCount
End Function
This is my first experience bringing SQL in VBA.
I need to have a combo box that its bound column sends the owner_ID. I need this query to list all the records where refer to given owner ID and all info (fName, lName, make, model, color and license plate) from other tables.
When I call this function in immediate window it shows 25 as number of rows (25 is the number of rows in another related table that is not called in this query). I was expecting to see the total number of rows (8 rows) in my query as it shows in "Datasheet view" when I run the query.

Please help.
relationships.JPG


Last edited by silversun; 09-03-2019 at 06:02 PM.
silversun is offline   Reply With Quote
Old 09-03-2019, 05:23 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,783
Thanks: 57
Thanked 1,267 Times in 1,248 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: A query to call in VBA

Hi. Are you able to post a sample copy of your db?
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 09-03-2019, 05:42 PM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,552
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: A query to call in VBA

If you were expecting anything to happen when you executed that function, you will be sadly disappointed. All you are doing is defining the SQL string. To execute it, you would need to include something like

Code:
CurrentDB.Execute sq
but worse, sq is not declared in your sub and neither is the rs that is used at the end of the SQL. Who knows WHAT question it is currently answering when you run that? If sq and rs are defined externally, you just introduced something called a "side effect" - in that the subroutine code depends on factors outside of itself but that were not passed in as parameters.

Then, there is the matter that you have concatenated a non-SQL action (the "debug.print") into an SQL string. That shouldn't work either. The command parser that interprets VBA is totally different from the query parser that interprets SQL. But you put those two strings together in what can only be a confusing context for VBA.

Finally, if you REALLY wanted to get a count, you might only need a DCount function, though I have to say that the criteria clause in that thing would be a nightmare.

If you created a named (i.e. permanent or stored) query that was the equivalent to that string you showed us (minus the debug.print), let's call it SSMakeModelQuery, you could get your record count with

Code:
DCount( "*", "SSMakeModelQuery" )

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-03-2019, 06:12 PM   #4
silversun
Newly Registered User
 
Join Date: Dec 2012
Posts: 53
Thanks: 25
Thanked 0 Times in 0 Posts
silversun is on a distinguished road
Re: A query to call in VBA

@ The_Doc_Man
Sorry for my typo when pasting the code. The "Debug.Print ..." line is not part of the string.
sq and rs are declared in very top part of my VBA module to make sure all the functions have access when needed. The rs is declared as Recordset and sq is declared as string.
What I am trying to do here is to display complete information of each car owner when the ID is selected. A car owner can own multiple cars and I want to list them all.
Every car has a unique ID that is related to someone in tbl_customers.
For example if user selects a customer's name (from combo box) then I need to have all cars belong to that customer in a list box so that user can select one car from that list box.
I believe I can do the rest after this point otherwise I need to open a new thread.

Last edited by silversun; 09-03-2019 at 06:17 PM.
silversun is offline   Reply With Quote
Old 09-03-2019, 06:31 PM   #5
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,405
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: A query to call in VBA

SELECT queries are not executed, action queries (UPDATE, INSERT, DELETE, etc) are.

Recordset object must be declared and opened. Code to open depends if you want a DAO or ADO recordset. You have not provided complete code for analysis.

If you have a query object with that SQL, why are you using that SQL in VBA?

However, I suspect don't need any recordset. Apply filter criteria to form or report. Review http://allenbrowne.com/ser-62.html

Possibly you should be using a form/subform arrangement.

If you want to provide db for analysis, follow instructions at bottom of my post.
__________________
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 online now   Reply With Quote
Old 09-03-2019, 10:35 PM   #6
silversun
Newly Registered User
 
Join Date: Dec 2012
Posts: 53
Thanks: 25
Thanked 0 Times in 0 Posts
silversun is on a distinguished road
Re: A query to call in VBA

@ June7
I opened your sample form. It looks great, but it created more questions now:
I don't see any table and didn't find where your data are stored in the form.
I don't know how to create a dynamic sub-form that extends as the number of data gets more.
If I was able to do that then I need enable my user to click on a record when that row is highlighted in order to select that record.
For example my sub-form can show all the cars a customer can have and when user clicks on one of them so that specific car appears in a text box and its ID (carOwner_ID) has to be stored somewhere for later usage.
I need to learn about dynamic sub-form creation, Selectable rows in a sub-form, passing the ID and other fields of the selection to somewhere else.
Can you please show me how to do those?
I appreciate your helps.
Thank you
silversun is offline   Reply With Quote
Old 09-03-2019, 11:06 PM   #7
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,405
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: A query to call in VBA

It's not my form or database, thank Allen Browne for the article. Did you download the sample database?

You don't 'extend' a subform, you scroll or use navigation buttons.

Code behind the subform could open another form when record is clicked.

You need to tackle this in steps, one issue at a time. Most of this is fairly basic Access functionality. Have you studied an introductory tutorial book? A forum is not really appropriate venue to instruct in basics, way too much to cover.

__________________
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 online now   Reply With Quote
Old 09-04-2019, 08:54 AM   #8
silversun
Newly Registered User
 
Join Date: Dec 2012
Posts: 53
Thanks: 25
Thanked 0 Times in 0 Posts
silversun is on a distinguished road
Re: A query to call in VBA

I downloaded the sample Database via link: http://allenbrowne.com/ser-62.html. There was only a form in there.
I said extending a form but I should say a dynamic table within a sub-form. Since there are only 5 records in the table at most then I am not sure what happens if we had more data. I can only assume the table expands
As I mentioned I am Access self learner using this forum and Youtube tutorials.
For the first step I need to know how to select a set of data when hovering on a table rows.
silversun is offline   Reply With Quote
Old 09-04-2019, 11:39 AM   #9
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: A query to call in VBA

In the first post, there was reference to a combo bound to Owner_ID. This is the key field in a table called tbl_CarOwners. It seems more appropriate that this should be called tblCars because it is a table with car details, not owner. Then the key field of the table should be changed from owner_ID to CarID.

Is the combo being used to select a car or select an owner?
Cronk is offline   Reply With Quote
Old 09-04-2019, 12:09 PM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,405
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: A query to call in VBA

Actually, there are 2 tables and a query in Allen's sample. He just has navigation pane set to only show form (I don't know why he did that). You can change that. Right click on pane header and select "Show all groups".

Certainly table expands for addition of records, however, subform size remains constant. You SCROLL through records to view those that are outside viewable area. Even when viewing a table or query directly, scrolling is necessary because no monitor can display all records of a large dataset all at once. Same is true for a spreadsheet. This is why mouse has SCROLL wheel - screens can only show so much at a time.
__________________
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; 09-04-2019 at 12:20 PM.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
silversun (09-05-2019)
Old 09-04-2019, 04:27 PM   #11
silversun
Newly Registered User
 
Join Date: Dec 2012
Posts: 53
Thanks: 25
Thanked 0 Times in 0 Posts
silversun is on a distinguished road
Re: A query to call in VBA

@ Cronk
Table customers stores personal info for each customer such as full name, phone number and etc. I created table carsOwners because I can have a customer owning multiple cars. As you can see in this table I am mostly storing ID numbers from four different tables. This helps me to avoid bringing all this fields into another table called tbl_services. I can have only one field for Owner_ID in tbl_services and with it I will have all the information from each car and owner in a single table. I thought this saves me some fields, smaller memory locations and not repeating. If you have a better idea then please present it with enough details.
Thank you
silversun is offline   Reply With Quote
Old 09-05-2019, 10:38 AM   #12
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: A query to call in VBA

Quote:
If you have a better idea then please present it

As I wrote in #9, I was suggesting the table should be named tblCars with the key field CarID.


The car entity has a number of properties, the owner being only one of the properties with the others color, make, licence etc. However, taking your view to the extreme, maybe the table should be called CarOwnerColorLicence...
Cronk is offline   Reply With Quote
Old 09-05-2019, 04:42 PM   #13
silversun
Newly Registered User
 
Join Date: Dec 2012
Posts: 53
Thanks: 25
Thanked 0 Times in 0 Posts
silversun is on a distinguished road
Re: A query to call in VBA

@ Cronk
Thank you for your comment. You made me thinking more about my table structures. I am still defending what I have now because a car always has a unique license plate and it belongs to a single customer at any given time. In my carsOwners table I have a complete list of all owners and cars without repeating. this way I won't need to add an extra field to store owner_ID in cars make and model table.
I am still open to accept advises if you think I am wrong.
silversun is offline   Reply With Quote
Old 09-05-2019, 04:44 PM   #14
silversun
Newly Registered User
 
Join Date: Dec 2012
Posts: 53
Thanks: 25
Thanked 0 Times in 0 Posts
silversun is on a distinguished road
Re: A query to call in VBA

@ theDBguy
I can't because it is still incomplete and too messy.
silversun is offline   Reply With Quote
Old 09-05-2019, 04:51 PM   #15
silversun
Newly Registered User
 
Join Date: Dec 2012
Posts: 53
Thanks: 25
Thanked 0 Times in 0 Posts
silversun is on a distinguished road
Re: A query to call in VBA

@ June7
I was able to see more details about the table and queries in that sample database.Thank you for your helps.
I now need to learn how to make a table row selectable like what it is in the sample database. Should I open a new thread or you can help me here?

silversun 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
Help: Static shell function call works, but dynamic call fails tranchemontaigne Modules & VBA 5 09-04-2013 12:10 PM
How to set call serial number according to call date and time naveen4pundir Queries 2 04-28-2012 04:07 AM
Call Call Audit_Trail Ambigious Error fenhow Modules & VBA 6 07-27-2007 08:26 AM
how to call a public function? call in a query? alienscript Queries 1 09-04-2004 06:49 AM
call (run) a query using VBA ghudson Queries 2 01-08-2003 07:00 PM




All times are GMT -8. The time now is 03:21 PM.


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