UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Subform View Multiple Queries, Access 2013    
 
   
Alhakeem1977
post Jan 18 2020, 10:45 PM
Post#1



Posts: 124
Joined: 8-July 17



Hi All,
I have got the main form called: [NavigationFrom] has one subform called: [qrySub subform] for search and retrieval purposes the subform should populate many queries depends on buttons available in the main form.
I would like to press a button called: [cmdSearchCH] then the subform will show me the "Cheques" the results of the SQL query below:
CODE
SELECT tblDocType.[Document Type], tblTransactions.Ref, tblTransactions.SubRef, tblTransactions.DDate, tblTransactions.CreatedDate, [tblUser].[FirstName] & " " & [tblUser].[LastName] AS [Sent by], tblTransactions.ReceivedDate, [tblUser_1].[FirstName] & " " & [tblUser_1].[LastName] AS [Received by]
FROM tblDocType RIGHT JOIN (tblUser AS tblUser_1 RIGHT JOIN (tblUser RIGHT JOIN tblTransactions ON tblUser.UserID = tblTransactions.CreatedID) ON tblUser_1.UserID = tblTransactions.ReceiverID) ON tblDocType.DocID = tblTransactions.DocID
WHERE (((tblDocType.[Document Type])="Cheques"));


Then if I want to show the "Nostro Payment" results I press a button called: [cmdSearchNPayment] with the below query:

CODE
SELECT tblDocType.[Document Type], tblTransactions.Ref, tblTransactions.SubRef, tblTransactions.DDate, tblTransactions.CreatedDate, [tblUser].[FirstName] & " " & [tblUser].[LastName] AS [Sent by], tblTransactions.ReceivedDate, [tblUser_1].[FirstName] & " " & [tblUser_1].[LastName] AS [Received by]
FROM tblDocType RIGHT JOIN (tblUser AS tblUser_1 RIGHT JOIN (tblUser RIGHT JOIN tblTransactions ON tblUser.UserID = tblTransactions.CreatedID) ON tblUser_1.UserID = tblTransactions.ReceiverID) ON tblDocType.DocID = tblTransactions.DocID
WHERE (((tblDocType.[Document Type])="Nostro Payment"));

I have created the below VBA SQL in on Click event but I didn't get any results in the subform:
CODE
Private Sub cmdSearchCH_Click()
On Error Resume Next
Dim strSQL As String
  strSQL = "SELECT tblDocType.[Document Type], tblTransactions.Ref, tblTransactions.SubRef, tblTransactions.DDate, tblTransactions.CreatedDate, [tblUser].[FirstName]" & " " & "[tblUser].[LastName] AS [Sent by], tblTransactions.ReceivedDate, [tblUser_1].[FirstName]" & " " & "[tblUser_1].[LastName] AS [Received by] " & _
    "FROM tblDocType RIGHT JOIN tblUser AS tblUser_1 RIGHT JOIN tblUser RIGHT JOIN tblTransactions ON tblUser.UserID = tblTransactions.CreatedID ON tblUser_1.UserID = tblTransactions.ReceiverID ON tblDocType.DocID = tblTransactions.DocID " & _
    "WHERE tblDocType.[Document Type]='Cheques'" & _
    "ORDER BY tblTransactions.DDate"
  Me.[qrySub SubForm].Form.RecordSource = strSQL
  Me.[qrySub SubForm].Requery
End Sub


Any help would be highly appreciated.

Thanks in advance!

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Jan 18 2020, 11:06 PM
Post#2


UA Admin
Posts: 37,239
Joined: 20-June 02
From: Newcastle, WA


Sometimes the best way to validate your dynamic VBA is to insert a Debug.Print strSQL statement to send the results to the Immediate Window. From there, you can copy the SQL string into a new query and actually see what it's doing. Often that reveals a logic or syntax error that's not apparent otherwise.

Try that.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
tina t
post Jan 18 2020, 11:17 PM
Post#3



Posts: 6,589
Joined: 11-November 10
From: SoCal, USA


