A query to call in VBA (1 Viewer)

silversun

Registered User.
Local time
Yesterday, 22:42
Joined
Dec 28, 2012
Messages
204
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:
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:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:42
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you able to post a sample copy of your db?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:42
Joined
Feb 28, 2001
Messages
27,001
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" )
 

silversun

Registered User.
Local time
Yesterday, 22:42
Joined
Dec 28, 2012
Messages
204
@ 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:

June7

AWF VIP
Local time
Yesterday, 21:42
Joined
Mar 9, 2014
Messages
5,424
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.
 

silversun

Registered User.
Local time
Yesterday, 22:42
Joined
Dec 28, 2012
Messages
204
@ 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
 

June7

AWF VIP
Local time
Yesterday, 21:42
Joined
Mar 9, 2014
Messages
5,424
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.
 

silversun

Registered User.
Local time
Yesterday, 22:42
Joined
Dec 28, 2012
Messages
204
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.
 

Cronk

Registered User.
Local time
Today, 16:42
Joined
Jul 4, 2013
Messages
2,770
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?
 

June7

AWF VIP
Local time
Yesterday, 21:42
Joined
Mar 9, 2014
Messages
5,424
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.
 
Last edited:

silversun

Registered User.
Local time
Yesterday, 22:42
Joined
Dec 28, 2012
Messages
204
@ 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
 

Cronk

Registered User.
Local time
Today, 16:42
Joined
Jul 4, 2013
Messages
2,770
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...
 

silversun

Registered User.
Local time
Yesterday, 22:42
Joined
Dec 28, 2012
Messages
204
@ 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

Registered User.
Local time
Yesterday, 22:42
Joined
Dec 28, 2012
Messages
204
@ theDBguy
I can't because it is still incomplete and too messy.
 

silversun

Registered User.
Local time
Yesterday, 22:42
Joined
Dec 28, 2012
Messages
204
@ 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?
 

Cronk

Registered User.
Local time
Today, 16:42
Joined
Jul 4, 2013
Messages
2,770
In my carsOwners table I have a complete list of all owners and cars without repeating.


The complete listing of all owners is in tbl_Customers, no? You have set up your database properly. My point is that your current tbl_CarsOwners has information about the cars, one of which is OwnerID and the owner details are in tbl_Customers.


Anyhow, at the end of the day, it's your database and you can call your tables anything you want.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:42
Joined
Feb 28, 2001
Messages
27,001
I am still defending what I have now because a car always has a unique license plate

Just for clarity...

Does your table only apply to sold vehicles? Because in Louisiana, a car still sitting on the dealership floor doesn't have a license plate - but ALL vehicles have a VIN.
 

silversun

Registered User.
Local time
Yesterday, 22:42
Joined
Dec 28, 2012
Messages
204
@ The_Doc_Man
Yes. They are the customers who bring their car to a shop for oil change or services. After a while when more data has been collected then mechanic person can find a customer's name from a combo box and then will see list of the cars that customer has brought to the shop. He clicks on one of the listed cars then stores the carsOwners_ID in tbl_services when submit the form. This way I will have all I want to know about a service, car and its owner.
 

Users who are viewing this thread

Top Bottom