well, first thing to check is the first two SQL statements you posted. if you paste each statement into a new query's SQL view, and then change to Datasheet view, does the SQL statement return the records you expect?

if yes to above on both SQL statements, then next thing to check is the SQL statement created by the VBA code. to do this, add the following line immediately after the last line of the SQL string:

Debug.Print strSQL

then click the button on the form to run the code. then press Ctl+G to see the Immediate Window and look at the SQL statement generated by the VBA code. if you see a mistake, fix it and test again. if it looks okay, copy it and then open a new query and paste the SQL statement into the SQL view and then change to Datasheet view. does the query return the records you expect?

i can tell you right now that in the following two lines of VBA code

"WHERE tblDocType.[Document Type]='Cheques'" & _
"ORDER BY tblTransactions.DDate"

you're missing a space. the code should read as

"WHERE tblDocType.[Document Type]='Cheques' " & _
"ORDER BY tblTransactions.DDate"

but you should run the above tests anyway, to see if there are any other issues with the SQL statements and/or SQL string. once the SQL is all good, if you're still not getting the expected results in the subform, the next thing to check is the VBA reference to the subform, as

Me.[qrySub SubForm].Form.RecordSource = strSQL
Me.[qrySub SubForm].Requery

make sure that you're referring to the name of the subform control within the mainform, not the name of the form being used as the subform. the two names may be the same, or they may be different. if you're not sure if you're using the correct name, do the following:

open the mainform in Design view.
click once on the subform to select it.
look at the Name property in the Property sheet.

that's the name of the subform control, the control that "holds" and displays the subform within the mainform. if you're using the correct name in your reference, and still not getting the result you expect, then you might need to upload a copy of your db for review - unless somebody else suggests something i missed (entirely possible). if you post your db, make sure you remove any sensitive data, compact, and zip to under 2 MB.

hth
tina

edit: beat me by a mile, George! :)
This post has been edited by tina t: Jan 18 2020, 11:21 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Alhakeem1977
post Jan 19 2020, 01:45 PM
Post#4



Posts: 124
Joined: 8-July 17



Thank you so much for your earliest response.

I created the query in the design view and it's getting me the right result as I expected with no issues, then when I copy the SQL statement and past it as a VBA SQL statement got a syntax error in the concatenated fields [tblUser].[FirstName] & " " & [tblUser].[LastName] AS [Sent by] and I can't figure it out.

I got in the Immediate window below syntax:
CODE
SELECT tblDocType.[Document Type], tblTransactions.Ref, tblTransactions.SubRef, tblTransactions.DDate, tblTransactions.CreatedDate, [tblUser].[FirstName] [tblUser].[LastName] AS [Sent by], tblTransactions.ReceivedDate, [tblUser_1].[FirstName] [tblUser_1].[LastName] AS [Received by] FROM tblDocType RIGHT JOIN tblUser AS tblUser_1 RIGHT JOIN tblUser RIGHT JOIN tblTransactions ON tblUser.UserID = tblTransactions.CreatedID ON tblUser_1.UserID = tblTransactions.ReceiverID ON tblDocType.DocID = tblTransactions.DocID WHERE tblDocType.[Document Type]='Cheques' ORDER BY tblTransactions.DDate


I have attached my db.
Thanks a lot.
This post has been edited by Alhakeem1977: Jan 19 2020, 01:54 PM
Attached File(s)
Attached File  Sample_DM.zip ( 283.14K )Number of downloads: 3
 

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Jan 19 2020, 01:47 PM
Post#5


UA Admin
Posts: 37,239
Joined: 20-June 02
From: Newcastle, WA


[tblUser].[FirstName] [tblUser].[LastName] AS [Sent by]

is not the same as

[tblUser].[FirstName] & " " & [tblUser].[LastName] AS [Sent by]

Can you spot the missing part?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Alhakeem1977
post Jan 19 2020, 02:38 PM
Post#6



Posts: 124
Joined: 8-July 17



I have tried both ways with and without I do not know where is the missing syntax.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
tina t
post Jan 19 2020, 03:37 PM
Post#7



Posts: 6,589
Joined: 11-November 10
From: SoCal, USA


QUOTE
when I copy the SQL statement and past it as a VBA SQL statement got a syntax error in the concatenated fields [tblUser].[FirstName] & " " & [tblUser].[LastName] AS [Sent by]

in the VBA code, try the following, as

strSQL = "SELECT tblDocType.[Document Type], tblTransactions.Ref, tblTransactions.SubRef, tblTransactions.DDate, tblTransactions.CreatedDate, [tblUser].[FirstName] & ' ' & [tblUser].[LastName] AS [Sent by]..."

notice that i changed the double quotes to single quotes and kept the ampersands, all inside the double quotes that delimit the string. you want to keep the concatenation inside the SQL statement, so the query will execute with the concatenated-values field - just as it would if the SQL statement were in a stored query.

hth
tina
This post has been edited by tina t: Jan 19 2020, 03:37 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Alhakeem1977
post Jan 20 2020, 10:39 AM
Post#8



Posts: 124
Joined: 8-July 17



Dear tina t,

I have changed the code to a single quote but it's still the same.

could you do me a favor to upload my DB attached and try your suggestions?

thank you so much!

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 11:18 AM
Post#9


UA Admin
Posts: 37,239
Joined: 20-June 02
From: Newcastle, WA


I don't see that particular VBA in the sample db you uploaded.

I can find the control called "cmdSearchCH_Click" on the form called NavCredit. I can't find that control on any other form. The VBA in that sub is different from what you posted.

Can you clarify?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Alhakeem1977
post Jan 20 2020, 11:44 AM
Post#10



Posts: 124
Joined: 8-July 17



Thanks for your prompt response.

The VBA code in the on click event of [cmdSearchCH] button.

Maybe I am wrong to do it this way, actually, I am trying to get a filtered data from tblTransacttions depends on DocID I will create a tempvar in the button pass the DocID after I solve the query issue.

I am so sorry to engage you with me.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 11:46 AM
Post#11


UA Admin
Posts: 37,239
Joined: 20-June 02
From: Newcastle, WA


The code in the event in the sample db you uploaded differs from the code you posted in this thread.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Alhakeem1977
post Jan 20 2020, 12:24 PM
Post#12



Posts: 124
Joined: 8-July 17



I am sorry, kindly go the query called: [qryTransactionsSearch] and get the SQL statement from it and past it to the control or I will post another db soon.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 01:33 PM
Post#13


UA Admin
Posts: 37,239
Joined: 20-June 02
From: Newcastle, WA


There's no source object in the subform control called "qrySub subform" on the form called NavCredit. Your code tries to set the recordsource for it, but there's no subform there. Is there supposed to be one? If so, which one.

Thanks.
This post has been edited by GroverParkGeorge: Jan 20 2020, 01:34 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Alhakeem1977
post Jan 20 2020, 03:21 PM
Post#14



Posts: 124
Joined: 8-July 17



You are wright unfortunately there is no form, I thought it's enough to write the SQL statement then I will get the returns data.

So the form how it should be? Bound form or unborn form?

Could you do the needful?

Thanks a lot.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
tina t
post Jan 20 2020, 03:49 PM
Post#15



Posts: 6,589
Joined: 11-November 10
From: SoCal, USA


AFAIK, you can use a query as the SourceObject of the subform control, but it must be a stored query object - not just a SQL statement created in VBA at runtime.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Alhakeem1977
post Jan 20 2020, 10:06 PM
Post#16



Posts: 124
Joined: 8-July 17



Thank you so much tina t, I got it solved, you've target me to the main issue. thanks.gif

Thanks again, I confirmed that you've solved my problem. thumbup.gif

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
tina t
post Jan 20 2020, 10:31 PM
Post#17



Posts: 6,589
Joined: 11-November 10
From: SoCal, USA


you're welcome, Al Hakeem, George and i are glad to help. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Alhakeem1977
post Jan 21 2020, 11:13 AM
Post#18



Posts: 124
Joined: 8-July 17



Thanks George for your help too. thanks.gif

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th May 2020 - 05:05 AM